SqlServer Cash Dependencies
Using SQL Cache Dependencies This is the C# tutorial (Switch to the Visual Basic tutorial) The simplest caching strategy is to allow cached data to expire after a specified period of time. But this simple approach means that the cached data maintains no association with its underlying data source, resulting in stale data that is held too long or current data that is expired too soon. A better approach is to use the SqlCacheDependency class so that data remains cached until its underlying data has been modified in the SQL database. This tutorial shows you how. Download the code for this tutorial | Download the tutorial in PDF format « Previous Tutorial | Next Tutorial » Introduction The caching techniques examined in the Caching Data with the ObjectDataSource and Caching Data in the Architecture tutorials used a time-based expiry to evict the data from the cache after a specified period. This approach is the simplest way to balance the performance gains of caching against data staleness. By selecting a time expiry of x seconds, a page developer concedes to enjoy the performance benefits of caching for only x seconds, but can rest easy that her data will never be stale longer than a maximum of x seconds. Of course, for static data, x can be extended to the lifetime of the web application, as was examined in the Caching Data at Application Startup tutorial. When caching database data, a time-based expiry is often chosen for its ease of use but is frequently an inadequate solution. Ideally, the database data would remain cached until the underlying data has been modified in the database; only then would the cache be evicted. This approach maximizes the performance benefits of caching and minimizes the duration of stale data. However, in order to enjoy these benefits there must be some system in place that knows when the underlying database data has been modified and evicts the corresponding items from the cache. Prior to ASP.NET 2.0, page developers were responsible for implementing this system. ASP.NET 2.0 provides a SqlCacheDependency class and the necessary infrastructure to determine when a change has occurred in the database so that the corresponding cached items can be evicted. There are two techniques for determining when the underlying data has changed: notification and polling. After discussing the differences between notification and polling, we ll create the infrastructure necessary to support polling and then explore how to use the SqlCacheDependency class in declarative and programmatically scenarios. Understanding Notification and Polling There are two techniques that can be used to determine when the data in a database has been modified: notification and polling. With notification, the database automatically alerts the ASP.NET runtime when the results of a particular query have been changed since the query was last executed, at which point the cached items associated with the query are evicted. With polling, the database server maintains information about when particular tables have last been updated. The ASP.NET runtime periodically polls the database to check what tables have changed since they were entered into the cache. Those tables whose data has been modified have their associated cache items evicted. The notification option requires less setup than polling and is more granular since it tracks changes at the query level rather than at the table level. Unfortunately, notifications are only available in the full editions of Microsoft SQL Server 2005 (i.e., the non-Express editions). However, the polling option can be used for all versions of Microsoft SQL Server from 7.0 to 2005. Since these tutorials use the Express edition of SQL Server 2005, we will focus on setting up and using the polling option. Consult the Further Reading section at the end of this tutorial for further resources on SQL Server 2005 s notification capabilities. With polling, the database must be configured to include a table named AspNet_SqlCacheTablesForChangeNotification that has three columns – tableName, notificationCreated, and changeId. This table contains a row for each table that has data that might need to be used in a SQL cache dependency in the web application. The tableName column specifies the name of the table while notificationCreated indicates the date and time the row was added to the table. The changeId column is of type int and has an initial value of 0. Its value is incremented with each modification to the table. In addition to the AspNet_SqlCacheTablesForChangeNotification table, the database also needs to include triggers on each of the tables that may appear in a SQL cache dependency. These triggers are executed whenever a row is inserted, updated, or deleted and increment the table s changeId value in AspNet_SqlCacheTablesForChangeNotification. The ASP.NET runtime tracks the current changeId for a table when caching data using a SqlCacheDependency object. The database is periodically checked and any SqlCacheDependency objects whose changeId differs from the value in the database are evicted since a differing changeId value indicates that there has been a change to the table since the data was cached. Step 1: Exploring the aspnet_regsql.exe Command Line Program With the polling approach the database must be setup to contain the infrastructure described above: a predefined table (AspNet_SqlCacheTablesForChangeNotification), a handful of stored procedures, and triggers on each of the tables that may be used in SQL cache dependencies in the web application. These tables, stored procedures, and triggers can be created through the command line program aspnet_regsql.exe, which is found in the $WINDOWS$\Microsoft.NET\Framework\version folder. To create the AspNet_SqlCacheTablesForChangeNotification table and associated stored procedures, run the following from the command line: /* For SQL Server authentication… */ aspnet_regsql.exe -S server -U user -P password -d database -ed /* For Windows Authentication… */ aspnet_regsql.exe -S server -E -d database -ed Note: To execute these commands the specified database login must be in the db_securityadmin and db_ddladmin roles. To examine the T-SQL sent to the database by the aspnet_regsql.exe command line program, refer to this blog entry. For example, to add the infrastructure for polling to a Microsoft SQL Server database named pubs on a database server named ScottsServer using Windows Authentication, navigate to the appropriate directory and, from the command line, enter: aspnet_regsql.exe -S ScottsServer -E -d pubs -ed After the database-level infrastructure has been added, we need to add the triggers to those tables that will be used in SQL cache dependencies. Use the aspnet_regsql.exe command line program again, but specify the table name using the -t switch and instead of using the -ed switch use -et, like so: /* For SQL Server authentication… */ aspnet_regsql.exe -S server -U user -P password -d database -t tableName -et /* For Windows Authentication… */ aspnet_regsql.exe -S server -E -d database -t tableName -et To add the triggers to the authors and titles tables on the pubs database on ScottsServer, use: aspnet_regsql.exe -S ScottsServer -E -d pubs -t authors -et aspnet_regsql.exe -S ScottsServer -E -d pubs -t titles -et For this tutorial add the triggers to the Products, Categories, and Suppliers tables. We ll look at the particular command line syntax in Step 3.
