s4ndr0ne.SqlCdc 3.1.0

SqlCdc

Real-time Change Data Capture (CDC) for SQL Server, built on top of native CDC. The package polls capture instances (cdc.fn_cdc_get_all_changes_*), reconstructs events (insert/update/delete with before/after images) and delivers them over a System.Threading.Channels.Channel<CdcChange>.

Features

• Native SQL Server CDC: no extra columns added to source tables.
• Rich events: before/after image, operation type, LSN, commit time, and update mask.
• Persistent LSN watermark: the watcher resumes exactly where it left off.
• Bounded channel with backpressure: a slow consumer stalls the poller instead of losing events.
• Single active instance by default: leader election over a SQL application lock, so a multi-replica deployment does not emit every change N times.
• At-least-once end to end: by default the watermark advances only once changes have actually been acknowledged.
• Observable: Meter and ActivitySource for OpenTelemetry, plus a health check that knows the difference between "idle" and "stuck".
• Handler retry and dead-letter queue: a failing change is retried and then parked for inspection instead of vanishing into a log line.
• Configurable from IConfiguration, with Entra ID / Managed Identity and a connection factory for anything the connection string cannot express.
• Fluent API (SqlCdcWatcherBuilder).

Prerequisites

• SQL Server (2016+) with CDC enabled on the database and the target tables:

-- Requires sysadmin or db_owner
EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table
     @source_schema = N'dbo',
     @source_name   = N'Orders',
     @role_name     = NULL;

Usage

using SqlCdc;

var watcher = SqlCdcWatcherBuilder
    .Create()
    .UseConnectionString("Server=.;Database=MyDb;TrustServerCertificate=True")
    .WatchTable("dbo", "Orders")
    .WatchTable("dbo", "Customers")
    .WithPollInterval(TimeSpan.FromMilliseconds(250))
    .Build();

await watcher.StartAsync(cts.Token);

await foreach (var change in watcher.Changes.WithCancellation(cts.Token))
{
    Console.WriteLine($"{change.TableName} {change.Operation}");
    foreach (var (column, value) in change.After)
        Console.WriteLine($"  {column} = {value}");
    change.Acknowledge();
}

Each run delivers on a fresh channel: Changes completes on StopAsync, and a watcher that is started again must be enumerated again — a cached enumerable (or Channel reference) from before the restart no longer receives anything. The token passed to StartAsync only bounds the startup work (resolving capture instances); cancelling it later does not stop the watcher. Stop it with StopAsync or by disposing it, as the sample does on Ctrl+C.

ASP.NET Core / Generic Host

builder.Services.AddSqlCdc(cdc => cdc
    .UseConnectionString(connectionString)
    .WatchTable("dbo", "Orders")
    .UseStateStore(new SqlCdcStateStore(connectionString)));

builder.Services.AddCdcChangeHandler<OrderChangedHandler>();
builder.Services.AddCdcDeadLetterSink(new SqlCdcDeadLetterSink(connectionString));

AddSqlCdc registers the watcher as a singleton and an IHostedService that starts it with the host and stops it on shutdown. The logger and ICdcStateStore are resolved from the container when available; settings configured inside the delegate take precedence.

public sealed class OrderChangedHandler(AppDbContext db) : ICdcChangeHandler
{
    public async Task HandleAsync(CdcChange change, CancellationToken ct = default)
    {
        // ...
    }
}

Each handler is scoped and resolved in a dedicated scope per event, so injecting a DbContext is safe. All registered handlers receive every event in registration order. A handler that throws is retried according to WithHandlerRetry and then dead-lettered — see Handler failures.

When no handlers are registered the watcher is still started by the host, and restarted if its polling loop crashes. Inject SqlCdcWatcher and consume Changes directly; after a restart enumerate Changes again, since each run delivers on a fresh channel.

Configuration

Everything can come from a configuration section instead of code:

builder.Services.AddSqlCdc(builder.Configuration.GetSection("SqlCdc"));

