Live Updates from SQL Server to Your Browser with Service Brokers and SignalR

Getting up-to-date data from SQL into your browser window is usually an active process. Let's say you have a simple web application that retreives data from a database. You tell your browser to fetch a page, it contacts the web server with your request, which pulls the data from a database, and then the whole process reverses all the way back up the chain until you get a webpage in your browser. What you now have is a snapshot of the state that database was in when you made that request. If someone else alters that data in the database, the only way you find about it is to repeat the process by refreshing your browser.

But what if we wanted to take that active process and flip it on its head, making it a passive one instead? Instead of the user querying for new updates, the web server would simply push them to the browser as they occur. If another user makes an update to the dataset and it appears on all user's screens just moments later. With SignalR and SQL Server Service Broker, we can do just that.

SQL Server to Web Application Updates

The first step of the process is to set up a service broker in our database which will watch for changes and can update any listeners of them. The setup of this service broker is non-trivial, but fortunately, better developers than I have done the leg work. I utilized the NuGet package SqlServiceBrokerListener, the source of which is available on github. I'm going to cover the high-level ideas, but if you're interested in the inner workings and exact syntax, I encourage you to check it out.

SqlServiceBrokerListener utilizes a class, SqlDependencyEx (Side note: there is a bit in the "How to Use" of the package's readme that claims you must copy the class into your project. This is a relic of the original package that this was forked from and the class can simply be referenced like any other), to handle all listening activities. The setup syntax is fairly simple, as demonstrated by this snippet from the github "How to Use" section:

// See constructor optional parameters to configure it according to your needs
var listener = new SqlDependencyEx(connectionString, "YourDatabase", "YourTable");

// e.Data contains actual changed data in the XML format
listener.TableChanged += (o, e) => Console.WriteLine("Your table was changed!");

// After you call the Start method you will receive table notifications with
// the actual changed data in the XML format
listener.Start();

// ... Your code is here

// Don't forget to stop the listener somewhere!
listener.Stop();

When we call listener.Start(), a couple of SQL objects are set up for the database and table you've specified. First a trigger is added to your table that fires on table modifications. It reacts to incoming changes, wraps them up as an xml object, and passes them to the second SQL object that this method adds, the service broker.

The service broker writes the messages passed to it to a corresponding SQL queue. Anyone can read from the queue, but if we want to make modifications to its contents, all such transactions must be performed by the service broker.

Once the SQL infrastructure has been created, the SqlDependencyEx instance will begin polling the queue for changes. It does this using the WAITFOR/RECEIVE commands:

DECLARE @ConvHandle UNIQUEIDENTIFIER
DECLARE @message VARBINARY(MAX)
USE [{DatabaseName}]
WAITFOR (RECEIVE TOP(1) @ConvHandle=Conversation_Handle
            , @message=message_body FROM {SchemaName}.[{TableName}]), TIMEOUT {TimeoutInMs};
BEGIN TRY END CONVERSATION @ConvHandle; END TRY BEGIN CATCH END CATCH

SELECT CAST(@message AS NVARCHAR(MAX))

With the default configuration, SqlDependencyEx will wait on a queue message with that command for 30 seconds. If it does not receive one, the command is simply restarted.

However, if a message is received, the message is removed from the queue, and the event handler we specified via listener.TableChanged is fired with the XML document that was originally generated by the table trigger at the beginning of the process. We can use that event handler to accomplish any number of actions we want to perform within our application logic.

Web Application to Client Updates

One of the things we might want to do with that event is to pass that data change on to any connected users, so we can update their browser's copy of the data. This sort of server-initiated push usually involves creating a websocket connection on the initial page load, and then streaming data to the connected user.

With a Blazor Server-Side application, this websocket-based communication is fundamental to how the application works. The user creates a signalR connection on page load, and then changes to the page they are viewing are streamed to them via that connection. In a standard application, the initial page load will follow the pattern of:

  1. Query DB for relevant data
  2. Render page with that data
  3. Display page to the user
  4. Wait for user interaction to make further render changes

While the first three steps of that process remain unchanged, we are not using user interaction as a catalyst for updates, but rather the TableChanged event above.

Now while you could set up one SqlDependencyEx for each connected user, you'd be introducing additional load onto your SQL instance in the form of redundant triggers and brokers on the same table. It therefore makes more sense to control the flow of updates via a singleton service within your web applications. A single SqlDependencyEx per table can be managed by this single object, moving the work of disseminating updates from the database to the application. A simple implmentation might look like this:

public class SqlUpdatesService : IDisposable
{
    public event Action<SqlDependencyEx.TableChangedEventArgs> TableUpdated;

    private readonly SqlDependencyEx tableListener;

    public SqlUpdatesService()
    {
        tableListener = new SqlDependencyEx("ConnectionString", "YourDatabase", "Table1");
        tableListener.TableChanged += Listener_TableChanged;
        tableListener.Start();
    }

    private void Listener_TableChanged(object sender, SqlDependencyEx.TableChangedEventArgs e)
    {
        TableUpdated.Invoke(e);
    }

    public void Dispose()
    {
        tableListener?.Stop();
    }
}

We can add this service to our dependency injection container during our startup via:

serviceCollection.AddSingleton<SqlUpdatesService>();

This allows us to inject the service into any Blazor component we have. Here I've got a simple component that uses a data service to fetch the original table objects, then listens for updates and updates the component's view when a table update is received.

@implements IDisposable
@inject Table1DataService table1DataService
@inject SqlUpdatesService sqlUpdatesService

<h3>Table 1 Objects</h3>
@if (tableObjects != null)
{
    <table>
        <thead>
            <tr>
                <th>Id</th>
                <th>Name</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var table1Obj in table1Objects)
            {
                <tr>
                    <td>@table1Obj.Id</td>
                    <td>@table1Obj.Name</td>
                </tr>
            }
        </tbody>
    </table>
}

