SQL Server CDC: How Change Data Capture Works & Production Setup

SQL Server CDC records every insert, update, and delete against a tracked table by reading the transaction log, then writes those changes into change tables you can query with SQL. It runs asynchronously, so your write never waits on the capture. Each change is recorded as a full row image, before and after, not just a flag that something changed.

The mechanism has been stable since 2008. Most of what goes wrong, however, is operational: which platform you are on, how you enable it, whether the capture job keeps up with your write rate, and whether anything reads the change tables before the cleanup job deletes them.

What is SQL Server change data capture?

Change data capture is the practice of identifying and delivering row-level changes from a source database so other systems can consume them. Several mechanisms qualify: reading the database's transaction log, polling a table on a timestamp or version column, or firing triggers on write. They differ in latency, in how much they cost the source, and in whether they can see a delete at all.

SQL Server's change data capture feature is the log-based kind. It was introduced in SQL Server 2008, it requires Standard or Enterprise edition, and it works by reading the same transaction log that the engine already writes for durability and recovery. Nothing about your table's schema changes when you enable it, and no triggers are created.

This results in a per-table change table holding one row per insert, one per delete, and two per update, carrying the before and after images. What it costs is a background process reading the log, storage for those change tables, and a retention window after which the changes are deleted whether you consumed them or not.

How SQL Server CDC works

Every committed change in SQL Server is already written to the transaction log, because that is how the engine guarantees durability and supports recovery. CDC reads that existing log rather than adding its own instrumentation: a capture process pulls committed transactions out of it and writes the relevant rows into change tables under a dedicated cdc schema. No triggers are created and the source table's schema is untouched.

The sequence runs like this: Your application commits a transaction, the engine writes it to the log, and some time later the capture process reads that record and inserts rows into the change table for each affected source table. A consumer then queries those change tables through table-valued functions.

That read happens out of band, so a gap opens between the commit and the change table row. The gap is both the main advantage and the main operational risk. Your transaction never waits for CDC, but the capture process can fall behind, and when it does nothing in your application will tell you.

CDC is not free. Enabling it changes what the engine logs, because operations that would otherwise be minimally logged have to be written in full for the capture to see them. The capture itself is asynchronous and costs your transaction no time, but that logging change is synchronous and does.

How SQL Server CDC reads the transaction log

SQL Server does not expose its transaction log to third-party tools. There is no public API for reading it directly, and the log format is undocumented and version-dependent. CDC is the supported path to log-based change data, which is why almost every replication tool that works against SQL Server either uses CDC, uses transactional replication, or asks you to enable one of them.

The reader is sp_replcmds, an internal procedure that transactional replication also uses. With CDC alone, a SQL Server Agent job runs sp_cdc_scan, which invokes it. With CDC and transactional replication both enabled, the Log Reader Agent takes over, populating the distribution database and the change tables together, and the standalone capture job is deleted.

The replication interaction surfaces in production. When both features run, replicated changes go to the distribution database first and captured changes second, and both commit together, so latency writing to the distribution database delays your change tables. Two teams sharing a server can slow each other down through a path neither can see from their own side.

Core SQL Server CDC components

Capture instances

A capture instance is what CDC creates for each enabled table: a change table, one or two query functions, and supporting metadata in the CDC system tables. The default name is the schema and table joined by an underscore, so enabling CDC on dbo.Orders produces the capture instance dbo_Orders and the change table cdc.dbo_Orders_CT.

A source table supports at most two capture instances. That ceiling is deliberate: it exists so two schema versions can run side by side while consumers move between them.

Change tables

The change table holds your captured columns plus six metadata columns:

There is no __$operation value meaning "update". An update operation is two rows, a 3 carrying the before image and a 4 carrying the after. Ordering within a transaction uses __$command_id, not __$seqval, and that column was added in a cumulative update across the 2012 to 2016 line precisely because change tables sorted updated rows incorrectly without it.

Log sequence numbers

A log sequence number (LSN) is the position of a record in the transaction log, and it is how you address a range of changes. Both query functions take a @from_lsn and a @to_lsn, and both endpoints are inclusive. Reusing the previous upper bound as your next lower bound will duplicate rows. Advance the boundary with sys.fn_cdc_increment_lsn instead.

