How To Enable CDC in SQL Server

SQL Server CDC is deceptively simple to enable: run a few stored procedures, and SQL Server starts writing row-level changes into tables under the cdc schema.

But enabling CDC is the easy part. The harder part is understanding what those change tables contain, how SQL Server populates them from the transaction log, and how to consume that stream safely without missing changes or duplicating events.

Change data capture (CDC) is the foundation for many real-time data workflows, including database replication, operational analytics, data warehouse ingestion, audit trails, and event-driven applications. Instead of repeatedly polling source tables, CDC lets you capture inserts, updates, and deletes as they happen.

In this guide, we’ll walk through how SQL Server CDC works, how to enable it on a database and table, how to inspect captured changes, and what to watch out for in production. We’ll also show how Estuary can use SQL Server CDC to build real-time pipelines from SQL Server to destinations such as Snowflake, BigQuery, PostgreSQL, Databricks, Apache Iceberg, Redshift, and ClickHouse.

By the end, you’ll understand not just how to turn CDC on, but how to work with the change stream it produces.

Prerequisites

Here’s what you’ll need in order to get change data capture up and running for SQL Server:

1. A SQL Server version, edition, or cloud deployment that supports CDC
2. Permissions to enable CDC at the database and table level
3. SQL Server Agent running for self-hosted SQL Server
4. Source tables identified for CDC capture

SQL Server CDC is available in supported SQL Server editions and versions, but support varies by deployment type and version. Before using CDC, confirm that your SQL Server edition supports it. For older SQL Server versions, edition support can be more restrictive, so always verify compatibility before planning a CDC-based pipeline.

In the world of SQL Server, CDC has three main ways of working: one called “change data capture”, another called “change tracking”, and a third option that lets you execute queries against the database to extract changes. There are major differences between these methods, so make sure you understand them before committing to one.

To quickly compare the SQL Server CDC options, take a look at this table:

As a rule of thumb, use CDC as the default when you just want to “replicate SQL Server changes in real time.” It gives the most complete semantics: inserts, updates, deletes, historical change data, and better support for tables without primary keys.

Use Change Tracking when the source team is sensitive to storage overhead, every captured table has a primary key, or the table has computed columns that CDC cannot capture.

Use Batch Query when the requirement is not really CDC: for example, “capture this view,” “run this custom query every 15 minutes,” or “this SQL Server environment won’t allow CDC/CT.”

5 steps to enable change data capture in SQL Server

The high-level flow is simple; you just have to enable CDC on the database and the required tables. But there are many gotchas along the way, so let's take a look at them.
SQL Server change data capture works for newly created and existing databases. Here we'll look at both methods.

Step 1: Creating a new database

To create a new SQL database, power up your Microsoft SQL Server Management Studio and run the following code:

sql
-- Create a database
CREATE DATABASE New_Source_DB
USE [New_Source_DB];
GO
EXEC sp_changedbowner 'admin'
GO

Let’s dissect this code block.

The CREATE DATABASE command takes the name of the database as an argument. In this example, the name of our new database is New_Source_DB.

Next is the sp_changedbowner command which changes the database owner. As mentioned earlier, you need admin privileges to enable CDC. So, with this command, you get the required access level. If you do not have access to the admin user, run these commands as any user with sufficient privileges, such as a member of the db_owner role.

Step 2: Creating a table

You probably already have a few of these, but for completeness, the next step is to create the SQL Server database table. You can use the following code to get this step done.

sql
-- Create a Users table
CREATE TABLE Users
(
  ID int NOT NULL PRIMARY KEY,
  FirstName varchar(30),
  LastName varchar(30),
  Email varchar(50)
)

In the code block above, we have added the required column names and constraints. Of course, you can customize it according to your own needs.

Step 3: Enabling CDC

Now comes the most-awaited part: enabling CDC. However, remember that you can enable CDC for the entire database or independent tables. We'll discuss both here.
First, let’s see how to set up an entire database for CDC.

sql
-- Enable Database for Change data capture
USE New_Source_DB
GO
EXEC sys.sp_cdc_enable_db
GO

That was easy, but where exactly did we enable the change data capture?

It’s that little thing saying sys.sp_cdc_enable_db.

This enables CDC for the whole database, and creates the required metadata structure for SQL Server to start capturing changes. But, you still need to enable CDC for the tables you actually want to track. Here's how you can do it.

sql
-- ======================
-- Enable a Table for CDC
-- ======================
USE New_Source_DB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'Users',
@role_name     = N'admin',
@supports_net_changes = 1
GO

