Change Data Capture in SQL Server

Nearly every integration problem eventually comes down to one question: how do I know what changed? A source system holds the truth, a downstream system needs to stay in step with it, and the naive answers all have a cost. You can re-copy the whole table on a schedule and waste enormous effort moving rows that never changed. You can poll on a modified-date column and quietly miss deletes and any update that forgets to touch the timestamp. Or you can let the database tell you precisely what changed, row by row, in the order it happened. On SQL Server that last option has a name, and it is built into the engine: Change Data Capture, or CDC. This guide is a practitioner's tour of how it works, where it fits, and where it does not.

The message up front: CDC is not a replication product and it is not a message bus. It is a low-overhead mechanism that reads the transaction log and records the exact inserts, updates and deletes on tables you choose into dedicated change tables, so a downstream consumer can pull them on its own schedule. It is a source of change events, and what you build on top of it is up to you. Understanding that boundary is what keeps a CDC project clean. For the wider picture of how sources and targets are wired together, start with the enterprise system integration pillar.

1. What CDC is in SQL Server

Change Data Capture is a feature of the SQL Server database engine that records data modifications made to user tables and exposes them in a queryable, relational form. When you enable CDC on a table, SQL Server begins tracking every insert, update and delete applied to that table and writes a record of each change into a companion change table living in a dedicated schema called cdc. Your applications keep reading and writing the source table exactly as before. Nothing about their behaviour changes. In the background, the engine maintains a parallel, append-only record of what happened.

The important word is record. CDC does not just tell you that a row changed, and it does not force you to go back and re-read the current state of the source table to find out what the change was. It captures the change itself, including the column values before and after an update, so a downstream consumer can reconstruct the full history of modifications without ever touching the live table. That is the property that makes CDC genuinely useful for integration: it turns a busy operational table into an ordered stream of change events that another system can consume at its own pace.

Historically CDC was an Enterprise-edition feature, and on older releases you needed the SQL Server Agent running because the capture and cleanup work is driven by Agent jobs. On modern versions the feature is available more broadly, and the mechanics are the same across editions that support it: enable it at the database level, enable it per table, and the engine takes care of populating the change tables. It has been part of the product since SQL Server 2008 and is well understood, well documented and battle-tested in production integration workloads.

2. CDC versus Change Tracking (and triggers)

SQL Server actually offers more than one built-in way to answer the "what changed" question, and choosing the wrong one is a common early mistake. The three that come up are Change Data Capture, Change Tracking, and hand-rolled triggers. They look similar from a distance and behave very differently up close. The right choice depends on whether you need the actual changed values, how much history you must retain, and how much overhead you can afford on the source workload.

The practical rule I use: if the downstream system needs the change itself, the old and new values and the full sequence of what happened, CDC is the right tool. If the downstream system only needs to know which rows are now stale so it can go and re-read the current state, Change Tracking is lighter and simpler. Triggers are the fallback when you need custom logic that neither built-in feature provides, but they run inside every transaction, so they add latency to the source workload and become a maintenance burden. For most integration feeds, CDC is the sweet spot because it captures the actual change with low overhead and no application code.

3. How SQL Server CDC works (log-based, change tables)

The reason CDC is low-overhead is that it does not intercept your transactions. It reads the transaction log after the fact. Every committed change in SQL Server is already written to the transaction log for durability and recovery, so that log is a complete, ordered record of everything that happened to the database. CDC piggybacks on it. An asynchronous capture process scans the log for changes to the tables you have enabled, decodes them, and writes matching rows into the change tables. Because this happens off to the side rather than inside the user transaction, the overhead on the application workload is small.

Each CDC-enabled source table gets its own change table in the cdc schema. A change table mirrors the columns of the source table you chose to capture, and adds metadata columns that describe each change: an operation code that says whether the row was an insert, a delete, or the before or after image of an update, a bitmask of which columns were updated, and a log sequence number that fixes the exact position of the change in the log. That log sequence number is what gives CDC its ordering guarantee. Changes come out in commit order, which is exactly what a downstream consumer needs to apply them correctly.

The flow is worth reading left to right and top to bottom. The application writes to the source table as normal, and those commits land in the transaction log. The asynchronous capture process reads the log, finds the changes on enabled tables, and populates the change tables in the cdc schema with the operation type, the before and after values, and the log sequence number. A downstream consumer then queries those change tables to pull the changes in commit order and apply them wherever they need to go. The source workload never waits on any of this, which is the whole point of a log-based design.