The helpers are sys.fn_cdc_get_min_lsn and sys.fn_cdc_get_max_lsn, which return the ends of the validity interval, meaning the range of change data still available for that capture instance, everything the cleanup job has not yet removed. Then sys.fn_cdc_increment_lsn to step past a consumed window, and sys.fn_cdc_map_time_to_lsn and sys.fn_cdc_map_lsn_to_time to move between the LSN timeline and wall-clock time. The cdc.lsn_time_mapping table backs the last two and lets you stamp each change with its source commit time.

All changes versus net changes

cdc.fn_cdc_get_all_changes_<capture_instance> is always created and returns every change in order. A row updated four times in your window gives you four changes.

cdc.fn_cdc_get_all_changes_<capture_instance> is created only when you enable it with @supports_net_changes = 1, which requires a primary key or a named unique index. It returns one row per changed source row, reflecting the final state, so those four updates collapse to one.

If your destination apply is an idempotent upsert keyed on the primary key, distinguishing an insert from an update is wasted work. In that scenario, 'all with merge' is the best option. 

SQL Server CDC vs Change Tracking vs Batch Query

Use CDC when you need row history or have tables without a primary key. Use Change Tracking when you only need to know which rows changed and want lower storage overhead. Use batch queries when the instance supports neither, or when you are reading views.

These three are different mechanisms with different guarantees, with the key factor being what each one actually tells you.

An easy-to-miss distinction is what each one trades. CDC's asynchrony buys you a transaction that never waits, at the price of a reader that can fall behind. Change Tracking has no reader to fall behind, and pays for that by adding work to every insert, update and delete as it happens.

Both can run on the same database, and Microsoft states no special considerations apply.

In two cases the choice is made for you: Computed columns are never captured by CDC, appearing in the change table with the correct type and a permanent NULL, so Change Tracking is the only native option when a computed column matters. Tables without a primary key cannot use Change Tracking at all, so CDC is the only native option there. For a fuller treatment of Change Tracking on its own terms, see our guide to Change Tracking in SQL Server.

Supported versions, editions, and deployments

Versions

SQL Server 2016 left extended support on 15 July 2026, so the range still patched by default is 2017 through 2025. Extended Security Updates are purchasable for 2016 in three annual increments running to 17 July 2029, which buys time but is not a reason to build something new on it. SQL Server 2017 has no ESU program listed. SQL Server 2019 has been extended-only since March 2025, meaning security fixes but no functional ones. Check your version against this table rather than assuming, because 2019 is still extremely common and reads as current when it is not.

Editions

CDC requires Standard or Enterprise. This is enforced rather than advisory: restoring or attaching a CDC-enabled database with KEEP_CDC to any other edition is blocked with error 932, whose message names the two supported editions explicitly. Change Tracking has no such restriction and runs on every edition including Express.

Deployments

Enablement differs on every platform, because sys.sp_cdc_enable_db stored procedure requires sysadmin and managed services do not grant it.

Table-level enablement is sys.sp_cdc_enable_table everywhere, requiring db_owner. That consistency is useful because the per-table decisions transfer across all five platforms even though the database-level step does not.

Beyond enablement, the platforms diverge in ways that change how you operate.

• Azure SQL Database has no SQL Server Agent service - A CDC scheduler runs capture every 20 seconds and cleanup every hour, and neither cadence is configurable. Microsoft gives no SLA on when changes reach the change tables and states that sub-second latency is not supported, which is the ceiling on any latency claim involving this platform. The service tier floor is any vCore tier, or S3 and above in the DTU model.
• Azure SQL Managed Instance runs the Agent permanently - It cannot be stopped, so the self-hosted move of pausing capture by stopping the Agent does not exist there, and Agent-level settings are read-only.
• Amazon RDS hides the jobs - AWS puts it plainly: "the RDS system account owns them. Therefore, the jobs aren't visible from native views, procedures, or in SQL Server Management Studio." They run normally, db_owner can still view, create, modify and delete them, and sp_cdc_change_job still tunes maxtrans and maxscans. More consequentially, an RDS restore disables CDC and deletes its metadata, for both snapshot and point-in-time restores, so you re-enable CDC and re-specify every tracked table afterward. There is no KEEP_CDC escape. On Multi-AZ, RDS recreates the CDC jobs on the new principal after a failover using parameters recorded beforehand, with a documented race window, so set post-failover values through rdsadmin.dbo.rds_show_configuration and rds_set_configuration.
• Google Cloud SQL wraps enablement but leaves the jobs tunable - You enable with EXEC msdb.dbo.gcloudsql_cdc_enable_db 'DATABASE_NAME', and sys.sp_cdc_change_job works normally afterward. Google's own setup instructions use it to slow the capture job right down, @job_type = 'capture', @pollinginterval = 86399, which is the documented way to reduce load when near-real-time capture is not the goal. Whether the jobs are visible in SSMS the way they are on self-hosted is not something Google documents either way, so check it on your instance rather than assuming it matches RDS.

