SQL Cache Dependency

I am working on a new project for our sister company. For easy maintenance I have decided to spend some extra time on their News and Events section. The simple admin page will allow them to easily update the section by entering a date, title, description and optionally uploading PDFs or specifying an external link.

The site is written in ASP.NET 2.0 and the hosting package we use includes SQL 2005 database support. So I've decide to store the information on the SQL 2005 database.

Among the first steps for setting the SQL cache dependency up is to prepare the database for notification. One only needs to run the following query:

-- Enable Service Broker:

ALTER DATABASE [Database Name] SET ENABLE_BROKER;


I was using the SQL Server Management Studio and the query took forever to run. Then I read in various articles that the query requires exclusive lock on the database.

First, make sure you don't have any open session to the database. (ie: close your management studio and server explorer in visual studio, etc). Then, through the commandline browse to


c:\windows\microsoft.net\framework\v2.0.50727

and run


aspnet_regsql -ed -S server_address -U database_uid -P password -d database_name

for a complete list of available options, run

aspnet_regsql /?

You may need to modify the code for your particular set up. If everything goes well, you will see the following message.


Enabling the database for SQL cache dependency.

.

Finished

I will write more about my experience with SQL cache dependency once everything is up and running.