Service Broker in Sql Server 2005, 2008
Service Broker is a message-queuing technology which allows developers to integrate SQL Server into distributed applications. Service Broker is a feature which provides facility to SQL Server to send an asynchronous, transactional message. It allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable.
The Service Broker consists of a number of SQL Server objects that represent the various components of the asynchronous messaging system. A service is a set of tasks that is available, while message types represent the messages exchanged to complete a task. The messages are held in a queue and these messages are processed at predefined intervals and sent. Once the message has been sent, the application updates the data to indicate that the message has been sent.
Enable service broker on database:
-- Enable Service Broker:
ALTER DATABASE [Database Name] SET ENABLE_BROKER
Disable service broker on database:
-- Disable Service Broker:
ALTER DATABASE [Database Name] SET DISABLE_BROKER;
Checking status of Service Broker
SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name';
-- Where 'Database name' is the name of the database you want to query.