Enabling SQL Service Broker in SQL Server 2005 Express

A piece of project Melbourne seems to crave the features provided by the SQL Service Broker and the SqlCacheDependency of .NET 2.0. It's the fact that we're using data that doesn't change all that often and we want to avoid going to the database each time to look it up.

Off we go, the first thing to do is to enable Service Broker in the database, since by default it is disabled. If you don't believe me, try this query that shows whether the broker is enabled or not in each of your databases:

USE master;
SELECT name, is_broker_enabled FROM sys.databases;

Then, the code to enable the Service Broker in your database is:

USE master;
ALTER DATABASE melbourne SET ENABLE_BROKER;

Now, after watching that little green dot chase its tail for about 5 minutes, not seemingly getting anywhere, I found this blog post, where the solution was in a comment - this query will not complete if other connections are open to the database! So be sure you have no other windows open in the Management Studio and that users are not connecting to this database.