4. Enabling CDC at a high level

Turning CDC on is a two-step, top-down operation, and you should treat it as a deliberate architectural decision rather than a flag you flip casually. First you enable CDC at the database level, which creates the cdc schema, its metadata tables, and the supporting objects. Then you enable CDC on each specific table you want to track. You do not enable it database-wide across every table, and you should not: you capture only the tables whose changes a downstream system actually needs, because every captured table adds change-table storage and a little log-reading work.

The two system stored procedures shown above, sys.sp_cdc_enable_db and sys.sp_cdc_enable_table, are the well-known entry points. The first is a one-time action per database. The second you call once per table, telling SQL Server which table to capture and, if you want, which subset of its columns. Once a table is enabled, its change table appears under the cdc schema and starts filling as modifications occur. The exact parameters for these procedures are documented, and I would always check the current documentation rather than rely on memory, because options such as which columns to capture, which filegroup to place the change table on, and how the capture instance is named are worth setting intentionally.

The gotcha most teams hit: schema changes on a CDC-enabled table do not automatically flow through to the change table. If you add or drop a column on the source, the existing capture instance keeps the shape it had when it was created. Handling schema evolution cleanly, often by creating a second capture instance and cutting the consumer over, is a real operational task, not an afterthought. Plan for it before you enable CDC on a table whose structure is still moving.

5. Consuming CDC data downstream

Enabling CDC only produces the change tables. The value comes from a consumer that reads them reliably and turns them into action somewhere else. SQL Server exposes table-valued functions in the cdc schema that let you query the changes for a capture instance over a range of log sequence numbers. There are two flavours worth knowing conceptually. One returns all changes in the range, including every intermediate update, which is what you want when the downstream system needs the full history. The other returns only the net change per row across the range, which is what you want when the downstream only cares about the final state.

A well-behaved consumer follows a simple, repeatable loop. It remembers the log sequence number it last processed. On each run it asks CDC for the current high-water mark, requests all changes between its saved position and that mark, applies them to the target in order, and then records the new position durably so it can resume from exactly there next time. That saved position is the entire secret to correctness. Because CDC hands you changes in commit order and lets you ask for a precise range, a consumer that persists its position can crash, restart, and pick up without missing or duplicating changes, as long as it commits its target write and its position update together.

This is a pull model, and that is a feature, not a limitation. The downstream system reads on its own schedule, so a slow or briefly offline consumer does not push back on the source workload at all. The changes simply wait in the change tables until the consumer comes back and asks for them, bounded only by the retention window. Contrast that with a trigger-based approach, where the source transaction carries the cost of every downstream write. With CDC the source is decoupled from the consumer entirely. That decoupling is exactly why CDC pairs so naturally with the loosely coupled integration patterns covered in the enterprise system integration pillar.

6. Performance, cleanup and retention

Nothing is free, and CDC has a cost profile you should understand before enabling it in production. The capture work is asynchronous and reads the transaction log, so the direct hit to transaction latency is small, but there are real second-order effects. The change tables consume storage, and on a high-churn table that storage grows quickly. The capture process itself consumes CPU and I/O to read and decode the log. And crucially, CDC changes how the transaction log behaves: log records must be retained until the capture process has read them, so if capture falls behind or the SQL Server Agent that runs it is stopped, the log cannot be truncated and can grow uncontrollably.

Retention is the other half of the story. Change tables are not meant to grow forever. A cleanup job, driven by SQL Server Agent, periodically removes change rows older than a configured retention window, and the change tables only ever hold changes within that window. This is why the retention setting matters so much: it must be long enough that your slowest downstream consumer can always catch up before the data it needs is cleaned away, but short enough that the change tables stay a manageable size. Getting that balance wrong in either direction is the most common operational failure with CDC.

• Size the change-table storage for your busiest captured tables, and place change tables thoughtfully rather than letting them land wherever the default filegroup happens to be.
• Keep capture healthy. If the capture job stops, the log stops truncating. Monitor the capture latency and the log size together, because a stalled capture process is a silent way to fill a disk.
• Set retention against your slowest consumer. If a downstream feed can be offline for a day, retention must comfortably exceed a day, or that consumer will come back to find changes already cleaned away.
• Watch cleanup and capture jobs. Both are Agent-driven; if Agent is not running or the jobs are disabled, CDC quietly stops working correctly even though the feature still looks enabled.

7. CDC in ETL and streaming pipelines

