Change Data Capture (CDC) is one of the most powerful—but underused—features in SQL Server. It allows you to track data changes (INSERT/UPDATE/DELETE) at the table level and store them in system-managed change tables.
In modern distributed architectures, CDC is extremely useful for:
Syncing primary and secondary databases
Real-time reporting
Event-driven systems
Cross-application data sharing
Background notifications and triggers
This article provides a complete implementation :
1. High-Level Architecture Diagram (CDC-Based Sync System)
+-------------------+ Changes +----------------------+
| Primary DB |--------------------->| CDC Change Tables |
| (SQL Server) | (Captured by CDC) | (auto-maintained) |
+-------------------+ +----------+-----------+
|
| Scheduled Fetch (API/Job)
v
+----------------------+
| .NET Sync API |
| (Background Service) |
+----------+-----------+
|
v
+----------------------+
| Secondary Database |
| (SQL / Azure SQL) |
+----------------------+
2. Enabling CDC in SQL Server
Enable CDC at the Database Level
USE MyPrimaryDB;
EXEC sys.sp_cdc_enable_db;
Enable CDC on a Table
Example: Sync Customer table.
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'Customer',
@role_name = NULL,
@supports_net_changes = 1;
SQL Server will now create:
3. What the CDC Table Contains
The change table includes:
| Column | Description |
|---|
| __$operation | 1 = Delete, 2 = Insert, 3/4 = Update |
| __$update_mask | Bitmask of updated columns |
| All actual columns | Snapshot of data |
4. Stored Procedure: Fetch Latest Changes (Incremental)
CREATE PROCEDURE usp_GetCustomerChanges
@from_lsn binary(10),
@to_lsn binary(10)
ASBEGIN
SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_Customer(@from_lsn, @to_lsn, 'all');
END
To get LSN values:
SELECT sys.fn_cdc_get_min_lsn('dbo_Customer');
SELECT sys.fn_cdc_get_max_lsn();
5. Stored Procedure: Update Sync Watermark
Create a log table:
CREATE TABLE SyncWatermark (
TableName VARCHAR(200),
LastLSN VARBINARY(10)
);
SP to update:
CREATE PROCEDURE usp_UpdateSyncWatermark
@TableName VARCHAR(200),
@LastLSN VARBINARY(10)
ASBEGIN
UPDATE SyncWatermark
SET LastLSN = @LastLSN
WHERE TableName = @TableName;
END
6. .NET Sync Architecture
Components
7. Sample .NET Background Service
public class CdcSyncService : BackgroundService
{
private readonly ICdcRepository _cdcRepo;
private readonly ISecondaryDbRepository _targetRepo;
protected override async Task ExecuteAsync(CancellationToken ct)
{
while (!ct.IsCancellationRequested)
{
var fromLsn = await _cdcRepo.GetLastSyncedLsn();
var toLsn = await _cdcRepo.GetMaxLsn();
var changes = await _cdcRepo.GetCustomerChanges(fromLsn, toLsn);
foreach (var row in changes)
{
switch (row.Operation)
{
case 1: await _targetRepo.DeleteCustomer(row.Id); break;
case 2: await _targetRepo.InsertCustomer(row); break;
case 3:
case 4: await _targetRepo.UpdateCustomer(row); break;
}
}
await _cdcRepo.UpdateWatermark(toLsn);
await Task.Delay(TimeSpan.FromSeconds(10), ct);
}
}
}
8. Repository Example (CDC)
public async Task<IEnumerable<CustomerChange>> GetCustomerChanges(byte[] from, byte[] to)
{
using var conn = new SqlConnection(_connStr);
return await conn.QueryAsync<CustomerChange>(
"usp_GetCustomerChanges",
new { from_lsn = from, to_lsn = to },
commandType: CommandType.StoredProcedure
);
}
9. Sequence Diagram (CDC Sync Flow)
Client/Job Scheduler
|
v
.NET Sync Background Service
|
|----- GetLastLSN() --------------------> Primary DB
|
|----- GetChanges(from,to) -------------> CDC Functions
|
|<---- Change Rows ----------------------
|
|----- ApplyChanges() ------------------> Secondary DB
|
|----- UpdateLSN() ---------------------> Primary DB
|
v
Process repeats every X seconds
10. Handling Conflicts
Recommended Approaches:
Use timestamps ( rowversion )
Use soft deletes
Maintain change priorities (Primary always wins)
Maintain a SyncHistory table
11. Best Practices for CDC
| Topic | Best Practice |
|---|
| Retention | Increase retention if sync is less frequent |
| Cleanup | Use sp_cdc_cleanup_change_table |
| Indexing | Make sure primary keys are indexed |
| Monitoring | Check for disabled SQL Agent jobs |
| Security | Restrict access to CDC tables |
12. When to Use CDC
Ideal scenarios:
Multi-tenant system syncing data
Offline/edge device replication
Integration between two applications
Event-driven microservices
Data warehousing
Avoid CDC when:
13. Conclusion
Implementing CDC with SQL Server and .NET gives you a high-performance, low-intrusion way to trigger near-real-time data synchronization between databases. With a smart combination of:
CDC change tables
Watermark tracking
Background sync services
Stored procedures
You can build a fully automated, self-healing, low-maintenance sync engine.