@code {
    ICollection<TableObject> tableObjects;

    protected override async Task OnInitializedAsync()
    {
        // Subscribe to table events
        sqlUpdatesService.TableUpdated += SqlUpdatesService_TableUpdated;

        // Fetch starting data set
        tableObjects = table1DataService.GetTable1Objects();
    }

    private async void SqlUpdatesService_TableUpdated(SqlDependencyEx.TableChangedEventArgs e)
    {
        // Any calls to StateHasChanged outside of the standard
        // Blazor lifecycle events must be wrapped in InvokeAsync
        await InvokeAsync(() => {
            // Refresh the component's data set
            tableObjects = table1DataService.GetTable1Objects();

            // Prompt component to render the new data
            StateHasChanged();
        });
    }

    public void Dispose()
    {
        // Unsubscribe from events
        sqlUpdatesService.TableUpdated -= SqlUpdatesService_TableUpdated;
    }
}

That's the gist of it. This component will fetch data on initial load, then again whenever the SqlUpdatesService receives a table change from SQL. From here there are a few logic enhancements that we might choose to implement:

  • Reading the SqlDependencyEx.TableChangedEventArgs event's Xml Data property to write the updates directly into memory without needing to call GetTable1Objects again.
  • Allowing blazor components to subscribe to updates to just a single entity rather than the entire table, by allowing them to provide SqlUpdatesService with a filter.
  • Watching multiple tables with SqlUpdatesService (more on this below).

Service Broker Identities

There is an optional argument in the constructor of SqlDependencyEx that turns out to be very important when attempting to listen to multiple tables, to the same table with multiple application instances, or to multiple tables with multiple application instances. The identity argument will default to 1, but you'll soon find that attempting to register multiple listeners with that same identity will result in previous listeners getting silently overwritten.

To avoid this behavior, we need to assign identities to each instance of SqlDependencyEx we create.

var table1Listener = new SqlDependencyEx(connectionString, "YourDatabase", "Table1", identity: 1);
var table2Listener = new SqlDependencyEx(connectionString, "YourDatabase", "Table2", identity: 2);

This works OK if your application is only ever running on a single server (no local debugging allowed), but causes conflicts if there are multiple instances of your application running at the same time. To solve this, we can create a hashing solution that takes the current environment name (or some other per-application value you configure), combines that with the database and table name and generates an identity specific to this particular SqlDependencyEx.

While your first instinct might be to use a solution involving Object.GetHashCode(), bear in mind that GetHashCode is not deterministic in .NET Core. That means that every time you fire up the application, you'll be creating a listener with a completely different identifier, even if everything you're using to compute your hash hasn't changed. If you're terminating your application mid-runtime as is common during development, these SQL objects will not be getting cleaned up, and they can tend to accumulate.

Therefore, I instead find this solution using SHA256 to hash together the various pieces of your identity to be preferable:

public SqlDependencyEx CreateSqlDependencyEx(string databaseName, string tableName)
{
    int identityVal;

    using (var mySHA256 = SHA256.Create())
    {
        var bytes = mySHA256.ComputeHash(
            Encoding.UTF8.GetBytes(
                Environment.MachineName + databaseName + tableName));
        identityVal = Math.Abs(BitConverter.ToInt32(bytes)); // Identity needs to be positive
    }

    return new SqlDependencyEx(connectionString, databaseName, tableName, identity: identityVal);
}

I'm using SHA256, but any hashing algorithm will probably be sufficient here, as we are unlikely to ever have enough application listeners to cause a hash collision.

Conclusion

While I used Blazor in this article, it is by no means a requirement to implement this pattern. Updates can be sent via standard websockets and received by a Javascript front end, and indeed ASP.NET Core is not required either, as the Service Broker/Trigger/WAITFOR/RECEIVE SQL pattern can be implemented in any language (including raw SQL querying).

This application structure is powerful, but in most cases users of a website don't require such real-time updates, and can be served just as well by the simpler user-initiated page refresh model. If you do choose to leverage it, I would only caution against having too many listener/triggers operating at once, as this can place unnecessary computing strain on your database.

A final important caveat: Service brokers are not supported in Azure SQL Database. If you want to use this in an Azure environment, you can use a Managed Instance instead. See a comparison of the two types of Azure SQL offerings here.

Let's talk about
your project

Give us a ring: 1.617.903.7604

Drop us a Note

Connect with
Cloud Construct

Let's talk about
your project

Give us a ring: 1.617.903.7604

Drop us a Note

Connect with
Cloud Construct

Our website uses cookies to distinguish you from other users of our website. This helps us to provide you with a good experience when you browse our website, and also allows us to improve our site. By continuing you accept the use of these cookies.

× dismiss