IntelliTect.AspNetCore.SignalR.SqlServer

A Microsoft SQL Server backplane for ASP.NET Core SignalR.

This project is largely based off of a fork of the SignalR Core Redis provider, reworked to use the underlying concepts of the classic ASP.NET SignalR SQL Server backplane. This means it supports subscription-based messaging via SQL Server Service Broker, falling back on periodic polling when not available.

SQL Server Configuration

For optimal responsiveness and performance, SQL Server Service Broker should be enabled on your database. NOTE: Service Broker is not available on Azure SQL Database. If you are running in Azure, consider a Redis or Azure SignalR Service backplane instead.

If Service Broker is not available, a fallback of periodic queries is used. This fallback querying happens very rapidly when messages are encountered, but slows down once traffic stops.

You can check if Service Broker is enabled with the following query:

SELECT [name], [service_broker_guid], [is_broker_enabled]
FROM [master].[sys].[databases]

To enable it, execute the following against the database. Note that this requires an exclusive lock over the database:

ALTER DATABASE [DatabaseName] SET ENABLE_BROKER WITH NO_WAIT;

If the above command does not work due to existing connections, try terminating existing sessions automatically using

ALTER DATABASE [DatabaseName] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

You can also set AutoEnableServiceBroker = true when configuring in your Startup.cs, but this requires that the application have permissions to do so and has the same caveats that there can be no other active database sessions.

Usage

1. Install the IntelliTect.AspNetCore.SignalR.SqlServer NuGet package.
2. In ConfigureServices in Startup.cs, configure SignalR with .UseSqlServer():

Simple configuration:

services
    .AddSignalR()
    .AddSqlServer(Configuration.GetConnectionString("Default"));

Advanced configuration:

services
    .AddSignalR()
    .AddSqlServer(o =>
    {
        o.ConnectionString = Configuration.GetConnectionString("Default");
        // See above - attempts to enable Service Broker on the database at startup
        // if not already enabled. Default false, as this can hang if the database has other sessions.
        o.AutoEnableServiceBroker = true;
        // Every hub has its own message table(s). 
        // This determines the part of the table named that is derived from the hub name.
        // IF THIS IS NOT UNIQUE AMONG ALL HUBS, YOUR HUBS WILL COLLIDE AND MESSAGES MIX.
        o.TableSlugGenerator = hubType => hubType.Name;
        // The number of tables per Hub to use. Adding a few extra could increase throughput
        // by reducing table contention, but all servers must agree on the number of tables used.
        // If you find that you need to increase this, it is probably a hint that you need to switch to Redis.
        o.TableCount = 1;
        // The SQL Server schema to use for the backing tables for this backplane.
        o.SchemaName = "SignalRCore";
    });

Alternatively, you may configure IntelliTect.AspNetCore.SignalR.SqlServer.SqlServerOptions with the Options pattern.

services.Configure<SqlServerOptions>(Configuration.GetSection("SignalR:SqlServer"));

OpenTelemetry Support

This library includes OpenTelemetry instrumentation that wraps background database queries in activities, making them more easily identified and grouped in your collected telemetry.

Setup

To enable OpenTelemetry collection of these trace spans and metrics, add the source and meter to your configuration:

builder.Services.AddOpenTelemetry()
    .WithTracing(tracing => tracing
        .AddSource("IntelliTect.AspNetCore.SignalR.SqlServer")
        // ... other instrumentation
    )
    .WithMetrics(metrics => metrics
        .AddMeter("IntelliTect.AspNetCore.SignalR.SqlServer")
        // ... other instrumentation
    );

Activity Names

The library creates activities for the following operations:

• SignalR.SqlServer.Install - Database schema installation/setup
• SignalR.SqlServer.Start - Receiver startup operations
• SignalR.SqlServer.Listen - Service Broker listening operations (database reads)
• SignalR.SqlServer.Poll - Polling operations (database reads, when Service Broker is not used)
• SignalR.SqlServer.Publish - Message publishing operations (database writes)

Metrics

The library also provides metrics to help monitor the health and performance of the SQL Server backplane:

• signalr.sqlserver.poll_delay - Histogram showing the distribution of polling delay intervals, useful for understanding backoff patterns and system health
• signalr.sqlserver.query_duration - Histogram tracking the duration of SQL Server query execution for reading messages
• signalr.sqlserver.rows_read_total - Counter tracking the total number of message rows read from SQL Server
• signalr.sqlserver.rows_written_total - Counter tracking the total number of message rows written to SQL Server

These metrics help you understand polling patterns, database performance, message throughput, and can be useful for tuning performance or identifying when Service Broker fallback to polling occurs.

Filtering Noise

Since the SQL Server backplane performs frequent polling operations, you may want to filter out successful, fast queries to reduce trace noise.

The following example assumes using package OpenTelemetry.Instrumentation.SqlClient >= 1.12.0-beta.3 for SqlClient instrumentation. There are currently 4 different packages for SqlClient instrumentation, so your method of collecting or filtering the command details may vary if you're using Aspire's instrumentation or Azure Monitor's instrumentation. Be sure to update the CommandText filter if you customize the schema name:

builder.Services.AddOpenTelemetry()
    .WithTracing(tracing => tracing
        .AddSqlClientInstrumentation()
        .AddSource("IntelliTect.AspNetCore.SignalR.SqlServer")
        .AddProcessor<SignalRTelemetryNoiseFilter>()
    );

internal sealed class SignalRTelemetryNoiseFilter : BaseProcessor<Activity>
{
    public override void OnEnd(Activity activity)
    {
        if (activity.Status != ActivityStatusCode.Error &&
            activity.Duration.TotalMilliseconds < 100 &&
            (activity.GetTagItem("db.query.text") ?? activity.GetTagItem("db.statement")) is string command &&
            command.StartsWith("SELECT [PayloadId], [Payload], [InsertedOn] FROM [SignalR]"))
        {
            // Sample out successful and fast SignalR queries
            activity.ActivityTraceFlags &= ~ActivityTraceFlags.Recorded;
        }
    }
}

Caveats

As mentioned above, if SQL Server Service Broker is not available, messages will not always be transmitted immediately since a fallback of periodic querying must be used.

Performance

This is not the right solution for applications with a need for very high throughput, or very high degrees of scale-out. Consider Redis or Azure SignalR Service instead for such cases. You should always do an appropriate amount of testing to determine if a particular solution is suitable for your application.

The results of some ad-hoc performance testing yielded that you can expect about 1000 messages per second per table (setting TableCount). However, increasing table count does have diminishing returns; attempting to push 20,000 messages/sec with 20 tables had a throughput of only ~10,000 messages/sec. This was observed with a SQL Server instance and load generator running on the same machine, an i9 9900k with an SSD, with a message size of ~200 bytes. A Redis backplane on the same hardware sustained 20,000 messages per second without issue.

Do note that a broadcast message is considered a single message. Any call to SendAsync within a hub is a single message.

License

Apache 2.0.

Credit to Microsoft for both Microsoft.AspNet.SignalR.SqlServer and Microsoft.AspNetCore.SignalR.StackExchangeRedis, upon which this project is based.