SqlDependency – Creating a Smarter Cache

I recently got the chance to learn about a technology that has interested me for quite some time: SQL dependencies.  The premise of my encounter with SqlDependency was to create a smarter cache.  The project I’m currently working on was employing a sliding expiration policy to invalidate the contents of a cache of database lookup values and I proposed some research be conducted into using SQL dependencies to automatically invalidate the cache items when the corresponding database contents were changed.  I had experimented with the SQL dependency classes when .NET 2.0 was first released using a test SQL 2000 database.  The example I was using employed a polling strategy to keep the contents of an ASP.NET web cache item fresh.  While the technology was cool, I was less than enamored by the fact that the web server was polling the database for changes.  It felt a little too similar to setting a sliding expiration value of 5 seconds on the cache and pinging the database that way.

I was, however, aware of the fact that SQL 2005 notifications could be used to invalidate the contents of a cache without the need for polling.  With this in mind, I set about investigating the possibilities of SQL dependencies using .NET 2.0 and SQL 2005.  While this certainly isn’t a new topic, I found that very little good documentation exists on the topic and decided to write this article to hopefully bring some clarity to others who might wish to take advantage of the technology.

When is this a good solution?
The gap that this solution is attempting to fill is the short window where data can be stale with a sliding expirationon cache items.  If the data in the database has changed but the sliding expiration on the cache has not yet expired then the values in the cache will be out of date but still considered valid and hence returned to the UI.  Using SQL 2005 notifications will invalidate the contents of the cache almost as soon as the database contents are changed.  You will need to determine for your application how long data in the cache can be stale before the usefulness of the application is compromised.  If the sliding expiration of cache items can be shortened to compensate satisfactorily then that may provide a “better” solution that SQL dependencies.  However, if near real-time cache invalidation is desirable then SQL dependencies using notifications could provide a good solution.

How does the solution work?
The basic mechanics of the solution involve a cache object, a rowset within SQL (I’ll simplify for now and just say a table), a trigger and a notification queue.  There are many moving parts under the scenes that make the technology come together, but that covers the basic list of participants.  In simplified terms, a rowset is read from the database and an object representation of that data is added to a cache.  A SQL dependency is established upon that rowset and a callback method is registered for that dependency such that when the rowset changes the callback method will be invoked.  A trigger is used within the database to detect when the rowset changes, and a notification queue within SQL Server is used to broker a message back to cause the callback method to be invoked.  The callback method invalidates the contents of the cache.  The next time the data is requested from the cache, the contents are found to be invalid and the process starts again.

Creating a Sample Application
This example uses Visual Studio 2005 and SQL Server 2005 to set up a sample database and two-tier web application running on the same machine to demonstrate use of the SqlDependency class as part of a caching strategy.

Let’s start by creating a sample database called SqlDependExample.  Add a table to the database called Color.  This table has two columns: ColorID, and ColorName.  It will represent a simple lookup table that contains a list of colors within your application.  Make the ColorID column an int that does not allow nulls, the primary key and set (Is Identity) to Yes.  Make the ColorName column a varchar(100) that does not allow nulls.  Add a couple of rows of data to the table with the following values: 1   Red 2   Blue 3   Green 4   Yellow

Create a login on the SQL Server called SqlDependExampleUser that uses SQL Server Authentication, and choose an appropriate password.  Add a user called SqlDependExampleUser to the SqlDependExample database for the SqlDependExampleUser login and grant the db_datareader, db_datawriter, and db_ddladmin roles.

Now we need to configure the database to be able to use the service broker and grant some additional permissions to the user we created in order that they can create and manage objects necessary for the notification strategy to work.  The following script performs each of the necessary steps to configure the database and grant the required permissions.  It is important to note that the first step requires SQL Server to have an exclusive lock on the database while performing the query, so be sure to close out any unused connections in the management studio; including the dialog that was used to create the Color table.

Open a query window and paste the following script: &nbps;(be sure that the selected database is SqlDependExample, not master)
ALTER DATABASE SqlDependExample SET ENABLE_BROKER
GO

GRANT CREATE PROCEDURE TO [SqlDependExampleUser];
GO

