One of the challenges of a load balanced environment is cache invalidation across application boundaries. If you've got an data-centric application that makes heavy use of the in-memory ASP.NET cache (or any other in memory cache for that matter), and also have decided to provide a reliable hosting solution to you clients by using load balancing, then eventually you'll face the challenge of how to invalidate the cache across application domain boundaries.
In most cases, the one thing that a farm of servers share is the the database, so using the database to trigger the cache to invalidate is a reliable option. In SQL Server 2000, your only option for determining from ASP.NET when data has changed, is by polling. With SQL Server 2005, the introduction of the Service Broker has provided the ability for SQL Server to notify your application.
For ASP.NET 2.0, you've got a few options for using the database to invalidate the cache. Each of the options build upon each other, and as I explain them in this series of posts, they become more and more specific and thus more and more flexible. For this series of posts, I am going to be focusing on using these caching features with SQL 2005 Notification.
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):
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.
The next thing you'll have to do is update the Global.asax to setup the SqlDependency in the Application_Start method. You do this with the following code:
{
SqlDependency.Start(ConfigurationManager.ConnectionStrings["BlogExampleDBConnectionString"].ConnectionString);
}
When you call this method, the runtime sets up the relationship between the application domain and the connection string that will be used later during the notification registration process.
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.)
Output CachING
Once you've started the application listening for SQL Commands, if you specify "CommandNotification" for the value of the SqlDependency attribute within the OutputCache page directive, all SqlCommands that execute for the processing of this page will be registered for change notification in the SQL Server Service Broker.
Note that there are limitations on the T-SQL you can execute to be successfully registered for notifications. There is a SQL Server BOL article that goes over valid queries for SQL Notifications.
This is what the page directive ends up looking like:
And this is what the rest of the page looks like:
<%@ OutputCache SqlDependency="CommandNotification" Duration="3600" VaryByParam="none" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<%= System.DateTime.Now.ToString() %>
<asp:GridView ID="myGridView" runat="server" DataKeyNames="PersonID" DataSourceID="myDataSource"
AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderText="First Name" DataField="FirstName" />
<asp:BoundField HeaderText="Last Name" DataField="LastName" />
</Columns>
</asp:GridView>
</div>
<asp:SqlDataSource ID="myDataSource" runat="server" ConnectionString="<%$ConnectionStrings:BlogExampleDBConnectionString%>"
SelectCommand="SELECT PersonID, FirstName, LastName FROM dbo.Person" SelectCommandType="Text" Sql />
</form>
</body>
</html>
Note that ALL commands executed with valid SQL will cause the page to be cached. Those include statements cached within the page's SQLDataSource controls, as well as any valid queries that are in the business objects used by the page.
SQL Server and ASP.NET manage this by creating a call context and the SqlCommand uses that context during the execution of the query to register the query for notification.
SqlDataSource
In addition to providing the ability to cache the entire page, you do have the option to cache commands for individual SqlDataSources as well. Below is the same SqlDataSource as above, but with the additional two attributes EnableCaching and SqlCacheDependency.
SelectCommand="SELECT PersonID, FirstName, LastName FROM dbo.Person" SelectCommandType="Text"
EnableCaching="true" SqlCacheDependency="CommandNotification" />
In this example, the wiring that ASP.NET needs to do is far easier. It can use the commands provided to the control to wire the SQL 2005 notifications.
You can see there's quite a bit of power in just these two applications of SQL 2005 cache dependencies. In the posts following this one, I'll go over the the SqlCacheDependency object, the SqlDependency object and also dive into SQL Server 2005 Service Broker programming.
Digg It!
Add to del.icio.us
2 comments:
Jim,
It seems in SQLServer 2008 notification services is not supported.
Do you know how these examples can be run against a SQLServer 2008 db?
sanagarwl
Hi Sanagarwl -
Yes, SQL 2008 removed SSNS and, I'm sorry to say, that these samples will not work at all on SQL 2008.
Jim
Post a Comment