SQL Server CDC: How Change Data Capture Works 

Your nightly SQL Server extract takes eleven minutes and moves 40,000 rows. Maybe 200 of them actually changed. The other 39,800 get re-read, re-transformed, and re-loaded because nothing told your pipeline which rows were new.

That’s the problem Change Data Capture (CDC) solves in SQL Server: instead of re-scanning a table to find what’s different, it reads the transaction log, the same log SQL Server already writes for every insert, update, and delete, and hands you just the changes.

What is SQL Server CDC?

SQL Server Change Data Capture is a built-in feature (Enterprise, Standard since SQL Server 2016 SP1, and Azure SQL Managed Instance) that records insert, update, and delete activity on selected tables by reading the transaction log asynchronously, then writes that activity into dedicated change tables you can query with ordinary SQL.

It’s not a trigger. It’s not a snapshot comparison. It’s a log reader with a scheduled job that lags the log by seconds, not milliseconds, which matters for how you design around it.

How it actually works

1. You enable CDC at the database level (sys.sp_cdc_enable_db), then per table (sys.sp_cdc_enable_table).
2. SQL Server creates a capture instance for that table, a shadow schema (cdc.<schema>_<table>_CT) that mirrors the source columns plus metadata columns (__$start_lsn, __$operation, __$update_mask).
3. A SQL Server Agent job, the capture job, polls the transaction log on an interval (default 5 seconds), decodes the relevant log records, and inserts rows into the change table.
4. A second Agent job, the cleanup job, deletes change-table rows older than the retention window (default 3 days, configurable in minutes).
5. Consumers query the change table using table-valued functions like cdc.fn_cdc_get_all_changes_<capture_instance>, bounded by LSN (log sequence number) ranges, not timestamp, SQL Server’s own log clock, not wall-clock time.

That LSN detail is the thing most people miss on first setup: you track progress with sys.fn_cdc_get_max_lsn(), not GETDATE().

CDC vs. the alternatives

SQL Server gives you at least four ways to detect changes. They are not interchangeable.

Method	Captures deletes?	Captures old values?	Log/table overhead	Needs SQL Agent	Best for
Change Data Capture	Yes	Yes (before/after image)	Change tables + log read	Yes	Incremental ETL, replication, auditing
Change Tracking	Yes (row marker only, no values)	No	Minimal (internal tracking table)	No	“What changed” sync where you re-fetch current state
Triggers	Yes	Yes	Per-transaction overhead on every write	No	Low-volume tables, synchronous business logic
Timestamp/rowversion polling	No (deleted rows vanish)	No	None extra	No	Simple append-mostly tables

If a business rule depends on knowing a customer’s address before it changed, or you need to replicate deletes downstream, CDC is the only one of the four that gives you that without custom trigger logic.

Setting it up

1. Enable CDC at the database level (requires sysadmin)

USE SalesDB;

EXEC sys.sp_cdc_enable_db;

2. Enable CDC on a specific table

EXEC sys.sp_cdc_enable_table

    @source_schema = N’dbo’,

    @source_name   = N’Orders’,

    @role_name     = NULL,          — NULL = no gating role

    @supports_net_changes = 1;      — enables “net changes” functions

3. Confirm the capture instance exists

SELECT * FROM cdc.change_tables;

4. Query changes since a given LSN

DECLARE @from_lsn BINARY(10) = sys.fn_cdc_get_min_lsn(‘dbo_Orders’);

DECLARE @to_lsn   BINARY(10) = sys.fn_cdc_get_max_lsn();

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Orders(@from_lsn, @to_lsn, ‘all’);

Two prerequisites people skip and then debug for an hour: SQL Server Agent must be running (CDC’s capture and cleanup jobs are Agent jobs), and the database must be in an edition/tier that supports CDC , Standard edition support arrived later than Enterprise, and some Azure SQL Database tiers still don’t support it at all (Managed Instance does).

Where this breaks in production

• Capture job falls behind. On high-write tables, the capture job can lag the log. Until it catches up, the transaction log can’t truncate past the oldest unprocessed record, so log growth becomes a side effect of CDC lag, not a separate problem. Monitor sys.dm_cdc_log_scan_sessions and log file growth together.
• Schema changes aren’t automatic. Add a column to the source table and the existing capture instance won’t see it. You need a new capture instance (sys.sp_cdc_enable_table again, often with a new @capture_instance name) and a cutover plan for consumers reading the old one.
• Retention vs. consumer downtime. If a downstream consumer is offline longer than the cleanup job’s retention window, those changes are gone, not delayed, gone. Set retention based on your worst realistic consumer outage, not the default.
• Net changes vs. all changes. @supports_net_changes = 1 lets you ask “what’s the current state of this row across all changes” instead of “give me every intermediate change.” Get this flag wrong at setup, and you re-enable the whole capture instance to fix it.

The business case: is CDC worth enabling?

Enabling CDC isn’t free, it adds storage (change tables), a small amount of log read overhead, and an operational dependency on SQL Server Agent. Before flipping it on, most teams are really deciding between three things: how fresh does downstream data need to be, how much of the current batch job’s runtime is wasted re-processing unchanged rows, and whether deletes and historical values actually matter to the business process on the other end.

A useful gut check:

• If your batch window is growing faster than your data volume, you’re probably re-scanning more than you’re syncing, a CDC-style incremental pattern usually fixes that regardless of which tool reads it.
• If audit or compliance requires knowing a value’s prior state, CDC’s before/after image does that natively; Change Tracking doesn’t.
• If the table is append-only with no updates or deletes, CDC is overhead you don’t need, timestamp polling is enough.

CDC software broadly has been growing as a category for exactly this reason: as real-time expectations replace nightly batch windows, the global CDC software market was estimated at roughly $3.2 billion in 2025 and is forecast to nearly triple by 2034, according to industry analyst MarketIntelo, driven by the load-and-freshness problem described above, not by hype.

Where DBSync fits

DBSync’s Cloud Replication product reads CDC from relational sources like SQL Server directly, without you writing LSN-tracking logic, managing capture instances by hand, or building your own cleanup-window monitoring. It’s built specifically for database and warehouse replication, this is distinct from Cloud Workflow, DBSync’s app-to-app product for platforms like Salesforce or QuickBooks Online, which don’t expose a transaction log and aren’t CDC sources at all; those syncs use API-based incremental detection instead.

DBSync isn’t a replacement for understanding what CDC does under the hood, the LSN model, retention windows, and schema-change behavior above still apply whether you read the change tables yourself or let a replication tool do it. What it removes is the plumbing: connection management, schema drift handling on the destination side, and idempotent writes so a replayed change doesn’t create a duplicate row.

FAQ

Does CDC slow down my SQL Server? It adds log-read and change-table write overhead, but because it reads the log asynchronously rather than intercepting the transaction (unlike a trigger), the impact on write latency is typically small. High-write tables still deserve monitoring.

Can I use CDC on Azure SQL Database? Support varies by tier, Managed Instance supports it; check your specific Azure SQL Database tier before planning around it. [verify against current Azure SQL tier documentation before publishing]

What happens if I disable CDC on a table? The capture instance and its change table are dropped. Any unconsumed changes in that window are lost, export or process before disabling.

Is CDC the same as replication? No. CDC is a change-detection mechanism; replication (transactional, merge, or a third-party tool like DBSync) is what moves those changes somewhere else.

---

Ready to sync your data, without hand-rolling LSN tracking? Start free at mydbsync.com | Schedule a Demo | Try the Playground