GRANT CREATE SERVICE TO [SqlDependExampleUser];
GO

GRANT CREATE QUEUE TO [SqlDependExampleUser];
GO

GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [SqlDependExampleUser];
GO

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [SqlDependExampleUser];
GO

GRANT CONTROL ON SCHEMA::[dbo] TO [SqlDependExampleUser];
GO

GRANT IMPERSONATE ON USER::DBO TO [SqlDependExampleUser];
GO

Execute the script to enable the service broker for the SqlDependExample database and grant the necessary permissions to SqlDependExampleUser.

Now, open Visual Studio and create a new project (File -> New Project).  Select Class Library as the project type, enter SqlDependExample as the solution name, and enter SqlDependExampleCache as the project name.  Add a class to the project called CacheProvider and paste in the following code:
using System;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;

namespace SqlDependExampleCache {

public class CacheProvider {

///
/// Reads the list of colors from the cache.
///
///
public StringCollection GetColorList() {
if (this.colors == null) {
this.colors = this.ReadColorList();
}
return this.colors;
}

///
/// Reads the list of colors from the database.
///
/// A that contains the list of colors.
private StringCollection ReadColorList() {
string connStr = “Database=SqlDependExample;Server=.;User ID=SqlDependExampleUser;Password=supersecret;”;
StringCollection colors = new StringCollection();

using (SqlConnection conn = new SqlConnection(connStr)) {
conn.Open();
using (SqlCommand cmd = new SqlCommand(“SELECT ColorID, ColorName from dbo.Color”, conn)) {
SqlDependency.Start(connStr);
SqlDependency sqlDependency = new SqlDependency(cmd);
sqlDependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
while (reader.Read()) {
colors.Add(reader[“ColorName”].ToString());
}
}
}
}

return colors;
}

///
/// Raised when the underlying rowset changes. Invalidates the list of colors.
///
void sqlDependency_OnChange(object sender, SqlNotificationEventArgs e) {
this.colors = null;
}

///
/// Internal list of colors.
///
private StringCollection colors;

}
}

Now add a new web site to the solution named SqlDependExampleWeb.  Add it as a file system web site underneath the solution directory we created earlier that was called SqlDependExample.  Open the property pages for the web site, select the References tab and add the SqlDependExampleCache project as a reference to the project.  Open the Default.aspx page and add the following bulleted list definition between the main div tags in the page:

Now open the code-behind file for the page and paste the following code as the contents of the class: using System;
using System.Collections.Specialized;
using System.Web;
using System.Web.UI.WebControls;

using SqlDependExampleCache;

public partial class _Default : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) {

CacheProvider cp = new CacheProvider();
StringCollection colors = cp.GetColorList();

foreach (string color in colors) {
ddlColorList.Items.Add(new ListItem(color));
}
}

}

Add a breakpoint to line 50 of the CacheProvider class (this.colors = null), select the SqlDependExampleWeb as the StartUp project and hit F5 to begin debugging.  Answer yes that a new web.config should be created to enable debugging when prompted and the web site should run, displaying the default.aspx page.  The four colors Red, Blue, Green, and Yellow that we added to the Color table earlier should be displayed in a bulleted list.  Now open the Color database table in SQL Management Studio and add a new row for the color Purple.  You should notice that the breakpoint on line 50 of CacheProvider is active.  This is because the SQL dependency on the Color table has fired and raised the OnChange callback event.  Allow execution to continue and the full list of five colors will be displayed in the page.

This short demonstration should give a good idea of the mechanism that is being employed.  The cache is vastly oversimplified and documentation is lacking simply for the brevity of the example.  However, the principal holds for a larger application that a rowset can be monitored by SQL server and a callback method invoked when that rowset changes thus prompting some action.

Some important points to note here are that the query syntax for rowsets that can be monitored is quite restrictive.  The query used in this example was:
SELECT ColorID, ColorName from dbo.Color

The table name is qualified both with the owner and table name.  This is required for the SQL dependency to work correctly with the row set.  A number of additional restrictions apply to the queries that can be used with SQL dependencies.

This entry was posted in SQL Server and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *