SQL Server

The SQL Server networked adapter, its options, and the honest polling-only latency story.

SQL Server is BackWave's Networked Adapter for shops that already run on SQL Server. It is a full-featured store: transactional enqueue, lease-based claiming that lets many worker processes share one database safely, and a fail-stop schema check that refuses to run against a version it does not recognize. The one place it differs from the Postgres adapter is latency. SQL Server has no push-notification path, so a freshly enqueued job is picked up on the next poll rather than the instant it commits. That tradeoff runs through everything below, so it is worth understanding before you wire the adapter in.

Registering the store#

There is no AddBackWaveSqlServer convenience extension. The SQL Server package ships three public types and you attach the store through the core builder. The minimal shape is a SqlServerJobStore constructed from a SqlServerStoreOptions with a connection string:

services.AddBackWave(backwave =>
{
    backwave.UseStore(new SqlServerJobStore(new SqlServerStoreOptions
    {
        ConnectionString = connectionString,
    }));
});

That is the store, but AddBackWave needs more than a store to be valid. A complete registration pairs UseStore with a job registry and at least one Worker Group; leave any of them out and AddBackWave throws an InvalidOperationException at startup rather than booting a half-configured cluster.

builder.Services.AddBackWave(bw => bw
    .UseStore(new SqlServerJobStore(new SqlServerStoreOptions
    {
        ConnectionString = builder.Configuration.GetConnectionString("BackWave")!,
    }))
    .UseJobs(BackWaveJobs.Module)
    .AddWorkerGroup(new WorkerGroupOptions
    {
        Name = "default",
        Policy = new DispatchPolicy.Strict("emails", "reports"),
    }));

When the connection string comes from IConfiguration or anything else in the container, use the factory overload of UseStore so the store is built once the provider is available:

backwave.UseStore(sp =>
{
    var connectionString = sp.GetRequiredService<IConfiguration>()
        .GetConnectionString("BackWave")!;
    return new SqlServerJobStore(new SqlServerStoreOptions
    {
        ConnectionString = connectionString,
    });
});

The store has a single constructor and it takes the options record. There is no string-only convenience constructor: you always go through SqlServerStoreOptions, whose ConnectionString is a required init-only property. Under the hood the store opens a fresh connection per operation and leans on ADO.NET connection pooling, so there is no pool object to dispose and nothing to keep alive between calls.

The connection string#

The adapter uses a standard Microsoft.Data.SqlClient ADO.NET connection string. Point it at the database that holds (or will hold) the BackWave schema:

Server=localhost,1433;Database=app;User Id=app;Password=secret;Encrypt=True;TrustServerCertificate=True

Options and their defaults#

SqlServerStoreOptions is a small record. Apart from the required connection string, every property has a sensible default and you can ignore it until you have a reason not to.

HistoryPolicy is a configuration setting, never a schema change. Lowering it from the default to transitions-only, or off, cuts write volume for hosts that do not need the full failure-detail trail. The store also exposes the effective history policy it is running under as a read-only capability; an environment kill-switch can downgrade failure-detail capture on PII-sensitive hosts, and the effective value reflects that.

The store advertises that it supports transactional enqueue through a capability flag you can read at runtime. On SQL Server that flag is always true.

Transactional enqueue#

SQL Server is a full transactional-enqueue adapter, which means you can write a job on the same transaction as your business data so the two commit or roll back as a unit. EnqueueAsync takes an optional transaction argument. Leave it out and the adapter opens its own connection, runs the enqueue, and commits. Pass one and the adapter enlists in it: your rollback means the job never existed, and your commit publishes the job atomically with the business write.

There is one hard rule, and it throws loudly if you break it. The transaction you hand BackWave must be a Microsoft.Data.SqlClient.SqlTransaction with a live connection. Anything else, a different DbTransaction type or one whose connection has been detached, throws an ArgumentException:

The SQL Server adapter enlists in SqlTransaction instances only.

In practice this means transactional enqueue requires you to be on Microsoft.Data.SqlClient and to hand BackWave the same SqlTransaction your business write uses. That is the natural fit with EF Core, where you begin a transaction on your DbContext and let the job ride along. The Transactional Enqueue with EF Core guide walks through the whole pattern.

Every multi-step state change the adapter performs, the job row together with its parent edges, tags, and transition-log entries, runs inside one transaction, so a crash partway through never leaves a half-applied result. Re-enqueuing the same job id is absorbed rather than duplicated: the second attempt reports a duplicate result instead of writing a second row.

Schema and migrations#

The schema lives in a dedicated backwave SQL schema alongside your own tables, and the current build requires a specific schema version. That name is configurable through SchemaName; change it to fit your conventions and pass the same value to the migrator's name-taking overload, as Naming the schema describes. You apply that schema in one of two ways.

For local development and tests, set AutoMigrate = true and the store applies every migration on first use. For production, leave AutoMigrate off and apply the schema yourself from a deployment pipeline using the public migrator:

await SqlServerMigrator.MigrateAsync(connectionString, cancellationToken);

MigrateAsync runs every schema script in order and is idempotent, so it is safe to run on every deploy. It does exactly what AutoMigrate = true does, just on your schedule instead of at first use. A companion VerifySchemaVersionAsync confirms a database is at the version this adapter expects, and SqlServerMigrator.ExpectedSchemaVersion is the version constant you can assert against.