Benefits and common use cases

The reason to reach for log-based CDC over a scheduled query is that it sees everything, and it reads changes without querying the tables your application is using. Deletes are captured rather than inferred. Intermediate states survive, so a row updated three times between syncs produces three changes rather than one. And because the capture reads the log rather than the table, it does not compete with your application for locks on the data.

CDC is a good choice when stale data or a missed delete has a measurably negative impact. The clearest case is an analytics warehouse that needs to stay current without a nightly reload, whether the destination is Databricks or somewhere else. The same pattern holds across the rest of the stack: cloud data warehouses like Snowflake and BigQuery, and the data lakes fed by that same change stream, where a continuous feed is what makes real-time analytics possible on operational data. Search indexes and caches have the same problem, since a polling query never sees a deletion at all; the row just stops appearing, and stale entries survive until something else clears them. Audit trails depend on a capability only CDC has, because the __$operation 3 rows carry the before image that Change Tracking discards. Live migrations need both halves of the mechanism: an initial snapshot, followed by a continuous stream of everything that changed since. And once the change stream exists, event-driven integration comes almost free, because other services and microservices can react to a row changing instead of polling for it.

There are two common scenarios when CDC is the wrong tool. The first is when you only need to know that a row changed, not what it looked like before. CDC pays for the before image whether you use it or not, in storage and in capture work, so a lighter mechanism like Change Tracking is the better fit. The second is when the change rate is low enough that the machinery outweighs the data. A capture instance and a cleanup job run continuously regardless of how much they find, so on a table that changes rarely you are paying a fixed operational cost to capture almost nothing.

How to enable SQL Server CDC

Enable at the database level first, then per table, then verify.

plaintext
-- 1. Database level. Requires sysadmin on self-hosted and Managed Instance,
--    db_owner on Azure SQL Database. On RDS use msdb.dbo.rds_cdc_enable_db,
--    on Cloud SQL use msdb.dbo.gcloudsql_cdc_enable_db.
USE MyDB;
EXEC sys.sp_cdc_enable_db;

-- 2. Table level. Same everywhere. Requires db_owner.
EXEC sys.sp_cdc_enable_table
  @source_schema        = N'dbo',
  @source_name          = N'Orders',
  @role_name            = N'cdc_reader',
  @supports_net_changes = 1;

-- 3. Verify.
SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'MyDB';
SELECT name, is_tracked_by_cdc FROM sys.tables WHERE name = 'Orders';

Enabling at database level creates the cdc schema, a cdc user, and the metadata tables. CDC requires exclusive use of both that schema name and that user name, so if either already exists, enabling fails until it is renamed or dropped.

Decide three parameters on the table-level call deliberately rather than taking the default. @captured_column_list defaults to every column, and narrowing it is how you keep sensitive columns out of the change tables entirely. @filegroup_name should point at a filegroup separate from the source tables, which is Microsoft's recommendation. @supports_net_changes needs a primary key or a named unique index.

For the full walkthrough, including the least-privilege grant sequence and the troubleshooting steps, see how to enable CDC in SQL Server.

Production considerations and limitations

Its log figures are the ones that still explain incidents today. Log bytes written grew to 250% of normal and the log file to 200 to 300% of its original size. Two mechanisms drive it:

• Operations that would be minimally logged under the simple or bulk-logged recovery model become fully logged when CDC is enabled, because CDC has to see them.
• Log space cannot be reused until the capture process has read those records, even in the simple recovery model, and even after a log backup under the full model. A stalled capture job is therefore a growing transaction log, and the disk filling up is usually what gets noticed before the capture lag does.

The capture job also has a throughput ceiling:

plaintext
(maxtrans * maxscans) / polling interval in seconds

At the documented defaults of 500, 10, and 5 seconds, that is 1,000 transactions per second, and no amount of hardware moves it until you change the parameters with sys.sp_cdc_change_job. Refer to this number first when someone reports CDC falling behind. Lowering the polling interval raises the ceiling and the load on the host together, which is the actual trade.

