SQL Server 2005 supports notifications, allowing it to tell you when data has changed. This is useful for many reasons, invalidating the ASP.NET cache for example. Someone asked me about this at DeveloperDeveloperDeveloper (which was most excellent as usual) and Al & I also had a query about it. The latter query was for a SiteMap provider that uses a databases, which needs to be notified when the data it depends upon changes. Since I always have to look up the steps I thought I'd blog them so it's easy to find.
- Cache invalidation works with both full and express editions of SQL Server 2005. The only constraint is that you must have an attached database - a user instanced express database (ie auto-attached from App_Data) will not provide notifications.
- The database must be at version 9 for its compatibility level. You can change this with:
exec sp_dbcmptlevel 'Northwind', '90'
- You must create a broker endpoint:
USE master
GO
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS )
GO
ALTER DATABASE db_name SET ENABLE_BROKER;
GO
Replace db_name with the database name.
- If you are connecting to SQL as a non-admin (which you should be) then you need to grant permissions for SQL Notifications:
-- sql_dependencey_subscriber role in SQL Server
EXEC sp_addrole 'sql_dependency_subscriber
-- Permissions needed for users to use the Start method
GRANT CREATE PROCEDURE to startUser
GRANT CREATE QUEUE to startUser
GRANT CREATE SERVICE to startUser
GRANT REFERENCES on CONTRACT:: [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to startUser
GRANT VIEW DEFINITION TO startUser
-- Permissions needed for users to Execute
GRANT SELECT to executeUser
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO executeUser
GRANT RECEIVE ON QueryNotificationErrorsQueue TO executeUser
GRANT REFERENCES on CONTRACT:: [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to executeUser
EXEC sp_addrolemember 'sql_dependency_subscriber', 'executeUser'
Replace startUser with the name of the user who will start the dependency and executeUser with the user who will execute the commands requiring the dependency. This will probably both be the same user and more often than not will be the ASPNET user.
- Use the correct query syntax. You must use explicit column names (you cannot use SELECT *) and the table name must be qualified with its owner (eq dbo.tblMenu).
- You must start the dependency monitoring, probably in
Application_Start in global.asax:
SqlDependency.Start("your connection string here");
The worst part about all of this is that it all fails silently. You'll receive an exception if you don't call the Start method, but otherwise nothing.