Enable Service Broker in SQL Server database

Enable Service Broker in SQL Server database

If you are new to Service Broker and want to know what service broker is and where can we use service broker, you can have a look at my two earlier articles.

Importance of Service Broker in SQL Server (Click Here)

Introduction of Service Broker in SQL Server (Click Here)

Once you understand what Service Broker is and finally decides to implement it in your database, you have to enable Service Broker for you database. Let us have a look at it.

[sourcecode language=”sql”]
–Enable service broker in SQL Server database for Adventureworks2012 database
ALTER DATABASE Adventureworks2012 SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

— TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the
–database and the contents within it. setting it ON if service broker is connecting outside database/instance
ALTER DATABASE Adventureworks2012 SET TRUSTWORTHY ON;
GO
[/sourcecode]

Once you enable service broker in your SQL Server database, you can confirm whether Service Broker enabled in your database or not. Execute following TSQL in your SQL Server instance.

[sourcecode language=”sql”]
SELECT is_broker_enabled,* FROM sys.databases
WHERE name=’Adventureworks2012′
GO
[/sourcecode]

If you get “1” in “is_broker_enabled” field in above SELECT query than you are fine as Service Broker is already enabled now.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Enable Service Broker in SQL Server database”

Comments are closed.