Microsoft's 2008 recommendation still holds: avoid CDC on tables that take frequent large update transactions. In that testing, an 800-second workload updating 18 million rows produced capture latency measured in thousands of seconds.

SQL Server CDC limitations

Most of these limitations give no error, surfacing long after the deployment that caused them.

• Computed columns are never captured, and nothing errors. The column sits there with the right type and a permanent NULL.
• BLOB columns store a before image only when the column itself changed. Microsoft states this as a documented limitation, so an update that touched other columns leaves you without the prior BLOB value. Separately, max text repl size caps the text, ntext, varchar(max), nvarchar(max), varbinary(max), and image data that can be captured from a single statement, and its default is 65,536 bytes. That setting applies to CDC as well as to transactional replication.
• Sparse columns are captured, but not when a columnset is in use.
• XML is captured whole, with no tracking of changes to individual elements.
• Both LSN endpoints are inclusive, so naive window paging duplicates rows.
• Indexed views cannot be captured, because a capture instance requires the base object to be a table. Debezium documents this as the single formal limitation of its SQL Server connector, inherited from the engine.
• CDC cannot be enabled directly on a read replica. Configure it on the primary. A tool can still read the resulting change tables from a replica, which is a separate question from where CDC is turned on.
• Schema changes on a CDC-enabled table are restricted. DDL needs sysadmin, db_owner, or db_ddladmin. Everyone else gets error 22914 even with explicit grants on the table.
• Restoring to a different server disables CDC and deletes its metadata unless you specify KEEP_CDC.

SQL Server CDC retention

Change data is retained for 4,320 minutes, which is 72 hours or three days, by default. One cleanup job serves the entire database and applies one retention value to every capture instance, so there is no per-table retention. Change it with sys.sp_cdc_change_job and the @retention parameter.

Cleanup takes the maximum LSN in cdc.lsn_time_mapping, which Microsoft calls the high water mark of the validity window, and uses its corresponding commit time as the base that the retention period is subtracted from. Retention is therefore measured from the last transaction that the capture process actually handled, not from the wall clock. A capture job running six hours behind does not have its unread changes deleted out from under it.

If a consumer is down longer than the retention window, those change events are gone. Recovery then means a complete backfill, re-reading every row of the source from scratch. Raising retention trades one problem for another, because the change tables then hold more data and the disk has to be sized for it.

SQL Server CDC schema changes

SQL Server does not break on a schema change. It ignores it, which causes a different problem.

Adding or dropping a column does not change the change table. The event is recorded in cdc.ddl_history and the change table definition stays exactly as it was. A new column is ignored, because it is not in the captured column list. A dropped column that is still in the list gets filled with NULL. Query cdc.ddl_history on a schedule if you want to know which DDL changes have gone past the capture process unhandled.

Changing a column's data type does alter the change table, when the capture process reaches that DDL record in the log. If you narrow a type, existing source values have to fit the new range first, or the alteration fails at that point.

The supported way through a breaking schema change is the two-capture-instance limit, used as a feature. Create a second capture instance with the new column set, let consumers migrate across, then drop the old one. Wrapper functions generated by sys.sp_cdc_generate_wrapper_function keep the names stable and hide the cutover from application code.

SQL Server CDC monitoring

Two dynamic management views carry what you need.

plaintext
-- Capture latency and throughput. session_id = 0 is the aggregate row.
SELECT  start_time, end_time, duration, command_count, latency, empty_scan_count
FROM    sys.dm_cdc_log_scan_sessions
WHERE   session_id = 0;

-- Errors raised by the capture process.
SELECT * FROM sys.dm_cdc_errors;

The latency column is the difference in seconds between end_time and last_commit_cdc_time, populated at the end of the session's final scan phase. On the aggregate row it holds the last nonzero latency that any session recorded, so it does not drop to zero during quiet periods. empty_scan_count is the number of consecutive sessions that carried no CDC transactions. Throughput is command_count / duration.

The view is a short rolling window, not a history. It holds at most 32 scan sessions plus the aggregate row, so 33 rows total. Anything older is gone, which is why a spot check tells you about the last few minutes and nothing else.

