Configuring Change Data Capture (CDC)

Change Data Capture (CDC) is a new feature that is introduced in SQL Server 2008. CDC provides the ability of database level auditing.
Using CDC we can capture the DML operations like Insert, Update and Delete statements.
Enabling CDC is done at database level first and then on the required tables. Once CDC is enabled, a similar table is created in the database with the schema “cdc” and this table will store the Tracked data.

Enabling Change Data Capture

Check if the database is enabled for CDC

/* Check CDC Status of the Database */ SELECT name AS DatabaseName        ,database_id AS DatabaseID        ,CDCStatus= CASE WHEN is_cdc_enabled=1 THEN ‘Enabled’                            ELSE ‘Disabled’                            END FROM sys.databases WHERE name=‘CDC_AdventureWorks’

Enable CDC on the database

/* Enable CDC */ USE CDC_AdventureWorks GO Exec sys.sp_cdc_enable_db GO

Once the CDC is enabled successfully, then you can see two new jobs (“cdc.<DatabaseName>_capture” and “cdc.<DatabaseName>_cleanup”) and one new schema named ‘cdc’.

Enable a Table for CDC. For this I am choosing a small table.

/* Enable CDC on Table */ USE CDC_AdventureWorks GO EXEC sys.sp_cdc_enable_table @source_schema = N’HumanResources’, @source_name   = N’Shift’, @role_name     = NULL GO

Once the CDC is enabled for a table the CDC jobs will be started



Also you can notice that the capture job runs continuously to capture the changes.

You can now verify that a new change tracking table will created.

/* Verify CDC tables */ SELECT SCHEMA_NAME(schema_id) AS SchemaName        ,name AS TableName FROM sys.objects WHERE name like ‘%Shift%’ and type=‘U’

The table “HumanResources_Shift_CT” with the schema name “cdc” from now will hold the tracked data.
Also you can notice that the newly created table will have 5 additional Columns to help capturing the changed data
These Columns are:
__$start_lsn
__$end_lsn
__$seqval
__$operation
__$update_mask



Testing Change Data Capture
As we know that the CDC captures inserts, deletes and updates we will start testing all the possibilities.

Testing insert Operation
When you insert data to the main table the CDC will capture one corresponding record for each insert.

/* Testing Insert */ INSERT INTO HumanResources.Shift VALUES (4,’LateEvening’,’1900-01-01 18:00:00.000’,’1900-01-01 02:00:00.000’,GETDATE()) SELECT * FROM  HumanResources.Shift SELECT * FROM cdc.HumanResources_Shift_CT

Testing Update Operation
When you update data in the main table, the CDC will capture two corresponding records for each update. One record will have values Before Update and the other will have values after update.

/* Testing Update */ UPDATE HumanResources.Shift SET ShiftID=5 WHERE ShiftID=4 SELECT * FROM  HumanResources.Shift SELECT * FROM cdc.HumanResources_Shift_CT

Testing Delete Operation
When you delete data from the main table the CDC will capture one corresponding record for each delete.

/* Testing Delete */ DELETE FROM HumanResources.Shift WHERE ShiftID=5 SELECT * FROM  HumanResources.Shift SELECT * FROM cdc.HumanResources_Shift_CT

Auditing the Tracked Data
Now after all the changed data is captured, it’s time to audit the Tracked Data.
Below query will get you all the changes done to the HumanResource.Shift Table in the last 24 Hours.

/* Get all changes to HumanResources.Shift table in last 24 Hrs*/ DECLARE @begin_lsn BINARY(10), @end_lsn BINARY(10); SELECT @end_lsn= MAX(start_lsn) FROM cdc.lsn_time_mapping where tran_begin_time<= GETDATE() and tran_end_time >= GETDATE()-1 SELECT @begin_lsn= MIN(start_lsn) FROM cdc.lsn_time_mapping where tran_begin_time<= GETDATE() and tran_end_time >= GETDATE()-1 SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Shift(@begin_lsn,@end_lsn,’all’) GO

Disabling the CDC

/* Get the list of tables involved in CDC */ USE CDC_AdventureWorks; GO EXEC sys.sp_cdc_help_change_data_capture GO

/* Disable CDC on Tables */ USE CDC_AdventureWorks; GO EXECUTE sys.sp_cdc_disable_table     @source_schema = N’HumanResources’,     @source_name = N’Shift’,     @capture_instance = N’HumanResources_Shift’; GO

After Disabling the CDC on the table, you can notice that the table “cdc.HumanResources_Shift_CT “ which was created for tracking changes will no more exist.

/* Disable CDC on Database */ USE CDC_AdventureWorks; GO EXEC sys.sp_cdc_disable_db GO

Once the CDC is disabled on the database, then you will notice that the schema by name “cdc” and the other CDC related tables, SP’s, functions and Jobs will no more exist.