Overview of SQL Server 2005 Database Cache Invalidation Options - Part 5 Roll Your Own

 

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.

Setup

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):

ALTER DATABASE AdventureWorks SET NEW_BROKER

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:

1. Create a SQL Server Service Broker Service and Queue
2. 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.
3. 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.

CREATE QUEUE PersonChangeMessages;
 
CREATE SERVICE PersonChangeNotifications
  ON QUEUE PersonChangeMessages
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

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:

CREATE TRIGGER [dbo].[tr_Person_NotifyCacheQueue]
   ON [dbo].[Person]
   AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
 
    -- Insert statements for trigger here
    DECLARE @Message XML
    DECLARE @Action VARCHAR(10)
    DECLARE @InsTblCnt INT
    DECLARE @DelTblCnt INT;
    SET @Action = '';
 
    SET @InsTblCnt = 0;
    SET @DelTblCnt = 0;
 
    SELECT @InsTblCnt = Count(*) FROM Inserted;
    SELECT @DelTblCnt = Count(*) FROM Deleted;
 
    IF @InsTblCnt > 0 AND @DelTblCnt > 0 SET @Action = 'UPDATE';
 
    ELSE IF @DelTblCnt > 0 SET @Action = 'DELETE';
 
    ELSE IF @InsTblCnt > 0 SET @Action = 'INSERT';
 
    ELSE SET @Action = 'NONE';
 
    IF @Action <> 'NONE'
        BEGIN
            DECLARE @tempTable TABLE ( [ID] INT )
 
            IF @Action = 'DELETE'
                SET @Message = (SELECT * FROM Deleted FOR XML AUTO, ROOT ('ChangedPersonRows'))
            ELSE
                SET @Message = (SELECT * FROM Inserted FOR XML AUTO, ROOT ('ChangedPersonRows'))
 
            DECLARE @DialogHandle UNIQUEIDENTIFIER
 
            BEGIN DIALOG @DialogHandle
            FROM SERVICE [PersonChangeNotifications]
            TO SERVICE 'PersonChangeNotifications'
            ON CONTRACT [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
            WITH ENCRYPTION = OFF, LIFETIME = 30;
            SEND ON CONVERSATION @DialogHandle
            MESSAGE TYPE [http://schemas.microsoft.com/SQL/Notifications/QueryNotification] (@Message);
        End
 
END

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.

public class Listener
{
    private static readonly string cacheKey = "Person:{0}";
    private static bool isAlreadyListening = false;
 
    public void Start()
    {
        if (isAlreadyListening) return;
 
        //create and call the delegate for the asynchronous call
        AsyncListenerDelegate asyncListener = new AsyncListenerDelegate(Listen);
        asyncListener.BeginInvoke(HttpContext.Current, new AsyncCallback(StopListening), null);
        isAlreadyListening = true;
        return;
    }
 
    private void Listen(HttpContext currentContext)
    {
        while (true)
        {
            //create the command that will listen to the queue
            using (SqlConnection connection = new SqlConnection(
                ConfigurationManager.ConnectionStrings["BlogExampleDBConnectionString"].ConnectionString))
            {
                connection.Open();
 
                SqlCommand command = connection.CreateCommand();
                command.CommandText = @"DECLARE @conversationHandle uniqueidentifier;
                        DECLARE @message AS XML;
                        WAITFOR (RECEIVE @conversationHandle = [conversation_handle],
                            @message = CAST(message_body AS XML) FROM PersonChangeMessages);
                            END CONVERSATION @conversationHandle;
                            SELECT @message;";
                command.CommandTimeout = 60 * 5; //listen in 5 minute increments
 
                try
                {
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        SqlXml sqlXml = reader.GetSqlXml(0);
                        XmlReader xmlReader = sqlXml.CreateReader();
                        while (xmlReader.Read())
                        {
                            while (xmlReader.MoveToAttribute("LastName"))
                            {
                                currentContext.Cache.Remove(string.Format(cacheKey, xmlReader.Value));
                                break;
                            }
                        }
                    }
                }
                catch (SqlException)
                {
                    //if the query times out, that means that no messages have
                    //been sent to the queue yet, so we should keep listening.
                }
            }
        }
    }
 
    private void StopListening(IAsyncResult result)
    {
        //no information needed here.
    }
}

And finally, here's my service that retrieves the person:

public static Person GetPerson(string lastName)
{
    string cacheKey = string.Format("Person:{0}", lastName);
 
    Person person = (Person)HttpContext.Current.Cache.Get(cacheKey);
 
    if (person == null)
    {
        DataClasses1DataContext ctx = new DataClasses1DataContext();
        var query = from p in ctx.Persons
                    where p.LastName == lastName
                    select p;
 
        person = query.Single();
 
        Listener listener = new Listener();
        listener.Start();
 
        HttpContext.Current.Cache.Insert(cacheKey, person);
    }
    return 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.