Change data capture is a facility to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in a relational format. The change tables used by change data capture contain columns that is same as the column structure of a tracked table. Also it will store the information about what changes occurred to the data.
Change data capture is now available only on the Enterprise, Developer, and Evaluation editions
How it works ?
The change data capture procedure gets data from SQL Server transaction log. When an insert, update, or delete happened to tracked tables, those changes with description will be added to the log. The log serves as input to the change data capture process. It extract the log information and adds metadata about the changes. There are inbuilt functions to get the changes that appear in the change tables over a specified range, it will provide report with filter for change data. This filtered data can be used to feed any decision making application or datawarehouse systems.
This is how change capture is enabled for a database
— to enable CDC
if not exists(select name, is_cdc_enabled
from master.sys.databases where name = ‘CDCTrackedDB’
and is_cdc_enabled = 1)
After that we may need to enable CDC for individual tables
— enable CDC for dbo.Employee table
@source_schema = ‘dbo’ , @source_name = ‘Employee’, @role_name = ‘cdcManager’;