My name is Michal Sporek. I am a contractor in IT (software development). I am available for IT contracting jobs working remotely (telecommuting).

Please visit my site to learn more...

Change notifications from SQL Server

Working on different projects many times I have come to the point where I needed to get notifications from a database implemented. Most of my work is done with .NET stack of technologies and MS SQL Server, so implementing notifications with MS SQL Server is usually what I have to do.

So what options does one have when you your application needs to be informed when there are changes in the SQL Server data? Well, there is a number of solutions that you can choose from:

  1. Back in time (up to SQL Server 2005) there was a solution called MS SQL Server Notification Services. It was quite a heavyweight platform which was abandoned with SQL Server 2008, so currently you should not build applications with it any longer. Here you can read more on this legacy solution: SQL Server Notification Services on Wikipedia

  2. With SQL Server 2005 Microsoft has introduced support for query notifications. Query notifications are build upon service broker architecture and what they allow applications to cache data from MS SQL Server database, and then get notified when the data is changed (in order to invalidate the cache, or a part from it with the new data). Although you need to enable Service Broker to use query notifications, you don't need to interact with the broker explicitly.
    When using ADO .NET you can implement the query notifications using a few approaches:
    • You can use the SqlNotificationRequest class which allows you to run a query against the database, and then get notified when the data is changed.
    • You can use the SqlDependency class which is a high level wrapper giving you a high level of abstraction (this class encapsulates the details of dealing with the database inside).
    • You can use the SqlCacheDependency helper class craved for web applications.
  3. An alternative approach is to use the built-in MS SQL Server change tracking mechanisms, which requires to reference the Synchronization dlls from Microsoft Synchronization Framework (Microsoft.Synchronization.Data.dll, Microsoft.Synchronization.dll, Microsoft.Synchronization.Data.Server.dll, and Microsoft.Synchronization.Data.SqlServerCe.dll). You can find more information about this mechanism here: How to: Use SQL Server Change Tracking. With this change tracking mechanism you need to keep in mind that it is based on polling the server for changes at the very bottom level.

  4. A custom change-tracking solution (notifications with polling):

    What should a developer consider for a very simple scenario of notifications? Let's say you can 2-3 tables central database, and many clients working against it. You don't want to go for using the "Service Broker" notifications, and you don't want to use the Microsoft Synchronization Framework which is quite a heavy-weight approach to a simple problem...

    Well, what you can do is to implement your own very simple notifications. The mechanism I will describe below I have used in a number of projects with SQL Server 2005, 2008, and 2012. It is very simple, it's quite lightweight, it doesn't require "Service Broker" to running, and it's not using Microsoft Sychronization Framework, even though it uses a similiar approach as the Microsoft Synchronization Framework does. To make clear you can understand me correctly - Microsoft Synchronization Framework is a robust and complex framework with a lot more functionality than the simple change-tracking approach I will demonstrate.

    Let's say we have a simple table named "Person" with 2 columns: "FirstName" and "LastName" representing the first name and the last name of the person.

    In order to implement my change-tracking notifications approach, we need to add a new column to the table. Let's name the column "Version". The name is not important, what is important is the type of the column which should be [TIMESTAMP]. The table definition SQL command with the 3 columns will look like that:

    CREATE TABLE [dbo].[Person](
        [FirstName] [TEXT] NOT NULL,
        [LastName] [TEXT] NOT NULL,
        [Version] [TIMESTAMP] NOT NULL,
    ) ON [PRIMARY]

    Once we have the table created in the database, we can approach to implementing the notifications with change-tracking.
    To do so first we need to explain what does the [TIMESTAMP] type which we've used for the "Version" column mean. SQL Server comes with a built-in functionality for tracking changes, it has a counter of changes kept internally and this counter is global (any insert/update in any table increments the counter and the inserted/updated rows get the current value). When you add/update a row in the [dbo].[Person] table, then the [TIMESTAMP] column will be assigned the current value of the counter of changes. With each subsequent insertions/editions made in the [dbo].[Person] table, the added and edited rows will have incremented values in the column of [TIMESTAMP] type. MS SQL Server deals with that internally, so having the [TIMESTAMP] column in your table. you receive a way to track changes in the database.

    The approach for implementing notifications with changes tracking will be like that:

    1. On the startup, your application needs to get the current value of the database counter. This you can do making this SQL query: SELECT CONVERT(BIGINT, MIN ACTIVE ROWVERSION()). With versions earlier than SQL Server Server 2005 SP2 you should use an alternative query to get the current database counter: SELECT CONVERT(BIGINT, @@DBTS) + 1
    2. Then you need to define your polling interval, it depends on how important it is for you to get information about changes made in the database. If it's very important, then the polling interval needs to be very short (like 100ms, 200ms, or 500ms). If it's not that crucial, then you can use a longer interval like 2 seconds, 5 seconds, or even more.
    3. Having the interval defined, now you need to query your table with the frequency of that that interval and get objects inserted/edited since the last call. For the case of [dbo].[Person] table you can do that with such a query: SELECT * FROM [dbo].[Person] WHERE ([Version] >= @LastCounterValue). This call will return all the rows which were inserted/updated since the moment when you have fetched the last counter value from the database. Knowing what was changed in the database, your application can deal with those changes appropriately. In addition to the polling query you should also call you should call MS SQL Server again to get the current version again (in such a way so that you make sure you don't skip any changes). Finally you wait the polling interval again, when it elapses, then you repeat the same procedure querying the database for new changes again.

    This mechanism for notifications with changes tracking is lightweight and very simple to be implemented. You have a full control over the polling interval, and you decide which tables you would like to track for changes. It doesn't require the "Service Broker", and it doesn't need the Microsoft Synchronization Framework to work.

    It's reasonable to have a separate thread for the SQL Server polling notifications. The thread will run the polling queries on defined time interval, and it can notify external observers about the changes firing an event. The main thread of your application can then handle the event to find out what changes were made the database and to display the changes to the user (or to handle them any other way).

Posted by Michal Sporek