Service Broker in SQL Server

Service broker is a framework that helps applications to exchange messages that contain the information required to complete the task. It was intoduced in MSSQL 2005 and it is used to implement native in-database asynchronous message processing functionalities.

Service Broker introduces a number of new terminologies, that are as follows:

message : It is a piece of information that is exchanged between applications.
conversation : It is a reliable, persistent , asynchronous exchange of messages.
dialog : It is a conversation between two services.
initiator : It is the participant that starts a dialog.
target : It is the participant that accepts the dialog started by the initiator.
conversation group is a group of related conversations.
contract : is an agreement between two services about the type of messages allowed in a conversation.
service : It is a particular task that can send and receive messages.
queue : It stores the 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 hold the letters after they are delivered. initiator is the sender and target is the receiver

The above terminologies can be divided into 3 sections :

Conversation components : Conversation groups, conversations, and messages.
Service definition components : They define the message types for the application, the conversation flow for the application, and the database storage for the application.
Networking and security components : These components define the infrastructure used to exchange 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.

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

Below is the implementation of above steps :

Example 1 : Sending a single message between two services.Here we are creating a service broker that exchanges messages within a same DB.

Tool : SQL Server Management Server(SSMS)

Steps :

Start SSMS and mssql 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 the in SSMS it gives the following results

Example 2 : Sending multiples messages between 3 services with a single DB. where we have two receive service.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 message from two different receiving services.

Article Tags :

SQL

SQL-Server