Service Broker in SQL Server

Last Updated : 23 Jul, 2025

A service broker is a framework that helps applications exchange messages containing the information required to complete the task. It was introduced in MSSQL 2005 and is used to implement native in-database asynchronous message processing functionalities.

Service Broker introduces several new terminologies, which are as follows:

  • Message: A piece of information exchanged between applications. 
  • Conversation: A reliable, persistent, asynchronous exchange of messages.
  • Dialog: A conversation between two services.
  • Initiator: The participant that starts a dialog.
  • Target: The participant that accepts the dialog started by the initiator.
  • Conversation Group: A group of related conversations.
  • Contract: An agreement between two services about the type of messages allowed in a conversation.
  • Service: A task that can send and receive messages.
  • Queue: A storage area for messages for a particular service.

You can relate this to the postal service, The messages act like letters. A service is the address where the post office delivers the letters. The queue is the mailbox that holds the letters after they are delivered. the initiator is the sender and the target is the receiver

Sections of Service Broker Components

  • Conversation Components: Conversation groups, conversations, and messages.
  • Service Definition Components: Define the message types, conversation flow, and database storage.
  • Networking and Security Components: Define the infrastructure for exchanging messages between instances.

Creating a Simple Service Broker Application 

The basic steps involved in creating any Service Broker application include:

1. Defining Message Types: To define a message type for a Service Broker application, we can use the CREATE MESSAGE TYPE  statement. 

CREATE MESSAGE TYPE ServiceMessage
VALIDATION = NONE

2. Defining Contracts: Secondly, you can use the CREATE CONTRACT statement to define a contract.

CREATE CONTRACT ServiceContract
(ServiceMessage SENT BY INITIATOR)

3. Creating Queues: You can create a queue with the CREATE QUEUE statement. Two queues are created one for receiving service and other for the sending service.

CREATE QUEUE SendQueue
CREATE QUEUE ReceiveQueue

4. Creating Services: Two services are created using the below code 

CREATE SERVICE SendService
 ON QUEUE SendQueue (ServiceContract)
CREATE SERVICE ReceiveService
 ON QUEUE ReceiveQueue (ServiceContract)

5. Sending and Receiving Messages
Finally, we will send and receive messages using the following statements,

  • BEGIN DIALOG CONVERSATION: sets up the conversation between the two services.
  • SEND ON CONVERSATION: sends a message.
  • RECEIVE: receives a message.
Service Broker in SQL Server
Service Broker In SQL Server

There are two services in the above diagram, in which one is sending a message and another is receiving. This concept is called service broker. The service broker can work within a single DB or between multiple DBs.

Example 1: Sending a Single Message Between Two Services

Tool: SQL Server Management Server(SSMS)

Steps:

  • Start SSMS and MySQL server.
  • Create two query pages in SSMS.
  • Paste and execute the statements mentioned below

service_broker_create.sql: 

-- Creating a DB
CREATE DATABASE Sample_DB
GO
USE Sample_DB;
GO


