An introduction to CDC can be found here.
When CDC (Change Data Capture) is enabled on a database, a SQL Agent job is automatically created called cdc.XXX_capture. The capture job reads asynchronously from the SQL Server transaction log to retrieve all modifications (DML statements) made to all CDC enabled tables. The changes are then written to a change table.
In this blog post I’ll describe the CDC capture job and how it can be configured.
An introduction to CDC can be found here.
All data modifications in a CDC enabled table are saved in a separate change table. But in order to limit the size of this change table, a SQL Agent job is automatically created, named cdc.XXX_cleanup, that deletes rows older than the configurable “retention value”. The job is created once per database at the moment when CDC is enabled.
Change Data Capture (CDC) was first introduced in SQL Server 2008, and is a tool that is used to keep track of data changes (DML statements) in tables. CDC must be enabled on the database level, then on the table level, and lastly on the column level (optional).
When CDC is enabled on a table, a new similar table is created, but under the cdc schema. In the new table all changes to to base table are recorded.
Traditionally, triggers have been created to track changes in tables. But CDC is a more light weight technology since the transaction log is read asynchronously for changes. Triggers are part of the original transaction.
CDC is only supported in the Enterprise or Developer editions of SQL Server.
Read on to see a demonstration of CDC basics.