NexJob.SqlServer 6.2.0

NexJob.SqlServer

Microsoft SQL Server storage provider for NexJob — the background job scheduler for .NET 8+.

Backed by Microsoft.Data.SqlClient and Dapper, this provider uses WITH (UPDLOCK, READPAST, ROWLOCK) hints for atomic, lock-contention-free job fetching across multiple distributed worker nodes.

Installation

dotnet add package NexJob.SqlServer

Quick Start

Register SQL Server storage in your dependency injection container before calling AddNexJob():

using NexJob;
using NexJob.SqlServer;

var builder = WebApplication.CreateBuilder(args);

// 1. Register SQL Server storage
builder.Services.AddNexJobSqlServer(
    builder.Configuration.GetConnectionString("NexJobConnection")!);

// 2. Register NexJob core services
builder.Services.AddNexJob(options =>
{
    options.Workers = 10;
    options.QueuePrefix = "myapp"; // the "default" queue below is stored as "myapp.default"
    options.Queues = ["default", "critical"];
});

Features

• Atomic Job Dispatching: Employs WITH (UPDLOCK, READPAST, ROWLOCK) locking hints to allow multiple workers to concurrently dequeue jobs without blocking each other or causing deadlocks.
• High-Throughput Batch Processing: Dynamic batch dequeue (FetchBatchAsync) based on worker idle slots and vectorized batch acknowledgment (AcknowledgeBatchAsync).
• Non-blocking Scheduled Promotion: Guarded with sp_getapplock and READPAST to eliminate deadlock 1205 contention under high concurrency.
• Automatic Schema Migration: Applies database schema migrations automatically on startup using sp_getapplock, guaranteeing safe concurrent execution in clustered environments.
• Idempotency Enforcement: Enforces strict and conditional deduplication policies (DuplicatePolicy) indexed on idempotency_key.
• Runtime Settings Store: Persists queue concurrency rules and runtime settings in nexjob_settings.
• Dashboard Read Replica Support: Route dashboard metrics and monitoring queries to an Azure SQL / SQL Server read-scale replica.

High-Throughput Batch Processing

For heavy ingestion workloads (e.g. streaming hundreds of thousands of events from Kafka/RabbitMQ into SQL Server), enable batch processing:

builder.Services.AddNexJobSqlServer(connectionString);

builder.Services.AddNexJob(options =>
{
    // Workers fetch jobs in atomic batches up to current idle capacity (TOP availableWorkers)
    options.Workers = 30;
    options.PollingInterval = TimeSpan.FromMilliseconds(20);

    // Commit successful jobs in asynchronous batches, eliminating per-job write roundtrips
    options.EnableBatchAcknowledgment = true;
});

Read Replica Configuration

To offload dashboard queries to a read replica (such as Azure SQL Database Read-Scale Out or Always On Availability Groups):

builder.Services.AddNexJobSqlServer(primaryConnectionString);

builder.Services.AddNexJob(options =>
{
    options.Workers = 10;
})
.UseDashboardReadReplica(readReplicaConnectionString);

Database Tables

The provider automatically manages the following tables in the configured database:

Connection String Example

{
  "ConnectionStrings": {
    "NexJobConnection": "Server=tcp:sqlserver.database.windows.net,1433;Initial Catalog=nexjob;User ID=admin;Password=secret;Encrypt=True;TrustServerCertificate=False;"
  }
}

Connection pool

NexJob shares your database with other applications. A node needs about Workers + 5 connections, so set Max Pool Size in the connection string to about Workers + 10 and multiply by the number of nodes. NexJob logs the pool size it finds at startup and warns when it is smaller than Workers. See Database connections and pool sizing.