Chicago .NET Users Group Presentation on SQL Cache Dependency Patterns

Thanks to all that came out.  I appreciate you taking a listen.

I've uploaded the Sql Cache Dependency Patterns presentation notes and demos for you all to enjoy.  Also, below is the copy from the handout that was available at the meeting (also in the zip file for download).

Summary of SQL Server 2005 Cache Dependency Patterns

Declarative SqlDependency

Overview

Declaratively add database dependencies into your ASP.NET web applications using an attribute-based approach. Dependencies can be added at the page level for all data sources, or at the individual SqlDataSource level.

Appropriate Uses

If there is heavy use of ASP.NET declarative databinding using simple T-SQL queries.

Pros

Extremely simple to use, piggy backs on the flexible OutputCache scheme.

Cons

Only available to web applications. Severely restricts query composition and database options.

System.Web.Cache.SqlCacheDependency.

Overview

Implementation of CacheDependency that integrates database dependency into the existing System.Web.Cache architecture.

Appropriate Uses

If there is heavy use of the System.Web.Cache architecture, and T-SQL queries are simple.

Pros

Very simple to use, and leverages the System.Web.Cache CacheDependency architecture.

Cons

Only available to web applications. Severely restricts query composition and database options.

System.Data.SqlClient.SqlDependency

Overview

Provides an event-based approach to the problem by notifying the application of when the set of data has changed.

Appropriate Uses

When cache storage is home grown, or complicated to invalidate.

Pros

Available to both WinForms and ASP.NET. Allows flexibility in the clearing of cache, which provides a wider range of cache storage options.

Cons

Severely restricts query composition and database options.

System.Data.SqlClient.SqlNotificationRequest

Overview

This is the lowest level of SqlNotifications provided by the .NET Framework. Monitors query changes and executes the delivery of messages to the Service Broker Queues .

Appropriate Uses

More control is required around how often the application is notified of changes.

Pros

Provides ultimate control over how the notification process is handled.

Cons

Queries and database options are still highly restrictive. No control of the message sent to the notification listener. Lots of plumbing to write.

Roll Your Own with SQL Server Service Broker

Overview

A custom approach, leveraging the SQL Server Service Broker to handle data changes and notification delivery.

Appropriate Uses

When a highly granular, row based cache dependency scheme is in place.

Pros

Allows you to receive detailed information about the exact record that caused the notification. Ultimate flexibility around the entire cache invalidation strategy. Does not restrict the types of commands that you need to execute.

Cons

Can be complicated to implement depending on how complex the cache invalidation rules are. Lots of plumbing to write.

Gotchas

· Not all versions of SQL Server have the SQL Server Service Broker. See http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.

· You must enable the Service Broker per database. See the \SQL\CreateNewBroker.sql from the sample application

· You must create an encryption key per database. See the \SQL\ CreateNewBroker.sql from the sample application

· The DB principal of the database is incorrect because it has been restored from another server. See http://support.microsoft.com/kb/913423

· You must call System.Data.SqlClient.SqlDependency.Start() if using SqlDependency or SqlCacheDependency.

· Queries must abide by the following rules if you aren’t rolling your own: http://msdn.microsoft.com/en-us/library/ms181122.aspx

· LINQ to SQL must generate valid queries.

· Wiring the SqlDependency, SqlCacheDependency and SqlNotificationRequest must happen prior to executing the command.