Summary of usage of the "cache" SQL Server 2005/2008 sqlcachedependency query notification

Under SQL Server 7.0/2000, SqlCacheDependency uses polling for cache failure checking, although Ms says there is little pressure on the server, but there are still some, and the endless polling for infrequently modified mixed content is a bit wasteful and not very economical.

SQL Server 2005/2008 increased the use of query notification method for cache invalidation, it through the internal SQL Server Message Queuing asynchronous notification, which greatly reduces the pressure on the server, the implementation is very economical, the following is the specific steps:

1. Detect if service Broker is enabled

Select databasepropertyex (' Database name ', ' isbrokerenabled ')--1 indicates that 0 is not enabled

2. Enable Service Broker

ALTER database name SET enable_broker;

Note: If you execute this statement in suspended animation, restart (restart) the database, and then do nothing, first execute the above-enabled Service Broker statement on the line!

3. Grant permissions to your database access account

GRANT SUBSCRIBE QUERY notifications to test

Note: This step is very important, if there is no permission, the database changes the notification will not be received, the cache will never be refreshed, I started to use the SA account, life and death are not refreshed, it took me two days to debug the problem, or can not grant this permission to SA (Ms Forbidden), so, change the database access account can be .

4. Check for previous cache errors

Hide Line number copy code ? This is a program code.

2. [Master]    

4. GO    

6. SET  single_user with ROLLBACK IMMEDIATE    

8. GO    

10. SET      

12. GO    

16. GO    

18. SET      

5, start SqlDependency monitoring.

Add the following code to the Application_Start and Application_End in Global.ascx:

Hide Line number copy code ? This is a program code.

2. Application_Start (e)

4. {

6.     ConnString2 = System.Web.Configuration. WebConfigurationManager. connectionstrings["NorthwindConnectionString1"]. ConnectionString;

8.     System.Data.SqlClient. SqlDependency. Start (CONNSTRING2);

10. }

14. Application_End (e)

16. {

18.     ConnString2 = System.Web.Configuration. WebConfigurationManager. connectionstrings["NorthwindConnectionString1"]. ConnectionString;

20.     System.Data.SqlClient. SqlDependency. Stop (CONNSTRING2);

22. }

6. Main procedure

WEBFROM1: Single Table cache

Webfrom2:linq to SQL Cache

Webfrom3:linq to SQL multi-table cache

Summary of usage of the "cache" SQL Server 2005/2008 sqlcachedependency query notification