Change Notification with Sql Server 2008

Question

I have an application that consists of a database and several services. One of these services adds information to the database (triggered by a user).

Another service periodically queries the databases for changes and uses the new data as input for processing.

Until now I used a configurable timer that queries the database every 30 seconds or so. I read about Sql 2005 featuring Notification of changes. However, in Sql 2008 this feature is deprecated.

What is the best way of getting notified of changes that occurred in the database directly in code? What are the best practices?

Answer 1

Notification Services was deprecated, but you don't want to use that anyway.

You might consider Service Broker messages in some scenarios; the details depend on your app.

In most cases, you can probably use SqlDependency or SqlCacheDependency. The way they work is that you include a SqlDependency object with your query when you issue it. The query can be a single SELECT or a complex group of commands in a stored procedure.

Sometime later, if another web server or user or web page makes a change to the DB that might cause the results of the previous query to change, then SQL Server will send a notification to all servers that have registered SqlDependency objects. You can either register code to run when those events arrive, or the event can simply clear an entry in the Cache.

Although you need to enable Service Broker to use SqlDependency, you don't need to interact with it explicitly. However, you can also use it as an alternative mechanism; think of it more as a persistent messaging system that guarantees message order and once-only delivery.

The details of how to use these systems are a bit long for a forum post. You can either Google for them, or I also provide examples in my book (Ultra-Fast ASP.NET).

Answer 2

Yes, this blog post explains that Notification Services is now deprecated, and also what the replacements or alternatives are, going forward.

For your purposes - getting notified of changes that occurred in the dataase - it sounds like you want SQL Server Change Tracking. But the notification is a pull model - your app has to do the query on the change table.

I failed to figure out if SqlDependency continues to work with Notification Services deprecated.

Answer 3

There are a number of different ways of tracking changes in the database: either by triggers that maintain temporal structures such as backlogs, tracking logs (aka 'audit tables') or using the change-tracking facilities in SQL 2008 as references in another answer. Irrespective of whatever mechanism you use, you have the problem of notifying your homegrown service of the change. For this, you can use the Service Broker and event-based activation. From what you describe, it seems like having the application wait on an event from the queue. http://msdn.microsoft.com/en-us/library/ms171581.aspx

If you don't wish to have the service hang around and sleep on the queue, you can investigate into firing the service into life 'on-demand' by using the external activation mechanism in service broker.

Answer 4

You can use the System.Data.SqlClient.SqlDependency (which works with Service Broker on) to subscribe to changes in a table.