{
  "SqlCdc": {
    "ConnectionString": "Server=.;Database=Sales;Authentication=Active Directory Default;Encrypt=True",
    "Name": "sales",
    "Tables": [
      { "Schema": "dbo", "Table": "Orders" },
      { "Schema": "dbo", "Table": "Customers", "CaptureInstance": "dbo_Customers_v2" }
    ],
    "PollInterval": "00:00:00.500",
    "BatchSize": 1000,
    "ChannelCapacity": 100000,
    "StartMode": "FromNow",              // FromNow | FromBeginning
    "CheckpointMode": "OnAcknowledgement", // OnEmit | OnAcknowledgement
    "RetryDelay": "00:00:05",
    "CommandTimeout": "00:00:30",
    "LeaseName": "sales",                 // defaults to Name; "SingleActiveInstance": false turns election off
    "LeaseRetryDelay": "00:00:10",
    "LeaseKeepaliveInterval": "00:00:10",
    "MaxHandlerAttempts": 3,
    "HandlerRetryDelay": "00:00:01"
  }
}

Schema defaults to dbo. Any setting left out keeps its default, so a section can be as small as a connection string and a table. Mixing is fine — the section is applied first and the optional delegate second, so code wins:

builder.Services.AddSqlCdc(
    builder.Configuration.GetSection("SqlCdc"),
    cdc => cdc.UseStateStore(new SqlCdcStateStore(connectionString)));

Configuration errors surface when the host starts, not at the first poll: a section that does not exist, a table without a name, or a missing connection string all fail StartAsync.

Connections and authentication

Authentication=Active Directory Default (or ... Managed Identity, ... Workload Identity) in the connection string is handled by Microsoft.Data.SqlClient and needs nothing from this package.

When the application wants to own how connections are made — a configured TokenCredential, a SqlRetryLogicBaseProvider for Azure SQL's transient faults, a custom SqlConnectionStringBuilder per tenant — supply a factory. Everything then goes through it: CDC reads, the watermark table, the dead-letter table and the lease.

builder.Services.AddSqlCdc(cdc => cdc
    .UseConnectionFactory(async ct =>
    {
        var connection = new SqlConnection(connectionString)
        {
            RetryLogicProvider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(retryOptions),
        };
        await connection.OpenAsync(ct);
        return connection;
    })
    .WatchTable("dbo", "Orders"));

The factory must hand out a new connection per call — they are opened and disposed per operation. Returning a connection that is not open yet is fine; it gets opened for you.

To own only token acquisition, keep the connection string and add a callback:

var credential = new DefaultAzureCredential();

builder.Services.AddSqlCdc(cdc => cdc
    .UseConnectionString(connectionString)
    .UseAccessTokenCallback(async (parameters, ct) =>
    {
        var token = await credential.GetTokenAsync(
            new TokenRequestContext(["https://database.windows.net/.default"]), ct);
        return new SqlAuthenticationToken(token.Token, token.ExpiresOn);
    })
    .WatchTable("dbo", "Orders"));

Registering an ICdcConnectionFactory in DI is picked up by AddSqlCdc automatically, and SqlCdcStateStore, SqlCdcDeadLetterSink and SqlApplicationLockLeaseProvider all accept one in their constructor.

Event model

record CdcChange
{
    string CaptureInstance;
    string SourceSchema, SourceTable;
    CdcOperationType Operation;        // Insert | Update | Delete
    byte[] StartLsn, SeqVal;
    DateTime CommitTime;
    IReadOnlyDictionary<string, object?> Before;
    IReadOnlyDictionary<string, object?> After;
    IReadOnlyDictionary<string, bool> UpdateMask;  // changed columns (update only)
    string Key;                                     // stable per-change identifier
}

Options

Delivery semantics

Batches are always cut on a transaction (LSN) boundary so the before and after images of an update stay together. For this reason WithBatchSize is a soft cap: a single transaction larger than the batch size is read in full (with a warning logged).

