Saturday, January 19, 2008

Overview of SQL Server 2005 Database Cache Invalidation Options - Part 4 SqlNotificationRequest

In my previous posts, I reviewed three basic implementation of the Sql 2005 notifications for cache invalidation.  In this post, I'm going to review the SqlNotificationRequest class.

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

SqlNotificationRequest

The SqlNotificationRequest object is the lowest level class you can use for SQL Server 2005 notifications in the .NET Framework.  If you open up Reflector, you'll see that each of the options I outlined in previous posts use this class.  You can attach a SqlNotificationRequest to a SqlCommand that tells the SQL Server Service Broker that any changes to the results of this command should be sent to a Service Broker Queue.

Here is the high level outline of what is going on in the example below:

  1. User requests data for the first time, and it does not exist in cache.
  2. We create the SqlCommand and attach a SqlNotificationRequest to it.
  3. We then start a background thread that is listening to the Service Broker Queue
  4. We put the results of the command in the cache.
  5. When an item is received from the Service Broker Queue, we clear the cache.

The first thing we need to do is create the SQL Server Service Broker Service and Queue entities.  This is something that the SqlCacheDependency and SqlDependency objects did for us.

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

The result of this will be a service for monitoring changes, and a queue for changes, both of these in SQL Server:

service broker screen shot

The next thing we need to do is create our listener.  The listener will have a method that is called asynchronously.  This method will execute a query that listens to the message queue for new messages.  If it receives a message, it will process that message, and then start listening again.

public delegate void AsyncListenerDelegate(HttpContext currentContext);
 
public class Listener
{
    private static readonly string cacheKey = "personsCacheKey";
    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 = "WAITFOR (RECEIVE * FROM PersonChangeMessages);";
                command.CommandTimeout = 60 * 5; //listen in 5 minute increments
 
                try
                {
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                        currentContext.Cache.Remove(cacheKey);
                }
                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.
    }
}

Now that we have our listener, we can update our Person getting method to add the SqlNotificationRequest to the command.  Note that in my previous examples I used Linq to SQL, however in this one, I am using plain old ADO.NET.  I was unable to use the SqlNotificationRequest with the DataContext object.  Maybe someone out there has figured that one out.  If so, let me know. 

Also note that we still need to be using a valid query for notification.

public static List<Person> GetPeople()
{
    string cacheKey = "personsCacheKey";
 
    List<Person> people = (List<Person>)HttpContext.Current.Cache.Get(cacheKey);
 
    if (people == null)
    {
        people = new List<Person>();
 
        using (SqlConnection connection = new SqlConnection(
            ConfigurationManager.ConnectionStrings["BlogExampleDBConnectionString"].ConnectionString))
        {
            connection.Open();
 
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT PersonID, FirstName, LastName FROM dbo.Person;";
 
            SqlNotificationRequest notificationRequest = new SqlNotificationRequest();
            notificationRequest.UserData = Guid.NewGuid().ToString(); //uniquely identifies notification
            notificationRequest.Options = "Service=PersonChangeNotifications"; //specifies service to use
            notificationRequest.Timeout = 60 * 4;  //make sure this is less than your listener timeout
 
            command.Notification = notificationRequest;
 
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                people.Add(new Person() {
                    PersonID = reader.GetGuid(0), FirstName = reader.GetString(1), LastName = reader.GetString(2)
                });
            }
        }
 
        Listener listener = new Listener();
        listener.Start();
 
        HttpContext.Current.Cache.Insert(cacheKey, people);
    }
    return people;
}

As you can see, this method is a bit more involved, but from here, you start getting even more of an idea of what is going on with interaction between the SQL Server Service Broker and the ASP.NET runtime, especially in light of the previous methods I discussed in earlier posts.

I've got one more to go in this series of posts, and it covers a roll-your-own approach that provides ultimate flexibility in you cache notifications.

2 comments:

Anonymous said...

I dont understand your aproximation...
Why do you use SqlNotificationRequest here? if you use a trigger and you are checking the messages table, why do you need alert from SqlNotificationRequest?

In your example, if you comments this lines,the code works the same...



SqlNotificationRequest notificationRequest = new SqlNotificationRequest();
notificationRequest.UserData = Guid.NewGuid().ToString(); //uniquely identifies notification
notificationRequest.Options = "Service=PersonChangeNotifications"; //specifies service to use
notificationRequest.Timeout = 60 * 4; //make sure this is less than your listener timeout

command.Notification = notificationRequest;

Jim Fiorato said...

I was just showing that this method is possible. Yes, you may use triggers, however in this example I wanted to show that the SqlNotificationRequest registration is an option if you don't want to use triggers.