Naive monitoring also produces false alarms here. A high empty_scan_count means the capture process found nothing to do, not that it stopped. And during idle periods the capture process inserts dummy entries into cdc.lsn_time_mapping specifically so it does not look like it has fallen behind when there is nothing to process.

For anything beyond a spot check, Microsoft documents wiring both views into the data collector on a five-minute schedule to build a history.

Native SQL Server CDC versus an end-to-end CDC pipeline

Native CDC gives you change tables, but does not give you a pipeline. Everything between the change table and the system that needs the data is yours to build: the query loop with correct LSN handling, the initial snapshot, schema evolution, ordering guarantees, retries, backfill after an outage, and the apply logic at the destination. That gap is where most SQL Server ETL projects spend their time.

Debezium states the boundary plainly in its own connector documentation, explaining why it has to take an initial snapshot at all: "SQL Server CDC is not designed to store a complete history of database changes." The three-day retention default is one of the reasons that holds.

The tools that read SQL Server split by mechanism rather than by vendor:

AWS DMS is a case of its own: it uses transactional replication for tables with primary keys and CDC for those without, except on Amazon RDS, where it has to use CDC for everything.

Each approach does offer an advantage: Reading change tables is supported, portable across managed platforms, and works wherever CDC works. Reading the log directly avoids the change tables entirely, which removes the storage and the cleanup job from your source database, and it is why Striim ships MSJet alongside its CDC-based reader. If you are comparing named products rather than mechanisms, our roundup of the best change data capture tools puts them side by side.

A managed pipeline, however, takes that work off you. The snapshot, the LSN bookkeeping, the schema handling, and an idempotent destination apply are all maintained rather than built once and inherited.

How Estuary supports SQL Server CDC

Estuary is a right-time data platform: it streams changes in real time when that matters and batches them when it does not, so your analytics, Ops, and AI systems get SQL Server data when they need it, not when your stack decides. Estuary ships three SQL Server capture connectors, and they map one for one onto the three mechanisms above.

The selection of a connector follows the same logic as the native mechanisms. Reach for the CDC connector when you need full history or the table has no primary key. Reach for Change Tracking when computed columns matter, accepting that it requires primary keys everywhere and must be read from the primary, since Change Tracking data is not available on replicas. Estuary's documentation is direct about the trade, noting that Change Tracking may combine intermediate changes when they occur in rapid succession.

The CDC connector needs CDC enabled on the database and on each table, a role holding VIEW DATABASE STATE, or VIEW DATABASE PERFORMANCE STATE on SQL Server 2022 and later, and SELECT on the cdc schema and the schemas holding captured tables. It is regularly tested against SQL Server 2017 and up, and runs against self-hosted instances, Azure SQL Database, Amazon RDS, and Google Cloud SQL.

Most CDC connectors, Estuary's included, write a little to your source database as well as reading from it. The writes go to a small watermarks table that the connector uses to track its own position. In a locked-down environment, permission to create that table can be the thing that blocks a project.

Estuary's SQL Server capture supports a watermarkless mode that removes those writes entirely. Two qualifications: it is opt-in through the read_only feature flag in the advanced endpoint configuration, and it runs alongside the watermark-based mechanism that existing captures continue to use.

Captured changes land in a collection, which is durable cloud storage rather than an in-flight buffer, so a destination can be added or re-backfilled later without re-reading the source. That decouples your recovery window from the source: once a change is in a collection, SQL Server's three-day cleanup no longer decides whether you can still get it. The collection has its own retention, which depends on where it is stored.

One caveat applies to Estuary and to every other tool in this category. Capture latency and end-to-end latency are different numbers. A sub-second capture feeding a warehouse that merges on a thirty-minute schedule is a thirty-minute pipeline. Set the destination cadence deliberately, because that is usually where the real latency lives.

Start Building For Free

Conclusion

SQL Server CDC is a well-documented, supported way to get row-level change data out of a database without changing its schema or adding triggers to its write path. Almost none of the difficulty is in the mechanism. It is in the operational detail around it: which edition and platform you are on, whether the capture job keeps pace with your write rate, and whether anything consumes the change tables before cleanup removes them.

If you take one thing from this into a design review, make it the retention window and what your consumer does after an outage. Everything else is recoverable, but expired change events are not.

If you would rather not build the pipeline around it, Estuary's SQL Server capture connectors cover the CDC, Change Tracking, and batch paths described above, including the watermarkless mode for locked-down instances. The setup guide walks through the permissions.