Track records of every business operation data change the sharp weapon--SQLCDC

For most enterprises to use, there is a basic function necessary, that is Audit Trail or Audit log, Chinese translation for tracking inspection, audit inspection or audit records. We use audit trail to record the basic information for each business operation, such as the basic description of the operation, the operating time, the operator, and so on. For some applications with high security levels, or to manipulate some more sensitive data, we even need to record the changes in the data caused by the business operation. Specifically, the "data change" here refers to the changes in the record of each effect before and after the operation is performed. For added records, the newly inserted records need to be recorded, the original records need to be recorded for the deleted records, and the records before and after the update must be recorded for the updated records.

When it comes to this, many people will think of triggers to capture data changes. However, this implementation has one of the biggest limitations: Because triggers are executed within the scope of the transaction in which the data is operating, all the performance problems , and the fact that the triggers are executing cause the transaction supermarket. So here we introduce a better solution:SQLCDC.

Directory
I. Introduction of SQLCDC
Second, open CDC at the database level
Third, open CDC for a data table
Iv. record data changes for added records
V. Record data changes for deleted data
Vi. data changes recorded in the record update

I. Introduction of SQLCDC

The CDC's full name is change data capture, which, as its name implies, is used to track and capture the changes. The CDC is a new feature that appears in SQL Server 2008, and this feature was earlier in Oracle. For previous versions of SQL Server, in the absence of CDC, if you need to record data changes based on a data table, we can only use triggers , specifically by manually creating after inserts, after update, and after Delete trigger to record the changed data. The CDC gives us a more convenient, easy-to-use, and hassle-friendly way to record the history of a data table.

Second, open CDC at the database level

By default, the CDC attribute of the database is closed, and you can determine whether the CDC of a database is turned on by using the is_cdc_enabled field of the system table sys.databases . If, by default, I execute the following SQL statement to see if the CDC of the database TestDB is turned on, you will see that the field has a value of 0.

You can turn on the CDC feature for the current database by executing the system stored procedure sys.sp_cdc_enable_db. In the following T-SQL code snippet, we open the CDC attribute for TestDB by executing the stored procedure.

Use TestDb

Go

Exec sys.sp_cdc_enable_db

Go

Third, open CDC for a data table

Because the CDC is used to record data changes based on a data table, you also need to explicitly turn on the CDC attribute for a data table when the current database CDC is turned on. As a demonstration, we created a simple users table under TestDB with the following T-SQL, which has only three fields: Id, name, and birthday.

CREATE TABLE [dbo]. [Users] (

    [ID] [varchar] (PRIMARY KEY),

    [Name] [nvarchar] () not NULL,

    [Birthday] [Date] Not NULL)

The CDC feature of the data table is opened by executing the sys.sp_cdc_enable_table stored procedure implementation. The simplest way to invoke the stored procedure is to specify the schema, name, and permissions (roles) of the data table that must be used to extract the changed data. I opened the CDC attribute of the users table we created by executing the following T-SQL, where the @role_name parameter was set to NULL, indicating that I did not authorize the read ALTER data operation. Sys.sp_cdc_enable_table has many parameters, and the CDC behavior affected by the corresponding parameters can be referenced in the SQL Server 2008 online documentation.

Use TestDb

Go

Exec sys.sp_cdc_enable_table ' dbo ', ' Users ', @role_name = NULL

Go

It is important to note that the CDC is actually built on top of the SQL Server Agent , so you need to start the SQL Server Agent before executing the above-mentioned T-SQL. When the CDC attribute of a data table is turned on, the system creates a tracking table (Tracking table) for saving data changes. The schema for this table is CDC, which is named after the table name of the tracked table with a "CT" suffix. After executing the above section of T-SQL, one of the following system tables is created, and we find that the three fields of the users table are also in the table. In addition The table also has 5 additional fields: __$start_lsn, __$end_lsn, __$seqval, __$operation, and __$update_mask, which represent the Log series number (log Sequence numbers), OPERATIONS (delete, INSERT, Pre-and post-modification) information.

Iv. Record data changes for added records

Now we can experiment with the CDC's capture of data changes to a data table, and let's try a record-adding operation first. To do this, we execute the following section of T-SQL, inserting two user records.

Insert into Users (Id, Name, Birthday)

Values (' 001 ', ' Foo ', ' 1981-08-24 ')

Insert into Users (Id, Name, Birthday)

Values (' 002 ', ' Bar ', ' 1981-08-24 ')

Then look at the data in the CDC.DBO_USERS_CT table with the following T-SQL to save the data changes involved in the add operation. From the query results we clearly see that the two records added above have been recorded, while the __$operation field 2 represents the " insert " operation.

v. Record data changes for updated data

Next we follow the CDC's tracking record of the update operation, so we changed the birthday of user Foo with the following T-SQL.

Where Name = ' Foo '

Perform the full table query for CDC.DBO_USERS_CT again, and you'll see two more records this time. The 3rd one records the modified data, and the fourth is the modified data, and their __$operation character Gede values are 3 and 4, respectively.

It is worth mentioning here that the value of the __$update_mask field, which represents the changed field of the record update operation. This is a number in the 16 notation, which needs to be converted to 2 when the modified field is judged. The above update operation corresponds to the __$update_mask value of 0x04, converted to 2 binary is three, which represents 3 fields respectively. But here the order is from right to left , so 100 of these three bits represent the fields birthday, name, and ID. 1 means change, and 0 means it stays the same. Because in the above T-SQL, we have only changed the birthday, this and 100 this value is consistent.

Vi. record deletion of recorded data changes

We later demonstrated what data the CDC will record for us when we implement a delete operation on a record. Now we do the following T-SQL to delete all the records in the Users table.

Delete from Users

View Cdc.dbo_users_ct's records, two more records of the user record that we deleted formally, the value of the __$operation field is 1 for the delete operation.

This article simply introduces the basic principles of SQLCDC and how to use them in general, and this article "Introduction to change Data Capture (CDC) in SQL Server 2008[]" will give you a more detailed introduction. If you want to delve deeper into SQLCDC, refer to the SQL Server 2008 online documentation.

Track records of every business operation data change the sharp weapon--SQLCDC