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.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.