How to use CDC (Change Data Capture) in SQL Server

2025.08.02

1. What is CDC?

Change Data Capture (CDC) is a SQL Server feature that records insert, update, and delete operations on tables, enabling non-intrusive change tracking. This makes it ideal for data synchronization, replication, and audit scenarios. For a detailed overview of CDC internals, refer to this explanation on dbapark.com.

2. Preparing the Database and Tables

Database Requirements

  • SQL Server Agent must be running
  • SQL Server edition must support CDC (e.g., Standard edition or higher)

Table Requirements

  • A primary key is strongly recommended (technically optional, but practically essential for identifying rows during synchronization)

Creating the Source and Target Database

-- Create the databases to hold source and target data
CREATE DATABASE SourceDB;
GO
CREATE DATABASE TargetDB;
GO

Sample Table Setup

-- Define the Employee table in both databases for synchronization
USE SourceDB;
GO
CREATE TABLE dbo.Employee (
    EmployeeId INT PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(50)
);

USE TargetDB;
GO
CREATE TABLE dbo.Employee (
    EmployeeId INT PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(50)
);

3. Enabling CDC

-- Enable CDC on the source database and table
USE SourceDB;
GO
EXEC sys.sp_cdc_enable_db;

-- Track changes on the Employee table
EXEC sys.sp_cdc_enable_table
  @source_schema = 'dbo',
  @source_name = 'Employee',
  @role_name = NULL;

-- Create LSN tracking table for app-side control
CREATE TABLE dbo.MyApp_LSNTracking (
    job_name NVARCHAR(100) PRIMARY KEY,
    last_lsn BINARY(10),
    last_updated DATETIME DEFAULT GETDATE()
);

-- Initialize LSN tracking with the earliest known LSN
INSERT INTO dbo.MyApp_LSNTracking (job_name, last_lsn)
VALUES ('cdc_sync_employee', sys.fn_cdc_get_min_lsn('dbo_Employee'));

4. Making Changes and Capturing CDC Output

-- Perform sample data operations on source
USE SourceDB;
GO

-- INSERT a new employee
INSERT INTO dbo.Employee (EmployeeId, Name, Department)
VALUES (1, 'Emily Johnson', 'HR');

-- UPDATE their department
UPDATE dbo.Employee
SET Department = 'Finance'
WHERE EmployeeId = 1;

-- DELETE the employee to simulate a full lifecycle
DELETE FROM dbo.Employee WHERE EmployeeId = 1;

-- Add a new record so the table is not empty after delete
INSERT INTO dbo.Employee (EmployeeId, Name, Department)
VALUES (2, 'John Smith', 'Sales');

-- Wait a few seconds then capture all changes using CDC
DECLARE @from_lsn BINARY(10) = sys.fn_cdc_get_min_lsn('dbo_Employee');
DECLARE @to_lsn BINARY(10) = sys.fn_cdc_get_max_lsn();

-- View CDC tracked changes
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Employee(@from_lsn, @to_lsn, 'all');

5-1. Getting Changes Based on Time

-- Get changes from today (UTC 00:00) until now
DECLARE @begin_lsn BINARY(10);
DECLARE @end_lsn BINARY(10);

DECLARE @from_time DATETIME = CONVERT(DATE, SYSUTCDATETIME());
DECLARE @to_time   DATETIME = SYSUTCDATETIME();

-- Convert time to LSN
SET @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @from_time);
SET @end_lsn   = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @to_time);

-- Fall back if NULL (e.g., no changes yet)
IF @begin_lsn IS NULL
    SET @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_Employee');
IF @end_lsn IS NULL
    SET @end_lsn = sys.fn_cdc_get_max_lsn();

-- Return changes
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Employee(@begin_lsn, @end_lsn, 'all');

5-2. Getting Changes Based on LSN

-- Use previously stored LSN and current max to pull deltas
DECLARE @from_lsn BINARY(10);
DECLARE @to_lsn BINARY(10);

SELECT TOP 1 @from_lsn = last_lsn 
FROM MyApp_LSNTracking 
WHERE job_name = 'cdc_sync_employee'
ORDER BY last_updated DESC;

SET @to_lsn = sys.fn_cdc_get_max_lsn();

-- View changes since last recorded LSN
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Employee(@from_lsn, @to_lsn, 'all');

6-1. Syncing Based on Time and Verifying Results

-- Use time window to get changes and sync to target table
DECLARE @begin_lsn BINARY(10);
DECLARE @end_lsn BINARY(10);
DECLARE @from_time DATETIME = CONVERT(DATE, SYSUTCDATETIME());
DECLARE @to_time   DATETIME = SYSUTCDATETIME();
SET @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @from_time);
SET @end_lsn   = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @to_time);
IF @begin_lsn IS NULL SET @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_Employee');
IF @end_lsn IS NULL SET @end_lsn = sys.fn_cdc_get_max_lsn();

-- Clean up temp table if it exists
IF OBJECT_ID('tempdb..#ChangesToSync') IS NOT NULL DROP TABLE #ChangesToSync;

-- Collect insert/update/delete records from CDC
WITH Changes AS (
  SELECT *
  FROM cdc.fn_cdc_get_all_changes_dbo_Employee(@begin_lsn, @end_lsn, 'all')
  WHERE __$operation IN (1, 2, 4)
)
SELECT * INTO #ChangesToSync FROM Changes ORDER BY __$start_lsn, __$seqval;

-- INSERT new rows (skip duplicates)
INSERT INTO TargetDB.dbo.Employee (EmployeeId, Name, Department)
SELECT c.EmployeeId, c.Name, c.Department
FROM #ChangesToSync c
WHERE c.__$operation = 2
AND NOT EXISTS (
    SELECT 1 FROM TargetDB.dbo.Employee t WHERE t.EmployeeId = c.EmployeeId);

-- UPDATE modified rows
UPDATE target
SET target.Name = c.Name, target.Department = c.Department
FROM TargetDB.dbo.Employee target
JOIN #ChangesToSync c ON c.EmployeeId = target.EmployeeId
WHERE c.__$operation = 4;

-- DELETE removed rows
DELETE target
FROM TargetDB.dbo.Employee target
JOIN #ChangesToSync c ON c.EmployeeId = target.EmployeeId
WHERE c.__$operation = 1;

-- VERIFY the sync result
SELECT * FROM SourceDB.dbo.Employee;
SELECT * FROM TargetDB.dbo.Employee;

6-2. Syncing Based on LSN and Verifying Results

-- FULL RESET: Clear CDC objects and tables
USE SourceDB;
GO
EXEC sys.sp_cdc_disable_table @source_schema='dbo', @source_name='Employee', @capture_instance='dbo_Employee';
GO
DROP TABLE IF EXISTS dbo.Employee;
DROP TABLE IF EXISTS dbo.MyApp_LSNTracking;
DELETE FROM TargetDB.dbo.Employee;
GO

-- RECREATE source table and CDC tracking
CREATE TABLE dbo.Employee (
    EmployeeId INT PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(50)
);
GO
CREATE TABLE dbo.MyApp_LSNTracking (
    job_name NVARCHAR(100) PRIMARY KEY,
    last_lsn BINARY(10),
    last_updated DATETIME DEFAULT GETDATE()
);
GO
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'Employee', @role_name = NULL;

-- Insert sample records
INSERT INTO dbo.Employee (EmployeeId, Name, Department)
VALUES (1, 'Emily Johnson', 'HR'), (2, 'John Smith', 'Sales');

-- Record initial sync point
INSERT INTO dbo.MyApp_LSNTracking (job_name, last_lsn)
VALUES ('cdc_sync_employee', sys.fn_cdc_get_min_lsn('dbo_Employee'));

-- Run the sync again
DECLARE @from_lsn BINARY(10);
DECLARE @to_lsn BINARY(10);

SELECT TOP 1 @from_lsn = last_lsn 
FROM MyApp_LSNTracking 
WHERE job_name = 'cdc_sync_employee'
ORDER BY last_updated DESC;

SET @to_lsn = sys.fn_cdc_get_max_lsn();

IF OBJECT_ID('tempdb..#ChangesToSync') IS NOT NULL
    DROP TABLE #ChangesToSync;

-- Extract CDC changes
WITH Changes AS (
  SELECT *
  FROM cdc.fn_cdc_get_all_changes_dbo_Employee(@from_lsn, @to_lsn, 'all')
  WHERE __$operation IN (1, 2, 4)
)
SELECT * INTO #ChangesToSync FROM Changes ORDER BY __$start_lsn, __$seqval;

-- Apply INSERTS
INSERT INTO TargetDB.dbo.Employee (EmployeeId, Name, Department)
SELECT c.EmployeeId, c.Name, c.Department
FROM #ChangesToSync c
WHERE c.__$operation = 2
AND NOT EXISTS (
    SELECT 1 FROM TargetDB.dbo.Employee t WHERE t.EmployeeId = c.EmployeeId);

-- Apply UPDATES
UPDATE target
SET target.Name = c.Name, target.Department = c.Department
FROM TargetDB.dbo.Employee target
JOIN #ChangesToSync c ON c.EmployeeId = target.EmployeeId
WHERE c.__$operation = 4;

-- Apply DELETES
DELETE target
FROM TargetDB.dbo.Employee target
JOIN #ChangesToSync c ON c.EmployeeId = target.EmployeeId
WHERE c.__$operation = 1;

-- Update the tracked LSN after sync
UPDATE MyApp_LSNTracking 
SET last_lsn = @to_lsn, last_updated = GETDATE()
WHERE job_name = 'cdc_sync_employee';

-- VERIFY
SELECT * FROM SourceDB.dbo.Employee;
SELECT * FROM TargetDB.dbo.Employee;

Conclusion

CDC is a powerful feature for capturing incremental changes and synchronizing data across systems. By managing LSNs or using time-based windows, you can implement efficient and reliable data movement for applications, reports, or ETL workflows. Adding meaningful comments in your SQL not only improves maintainability, but helps readers (and future you) understand the intent and flow of change tracking logic.