The channel is bounded: if the consumer falls behind, the poller blocks (BoundedChannelFullMode.Wait). Consumers should deduplicate using CdcChange.Key: in both checkpoint modes a batch can be re-emitted after a restart.

The pipeline understands only the operation values SQL Server emits (__$operation 1-4: insert, delete, update before/after image). An unsupported operation or an unpaired update image fails the poll of that capture instance without advancing its checkpoint: the failure counts in sqlcdc.poll.failures, the health check turns unhealthy after a few retries, and the rows stay where they are for investigation instead of being silently lost. The other tables keep polling.

Checkpoint mode

WithCheckpointMode decides when the watermark LSN is persisted, which is what a restart resumes from.

OnAcknowledgement is the default for at-least-once delivery end to end. Choose OnEmit only when losing an in-flight change is acceptable:

builder.Services.AddSqlCdc(cdc => cdc
    .UseConnectionString(connectionString)
    .WatchTable("dbo", "Orders")
    .WithCheckpointMode(CdcCheckpointMode.OnEmit));

Registered ICdcChangeHandlers are acknowledged automatically, once every handler has been given the change. Consuming watcher.Changes directly means acknowledging by hand:

await foreach (var change in watcher.Changes.WithCancellation(ct))
{
    await ProcessAsync(change);
    change.Acknowledge();   // required: without it the watermark never advances
}

Polling pauses at the batch boundary until the batch is acknowledged, so throughput follows the consumer. A change that is never acknowledged stalls polling for that table, and the stall is logged every 30 seconds rather than left silent.

Note that acknowledgement covers crashes, not handler bugs: a handler that has used up its attempts still has its change acknowledged, once the change is in the dead-letter sink, so one bad event cannot block the pipeline — see Handler failures.

Running more than one instance

By default the watcher elects one active instance among the replicas of the application, on a lease named after the watcher (WithName, default unless set). Watchers with different names therefore never contend, and replicas of the same application, which share the name, elect one leader between them. Nothing needs configuring for the common case:

builder.Services.AddSqlCdc(cdc => cdc
    .UseConnectionString(connectionString)
    .WatchTable("dbo", "Orders")
    .WithName("sales"));           // the lease is SqlCdc:sales; the state store is shared via the database

To share a lease across differently named watchers, give it a name explicitly with UseSingleActiveInstance("orders"). A deployment that runs exactly one instance can turn election off with WithoutSingleActiveInstance() (or "SingleActiveInstance": false in configuration) and save the dedicated lease connection — with two instances that is a duplicate stream and a watermark fight, so leave it on unless you are sure.

The lease is a session-scoped SQL Server application lock (sp_getapplock) held on a dedicated connection to the watched database. SQL Server drops it as soon as that connection goes away, so a crashed, killed or partitioned instance loses the lease on its own: there is no expiry to tune and no clock to keep in sync, and two instances can never poll at the same time.

The active instance verifies it still holds the lease every WithLeaseKeepaliveInterval (10 seconds by default) and keeps polling in between, so the keepalive does not add a round-trip to every polling cycle. The check runs between tables and between batches as well as at the top of the cycle, so a long wait on a slow consumer is followed by a real verification before the next batch is read. A lost lease can therefore go unnoticed for up to one interval; during that window the monotonic watermark keeps the old and the new leader from rewinding each other, and delivery stays at-least-once. The corollary of at-least-once is that the same change can be delivered more than once — during a takeover, or when a batch is replayed after a crash — so handlers and direct consumers must be idempotent.

Standby instances retry every WithLeaseRetryDelay and expose SqlCdcWatcher.IsLeader. On taking over, a standby reloads the watermarks from the shared state store and continues from where the previous leader had checkpointed. A graceful shutdown releases the lease, so failover is immediate rather than waiting for SQL Server to notice a dead session.

A standby that acquires the lease but then cannot read the watermarks — the shared state store's database is down, for instance — releases the lease again instead of holding it while delivering nothing. Another instance can take over, and the failed one keeps retrying from standby.

For a different election mechanism altogether, implement ICdcLeaseProvider and pass it with UseLeaseProvider (or register it in DI, where AddSqlCdc picks it up automatically). The lease connection is unpooled, and its pool is cleared when the lease is dropped, so a custom connection factory handing out pooled connections cannot leave the session lock lingering in an idle pool.

CDC retention

SQL Server's cleanup job trims change tables beyond the configured retention window (3 days by default). If the service is down longer than that, the saved watermark points to rows that no longer exist: the watcher resumes from the oldest available LSN and logs a warning. Changes in between are lost — a data loss that is explicit rather than a silent error loop. A running watcher keeps every table's watermark inside the retained range even when the table has no changes (empty polls persist the current end of the log, at most once every few minutes per table), so the warning only ever means an outage longer than the retention window. For longer outage windows, increase the retention: EXEC sys.sp_cdc_change_job @job_type='cleanup', @retention=<minutes>.

Error isolation

Each table has independent error state: if one capture instance fails (permissions, CDC disabled, table dropped), the others continue polling normally. The failing instance is retried after WithRetryDelay, not on every polling cycle, and the delay doubles with each consecutive failure up to 5 minutes — so a capture instance that stays broken for hours is probed (and logged) every few minutes rather than hammered on a fixed schedule. The first successful poll resets the backoff.

Capture instances and schema migrations

SQL Server allows two capture instances per table, which is how a column is added without stopping capture: create a second instance, let consumers catch up, drop the first.

While both exist, the watcher reads the older one — the instance the stored watermark belongs to — and logs a warning naming both. Switching is an explicit operational step, not something that happens by itself at the next restart:

cdc.WatchTable("dbo", "Orders", captureInstance: "dbo_Orders_v2");

Watermarks are per capture instance, so a switch starts dbo_Orders_v2 from its watermark — which does not exist yet, meaning StartFrom decides: FromNow skips whatever the old instance had not delivered, FromBeginning replays from the new instance's earliest retained LSN. Let the old instance drain first, then switch.

An explicit capture instance is verified at startup: a name that is not defined for the table fails StartAsync listing the ones that are, instead of failing on every poll with Invalid object name 'cdc.fn_cdc_get_all_changes_...'.

The watermark table

SqlCdcStateStore writes the watermark in a single atomic statement that takes a key-range lock, so concurrent writers cannot both decide the row is missing and collide on the primary key. The write is also monotonic: a lower LSN is a no-op rather than a rewind, so a watcher that lost its lease with a save already in flight cannot drag the new leader backwards. It is the builder default and is created automatically in the watched database. Use InMemoryCdcStateStore only for tests or deliberately ephemeral consumers: its checkpoint is lost when the process exits.

When the store is built on the same connection string (or the same connection factory instance) as the watcher, watermarks are saved on the poll connection, so a batch costs no extra connection. A store pointed at a different database, or authenticating differently, is honoured as such: it opens its own connection per save.

Both the watermark table and the dead-letter table are created on first use. Where the application has no DDL rights at runtime, provision them with scripts/create-state-tables.sql and turn creation off:

new SqlCdcStateStore(connectionString, createTableIfMissing: false)
new SqlCdcDeadLetterSink(connectionString, createTableIfMissing: false)

A missing table is then reported as such at startup. Both also take a commandTimeout (30 seconds by default).

Handler failures, retry and dead-letter queue

By default a handler is called once per change: if it throws, the change goes to the dead-letter sink, which is required as soon as a handler is registered. One transient timeout is then one dead letter to replay, so configure a retry as well:

builder.Services.AddCdcChangeHandler<OrderChangedHandler>();
builder.Services.AddCdcDeadLetterSink(new SqlCdcDeadLetterSink(connectionString));

builder.Services.AddSqlCdc(cdc => cdc
    .UseConnectionString(connectionString)
    .WatchTable("dbo", "Orders")
    .WithHandlerRetry(maxAttempts: 3, retryDelay: TimeSpan.FromSeconds(1)));

