Using SqlCacheDependency with SQL Server 2005 in ASP.NET 2.0
Some time ago I spent an afternoon hunting around the web for the details on how the SqlCacheDependency in ASP.NET 2.0 works with SQL Server 2005. It turns out that the class performs an automatic configuration of a SQL Server 2005 query notification. Not sure what that is?Using and Monitoring Sql Query Notifications This is a truly excellent article to get started.
I've included the relevant links to SQL Books Online and quoted particularly relevant passages in italics. I certainly don't intend this post to be a "how-to", but I hope it helps the reader to get her arms around how to use the SqlCacheDependency and query notifications (by extension).
First, we only need to enable the service broker. Notification services is not utilized, surprisingly.
The Database Engine uses Service Broker to deliver notification messages. Therefore, Service Broker must be active in the database where the application requests the subscription. The query notification functionality does not require or use Notification Services. Query notifications are independent of event notifications.
Secondly, we cannot use query notifications for just any type of query. There are a lot of rules on how the query is constructed that my restrict what data you can cache with the SqlCacheDependency.
Creating a Query for Notification
The query notifications functionality builds on the change detection mechanisms that the Database Engine uses to maintain indexed views. The requirements and restrictions for statements in a query for notification are similar to the requirements and restrictions for an indexed view.
It seems the overhead associated with these query notifications is mainly on updates. I'm guessing that SQL Server uses a mechanism similar to its query plan caching/reuse mechanism to identify similar queries in subscriptions.
When query notifications are active on a table, updates to the table are more expensive. The Database Engine performs extra work to check subscriptions and, if necessary, generate notifications. Reusing internal templates helps to minimize the overhead per subscription. Therefore, you should use query notifications only for applications that submit queries with a similar structure. An application that submits queries with different structures should not use query notifications.
In addition to having normal execute and select permissions, the application user needs to have a couple of other permissions. Although granting DDL rights to the application seems deeply wrong to me, there is a wonderful article on granting minimum rights to the application.You can read more about them here.
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <database_principal>
GRANT RECEIVE ON QueryNotificationErrorsQueue TO <database_principal>
Because the SqlDependency object in the ASP.NET runtime auto-magically creates the Service Broker service, queue, and notification procedure, the application user also needs the following rights.
GRANT CREATE PROCEDURE TO <database_principal>
GRANT CREATE QUEUE TO <database_principal>
GRANT CREATE SERVICE TO <database_principal>
Actually, the class in the ASP.NET runtime that actually sets all this up is called the SqlConnectionContainer. It's quite the sophisticated class, doing all of the heavy-lifting. From what I gather, it creates procedures (named SqlQueryNotificationStoredProcedure-* where * is a GUID) that drop the service and queue when the conversation timer expires. I'm a little fuzzy on the low-level implementation, but I wanted to know why it needed these rights. So, I used Lutz Roeder's .NET Reflector to take a peek at what is going on inside the class.
The SQL Books online mentions that the user that attaches a query notification request to a command must have the following:
GRANT SEND ON SERVICE:://theservice to <database_principal>
However, the service doesn't exist until the SqlConnectionContainer creates it, so the SqlConnectionContainer grants the SEND ON SERVICE to a specific role: sql_dependency_subscriber. This role does not exist and must be created if you need to lock-down permissions as in the article above.
