Change Data Capture or Change Tracking

Two SQL Server features can tell a downstream process that data changed, but they answer different questions. Change data capture or change tracking is a choice about detail, retention, and the way a consumer catches up after being offline.

Start With the Consumer’s Question

A data pipeline asking what values changed needs different evidence from a mobile client asking which rows to refresh. Change data capture, or CDC, records committed changes in change tables and exposes functions for reading them by log sequence number. Change tracking records that a row changed and gives a version to support synchronization. It does not keep the previous and new full row images.

I ask the consumer to describe the expected output before enabling either feature. Does it need an ordered feed of inserts, updates, and deletes with column values? Does it merely need a list of keys to revisit? Those answers determine whether CDC’s richer history or change tracking’s lighter metadata is the right starting point. Neither change data capture nor change tracking is a substitute for a documented data contract.

What CDC Records

CDC reads the transaction log and populates change tables for enabled source tables. The capture records include metadata about the operation and log sequence number, with captured column values. Consumers can request all changes or net changes when the table and capture instance support that mode. This makes CDC useful for incremental warehouse loads and integrations that need the values associated with each recorded operation.

CDC does not by itself identify the application user who made a change. The log describes database operations, not a complete business audit narrative. If an audit requires an actor, reason, approval, or request identifier, capture those facts separately in a reliable application or database design. Calling a CDC table an audit ledger without those details creates a false sense of coverage.

What Change Tracking Records

Change tracking is designed to tell a client which rows changed since a saved version. CHANGETABLE(CHANGES …) returns changed keys, operation metadata, and a version. The client can join those keys back to the base table to retrieve current values. A deleted row has no current base row, so the consumer must handle the delete marker rather than expecting a value from that join.

Change tracking does not preserve each intermediate update. If a row changes three times between syncs, the client gets the information needed to reach current state, not a three step narrative. That is exactly what many synchronization workloads need. It is not enough when a downstream process must replay every value transition.

Measure the Operating Cost of Change Data Capture and Change Tracking

CDC adds capture and cleanup work, change tables, and SQL Server Agent jobs on SQL Server. Its storage cost follows change volume, selected columns, and retention. Monitor lag between source commits and consumer reads, and watch cleanup so a delayed consumer does not lose its window. A busy table can produce a large feed even when the final row values barely change.

Change tracking keeps less detail but still adds write overhead and side table storage. Its cleanup also advances a minimum valid version. The lower metadata volume does not remove the need to monitor consumers. Change data capture and change tracking both need a recovery path for a client that falls behind retention. A test with representative writes is more useful than assuming that the lighter label means zero cost.

SELECT name, is_cdc_enabled
FROM sys.databases
WHERE database_id = DB_ID();

Handle Change Data Capture and Change Tracking Retention as a Contract

A consumer must save its last successful position only after its target commit succeeds. For CDC, that position is an LSN. For change tracking, it is a change tracking version. Retention must exceed the longest expected outage plus recovery and replay time. If a consumer’s saved position is older than the valid window, do not quietly skip missing changes. Reinitialize from a consistent baseline and record the gap.

This check shows the minimum valid change tracking version for an enabled table. Compare it with the consumer’s saved version before asking for changes. The example assumes dbo.Orders has change tracking enabled. Adapt the table name and database context for a real workload. An invalid version is a reason to resynchronize, not a reason to reset the saved value to today.

DECLARE @last_sync_version bigint = 100;
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version,
       CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.Orders'))
           AS minimum_valid_version,
       @last_sync_version AS consumer_version;

Choose Change Data Capture or Change Tracking for Sync or Audit

Choose change tracking when clients need changed keys and current rows, including deleted keys, to converge on the source. Choose CDC when a pipeline needs change operations and captured values within a known time window. For a regulated audit, define exactly what must be proved, including actor identity and tamper controls. CDC can contribute data, but it does not satisfy every audit requirement automatically.

I test a consumer by stopping it beyond an ordinary sync interval, then checking whether it resumes without gaps. That exercise reveals more than a feature checkbox. Ask what happens during a long outage, a schema change, and a failed target write. The correct choice is the one whose failure behavior the team can operate, not merely whose first demo looks simpler.

Plan Schema and Consumer Changes Together

Adding or changing captured columns affects the shape of the CDC feed and can require a new capture instance or consumer adjustment. Change tracking clients depend on stable primary keys and must account for their own schema mapping. Document the consumer version and expected columns alongside the source table change. A deployment that updates the table while leaving an old consumer running needs a compatibility window.

Verify the entire path after release: source write, captured metadata, consumer read, target commit, and saved checkpoint. Keep an alert for a stalled consumer and a documented resynchronization procedure. If a consumer cannot explain its checkpoint, it cannot prove that it saw the full feed. This is where a simple synchronization feature becomes an operational service.

A change consumer needs a clear failure alarm. Track the age of its last committed checkpoint and compare that age with the configured retention window. I alert before the gap becomes unrecoverable. Which team owns reinitialization when the window is missed? Put that answer in the runbook, including how to take a consistent new baseline.

Related reading on this blog: FIX: Error: Msg 4928, Level 16, State 1. Cannot Alter Column Because it is 'Enabled for Replication or Change Data Capture' and Introduction to Change Data Capture (CDC) in SQL Server 2008.

A change feed is not an audit trail, it is a contract with a consumer.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.