You’ll also need to double-check things to ensure everything is in place. Luckily, SQL Server provides a helper function for this. Use the following snippet to verify the enabled CDC.

sql
-- Check that CDC is enabled on the database
SELECT name, is_cdc_enabled
FROM sys.databases WHERE database_id = DB_ID();

This tells you if the actual functionality is in place for the database, but it’s always a good idea to test CDC in action. Let's take a look at how to do that easily.

Step 4: Insert values within the table(s)

Now it's time to put our work to the test.

We’ll add some values in the table and see if the changes are being captured appropriately. The expected result for inserting new records is to capture “creation”-type changes.

sql
INSERT INTO Users Values (1, 'Jorge', 'Ramos', 'ramos@yahoo.com')
INSERT INTO Users Values (2, 'Andrew', 'Strudwick', 'andrew@yahoo.com')
INSERT INTO USERS Values (3, 'Thomas', 'Tuchel', 'tuchel@gmail.com')

After inserting the values, run the following command to see the changes that have been captured.

sql
-- Query the results of the changes captured
SELECT * FROM [cdc].[dbo_Users_CT]
GO

After those three INSERT statements, the CDC change table would show one row per inserted user. The exact values for __$start_lsn, __$end_lsn, and __$seqval are binary log sequence numbers, so they won’t look human-readable. The query results would look something like this:

The important part is this column:

plaintext
__$operation = 2

For SQL Server CDC, 2 means insert. So each inserted row appears in the CDC table as an insert event.

Step 5: Verify whether the CDC Is functioning correctly or not

Now that CDC is enabled and we’ve inserted a few rows, let’s make additional changes to the Users table and verify that SQL Server captures them.

Run the following statements:

sql
DELETE FROM Users WHERE ID = 1;


UPDATE Users

SET LastName = 'Snow'

WHERE ID = 2;


DELETE FROM Users WHERE ID = 3;

Then query the CDC change table again:

sql
SELECT *

FROM [cdc].[dbo_Users_CT];

GO

The output should look similar to this:

The system columns are generated by SQL Server CDC. The exact __$start_lsn, __$seqval, and __$update_mask values will be different in your environment, but the operation codes are the important part:

In this example, the CDC table shows:

Notice that the UPDATE operation creates two CDC records: one for the row before the update and one for the row after the update. This is useful because downstream systems can see both the previous and current state of the changed row.

At this point, we’ve verified that CDC is capturing inserts, updates, and deletes from a newly created SQL Server database. Next, let’s look at how to enable CDC for an existing database.

How to enable CDC for an existing SQL Server database

Creating a CDC table for an existing database is much easier. We will use the Adventureworks2019 sample database for this example, as it comes with an MS SQL Server.

Start by running the following queries for databases or tables:

sql
Use Adventureworks2019;
EXEC sys.sp_cdc_enable_db;

Instead, if you want to enable CDC for a specific table, you can use this code:

sql
USE Adventureworks2019
GO
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'DimCustomer',
@role_name = null,
@supports_net_changes = 0;

This code enables CDC for the DimCustomer table. Now you can verify if the CDC is working (i.e., it’s enabled). Here's how you do it:

sql
USE Adventureworks2019
GO

select name, is_cdc_enabled
from sys.databases where name = 'Adventureworks2019'
GO

If you enabled CDC for a table, use the following snippet to verify CDC. For this example, remember that the SQL server database name is Adventureworks2019 and the table name is DimCustomer, so the code looks something like this:

sql
USE Adventureworks2019
GO
select name,type,type_desc,is_tracked_by_cdc
from sys.tables
where name = 'DimCustomer'
GO

How to enable SQL Server Agent

SQL Server Agent is a Windows service that runs scheduled SQL Server jobs in the background. For CDC on self-hosted SQL Server, it runs the capture and cleanup jobs that read changes from the transaction log and manage CDC change tables.

If you’re self-hosting SQL Server, SQL Server Agent must be set up to start and run automatically for CDC to work correctly. If you use a cloud hosting provider like Azure SQL Database, this is automatically taken care of and you can skip this step.

Here are the steps to enable SQL Server Agent:

1. Start the MS SQL Server and run the SQL Server Configuration Manager from the left pane.
2. Open the Properties of SQL Server agent service. In the properties, windows populate the necessary details of the desired account.
3. In the Log On tab, enter the account details.
4. Move to the Service tab and click Start Mode. From the drop-down menu, select Automatic. Click Apply, then click OK to save the changes.
5. Finally, start the SQL Server agent service.

Common challenges when using SQL Server CDC

Although SQL Server CDC is straightforward to enable, production deployments often run into issues around permissions, SQL Server Agent jobs, transaction log retention, schema changes, and downstream consumption semantics. Here are the most common technical challenges.

1. SQL Server Agent is not running

On self-hosted SQL Server, CDC depends on SQL Server Agent jobs to move changes from the transaction log into CDC change tables. When CDC is enabled on a database, SQL Server creates capture and cleanup jobs. The capture job reads committed changes from the transaction log, while the cleanup job removes older rows from the CDC tables based on the configured retention period.

If SQL Server Agent is stopped, CDC may appear to be enabled, but no new changes will be written to the CDC tables.

You can verify the CDC jobs with:

sql
EXEC sys.sp_cdc_help_jobs;

You can also check whether the capture job exists:

sql
SELECT
   name,
   enabled,
   date_created,
   date_modified
FROM msdb.dbo.sysjobs
WHERE name LIKE 'cdc.%';

A healthy CDC-enabled database should usually have jobs with names similar to the examples below:

plaintext
cdc.<database_name>_capture
cdc.<database_name>_cleanup

2. CDC is enabled on the database, but not on the table

CDC must be enabled at two levels: first, on the database, then on each table you want to track. Enabling CDC on the database only creates the CDC metadata schema and supporting infrastructure. It does not automatically capture every table.

Check database-level CDC status:

sql
SELECT
   name,
   is_cdc_enabled
FROM sys.databases
WHERE name = DB_NAME();

Check table-level CDC status:

sql
SELECT
   name,
   is_tracked_by_cdc
FROM sys.tables
WHERE name = 'Users';

If is_cdc_enabled = 1 but is_tracked_by_cdc = 0, then the database is CDC-enabled but that table is not being captured.

3. Missing permissions for CDC configuration or access

Enabling CDC requires elevated privileges. In most cases, the user enabling CDC must be a member of the sysadmin server role or the db_owner database role.

Reading CDC data can also require explicit access depending on the role configured with @role_name when calling sys.sp_cdc_enable_table.

For example:

sql
EXEC sys.sp_cdc_enable_table
   @source_schema = N'dbo',
   @source_name = N'Users',
   @role_name = N'cdc_reader',
   @supports_net_changes = 1;

In this case, users querying the CDC change table or CDC functions may need membership in the cdc_reader role.

If you don't want to gate CDC access behind a role, you can set:

sql
@role_name = NULL

However, in production, using a dedicated CDC reader role is usually safer.

4. Transaction log growth

CDC reads from the SQL Server transaction log. If the CDC capture job falls behind or is not running, SQL Server may be unable to truncate parts of the transaction log that are still needed by CDC.

This can cause transaction log growth, especially on high-write databases.

Monitor log reuse waits with the following command:

sql
SELECT
   name,
   log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();

If you see REPLICATION, then it may indicate that CDC, replication, or another log reader is preventing log truncation. You should also monitor capture job latency and ensure that the CDC job keeps up with the write volume.

For information about all CDC cleanup or capture jobs in the current database:

sql
EXEC sys.sp_cdc_help_jobs;

5. Schema changes are not always captured the way you expect

CDC captures row-level data changes, but schema evolution requires careful handling.

For example, if you add a column to a CDC-enabled table, the existing CDC capture instance may not automatically behave the way downstream consumers expect. CDC capture instances are based on the table schema at the time capture is enabled.

Common schema-change issues include:

For production systems, schema changes should be coordinated with downstream consumers. In some cases, you may need to disable and re-enable CDC for the table, creating a new capture instance.

Check active capture instances with:

sql
EXEC sys.sp_cdc_help_change_data_capture
   @source_schema = N'dbo',
   @source_name = N'Users';

Estuary handles all schema evolution in a completely automated manner, so you don't have to worry about any schema changes breaking your pipelines.

6. CDC is not the same as Change Tracking 

SQL Server has both Change Data Capture and Change Tracking (CT), but they are not interchangeable.

CDC is the right choice when downstream systems need a detailed stream of inserts, updates, and deletes. Change Tracking is better when an application only needs to know which rows changed and can query the current table state separately.

How to stream SQL Server CDC data with Estuary

Estuary is a real-time data integration platform for building scalable pipelines from operational systems into warehouses, lakes, analytics platforms, and other destinations. For SQL Server, Estuary supports multiple capture methods: Change Data Capture (CDC), Change Tracking, and Batch Query capture.

