# SQL Server

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:

```csharp title="Program.cs"
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.

```csharp title="Program.cs"
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:

```csharp title="Program.cs"
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:

```text
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.

| Property | Type | Default | What it controls |
|---|---|---|---|
| `ConnectionString` | `string` | required | The ADO.NET string used for every database operation. |
| `AutoMigrate` | `bool` | `false` | When `true`, the schema is applied on first use. When `false`, a missing schema is a fail-stop. |
| `SchemaName` | `string` | `"backwave"` | The SQL schema BackWave's objects live under. Change it to fit a naming convention or to share a database with another application; it must be a valid identifier. See [Naming the schema](/docs/storage/schema-migrations#naming-the-schema). |
| `Bounds` | `StoreBounds` | `StoreBounds.Default` | The size and batch caps the store enforces (see below). |
| `HistoryPolicy` | `JobHistoryPolicy` | `TransitionsAndFailureDetail` | How much per-job history is recorded: off, transitions only, or transitions plus failure detail. |

`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](/docs/guides/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](/docs/storage/schema-migrations#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:

```csharp title="Deploy.cs"
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](/docs/storage/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:

```csharp title="Program.cs"
.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.

The adapter does report the instant the next scheduled job comes due, which is what makes `MaxPollInterval` useful here. Set it above `PollInterval` and an idle group sleeps until that instant instead of polling at nothing every second. The delay drops back to `PollInterval` the moment work appears. Because SQL Server has no hint channel, the ceiling you set is the direct worst case for a job enqueued while the group is idle. Treat it as the latency budget for newly enqueued work.

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](/docs/storage/wakeup-hints) page and [choosing an adapter](/docs/storage/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:

| Bound | Default | Behavior over the limit |
|---|---|---|
| `MaxPayloadBytes` | 65,536 | Enqueue rejected |
| `MaxWireNameLength` | 128 | Enqueue rejected |
| `MaxParentsPerJob` | 16 | Enqueue rejected |
| `MaxOutputBytes` | 65,536 | Job output rejected (see below) |
| `MaxClaimBatch` | 32 | Claim request clamped |
| `MaxMonitorPageSize` | 200 | Listing page size clamped |
| `MaxPurgeBatch` | 500 | Purge batch clamped |
| `MaxTransitionsPerJob` | 64 | Oldest transition dropped |
| `MaxFailureDetailBytes` | 8,192 | Failure detail truncated |

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](/docs/reference/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](/docs/core-concepts/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](/docs/reference/packages-compatibility) reference pins the exact versions.

## Where to go next

- [Choosing an adapter](/docs/storage/choosing-an-adapter) to weigh SQL Server against the other stores.
- [PostgreSQL](/docs/storage/postgres) for the sibling Networked Adapter that adds a push-based wake-up path.
- [SQLite](/docs/storage/sqlite) for the Embedded Adapter contrast.
- [Wake-up hints](/docs/storage/wakeup-hints) for why SQL Server is polling-only and what that costs.
- [Schema migrations](/docs/storage/schema-migrations) for applying the schema from a deployment pipeline.
- [Transactional Enqueue with EF Core](/docs/guides/transactional-enqueue-with-ef-core) for enlisting a job in your own transaction.
- [Storage contract](/docs/reference/storage-contract) for the behavior every adapter must honor.
- [Configuration reference](/docs/reference/configuration) for the full set of options.