SQL Server Change Data Capture (CDC): How It Works, Limitations & Best Practices

Native SQL Server CDC operates through a series of internal SQL Server Agent jobs. Here is the architectural flow:

1. Transaction Log Execution: When a user or application makes a change to a CDC-enabled table, the change is written to the SQL Server transaction log.

2. The Capture Job: A dedicated SQL Server Agent job (the "capture" process) continuously reads the transaction log to identify changes made to tracked tables.

3. Change Tables: The capture job writes the details of these modifications (including the before and after values) into dedicated CDC system tables (cdc.fn_cdc_get_all_changes_...).

4. Data Extraction: Downstream ETL tools or custom scripts query these system tables to extract the net changes and move them to a target destination.

5. The Cleanup Job: To prevent the database from bloating with historical change data, a secondary SQL Server Agent cleanup job runs periodically (typically every 3 days by default) to purge old records from the change tables.

Enabling native CDC requires sysadmin privileges and is a two-step process:

• Step 1: Enable at the Database Level. You must first enable CDC for the entire database by running the sys.sp_cdc_enable_db stored procedure.

• Step 2: Enable at the Table Level. Once the database is enabled, you specify which individual tables to track by running the sys.sp_cdc_enable_table stored procedure for each target table.

While they sound similar, Change Data Capture and Change Tracking serve fundamentally different purposes:

• Change Tracking is a lightweight mechanism that only flags that a specific row changed. It does not record how many times it changed, nor does it keep the historical data of what the change actually was. You must query the source table again to get the new values.

• Change Data Capture captures the actual historical payload. It provides a full log of the before-and-after values, making it the required choice for auditing and streaming replication.

Microsoft's native CDC is fine for basic, internal auditing. However, enterprise teams moving data to cloud warehouses (like Snowflake or BigQuery) at scale utilize third-party log-based CDC tools. Here is why:

• Zero-Downtime Cloud Migration: Syncing legacy SQL Server data continuously to a modern cloud warehouse without taking the production systems offline.

• Real-Time Analytics: Feeding live transactional data to BI dashboards, allowing executives to make decisions on data that is seconds old, not days old.

• Event-Driven Architecture: Streaming database changes directly to message brokers like Apache Kafka to trigger downstream microservices instantly.

• AI Readiness: Ensuring predictive models and AI agents are trained on the freshest, most accurate operational data available.

Microsoft now supports native CDC on Azure SQL Database. However, the architectural limitations remain identical to the on-premises version. The internal capture and cleanup jobs consume the database's allocated cloud compute resources (DTUs or vCores). Tracking heavily modified tables can unexpectedly spike your Azure billing and degrade the performance of your primary application.

Relying on native SQL Server CDC to power your enterprise data pipelines is a recipe for bottlenecked production servers and brittle integrations. Qlik helps teams get more out of AI with data they can rely on and control.

When establishing real-time pipelines, Qlik Replicate utilizes agentless, log-based CDC to read the transaction log directly. It moves massive volumes of data in real-time without adding computational overhead to your SQL Server. It automatically handles schema drift and formats the data for your target cloud warehouse, delivering trusted data products and a powerful analytics engine.

Whether you are scaling Qlik Data Streaming to Kafka or managing complex Qlik Data Movement to Snowflake, Qlik works with the systems and partners you already use, preventing vendor lock-in. This helps teams reduce risk, keep operating costs in check, and scale AI responsibly as needs evolve.