Enabling Service Broker on a database

To check the status of service broker on a database run the following (in the database)…

SELECT is_broker_enabled
FROM master.sys.databases
WHERE database_id = DB_ID()

If the broker is enabled it will return a 1, otherwise it will return 0.

The catch…

You need exclusive access to the database to perform this task. That will mean a potential outage, no matter how small. You can run the following command to perform this in a single operation…

 

ALTER DATABASE <my_database> SET SINGLE_USER WITH ROLLBACK AFTER 5
ALTER DATABASE <my_database> SET ENABLE_BROKER
ALTER DATABASE <my_database> SET MULTI_USER


If you want to do this to a mirrored database, you can’t. You have to break the mirror, enable the broker and then reinstate the mirror from scratch. There is no other way.

This has other side effects too because if you are using the failover partner in your connection string, breaking the mirror is likely to cause connection errors.

This entry was posted in Service Broker. Bookmark the permalink.