Change Tracking or CDC: Choose the Right SQL Server Feed

A search index needs to know which products changed so it can refresh their current documents. A warehouse may need the old and new values of every captured update. These are different requirements, even though both are often described as "incremental synchronization". SQL Server Change Tracking and Change Data Capture solve different parts of that problem.

Start by deciding whether intermediate states matter. If a product price changes three times while the consumer is offline, is the final price enough, or must all three changes be processed? Also decide how deletes should propagate. Polling a LastModified column cannot discover a row that no longer exists unless the application separately records its deletion.

Match the feed to the required meaning

Change Tracking records changed primary keys and change metadata. The consumer reads current values from the source table. It is suited to refreshing a downstream representation of current state, not reconstructing every historical transition. Repeated changes to one key are not a complete business event log, and column tracking does not provide the old column values.

CDC reads committed changes from the transaction log into capture tables. Depending on the enumeration option, a consumer can obtain captured before and after values or net changes. There is capture latency, so a successful source commit does not mean its change is already available in the CDC query range. On conventional SQL Server installations, the capture and cleanup jobs also require operational attention.

This read-only inspection shows database-level configuration and tables using Change Tracking.

SELECT d.name, d.is_cdc_enabled,
       ct.retention_period, ct.retention_period_units_desc,
       ct.is_auto_cleanup_on
FROM sys.databases AS d
LEFT JOIN sys.change_tracking_databases AS ct
    ON ct.database_id = d.database_id
WHERE d.database_id = DB_ID();

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       OBJECT_NAME(object_id) AS TableName,
       is_track_columns_updated_on
FROM sys.change_tracking_tables;

A database-level setting alone does not mean every table is tracked. Confirm the intended tables, primary keys, captured columns, consumer permissions, and feature support for the actual SQL Server version and edition.

Neither feature automatically delivers an immutable audit trail. Retention cleanup removes history, administrators can alter configuration, and a change record does not necessarily identify the business reason or user behind it.

Treat the checkpoint as part of the data

A Change Tracking consumer stores the last successfully applied version. Before requesting more changes, it must compare that version with the table's minimum valid version.

-- Replace dbo.Products with an existing tracked table.
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CurrentVersion,
       CHANGE_TRACKING_MIN_VALID_VERSION(
           OBJECT_ID(N'dbo.Products')
       ) AS MinimumValidVersion;

A checkpoint older than the minimum is no longer safe. Some required metadata has been cleaned up; silently continuing can leave stale rows in the destination. Reinitialize from a consistent baseline instead. A NULL result must also be investigated, including table configuration and permissions, rather than treated as version zero.

For a coherent Change Tracking extraction, validate the checkpoint, capture the next version, and enumerate changes with the source rows under the documented snapshot-isolation pattern. Snapshot isolation must already be enabled. Deletes require a left join from changed keys because the source row may be absent. Materialize the required extraction before ending that consistent read; do not hold a database transaction open throughout a slow network delivery.

Apply destination changes and advance the destination checkpoint atomically where possible. Otherwise make delivery idempotent so that replaying the same batch is harmless. Advancing the checkpoint before the destination commits creates a loss window; committing the destination first without replay protection creates a duplicate window.

CDC consumers use LSN boundaries instead of Change Tracking versions. Respect the capture instance's available range and the enumeration function's inclusive endpoints. Use the documented next-LSN function when advancing between adjacent ranges, rather than inventing arithmetic on binary LSN values.

Design recovery before scheduling polling

Retention must exceed the longest credible consumer outage plus catch-up time and a margin. Monitor how close each consumer is to losing its usable history. "The polling job succeeded" is insufficient if it reads nothing because capture is stalled.

Test an initial load while writes continue, a deleted row, several updates to the same key, a consumer crash after destination commit, and an outage beyond retention. A baseline copied at one moment with a checkpoint from an unrelated later moment can permanently miss intervening changes.

Schema changes also need a contract. Adding a source column does not automatically add it to an existing CDC capture instance. Coordinate capture configuration and destination schema rollout. Choose Change Tracking when current-state refresh is enough, and CDC when captured row changes are needed, then give either choice a recovery procedure that has actually been rehearsed.

Technical references: Microsoft Learn: Change Tracking · Microsoft Learn: Change Data Capture · Microsoft Learn: Working with Change Tracking.