SQL Server 2005 Query Notifications Tell .NET 2.0 Apps When Critical Data Changes

One of the classic problems with database applications is refreshing stale data. Imagine a typical e-commerce site with products and categories. A vendor’s product list most likely does not change very often and their category list changes even less frequently. However, those same lists must be queried from the database over and over again every time a user browses to that Web site. This is an annoyingly inefficient use of resources and developers and architects have been stuck playing cat-and-mouse trying to reduce the waste.

Caching is one technique used to minimize this repetitive querying of nearly stagnant data. The data can be queried once and stored in a cache, and the application repeatedly accesses the cache for the data. Occasionally, the cache is updated to get fresh data. But here is where many of us get caught up in trying to pinpoint the perfect schedule for updating the cache. How often should you do it? How often do you expect your categories to change, for example? Once every few months? Then what if you refresh the cache every two months? You know what would happen. The categories would get updated the day after you refreshed the cache and the cache would then be stale for two months before the next update.

Enter Query Notification, a collaboration between Microsoft’s ADO.NET and SQL Server teams. In a nutshell, Query Notification allows you to cache data and be notified when the data has been changed in SQL Server. Upon notification, you can then refresh your cache or take whatever action you need to.

Query Notification is possible because of a new feature in SQL Server 2005 called Service Broker. Service Broker puts queuing functionality into the database with a coordination of queues that communicate with services that, in turn, know how to communicate back to the calling entity. The queues and services are first class objects just as tables, views, and stored procedures are. Although Service Broker can be leveraged completely within SQL Server, ADO.NET knows how to communicate with Service Broker to trigger this mechanism and retrieve the notifications back from the Service Broker.

On the .NET side, there are a number of ways of hooking into this functionality. ADO.NET 2.0 provides the System.Data.SqlClient.SqlDependency and System.Data.Sql.SqlNotificationRequest classes. SqlDependency is a higher-level implementation of SqlNotificationRequest and is most likely the one you will use when working with ADO.NET 2.0. ASP.NET 2.0 also communicates with Service Broker through the System.Web.Caching.SqlCache-Dependency class (that provides a wrapper around SqlDependency), as well as directly through functionality provided declaratively in an ASP.NET page using the <%OutputCache> directive. This allows ASP.NET developers to easily invalidate caches that are dependent on data from SQL Server.

How .NET Communicates with Service Broker

How does all of this come together to solve the caching conundrum? Although there are a number of actions you must take to allow SQL Server to provide this service to .NET, the key is that queries sent to SQL Server have a flag attached to them telling SQL Server that in addition to returning the resultset, SQL Server should register the query (and its requestor) into the Service Broker. It does this by creating a queue that is aware of the query and a service that is attached to the queue and knows how to get back to the client. If any of the rows in that resultset get updated in the database, the item in the related queue is triggered and in turn, sends a message to its service, and then sends a notice back to the application that initiated the request.

A screenshot of SQL Server Management Studio in Figure 1 shows the Queues and Services inside of the Service Broker section of the pubs database.

Here are some important things to understand about this process:

• There are rules about what types of queries are acceptable to SQL Server.
• Once SQL Server sends back the notice, the queue and service are removed. This means that you only get one notice per request. A typical application re-queries the database and at the same time, requests that a new dependency be created in Service Broker.
• The information returned to the application is not much more than “something changed.” The application does not get notified about what changed. (See the SQLNotificationEventArgs section below for more on this.)
• Although the dependency is tied to the rows that are returned from the query, it is not filtered by the individual columns of the query. If you have a query that returns the first and last names of the founding members of your organization and the address of one of those individuals changes (but their first or last name doesn’t), this triggers a change notification. Hopefully, this particular behavior will change in future versions.
• Notifications are returned through a single SqlConnection that is established solely for this purpose. This connection is not engaged in connection pooling. (See the information on SqlDependency.Start and Stop further on in this article for more information.)

By: Julia Lerman

Julie Lerman is a Microsoft MVP, .NET mentor and consultant who lives in the hills of Vermont. You can find Julie presenting on data access and other topics at user groups and conferences around the world. Julie blogs at thedatafarm.com/blog and is the author of the highly acclaimed Programming Entity Framework (O’Reilly Media). Follow Julie on twitter at julielermanvt.

jlerman@thedatafarm.com