Whichever path you take, the adapter checks the schema lazily on first store use and fail-stops if anything is wrong. There are two failure modes, both InvalidOperationException:

• A missing schema (with AutoMigrate off) tells you to apply the schema scripts or opt in to AutoMigrate.
• A version mismatch halts the Worker Group with a message that names the database's version and the version this adapter requires, because version skew must never be allowed to corrupt job state.

That second guarantee is the important one. Even with AutoMigrate = false, workers refuse to run against a database that is older or newer than they expect. They stop cleanly rather than risk writing job state in a shape the schema cannot hold.

One callout for production deployments. The initial schema enables READ_COMMITTED_SNAPSHOT (RCSI) on the database, which BackWave needs so that Monitor reads see committed state and never block on an in-flight transactional enqueue. Enabling RCSI issues an ALTER DATABASE ... WITH ROLLBACK IMMEDIATE, which needs elevated database permissions and momentarily forces other sessions out of the database. That is a strong reason to apply the schema in a controlled deployment window with MigrateAsync rather than letting AutoMigrate do it at an arbitrary first-use moment in production. If you ever apply the schema by hand, make sure RCSI ends up enabled; the shipped scripts handle it for you. The schema migrations page covers the deploy-pipeline story in full.

The latency story: polling only#

This is the honest tradeoff with the SQL Server adapter, and it is the main reason to choose it deliberately rather than by default.

Postgres and SQLite both implement a wake-up path: when a job is enqueued, the store nudges the worker pump so it claims the new job almost immediately. The SQL Server adapter has no such path. There is no Service Broker integration, no query-notification hook, no LISTEN/NOTIFY equivalent. New-work latency on SQL Server is governed entirely by how often the Worker Group polls.

That knob is PollInterval on WorkerGroupOptions, and it defaults to one second:

.AddWorkerGroup(new WorkerGroupOptions
{
    Name = "default",
    Policy = new DispatchPolicy.Strict("emails", "reports"),
    PollInterval = TimeSpan.FromMilliseconds(500),
})

A job that is enqueued and immediately due is picked up within roughly one poll interval, so about a second in the worst case at the default. Shortening PollInterval lowers that latency at the cost of more frequent store queries; lengthening it does the reverse. On SQL Server this is the only lever, because there is no push path to fall back on.

It is worth being precise about what this does and does not affect. Wake-up hints are a latency optimization, never a correctness mechanism. Polling picks up every job regardless of whether a hint fired. SQL Server simply forgoes the optimization that Postgres and SQLite get, so its floor on pickup latency is the poll interval rather than near-instant. If sub-second pickup latency matters to you, the wake-up hints page and choosing an adapter explain the comparison; Postgres is the Networked Adapter that adds the push path.

Enforced limits#

The store enforces a set of size and batch caps from StoreBounds. Some reject an over-limit operation, some clamp it, and one truncates. The defaults are:

The distinction between reject and truncate matters. Failure detail is write-only diagnostics, so over-limit detail is truncated rather than failing the operation. Job output is different: it is data a descendant job may deserialize, so an over-limit output is rejected outright rather than silently clipped. The limits and defaults reference lists every bound.

Exceptions you can hit#

A few exceptions are part of the surface a SQL Server user touches:

• JobOutputTooLargeException is thrown when a successful job reports an output blob larger than MaxOutputBytes (64 KiB by default). The check runs before any write, so an over-limit attempt leaves the store untouched, and the exception carries the job id, the actual byte count, and the limit. The fix is to store a reference, an id or a blob key, instead of the data itself.
• ArgumentException is thrown by EnqueueAsync when you pass a transaction that is not a SqlTransaction, or one with no connection (see the transactional enqueue section above).
• InvalidOperationException is thrown by the migrator and the first-use check on a missing or mismatched schema.

How it behaves under load#

A few properties are worth stating plainly because they shape how the adapter behaves across a cluster.

Execution is at-least-once with lease-based claiming. Contention is resolved inside the database, so many worker processes can share one database without ever claiming the same job. That is what makes SQL Server a Networked Adapter: the database is reachable across hosts, so a BackWave cluster can span many machines, in contrast to the single-host SQLite Embedded Adapter.

Outcome writes are fenced. A worker that has lost its lease, say after a network partition isolated it, cannot overwrite a result that a fresh worker has since produced; the stale write mutates nothing and reports a stale-lease result. Concurrency limits and pause are enforced at claim time. And the all-or-nothing transaction guarantee, that parent edges, tags, transitions, latch cascades, and the job row all commit together, is verified under a simulated mid-operation crash. See execution guarantee for the model these properties add up to.

The package targets net10.0 and depends on Microsoft.Data.SqlClient; the packages and compatibility reference pins the exact versions.

Where to go next#

• Choosing an adapter to weigh SQL Server against the other stores.
• PostgreSQL for the sibling Networked Adapter that adds a push-based wake-up path.
• SQLite for the Embedded Adapter contrast.
• Wake-up hints for why SQL Server is polling-only and what that costs.
• Schema migrations for applying the schema from a deployment pipeline.
• Transactional Enqueue with EF Core for enlisting a job in your own transaction.
• Storage contract for the behavior every adapter must honor.
• Configuration reference for the full set of options.