Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

How to enable SQL Server Service Broker

1) Sql Server Service Broker is enabled per database , so  If you need to enable Service Broker on multiple databases , some sort of dynamic level sql is required , as there is no Server level command to activate all databases .

2) When enabling Service Broker on a  database consider that if you recover and restore from a copy that does not have Service Broker enabled you'll need to have a process to enable Service Broker.

3) Attempt to complete the enabling of Service Broker at a non-critical time. Service Broker Activation requires an exclusive lock on the database - so all transactions are terminated , and all transactions which have not commited will roll-back.

4)If you need to activate a database automatically upon CREATE DATABASE , you have an option to enable service broker on the model database. That way , every time the CREATE DATABASE statement is used , the newly created database will include service broker enabled

5)Once you've enabled Service Broker , you may need to investigate issues. A good starting point - to give you some insight into Service Broker and service broker related logs use Troubleshooting SQL Server Service Broker

Check to see if Broker Service is enabled on a database

SELECT     name, database_id, is_broker_enabled FROM sys.databases

Enable Service Broker on a SQL Server database

ALTER DATABASE [DB NAME] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE


This post first appeared on SQLSERVER-DBA.com, please read the originial post: here

Share the post

How to enable SQL Server Service Broker

×

Subscribe to Sqlserver-dba.com

Get updates delivered right to your inbox!

Thank you for your subscription

×