Contents
What does change data capture do?
Change data capture (CDC) is a process that captures changes made in a database, and ensures that those changes are replicated to a destination such as a data warehouse.
What is Change Data Capture in SQL?
SQL Server CDC (change data capture) is a technology built into SQL Server that records insert, update, and delete operations applied to a user table and then stores this changed data in a form consumable by an ETL application such as SQL Server Integration Services (SSIS).
How to enable change data capture in SQL Server?
When the database is enabled, source tables can be identified as tracked tables by using the stored procedure sys.sp_cdc_enable_table. When a table is enabled for change data capture, an associated capture instance is created to support the dissemination of the change data in the source table.
How does DML and DDL changes impact change data capture in SQL?
Solution. The SQL Server CDC capture process is responsible for populating change tables when DML changes are applied to source tables, but it ignores any DDL changes such as adding or dropping columns. It only captures data changes based on the format of the table at the time the table was enabled for CDC.
What are the first five columns of a change data capture table?
The first five columns of a change data capture change table are metadata columns. These provide additional information that is relevant to the recorded change. The remaining columns mirror the identified captured columns from the source table in name and, typically, in type.
How does the change data capture process work?
The change data capture cleanup process is responsible for enforcing the retention-based cleanup policy. First, it moves the low endpoint of the validity interval to satisfy the time restriction. Then, it removes expired change table entries. By default, three days of data is retained.