CDC is one of the new data tracking and capturing features of SQL Server 2008. It only tracks changes in user-created tables. Because captured data is then stored in relational tables, it can be easily accessed and retrieved subsequently, using regular T-SQL.
- Works only in Enterprise, Developer and Evaluation Edition
- SQL Agent Service should be up & running
- There should not be any schema with a name "cdc"
- There should not be any column in a table called __$start_lsn, __$end_lsn, __$seqval, __$operation, and __$update_mask
- all cdc changes should be tracked in a separate filegroup rather than using Primary
- configure only required tables and columns
ENABLE CDC ON DATABASE
SELECT [name], database_id, is_cdc_enabled
This will create some columns under "cdc" schema which will be introduced.
ENABLE CDC ON TABLE
@source_schema = 'dbo', -- Schema Name
@source_name = 'CDCCheck' , -- Table Name
@role_name = NULL,
@captured_column_list = 'a,b,c', -- Comma separate column list, if not given all columns will be considered
@filegroup_name = 'CDCFileGroup'
A new table will be created under CDC schema called "CDC.dbo_CDCCheck_CT". Apart from columns specified above, it will have 5 extra columns
__$Start_lsn - Log Sequence Number
__$end_lsn - Log Sequence Number
- 1 - Insert
- 2 - Delete
- 3 - Before Update
- 4 - After update
- if the value is 0x12 means 0b10010 in binary means 2nd and 5th column has been updated
- if the value is 0x1F means 0b11111 in binary means all 5 columns have been updated
DISABLE CDC ON TABLE
@source_schema = N'HumanResources',
@source_name = N'Shift'
ADVANTAGES OF CDC
- CDC talks with transaction log, pulls data from there and put them in cdc table. So it gives best performance in comparison with trigger approach
- Low development cost, easy maintenance, clean approach of tracking audit.
LIMITATION/PROBLEM WITH CDC
TRUNCATE won't be allowed if CDC is enabled on table
If a new column gets added or existing column gets removed, it does not reflect CDC table. Alternate solution is, cdc should be disabled and enabled.
NVARCHAR(MAX), VARBINARY(MAX) columns will be part of each update operation even though their value is untouched. Ex. In a table, there are 4 columns. Out of this one column in NVARCHAR(MAX). Assume that 1 int column value has been changed but NVARCHAR(MAX) is untouched, still in CDC table, it will be tracked as the previous value was NULL and new value is existing value.