The feature is introduced in SQL Server 2008 to track Insert, Update & Deletes made on Tables. Prior to this version to track any information we need to write an additional programming using triggers (After Insert, After Delete etc,) to track the information, using this version we need not put any development effort to achieve this. It tracks only user created tables. If CDC is enabled then it starting reading transaction log for tracking.
To Start with this
1. Needs to enable CDC on the database
2. Need to specify each table name to be captured (for each captured table a mirror table will be created with same structure to store the modified values) We can call them as audit tables
Example
USE master
SELECT [name], database_id, is_cdc_enabled FROM sys.databases
Note - If cdc_enabled parameter returns 1 then it is enabled.
--- -To enable CDC ----
Use Kalyandb
Exec sys.sp_cdc_enable_db
SELECT [name], database_id, is_cdc_enabled FROM sys.databases
The sp_cdc_enable_db stored procedure creates few system tables and creates a schema with name “cdc” in the current database. These will be maintained by system to track the changes.
----Below are system tables----
cdc.captured_columns – Holds the list of captured columns information.
cdc.change_tables – Holds the list of tables which are enabled for capture.
cdc.ddl_history – Holds the history of all DDL changes.
cdc.index_columns – Holds indexes which are related to change table.
cdc.lsn_time_mapping – It is used to map the LSN Number
USE Kalyandb
SELECT [name], is_tracked_by_cdc FROM sys.tables
sp_cdc_enable_table --This stored procedure helps to enable the table to track the DML Operations.
USE Kalyandb
GO
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo',
@source_name = N'items', @role_name = NULL
Note – Once you invoke the above command then it creates two jobs in the Agent with names dbname_capture, dbname_cleanup and creates a table dbo_items_CT in system tables.
Job 'cdc.kalyandb_capture' started successfully.
Job 'cdc.kalyandb_cleanup' started successfully.
After enabling CDC on a table then from that moment onwards it starts capturing the information into tracking tables. For the above example it creates a table in the system tables section inside the database with table name like this cdc.dbo_items_CT.
The mirror table has 5 additional columns along with original columns which helps to track the changed data. Below is the list.
__$start_lsn
__$end_lsn
__$seqval
__$operation
__$update_mask
Among the above five columns we need to concentrate on two columns __$operation, __$update_mask. The operation column contains the DML Operation such as Insert, Update and delete. The column value indicated with the below number value.
Delete Statement = 1
Insert Statement = 2
Value before Update Statement = 3
Value after Update Statement = 4
Below command is to view on which tables CDC is enabled
USE kalyandb
EXEC sys.sp_cdc_help_change_data_capture
To disable CDC
For enabling CDC first we enabled at database level and then table level, for disable also we need to follow the same procedure.
USE kalyandb
GO
EXECUTE sys.sp_cdc_disable_table @source_schema = 'dbo',
@source_name = 'Items', @capture_instance = 'dbo_items'
Use kalyandb
Go
Execute sys.sp_cdc_disable_db
There is an auto cleanup process that occurs every three days (which can be configured)
Sys.sp_cdc_cleanup_change_table
No comments: