Implementing Change Data Capture (CDC) and Syncing Two Databases (SQL Server + .NET)

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 :

Enabling CDC in SQL Server
CDC queries
Syncing logic in .NET
Stored procedures
Architecture diagram
Sequence diagram
Best practices

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:

CDC change table:
cdc.dbo_Customer_CT
CDC system functions:
- cdc.fn_cdc_get_all_changes_dbo_Customer
- cdc.fn_cdc_get_net_changes_dbo_Customer

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

BackgroundWorker / HostedService
CDC Repository
Sync Processor
Secondary DB Repository
Logging

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:

Tables exceed heavy write-load + long retention
You need schema-level tracking (use DDL triggers instead)

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.