This is the last in my series of posts about different options you have with SQL Server 2005 database cache invalidation.
Up until now, we've seen 4 different options for receiving notifications in our web or Windows application that data has changed. Each of the options there, had one limitation, in that you can only receive notifications if a set of data has changed.
So, what if you've got a more granular caching system. One that caches individual entities, as opposed to collections of entities? Up until this post, none of the options would provide you the ability to receive a notification on a single row changing.
I'm going to cover how to do this in this post, by showing you how to roll your own notification messages using SQL Server Service Broker.
For each of these options, you'll need to ensure that you've enabled the SQL Server Broker on your database. To do this, you can execute the following query (this query needs exclusive access to the database, so if you see it hanging for a while, chances are you'll need to open up SQL Server Activity Monitor and terminate any additional connections. IISRESET works well too):
Note that you'll need to do this whenever you restore a database. Additionally, I've seen the following issue with database notifications occur when restoring a database that was backed up on another instance of SQL server.
UPDATE: Garry Grimshaw also pointed out to me that the version of SQL Server 2005 that you have installed makes a difference. Certain versions of SQL Server 2005 don't include Notifications Services, and your app will immediately never notify you of cache changes (or if using SQLCacheDependency, an immediate invalidation of your cache item will occur.)
UPDATE #2: Updated post to include the issues that I've discovered after using this code for some time. See this post about CPU and tempdb issues with SQL Service Broker.
Roll Your Own Cache Notification
Here is the list of tasks I'm going to be doing to get this to work:
- Create a SQL Server Service Broker Service and Queue
- Create a trigger on the table I'm interested in, that loops through the changed rows, and creates custom XML message with the identifying attributes of the row that has changed, and inserts that XML message into the Service Broker Queue.
- Write a background service in my ASP.NET application that listens for new entries in that Queue.
The first thing we have to do is create our SQL Service Broker Queue and Service. These will be the structures for the queuing of notifications. It's the same as in the SqlNotificationRequest example.
The result of this will be the following objects in SQL Server:
The next thing we need to do is create our trigger on the table that we need to be notified of changes for, which will look like so:
The trigger loops through all the changes, and builds an Xml document out of it, then sends that Xml document to the SQL Server Service Broker Queue.
Just as before, we need to create a service in our application that listens to the queue for changes. It's really similar to before, but in this case, we're getting the information from the queue message to construct our cache key.
And finally, here's my service that retrieves the person:You can see here that I'm interested in more granular caching information. I'm caching by individual person id's, and I don't want to dump the cache of all individual people when the person table changes. I want to clear the cache for individual items, and this method allows me to do it.