Data Capture (Change Data Capture)

A SQL Server feature that tracks insert, update, and delete activity on database tables, making a historical record of changes available in a structured, queryable format.

• Change Data Capture (CDC) captures the before and after values of modified rows: This enables downstream consumers, such as ETL processes, audit systems, and replication, to process only changed data.
• CDC uses the SQL Server transaction log as its source: Changes are captured asynchronously without impacting DML performance on source tables.
• DBAs enable CDC at the database and table level using system stored procedures: sys.sp_cdc_enable_db and sys.sp_cdc_enable_table.
• Change tables are created in the database to store captured changes: DBAs must manage retention and cleanup to prevent unbounded growth.
• Common use cases include: Incremental data loads to data warehouses, real-time replication, compliance audit trails, and event-driven integrations.
• CDC should not be confused with Change Tracking: CDC captures the actual data values changed, whereas Change Tracking only records which rows changed.
• Relevant Idera tools: SQL Compliance Manager can audit data access and modification activity for compliance reporting alongside CDC.
• Related terms: Change Tracking, Transactional Replication, ETL, Transaction Log, Temporal Tables.