Enabling Service Broker in SQL Server 2008

Thursday

Activate  Service Broker in SQL Server 2008:
                          If you are working in sql dependency, SignalR related projects you must enable service broker for  messaging and queuing functions between instances. The basic functions of sending and receiving messages forms a part of a “conversation.”

What is services broker?

                        According  from Microsoft SQL Server Service Broker provides native support for messaging and queuing applications in the SQL Server Database Engine. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications.

                        Service broker find applications when single or multiple SQL server instances are used. This functionality helps in sending messages to remote databases on different servers and processing of the messages within a single database. In order to send messages between the instances, the Service Broker uses TCP/IP.

Check the sql server services broker status?

                          Before enable service broker better  we need check the current services broker status of the database. The following simple command used to do that.

SELECT name, is_broker_enabled FROM sys.databases

Then activate services broker on "codingclusterDB" database by using the following command.

ALTER DATABASE CodingClusterDB SET ENABLE_BROKER
GO

Enable SQL Server Broker taking too long time?

                                         If your Alter Database query takes long time to process, then use the following command to activate services broker on your database

ALTER DATABASE CodingClusterDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO