Saturday, January 12, 2008

Overview of SQL Server 2005 Database Cache Invalidation Options - Part 3 SqlDependency

In my previous two posts, I reviewed a basic implementation of the Sql 2005 notifications for cache invalidation.  In this post, I'm going to go over the SqlDependency class and how to use with SQL Server 2005 notifications services.

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

SqlDependency

The SqlDependency class is a bit more flexible than the other two examples that I gave an overview for in my previous posts, as it is a lowe level class than the SqlCacheDependency and the ASP.NET page output caching.  This option has no dependency on the ASP.NET framework, an can be used with or without an HttpContext (which in turn means you can use it with Windows Forms application or even a console application).

What the SqlDependency class allows you to do is register an event that gets fired when a SqlCommand's result changes.  Again, the query needs to be a valid notification query (just as in the previous posts).

Below is the example:

public class PersonService
{
    private static List<Person> _people = null;
 
    public static List<Person> GetPeople()
    {
        if (_people == null)
        {
            DataClasses1DataContext ctx = new DataClasses1DataContext();
            var query = from p in ctx.Persons select p;
 
            SqlDependency dependency = new SqlDependency(ctx.GetCommand(query) as SqlCommand);
            SqlDependency.Start(ctx.Connection.ConnectionString);
            dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
 
            _people = query.ToList();
        }
        return _people;
    }
 
    static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
            _people = null;
    }
}

You see that my operation that retrieves the collection, looks to see if the collection exists in a static class-scoped variable.  If it does not, then I load up the collection, store it in the static variable, and wire up the event to handle the notifications coming from SQL Server.

Note that there's a couple different notification types for the notification event that is handled.  The other two options in the SqlNotificationType enumeration are "Subscribe" and "Unknown".

You can see how some the higher level classes discussed in my previous posts work after looking at the SqlDependency class in action.

Now, I mentioned being able to use this in Windows Forms applications, but this may not be the best option for cache invalidation in this case.  Having many, many clients listening for Sql Server 2005 notification is a very bad idea.  You'll quickly end up running out of available connections, and even before that, experiencing some  serious network and database performance issues.