Know-how can be used to capture database changes using the CDC function

Back to Catalog

If we want to monitor the change of a data table, in the previous version of sql2008, it is possible to think of only triggers on the database side, or to implement the corresponding functions by monitoring their own insert,update,delete at the terminal, which is undoubtedly frightening to us, Not flexible enough, and when the sql2008, this situation has been fundamentally changed, SQL2008 provides us with the CDC function, it can be in real-time to monitor the specified data table, and currently it also generates some SQL load.

CDC Work Flow

The CDC feature captures the deletion and modification of SQL Server-specified tables, because any operation writes the log (even truncate), so the CDC capture originates from the log file. The log file applies the changes to the data file, and it also marks the items that meet the required data mark as needing to add the trace. Then through some supporting functions, and finally written to the Data warehouse.

CDC implementation steps

The first step, the target library To explicitly enable CDC:

Use sys.sp_cdc_enable_db in the current library. Returns 0 (Success) or 1 (failed). Note that this feature cannot be enabled on the system database and the distribution database. And the performer needs to use sysadmin role permissions.

The scope of the stored procedure is the entire target library. Contains metadata, DDL triggers, CDC schemas, and CDC users.

Use the following code to enable:

 Use TableName Go EXECUTE sys.sp_cdc_enable_db GO

After setting the CDC function of the database, we can see if it has been set successfully

SELECTis_cdc_enabled, Case  whenIs_cdc_enabled= 0  Then 'CDC feature disabled'             ELSE 'CDC feature enabled'        ENDDescription fromSYS. DATABASESWHERENAME= 'TableName' 

At the same time, users of the database will have one more CDC user

Second step, enable CDC on the target table

Use the members of the db_owner role to perform sys.sp_cdc_enable_table to create a capture instance for each table that needs to be traced. You can then determine whether the creation was successful by sys.tables the IS_TRACKED_BY_CDC column in the catalog view.

By default, all columns of the table are captured. If you only need to capture certain columns, you can use the @captured_column_list parameter to specify the columns.

If you want to put the change table in a filegroup, it is best to create a separate filegroup (at least independent of the source table).

EXEC @source_schema = ' DBO ' ,     @source_name = ' webmanageusers ' @role_name = NULL

Note thatSource_schema is talking about database architects, such as DBO,CDC, etc.

@source_name say the name of the data table,@role_name is the role name, and NULL indicates no special restrictions on the role

After setting the CDC function for the data table, let's see if it has been configured successfully, using the following code

SELECTNAME, IS_TRACKED_BY_CDC, Case  whenIs_tracked_by_cdc= 0  Then 'CDC feature disabled'             ELSE 'CDC feature enabled'        ENDDescription fromSYS. TABLESWHERE   object_id inch(object_id('DBO. Webmanageusers') )

When we modify the data in the webmanageusers table, we can find the corresponding results in the DBO_WEBMANAGEUSERS_CT table, as can be seen from the table name, the CDC table naming rules is the schema name _ table name _ct, hehe.

SELECT *  from CDC. Dbo_webmanageusers_ct

As we can see from the results, the CDC has been capturing the update operation two times before and after the modification, very clear! Well, for the CDC function of the database here today, I hope you have some help!Back to Catalog

Know-how can be used to capture database changes using the CDC function