I just emerged from SqlCacheDependency hell–actually, SQL Server 2005 hell. I thought I’d document the problem (and the solution) so others won’t have to waste as much time as I did.
First, I’ve been using SqlCacheDependency with great success since early betas of ASP.NET 2.0 and SQL Server 2005. I marveled at how little work was required to make dependencies work–especially if you use SQL Server 20005 rather than SQL Server 2000.
Then I uninstalled the most recent SQL Server 2005 beta and replaced it with the release version of SQL Server 2005 Developer Edition. All of a sudden my SqlCacheDependencies started throwing exceptions complaining that SQL Server 2005 Service Broker wasn’t enabled for the database I was targeting. I was calling SqlDependency.Start (a new requirement in the RTM version of ASP.NET 2.0), so I was puzzled as to why cache dependencies were suddenly barfing.
The solution wasn’t difficult once I discovered what it was. What cost me hours was figuring out how to enable Service Broker for a database. With all the glitzy new GUI admin tools in SQL Server 2005, you’d think it would be a button click or a dialog box. But no; that would be too easy. Some documentation would help, but neither SQL Server Books Online nor any other source I could find describes how to enable Service Broker for a database. So here’s how you do it:
1) Use SQL Server Management Studio or a similar tool to execute the following command to create a Service Broker endpoint:
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS );
2) Enable Service Broker for a given database (for example, Contoso) with this command:
ALTER DATABASE Contoso SET ENABLE_BROKER;
Now you can use SQL cache dependencies against the Contoso database.
One of the articles in Books Online states that Service Broker is enabled by default for databases. Don’t believe it. That statement is merely designed to throw you off and garner consulting fees for SQL Server DBAs. Elsewhere, Books Online correctly states that Service Broker is NOT enabled by default for individual databases. Nothing like self-conflicting docs to make life interesting.