Information2 use cookies to help you have a superior and more admissible browsing experience on our website. Privacy Policy
Loading...
By:
SQL Server Change Data Capture (CDC) is a built-in feature that records INSERT, UPDATE, and DELETE operations on database tables. It captures changes asynchronously from the transaction log and stores them in change tables, allowing applications to access incremental data without scanning entire tables.
For data engineers and DBAs, CDC in SQL Server provides an efficient way to support data synchronization, ETL workflows, and analytics by tracking only the data that has changed.
SQL Server CDC captures three main types of data changes:
For example, when an employee’s salary changes from $75,000 to $80,000, CDC records both the previous and updated values in a change table. This allows downstream systems to identify what changed and when the change occurred.
Understanding how SQL Server CDC works makes it easier to configure and manage. After CDC is enabled, data changes are captured asynchronously through a four-step process with minimal impact on normal database operations.
Whenever data is inserted, updated, or deleted, SQL Server records the operation in the transaction log. Instead of monitoring user queries or using triggers, CDC reads these existing log records to capture data changes.
After CDC is enabled, SQL Server creates a capture job that continuously scans the transaction log for committed changes. Running in the background, it processes changes asynchronously with minimal impact on write performance.
The capture job writes the detected changes to system-managed change tables in the cdc schema, such as cdc.dbo_TableName_CT. Each record includes the changed data along with metadata like the Log Sequence Number (LSN) and operation type.
Applications, ETL tools, and replication services can retrieve incremental changes from the CDC change tables. Microsoft recommends using the built-in CDC functions, such as fn_cdc_get_all_changes and fn_cdc_get_net_changes, instead of querying the change tables directly.
Enabling SQL Server Change Data Capture (CDC) involves three steps: verifying the prerequisites, enabling CDC at the database level, and then enabling it for the tables you want to track.
Before enabling CDC, make sure your environment meets these requirements:
db_owner permissions on the target database or sysadmin server-level privileges. net changes function.Before you can track table changes, enable CDC for the database.
USE YourDatabaseName;
GO
-- Check whether CDC is enabled
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = DB_NAME();
-- Enable CDC
EXEC sys.sp_cdc_enable_db;
GO
After enabling CDC for the database, you can enable it for individual tables.
USE YourDatabaseName;
USE YourDatabaseName;
GO
-- Create a sample table if it does not exist
IF OBJECT_ID('dbo.Employees', 'U') IS NULL
BEGIN
CREATE TABLE dbo.Employees (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary DECIMAL(18,2),
LastModified DATETIME DEFAULT GETDATE()
);
END;
GO
-- Enable CDC on the table
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Employees',
@role_name = NULL,
@supports_net_changes = 1;
GO
Run the following queries to verify that CDC has been enabled successfully.
-- Verify that the table is tracked by CDC
SELECT name, is_tracked_by_cdc
FROM sys.tables
WHERE name = 'Employees';
-- Verify that the capture and cleanup jobs exist
EXEC sys.sp_cdc_help_jobs;
If CDC is enabled successfully, is_tracked_by_cdc returns 1, and both the capture and cleanup jobs are listed.
SQL Server provides built-in functions for querying captured changes instead of accessing the CDC change tables directly.
Understanding Log Sequence Numbers (LSNs)
Each transaction in SQL Server is identified by a Log Sequence Number (LSN). To query changes within a specific time range, first convert the start and end timestamps into LSN values.
DECLARE @from_lsn BINARY(10), @to_lsn BINARY(10);
-- Map a time range to LSN values
SET @from_lsn = sys.fn_cdc_map_time_to_lsn(
'smallest greater than or equal',
'2026-07-15 08:00:00'
);
SET @to_lsn = sys.fn_cdc_map_time_to_lsn(
'largest less than or equal',
'2026-07-15 17:00:00'
);
Using fn_cdc_get_all_changes
Use fn_cdc_get_all_changes to return every INSERT, UPDATE, and DELETE operation within an LSN range. This function is commonly used for auditing and detailed change tracking.
SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_Employees(
@from_lsn,
@to_lsn,
'all'
);
Using fn_cdc_get_net_changes
Use fn_cdc_get_net_changes when you only need the final state of each modified row within the selected LSN range.
SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_Employees(
@from_lsn,
@to_lsn,
'all'
);
Understanding CDC Operation Codes
The __$operation column identifies the type of data change returned by the CDC functions.
| Operation Code | Operation Type | Description |
|---|---|---|
| 1 | Delete | The row was deleted. |
| 2 | Insert | A new row was inserted. |
| 3 | Update (Before) | The row values before the update. |
| 4 | Update (After) | The row values after the update. |
SQL Server provides two built-in features for tracking data changes: Change Data Capture (CDC) and Change Tracking (CT). Although both record data modifications, they serve different purposes. CDC captures detailed change history, while Change Tracking simply identifies which rows have changed.
| Feature | Change Data Capture (CDC) | Change Tracking (CT) |
|---|---|---|
| Primary Purpose | Captures detailed change history for auditing and ETL. | Tracks changed rows for data synchronization. |
| Captured Data | Stores the complete changed row. | Stores only the primary key and change information. |
| Old and New Values | Captures both before and after values. | Does not store historical values. |
| Performance Impact | Runs asynchronously with minimal impact on transactions. | Tracks changes during transactions with low overhead. |
| Storage | Higher storage usage. | Lower storage usage. |
| Best For | ETL, analytics, auditing, and data replication. | Application and client synchronization. |
| Audit Capability | Supports complete change history. | Does not support historical auditing. |
In short, choose CDC when you need detailed change history, and choose Change Tracking when you only need to identify changed rows efficiently.
When Should You Choose CDC?
Choose SQL Server CDC when you need a complete history of data changes rather than just the latest state. It is well suited for ETL pipelines, data warehouses, real-time analytics, and streaming platforms where every INSERT, UPDATE, and DELETE needs to be captured.
When Is Change Tracking a Better Choice?
Choose Change Tracking when you only need to know which rows have changed since the last synchronization. Because it stores less information than CDC, it uses less storage and is better suited for lightweight synchronization between applications and SQL Server.
Unlike CDC, Change Tracking does not preserve historical updates. If a row is updated multiple times before synchronization, only its latest state is available.
Although SQL Server CDC is a powerful feature, it has several limitations that should be considered before using it in production.
CDC relies on SQL Server Agent to run the capture and cleanup jobs. If the service stops, changes are no longer processed, which can prevent the transaction log from being truncated and cause it to grow over time.
CDC stores copies of changed data in system change tables under the cdc schema. In databases with frequent updates or large columns, these tables can grow quickly, so sufficient storage should be planned.
By default, captured changes are retained for 72 hours (3 days) before the cleanup job removes them.
If downstream applications or ETL processes do not read the changes within this period, the data is permanently deleted. You can increase the retention period, but doing so also increases storage usage.
Some schema changes are not handled automatically by CDC and may require additional configuration.
Enabling and managing CDC requires sysadmin or db_owner permissions. Access to captured data can be restricted by configuring a gating role when CDC is enabled.
CDC is supported in SQL Server Developer, Enterprise, and Standard editions (SQL Server 2016 SP1 and later). It is not available in SQL Server Express.
CDC is built for tracking changes inside a single database, not for keeping two databases in sync in real time. Its capture job runs on a schedule, which means there is always some delay between a change happening and that change showing up in the change tables. For auditing or ETL pipelines, that delay is fine. For disaster recovery, cross-database sync, or feeding a reporting system that needs fresh data, it becomes a limitation.
This is where a dedicated database replication tool like i2Stream fits in. Instead of periodically capturing changes into tables within the same database, i2Stream reads the transaction log directly and streams those changes to a target database in real time, whether that target sits on the same platform or a different one entirely.
i2Stream comes with several features that address the exact gaps CDC leaves open:
If your use case is closer to keeping two databases continuously synchronized, whether for disaster recovery, migration, or automatic failover, i2Stream extends what CDC starts, turning periodic change capture into continuous, cross-platform replication.
Info2Soft also offers other solutions for data resilience depending on what you’re protecting. i2Backup handles centralized backup across databases, VMs, and physical servers, while i2Availability provides real-time replication built specifically for high-availability failover scenarios.
Following a few best practices can help improve the performance, reliability, and maintainability of SQL Server CDC in production environments.
@captured_column_list parameter to reduce the size of the change tables.EXEC sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 5760;
GO
EXEC sys.sp_cdc_stop_job @job_type = N'cleanup';
EXEC sys.sp_cdc_start_job @job_type = N'cleanup';
GO
fn_cdc_get_all_changes and fn_cdc_get_net_changes. These functions provide a consistent way to retrieve changes within a specified LSN range.SELECT
session_id,
start_time,
end_time,
duration,
scan_phase,
log_record_count,
latency
FROM sys.dm_cdc_log_scan_sessions;
If capture latency continues to increase, consider adjusting the @maxtrans or @maxscans settings for the capture job to process more transactions during each scan.
Change Data Capture gives SQL Server a built-in way to track row-level changes for auditing, ETL, and incremental sync, without building custom triggers or timestamp logic. Enabling it takes just a few steps, but getting the most out of it means understanding its retention limits, storage impact, and how it differs from Change Tracking.
If your needs go beyond capturing changes and into keeping databases continuously synchronized across platforms, a real-time replication tool like Info2soft’s i2Stream is worth exploring next.
