The sync job had been double-counting rows for three weeks before anyone traced it to the design meeting where one engineer said "just turn on change tracking" and the other heard "CDC". The two features share a goal — tell consumers what changed — and almost nothing else. One is an asynchronous log reader that preserves full row history; the other is synchronous in-transaction bookkeeping that remembers only the latest version of each key. Picking the wrong one is how you end up re-engineering a data pipeline in month four.
What does each feature actually capture?
Change Data Capture reads the transaction log asynchronously and writes every captured insert, update (as separate before and after rows), and delete into change tables under the cdc schema — full row values, every version, with LSN-based ordering metadata on each row. Change Tracking records, synchronously inside the transaction making the change, only the fact that a key changed: the primary key, a monotonically increasing version number, the operation type, and optionally which columns were touched. No row values, no history. If a row was updated five times between your sync runs, Change Tracking reports it changed once, at the latest version. CDC hands you all five versions with the values attached. That single difference — values and history versus keys and a bookmark — drives everything else.
How do latency and overhead compare?
Change Tracking's cost is paid in the write path: every insert, update, or delete on a tracked table also writes to internal change tables inside the same transaction, so write latency rises and the write load grows by roughly one extra internal-table write per change. Cheap per row, but it never sleeps. CDC's cost is paid by a background capture process scanning the log, so foreground writers barely notice it — but the change tables lag the committed data, seconds on a quiet system and minutes under a heavy log stream, and that lag is a first-class thing you must monitor rather than a constant. Net: Change Tracking taxes writers and answers immediately; CDC leaves writers alone and answers late.
What breaks when the schema changes?
CDC, mostly. A capture instance is a frozen column list taken the moment sp_cdc_enable_table ran. Adding a column to the source table does not add it to the change table, and capture keeps writing rows without it. The supported move is creating a second capture instance — sp_cdc_enable_table again with a new capture_instance name, which a table gets at most two of — transitioning consumers, then dropping the old instance. Dropping or retyping a column is the same dance in the other direction. Change Tracking shrugs at DDL: it tracks keys, and as long as the primary key survives, version bookkeeping continues without a hiccup. If your source schema evolves monthly, that difference alone can make the decision for you.
What are the moving parts, and how do they fail?
CDC depends on SQL Server Agent: a capture job driving the log scan and a cleanup job pruning change tables past the retention, which defaults to 4320 minutes, three days. In Azure SQL Database, where Agent does not exist, a built-in scheduler runs both instead. The failure mode that fills databases is the cleanup job stopping — Agent disabled after a failover, the job silently dropped during a migration, retention set to a year by someone feeling generous — and the change tables then grow without bound. I have walked into a 380 GB database where 300 GB was cdc.dbo_ tables because cleanup had been failing for five months and nothing alerted. Note also that the Agent jobs live per-instance: after an availability group failover the jobs must exist and be running on the new primary, the same class of operational gap I write about in the AG lag monitoring notes.
Change Tracking has a quieter version of the same disease. Auto-cleanup runs on a background thread against its own retention, default two days, and if it falls behind or someone disables it, the internal tables grow with far less visibility than the cdc schema gives you.
How do I monitor CDC health?
sys.dm_cdc_log_scan_sessions is the dashboard: one row per scan session with start_time, duration, command_count, and empty_scan_count. The gap between the latest scanned LSN's commit time — resolve it through cdc.lsn_time_mapping — and right now is your lag.
SELECT TOP (20)
s.session_id,
s.start_time,
s.end_time,
s.duration,
s.command_count,
s.empty_scan_count
FROM sys.dm_cdc_log_scan_sessions AS s
ORDER BY s.session_id DESC;I alert on lag above five minutes and on the gigabyte size of the cdc schema against the configured retention. For Change Tracking the tripwire is different: CHANGE_TRACKING_CURRENT_VERSION() against CHANGE_TRACKING_MIN_VALID_VERSION per table. A consumer whose saved bookmark falls behind the minimum valid version has already missed changes, and the only correct move is a full resync — so the consumer must compare its bookmark against the minimum before every single sync, not after something looks wrong.
How does the consumer read Change Tracking?
Through CHANGETABLE, which returns the keys changed since a bookmark version, joined back to the live table for current values:
SELECT ct.SYS_CHANGE_VERSION,
ct.SYS_CHANGE_OPERATION,
o.OrderId,
o.Status,
o.OrderTotal
FROM CHANGETABLE(CHANGES dbo.Orders, 412300) AS ct
LEFT JOIN dbo.Orders AS o
ON o.OrderId = ct.OrderId
ORDER BY ct.SYS_CHANGE_VERSION;Notice what the join implies: the values come from the table as it exists now, not as it existed when the change happened. A row updated and then deleted between syncs shows up as a delete with no final values to inspect. Consumers that cannot tolerate net-change semantics — warehouses that replay history, audit trails, anything reconstructing state at a point in time — are CDC consumers whether they know it yet or not.
Which one belongs in my pipeline?
Four questions decide it. Do consumers need actual row values or full version history? That is CDC; Change Tracking cannot give you values at all. Is the use case cache invalidation or a lightweight "sync since version N" between tables the same team owns? That is Change Tracking, where CHANGETABLE plus a version bookmark is the entire integration surface. Is the write path latency-critical with no headroom? Lean CDC, because Change Tracking's synchronous bookkeeping lands directly in your p99. Does the schema change often, or is Agent availability doubtful? Lean Change Tracking, or budget CDC's capture-instance maintenance as a first-class chore. And one trap worth naming: enabling both "to be safe" pays both overheads forever to avoid a decision you could make in an afternoon.
Change-feed health with MonPG when SQL Server support lands
What belongs on the dashboard: CDC scan lag in seconds, change-table gigabytes against retention, empty-scan streaks, and for Change Tracking the distance between current and minimum valid versions per table — the resync tripwire. MonPG monitors PostgreSQL in production today; SQL Server support is in active development and on the roadmap, with the honest status on the SQL Server monitoring (coming soon) page. Until then, sys.dm_cdc_log_scan_sessions in a scheduled query with an alert threshold is the whole stack — and it beats finding out from the warehouse team.