*Words of Wisdom From The Elder

This past week we were building some integration tests for a new feature we are adding into our framework.  In order to do the integration tests we needed to enable Service Broker on the SqlExpress database so SqlDependency could be used.

NOTE: Before you try to run this, be sure you are using the correct version as noted on MSDN.

Microsoft SQL Server 2005 Express Edition (SQL Server Express) database engine supports Service Broker only with a different edition of SQL Server 2005, such as Workgroup, Enterprise or Developer edition. Service Broker functionality is not supported between two editions of SQL Server Express.

The easiest way to enable ServiceBroker is connect to your database file and within the TSQL Editor run the following commands.

   1: alter database [C:\PATH\TO\DB.MDF] set NEW_BROKER

   2:  

   3: USE master;

   4:  

   5: ALTER DATABASE [C:\PATH\TO\DB.MDF] SET ENABLE_BROKER;

To see whether or not these commands work, run the following commands.  You should see that broker is enabled.

   1: USE master;

   2:  

   3: SELECT name, is_broker_enabled FROM sys.databases where name = 'C:\PATH\TO\DB.MDF'

DISCLAIMER: We have not finished running our tests yet to see if this works as we expected.  Stay tune for updates.