The delay doubles with each attempt (1 s, 2 s, 4 s …) up to one minute. When the attempts run out the change goes to the ICdcDeadLetterSink, is acknowledged, and delivery continues: one poisonous change must never block everything queued behind it.

SqlCdcDeadLetterSink writes to dbo.cdc_dead_letter (created automatically), keeping the before/after images as JSON alongside the handler name, the attempt count and the last exception — enough to inspect and replay. Implement ICdcDeadLetterSink to send them anywhere else. If the sink is temporarily unavailable, the write is retried with exponential backoff and delivery pauses until it succeeds. Under OnAcknowledgement the change also stays unacknowledged, so a restart during the outage replays it; under OnEmit it is lost if the process restarts before the sink is back.

Registering a handler without a sink fails when the host starts: a failed handler has its change acknowledged and the watermark moves past it, so without a sink the change would be gone. Retries and dead-lettering apply to handlers registered with AddCdcChangeHandler. Code that reads watcher.Changes directly owns its own error handling and must call Acknowledge() for every change under the default checkpoint mode.

Observability

The package publishes a Meter and an ActivitySource, both named SqlCdc:

builder.Services.AddOpenTelemetry()
    .WithMetrics(m => m.AddMeter(SqlCdcDiagnostics.MeterName))
    .WithTracing(t => t.AddSource(SqlCdcDiagnostics.ActivitySourceName));

Lag is measured entirely against the SQL Server clock — fn_cdc_map_lsn_to_time returns the server's local time, so comparing it with the client's UTC would measure the time zone offset. It costs no extra round-trip.

Spans: SqlCdc.Poll per poll of a capture instance, SqlCdc.Handle per handler call (tagged with the change key, operation and attempt number).

Health check

builder.Services.AddHealthChecks().AddSqlCdc(tags: ["ready"]);

A standby is deliberately healthy: it is doing exactly what it should, and failing its probe would take a good replica out of rotation. A standby that cannot even ask for the lease — the database unreachable, sp_getapplock denied — is not waiting its turn, and is reported like a capture instance whose polls keep failing (sqlcdc.lease.failures counts the same events). MaxStandbyDuration is for deployments where a long standby is itself suspicious: a single instance that stands by can only mean another watcher with the same name holds the lease, in another application or in a session not yet cleaned up. Leave it unset for a replica set where the standbys are meant to wait indefinitely.

The check reports per capture instance failure counts, changes emitted and time since the last successful poll in its data, plus the lease failure count and time in standby, and the same snapshot is available programmatically from SqlCdcWatcher.GetStatus().

Development setup

dotnet restore
dotnet build SqlCdc.slnx
dotnet test tests/SqlCdc.Tests -f net10.0         # unit tests, no external dependencies
dotnet test tests/SqlCdc.IntegrationTests         # requires Docker (see below)
dotnet pack src/SqlCdc/SqlCdc.csproj -c Release   # build NuGet package

The unit tests also target net8.0, which needs the .NET 8 runtime installed next to the .NET 10 SDK — hence the -f net10.0 above. See CONTRIBUTING.md for the full workflow, including how to accept a public API change and how releases are cut.

The package version comes from the git tag (MinVer), so a release is a v* tag and nothing else. dotnet pack validates the result against the last published version and fails on an accidental breaking change.

Integration tests

Run against a real SQL Server instance started with Testcontainers. Docker must be running. The container starts with SQL Server Agent enabled because without it the CDC capture job does not run and the change tables stay empty. On Apple Silicon the image is amd64 and runs under emulation (Rosetta must be enabled in Docker Desktop).

A full run takes roughly 30 seconds plus initial container startup. To skip them:

dotnet test SqlCdc.slnx --filter "Category!=Integration"

Sample

SQLCDC_CONNECTION="Server=.;Database=MyDb;User Id=sa;Password=...;TrustServerCertificate=True" \
  dotnet run --project samples/SqlCdc.Sample

See scripts/enable-cdc.sql to enable CDC on a sample table.