-- Creating a message type for both initiator and target
CREATE MESSAGE TYPE
       [//gfg/ServiceRequest]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//gfg/ServiceResponse]
       VALIDATION = WELL_FORMED_XML;
GO


-- Creating a contract between the services
CREATE CONTRACT [//gfg/ServiceContract](
    [//gfg/ServiceRequest]  SENT BY INITIATOR,
    [//gfg/ServiceResponse]  SENT BY TARGET
      );
GO


-- Create queues
CREATE QUEUE serviceIntiatorQueue
WITH STATUS=ON;
GO
CREATE QUEUE serviceTargetQueue
WITH STATUS=ON;
GO
       


-- Finally the services
CREATE SERVICE
       [InitiatorService]
       ON QUEUE serviceIntiatorQueue
       ([//gfg/ServiceContract]);
GO

CREATE SERVICE
       [TargetService]
       ON QUEUE serviceTargetQueue
       ([//gfg/ServiceContract]);
GO

service_broker_send_receive.sql:

-- Declare a unique number and begin the transaction
DECLARE @ReqDialogHandle UNIQUEIDENTIFIER;
DECLARE @RequestMessage XML;
BEGIN TRANSACTION;
    BEGIN DIALOG @ReqDialogHandle
         FROM SERVICE
            [InitiatorService]
         TO SERVICE
               N'TargetService'
         ON CONTRACT
         [//gfg/ServiceContract]
         WITH ENCRYPTION = OFF;
    SELECT @RequestMessage =
           N'Hello World';
    
    SEND ON CONVERSATION @ReqDialogHandle
         MESSAGE TYPE 
           [//gfg/ServiceRequest]
         (@RequestMessage);
COMMIT TRANSACTION;
GO

-- Receiving from queue
DECLARE @ResponseDialogHandle UNIQUEIDENTIFIER
DECLARE @checkmessagename NVARCHAR(256)
DECLARE @messagebody XML
DECLARE @responsemessage XML
BEGIN TRANSACTION ;


--WAITFOR command is used to wait for 
messages to arrive on the queue, 
TIMEOUT is specified in miliseconds
WAITFOR(
RECEIVE TOP(1)
@ResponseDialogHandle = conversation_handle,
@checkmessagename = message_type_name,
@messagebody = CAST(message_body AS XML)
FROM ServiceTargetQueue),TIMEOUT 1000;
 
PRINT 'Conversation handle: ' + CAST(@ResponseDialogHandle AS NVARCHAR(MAX))
PRINT 'Message type: ' + @checkmessagename
PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX))

Output: After executing the above sql statement in SSMS it gives the following results

Output
Output

Example 2: Sending Multiple Messages Between Three Services

Sending multiples messages between 3 services with a single DB. where we have two receive services. Here we are sending two messages which are Hello World to one service and Bye... to some second service from a single sending service.

Steps:

  • start MSSQL
  • Create a new query page
  • Paste and execute the statements mentioned below

service_broker_create.sql: 

CREATE DATABASE Sample_DB
GO

USE Sample_DB;
GO

CREATE MESSAGE TYPE
       [//gfg/ServiceRequest]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//gfg/ServiceResponse]
       VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT [//gfg/ServiceContract](
    [//gfg/ServiceRequest]  SENT BY INITIATOR,
    [//gfg/ServiceResponse]  SENT BY TARGET
      );
GO
CREATE QUEUE serviceIntiatorQueue
WITH STATUS=ON;
GO
CREATE QUEUE serviceTargetQueue
WITH STATUS=ON;
GO
CREATE QUEUE serviceTargetQueueSecond
WITH STATUS=ON;
GO
       

CREATE SERVICE
       [InitiatorService]
       ON QUEUE serviceIntiatorQueue
       ([//gfg/ServiceContract]);
GO

CREATE SERVICE
       [TargetService]
       ON QUEUE serviceTargetQueue
       ([//gfg/ServiceContract]);
GO

CREATE SERVICE
       [TargetServiceSecond]
       ON QUEUE serviceTargetQueueSecond
       ([//gfg/ServiceContract]);
GO

service_broker_send_receive.sql:

DECLARE @ReqDialogHandle UNIQUEIDENTIFIER;
DECLARE @RequestMessage XML;
BEGIN TRANSACTION;
    BEGIN DIALOG @ReqDialogHandle
         FROM SERVICE
            [InitiatorService]
         TO SERVICE
               N'TargetService'
         ON CONTRACT
         [//gfg/ServiceContract]
         WITH ENCRYPTION = OFF;
    SELECT @RequestMessage =
           N'Hello World';
    
    SEND ON CONVERSATION @ReqDialogHandle
         MESSAGE TYPE 
           [//gfg/ServiceRequest]
         (@RequestMessage);

    BEGIN DIALOG @ReqDialogHandle
         FROM SERVICE
            [InitiatorService]
         TO SERVICE
               N'TargetServiceSecond'
         ON CONTRACT
         [//gfg/ServiceContract]
         WITH ENCRYPTION = OFF;
    SELECT @RequestMessage =
           N'Bye...';
    
    SEND ON CONVERSATION @ReqDialogHandle
         MESSAGE TYPE 
           [//gfg/ServiceRequest]
         (@RequestMessage);
COMMIT TRANSACTION;
GO



DECLARE @ResponseDialogHandle UNIQUEIDENTIFIER
DECLARE @checkmessagename NVARCHAR(256)
DECLARE @messagebody XML
DECLARE @responsemessage XML
BEGIN TRANSACTION ;

    --WAITFOR command is used to wait for messages to arrive on the queue, 
    TIMEOUT is specified in miliseconds
WAITFOR(
RECEIVE TOP(1)
@ResponseDialogHandle = conversation_handle,
@checkmessagename = message_type_name,
@messagebody = CAST(message_body AS XML)
FROM ServiceTargetQueue),TIMEOUT 1000;
 
PRINT 'Conversation handle: ' + CAST(@ResponseDialogHandle AS NVARCHAR(MAX))
PRINT 'Message type: ' + @checkmessagename
PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX));

DECLARE @ResponseDialogHandleSecond UNIQUEIDENTIFIER
DECLARE @checkmessagenameSecond NVARCHAR(256)
DECLARE @messagebodySecond XML
BEGIN TRANSACTION ;

    --WAITFOR command is used to wait for messages to arrive on the queue, 
    TIMEOUT is specified in miliseconds
WAITFOR(
RECEIVE TOP(1)
@ResponseDialogHandleSecond = conversation_handle,
@checkmessagenameSecond = message_type_name,
@messagebodySecond = CAST(message_body AS XML)
FROM ServiceTargetQueueSecond),TIMEOUT 1000;
 
PRINT 'Conversation handle: ' + CAST(@ResponseDialogHandleSecond AS NVARCHAR(MAX))
PRINT 'Message type: ' + @checkmessagenameSecond
PRINT 'Message body: ' + CAST(@messagebodySecond AS NVARCHAR(MAX))

Output: There are two messages from two different receiving services.

Conclusion

Service Broker in SQL Server is like a postal service within your database, enabling different parts of an application to communicate reliably and efficiently. By setting up message types, contracts, queues, and services, it ensures that messages are delivered correctly and stored properly. This makes it ideal for handling asynchronous tasks and complex workflows, providing a robust solution for reliable communication within and between databases.

Comment
Article Tags:
Misc
SQL
Databases
SQL-Server

Explore