This section focuses on Estuary’s SQL Server CDC connector, which uses SQL Server’s native CDC functionality to capture inserts, updates, and deletes from source tables and stream those changes into Estuary collections.

NOTE: If you prefer working from an AI coding assistant instead of configuring everything manually in the Estuary UI, you can also use Estuary Agent Skills. These skills give AI agents the context they need to create, troubleshoot, and operate Estuary pipelines with flowctl, including SQL Server CDC captures.

Estuary’s SQL Server CDC connector supports the following: self-hosted SQL Server, Azure SQL Database, Amazon RDS for SQL Server, and Google Cloud SQL for SQL Server.

The connector is designed for SQL Server databases with CDC support and is regularly tested against SQL Server 2017 and later.

Before creating the capture in Estuary, make sure CDC is enabled on the source database and on each table you want to capture. You’ll also need to create a dedicated SQL Server user for the Estuary connector.

Configure SQL Server for Estuary CDC

The following example assumes:

• The source tables are in the dbo schema.
• You want Estuary to capture changes from a table named Users.
• The connector user is named flow_capture.

Start by enabling CDC on the database:

sql
USE <database_name>;

GO


EXEC sys.sp_cdc_enable_db;

GO

Next, create a dedicated login and database user for Estuary:

sql
CREATE LOGIN flow_capture WITH PASSWORD = 'secret';

CREATE USER flow_capture FOR LOGIN flow_capture;

GO

Grant the connector user read access to the source schema and the CDC schema:

sql
GRANT SELECT ON SCHEMA :: dbo TO flow_capture;

GRANT SELECT ON SCHEMA :: cdc TO flow_capture;

GO

Grant the connector permission to inspect the database state:

sql
GRANT VIEW DATABASE STATE TO flow_capture;

GO

For newer SQL Server versions, your environment may require VIEW DATABASE PERFORMANCE STATE instead:

sql
GRANT VIEW DATABASE PERFORMANCE STATE TO flow_capture;

GO

Now enable CDC on the table you want Estuary to capture:

sql
EXEC sys.sp_cdc_enable_table
   @source_schema = N'dbo',
   @source_name = N'Users',
   @role_name = N'flow_capture';
GO

Repeat this step for every table you want to capture.

You can verify that CDC is enabled for the database with:

sql
SELECT
   name,
   is_cdc_enabled
FROM sys.databases
WHERE name = DB_NAME();

You can also verify that CDC is enabled for the table with:

sql
SELECT
   name,
   is_tracked_by_cdc
FROM sys.tables
WHERE name = 'Users';

If everything is configured correctly, is_cdc_enabled should return 1 for the database, and is_tracked_by_cdc should return 1 for the captured table.

Connect Estuary to SQL Server

After SQL Server is configured, you can create the capture in the Estuary web application.

In Estuary:

1. Go to Sources.
2. Select New Capture.
3. Choose the SQL Server CDC connector.
4. Enter your SQL Server host, port, database name, username, and password.
5. Select the tables you want to capture.
6. Save and publish the capture.

Estuary will use SQL Server’s CDC change tables to read committed inserts, updates, and deletes from the selected source tables and stream them into Flow collections.

From there, you can materialize the captured data into destinations such as Snowflake, BigQuery, PostgreSQL, Databricks, Apache Iceberg, Redshift, ClickHouse, or other supported systems.

When to use Estuary’s other SQL Server capture options

CDC is the best fit when you need a high-fidelity stream of inserts, updates, and deletes from SQL Server. However, Estuary also supports other SQL Server capture modes.

For most production CDC use cases, start with the SQL Server CDC connector. Use Change Tracking when you need lower source overhead and do not need full change history. Use Batch Query when you need to capture from views or run custom SQL on a schedule.

And that’s it. Estuary can now connect with your SQL Server.

Conclusion

CDC is a powerful technique that replicates changes from data sources to target systems, enabling efficient data analysis and accurate, time-sensitive business decisions.

However, managing all that data can be next to impossible without good resources. That's where GUI tools and database engines come into the scene. Microsoft SQL Server is a terrific database engine, and it supports CDC alongside other performance-intensive operations.

In this article, we looked at how to set up SQL Server change data capture. We also discussed how to set up Microsoft SQL Server CDC for use with Estuary, a compelling option for setting real-time change data capture pipelines. Sign up for a free account and get started with your real-time data processing journey today.

For more on this subject, check out our Complete Guide to Change Data Capture in SQL Server!