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.

 --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
 

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.

 SELECT is_broker_enabled,* FROM sys.databases
 WHERE name='Adventureworks2012'
 GO
 

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.

One thought on “Enable Service Broker in SQL Server database

Comments are closed.