SQL Server Notifications - Polling and ServiceBroker

Introduction

As part of the CQRS in Azure MSDN blog series, two forms of notifications were used to notify the application tier of changes to the underlying data model.  For the SQL Azure, a polling mechanism was used and push notification was used for SQL Server versions that supported Service Broker.

The source can be found on MSDN Samples: here.

SQL Azure vs SQL Server

Service Broker is not supported in SQL Azure so using its support for messaging and queuing is not available.  Because of this alternative approaches are required.  In this instance, polling was chosen.  Simply put, the application will periodically poll for changes in the database.  This could be simply checking for a change to a last update date, number of records, and in some instances become quite sophisticated in detecting change.  In this example, a simple query is used to identify if a new entry has been added to a insert only table.

Insert Only Table

Insert only tables are popular in accounting or ledger solutions as it supports both auditing and history by preventing updates or delete operations.  The advantage is access can be optimized for write operation and a clear history of all activity is captured.  The disadvantage is the final state is determined by performing a full read of the table and often requires another persistent view or table to provide aggregated information.  

Note: Blockchain implementations use insert only tables to guarantee state among distributed repositories.

Building for different versions of SQL

For this sample, the version of SQL was determined by retrieving the Edition server property.  This will have a string value of "SQL Azure" when the database is SQL Azure:

public bool  IsAzureDb
{
    get
    {
        if(string.IsNullOrEmpty(_version))
        {
            _version = Database.SqlQuery<string>("select SERVERPROPERTY ('Edition')").Single();
        }
 
        switch(_version)
        {
            case "SQL Azure": return true;
            default: return  false;
        }
    }
}

RepositoryListener

To simplify the solution, an interface was created to allow the listener to be created at run-time based on if the database supports notifications or has to rely on polling:

/// <summary>
/// used for detecting changes related to specific conditions
/// </summary>
interface IRepositoryListener
{
    void StartListener();
    void StopListener();
    void Register(string query, Action callback);
}

In the startup of the application (Global.asax Application_Start), the specific listener is created, started and a register method is called.  The register method will use a given query in order to determine if the state has been updated:

string query;
 
using (var context = new PieShopContext())
{
    if(context.IsAzureDb)
    {
        _listener = new  PollingListener();
        query = "select top 1 [InventoryEntryId] from [ledger].[InventoryEntries] order by 1 desc";
    }
    else
    {
        _listener = new  DependencyListener();
        query = "select [InventoryEntryId] from [ledger].[InventoryEntries]";
    }                
}
 
_listener.StartListener();
_listener.Register(query, InventoryRepository.Instance.RefreshInventory);

PollingListener

The PollingListener will run until cancelled and will simply compare the result of the query against until change is detected.  Once it is detected, a callback method will be called:

public void  StartListener()
{
    _poller = Task.Run(() => 
    {
        while(!_token.IsCancellationRequested)
        {
            if(!string.IsNullOrEmpty(_query) && _callback != null)
            {
                using (var context = new PieShopContext())
                {
                    var result = context.Database.SqlQuery<int>(_query).Single();
 
                    if(result != _currentValue)
                    {
                        _currentValue = result;
                        _callback();
                    }
                }
            }
 
            Thread.Sleep(_frequency);
        }                    
    }
    , _token);
}

DependencyListener

The DependencyListener will take advantage of ServiceBroker by registering a new SQLDependency.  The dependency will then be triggered when a change happens.  

public int  GetData()
{
    using (var connection = new SqlConnection(_connectionString))
    {
        connection.Open();
 
        // simple queries work best - take a look at what is not supported
        // https://msdn.microsoft.com/library/ms181122.aspx?f=255&MSPPError=-2147217396
        using (SqlCommand command = new SqlCommand(_query, connection))
        {
            // Make sure the command object does not already have
            // a notification object associated with it.
            command.Notification = null;
 
            SqlDependency dependency = new  SqlDependency(command);
            dependency.OnChange += new  OnChangeEventHandler(dependency_OnChange);
 
            if (connection.State == ConnectionState.Closed)
                connection.Open();
 
            using (var reader = command.ExecuteReader())
                return reader.Cast<IDataRecord>().Last().GetInt32(0);
        }
    }
}

An important note is the query that is used to detect is limited.  Take a look at the documentation for details.  Also, it is important to note the starting and stopping of SQLDependency:

public void  StartListener()
{
    SqlDependency.Start(_connectionString);            
}
 
public void  StopListener()
{
    SqlDependency.Stop(_connectionString);
}

Summary

Both SQLDepenency and polling were used to solve the problem of detecting change in model state.  The CQRS in Azure posts cover the scenario of having a dashboard page that periodically refreshes to display a consistent view of the model.  

A related part, illustrating the updating of the table, is also in TechNet Wiki: Azure Functions - Entity Framework

Please see the sample project for full implementation details.