Where CDC really earns its place is as the change-detection layer in a data pipeline. The classic use is incremental extract in an ETL or ELT process feeding a data warehouse. Instead of a nightly full reload of a large source table, the pipeline asks CDC for just the rows that changed since the last run and applies only those. That turns a heavy full-table scan and reload into a light incremental delta, which is faster, cheaper, and far kinder to the source system. If you are weighing where the transformation work should sit relative to the load, the trade-offs are the subject of the ETL versus ELT comparison.

CDC is also the foundation under a lot of modern streaming and replication tooling. Log-based change capture is the general technique behind keeping a search index, a cache, a reporting replica, or an event stream in step with an operational database, and SQL Server's native CDC is one concrete implementation of that idea. Many streaming connectors and integration platforms read SQL Server's change tables, or read the log directly using the same principle, to emit change events onto a message platform. For the broader concept independent of any one product, see the general what is change data capture explainer, and for how CDC-style feeds relate to keeping whole datasets in sync, the data replication overview.

The pattern generalises well beyond warehousing. I have used log-based change capture to keep a downstream ERP or line-of-business system in step with a system of record without either side polling the other, and the same principle drives event-based integrations with cloud platforms. When you are syncing SQL Server data into a business application, the change feed is the clean way to trigger the downstream write only when something actually changed, which is exactly the model that pairs well with API-based targets such as those described in the Business Central APIs and integrations pillar.

8. Limitations and gotchas

CDC is excellent at what it does, but it is not a universal answer, and the honest practitioner names the limits before the client discovers them. The most important ones to weigh:

• Schema changes are not automatic. As noted earlier, altering a captured table does not reshape its change table. You manage schema evolution deliberately, usually with a second capture instance and a controlled consumer cutover.
• It depends on SQL Server Agent. Capture and cleanup run as Agent jobs. If Agent is down, capture stalls, the log stops truncating, and cleanup stops. CDC health is Agent health.
• Retention is a hard boundary. Changes outside the retention window are gone. A consumer offline longer than retention will silently miss data, so retention must be sized against your worst-case downtime, not your average.
• It adds storage and log pressure. High-churn tables produce large change tables, and the log must be retained until capture reads it. Both need monitoring and capacity planning.
• It is not a message queue. CDC records changes; it does not deliver them, retry them, or guarantee any consumer saw them. The delivery, ordering-into-the-target, and exactly-once handling are the consumer's job.
• It captures data changes, not intent. CDC sees that a row went from state A to state B. It does not know the business reason, and it cannot capture things that never touched a table, such as a computed result that was never persisted.

None of these is a reason to avoid CDC. They are the boundaries within which it works beautifully. The projects that go wrong are the ones that expected CDC to be a full replication or messaging solution and were surprised when it turned out to be a change-capture mechanism that leaves delivery and consumption to you. Set the expectation correctly and CDC is one of the most reliable, lowest-drama tools in the SQL Server integration kit.

9. References

Everything described here reflects well-established, documented SQL Server behaviour rather than any one deployment. For the authoritative and current detail, the reference to rely on is the Microsoft Learn SQL Server documentation, which covers the Change Data Capture feature, the cdc schema and its change tables, the enable and disable system stored procedures, the query functions for reading changes, and the capture and cleanup jobs. Because option names, edition support and defaults do change across releases, I always confirm the specifics against the documentation for the exact SQL Server version in front of me rather than trusting a fixed recollection. Treat that documentation as the source of truth for syntax and version-specific behaviour, and treat this guide as the map of how the pieces fit together and where the judgement calls lie.

Final thoughts

Change Data Capture answers the oldest question in integration, what changed, and it answers it the right way: by reading the transaction log the database already maintains, recording the actual changes into dedicated tables, and letting downstream consumers pull them in commit order on their own schedule. It is low-overhead because it stays off the transaction path, it is accurate because it captures before and after values with a strict ordering guarantee, and it decouples the source from every consumer that reads it.

The discipline that makes CDC work is unglamorous but small: enable it only on the tables that a downstream system truly needs, size retention against your slowest consumer, keep the capture and cleanup jobs healthy, plan for schema change before it bites you, and remember that CDC hands you the change but leaves delivery to your consumer. Get those right and CDC becomes the quiet, dependable heart of an incremental ETL feed, a streaming pipeline, or a system-to-system sync. It is one of the cleanest ways SQL Server gives you to feed downstream systems, and used within its limits it rarely lets you down.

Related reading: Enterprise system integration explained, What is change data capture, What is data replication, ETL vs ELT, Business Central APIs and integrations.