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
Enable CDC on the database
Enable a Table for CDC. For this I am choosing a small table.
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.
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.
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 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 Delete Operation
When you delete data from the main table the CDC will capture one corresponding record for each delete.
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.
Disabling the CDC
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.
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.
/* 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.
No comments:
Post a Comment