Change data capture
Change data capture (CDC) is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
CDC minimizes the resources required for ETL ( extract, transform, load ) processes because it only deals with data changes.
Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.
How to check if you have enabled Change data capture on your database?
Enabling Change data capture on database level
- cdc.captured_columns – This table returns result for list of captured column.
- cdc.change_tables – This table returns list of all the tables which are enabled for capture.
- cdc.ddl_history – This table contains history of all the DDL changes since capture data enabled.
- cdc.index_columns – This table contains indexes associated with change table.
- cdc.lsn_time_mapping – This table maps LSN number (for which we will learn later) and time.
Enabling Change Data Capture on Database Tables
Column _$operation contains value which corresponds to DML Operations.
Insert Statement = 2
Value before Update Statement = 3
Value after Update Statement = 4
The column _$update_mask shows, via a bitmap, which columns were updated in the DML operation that was specified by _$operation. If this was a DELETE or INSERT operation, all columns are updated and so the mask contains value which has all 1’s in it. This mask is contains value which is formed with Bit values.