Imagine an online retail web application which has an order fulfilment system that comes into play whenever a customer wants to place an order. This system might have different business processes such as payment processing, a CRM, inventory management, shipping etc. and each of them have their own databases. In a scenario when one of the systems is down, customers may not be able to place an order if there is no way to queue their order request and guarantee that their order request will be delivered to subsequent systems for processing. This is where an asynchronous queueing and messaging system like SQL Service Broker comes into picture.

SQL Server Service Broker provides native support for messaging and queuing in the SQL Server Database Engine and Azure SQL Managed Instance. Application developers can use Service Broker to distribute data workloads across several databases without programming complex communication and messaging internals. Service Broker ensures that all tasks are managed in the context of transactions to assure reliability and technical consistency. It’s part of SQL Server so if you’re already utilising SQL Server for your project, then there’s no additional licensing required.

Now that we know what SQL Service Broker is, let’s look at how we can use Service Broker in our order fulfilment system. We can leverage Service Broker to exchange data between the business processes. Even if there is a payment processing outage, customer’s order will be accepted and a request for payment processing will be queued and held as a message. The system can continue accepting other orders and queueing payment request messages. Once the payment processing system is back online, it’ll dequeue and process the payment request messages. Once done, the subsequent CRM systems will be updated with the sale information and the shipping systems may do the needful and ship the products to the customer that made the order. Service Broker ensures that the payment processing request has reached the destination system and as the processing task will be done in the context of a transaction it ensures consistency.

Let’s try and set up a simple Service Broker queue and see how we can send or receive messages through Service Broker.

Things you’ll need:

1. SQL Server
   • Developer Edition available here.
2. Microsoft Docs
   • Documentation for reference here.

Enabling Service Broker in database

Let’s start off by creating a a database and enabling Service Broker for that database.
Note: To ensure that no users are connected to the database when you enable Service Broker we’ll use the ROLLBACK IMMEDIATE option and this works as we are currently executing this in our DEV database.

CREATE DATABASE PaymentProcessingDB
GO
ALTER DATABASE PaymentProcessingDB
      SET ENABLE_BROKER
      WITH ROLLBACK IMMEDIATE;
GO

Create Message Types

Message Type is the basic object of the messaging infrastructure that is used to define what kind of messages can and cannot be sent.
You can set the type of a message by defining the ‘VALIDATION’ of a message type as follows EMPTY, NONE, WELL_FORMED_XML, VALID_XML WITH SCHEMA COLLECTION.
We’ll create a PaymentRequestMessage which will be sent by the service initiating the DIALOG and a PaymentResponseMessage which will be sent back to the Initiator Service by the Target Service.

CREATE MESSAGE TYPE
       [PaymentRequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [PaymentResponseMessage]
       VALIDATION = WELL_FORMED_XML;

Create Contract

The CONTRACT is used to define the kind of message that will be sent by the INITIATOR – the party that sends the first message and the TARGET – the party that receives the message and sends a response.

CREATE CONTRACT [PaymentProcessContract]
      ([PaymentRequestMessage]
       SENT BY INITIATOR,
       [PaymentResponseMessage]
       SENT BY TARGET
      );

Create Queues

A queue is a storage space where the messages reside. You can also query queues to see what messages are currently present in it.
We will be creating two queues, the TagetQueue will hold messages sent be the INITIATOR to the TARGET and the InitiatorQueue will hold messages sent by the TARGET back to the INITIATOR.

CREATE QUEUE PaymentProcess_TargetQueue;
GO
CREATE QUEUE PaymentProcess_InitiatorQueue;
GO

Create Services

A service is created on top of a queue which defines what contract is to be adhered to send messages to that queue. A queue can have multiple services adding messages to it by adhering to different contracts.
For our example we are going to create two services for the Target and Initiator queues that adhere to the PaymentProcessContract.

CREATE SERVICE
       [PaymentProcess_TargetService]
       ON QUEUE PaymentProcess_TargetQueue
       ([PaymentProcessContract]);
CREATE SERVICE
       [PaymentProcess_InitiatorService]
       ON QUEUE PaymentProcess_InitiatorQueue
       ([PaymentProcessContract]);

Send a Request Message

To send a message from the InitiatorService to the TargetService we will start a DIALOG and mention the CONTRACT that will be used for this DIALOG. We will also use a unique identifier(GUID) for this DIALOG which will be used for all the conversations that will be sent to the TargetQueue.

Once the DIALOG has started, we can send messages on the CONVERSATION using the unique identifier used to start the DIALOG and mention the MESSAGE TYPE and the message body which, for our example, contains a PaymentProcessingRequest number.

DECLARE @conversation_handle UNIQUEIDENTIFIER;
DECLARE @message_body XML;

BEGIN TRANSACTION;


--Begin conversation
BEGIN DIALOG @conversation_handle
     FROM SERVICE [PaymentProcess_InitiatorService]
     TO SERVICE N'PaymentProcess_TargetService'
     ON CONTRACT [PaymentProcessContract]
     WITH ENCRYPTION = OFF;

SELECT @message_body = N'<PaymentRequestMessage>PAYREQ001</PaymentRequestMessage>';

--send message on conversation using the same GUID
SEND ON CONVERSATION @conversation_handle
     MESSAGE TYPE [PaymentRequestMessage] 
(@message_body);

COMMIT TRANSACTION;
GO

You might have noticed that queues are never mentioned while sending messages on a CONVERSATION, as a DIALOG can start only between two services that logically defines the contract to be adhered to send messages to the queue.
Once the message has been sent to the TargetService, we can view the message in the TargetQueue using a simple select statement as below.

SELECT *, CAST(message_body AS XML) AS message_body_xml
FROM PaymentProcess_TargetQueue
;

Receiving and Processing a Request Message

The initiator has sent the PaymentRequestMessage to the Payment processing system and it is now free to accept other orders. The Payment processing system will receive messages from the TargetQueue asynchronously and respond back to the INITIATOR once the processing is done.

To process the request message, we will have to first RECEIVE the message from the TargetQueue. Once the message is received we can enter the logic to process the request message, but for simplicity we are just going to display the message contents here. When we receive the message body, we also receive the conversation handle and message type.

We can use this conversation handle to send a response message on the initiator queue on the same conversation. We can also check the message type we received and process the message accordingly. As said earlier, in this example we are just going to respond back with a PaymentResponseMessage containing the PaymentProcessingRequest number indicating we have finished processing the Payment Request.

-- Receive the request and send a reply
DECLARE @conversation_handle UNIQUEIDENTIFIER;
DECLARE @message_body XML;
DECLARE @message_type_name sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @conversation_handle = conversation_handle,
    @message_body = message_body,
    @message_type_name = message_type_name
  FROM PaymentProcess_TargetQueue
), TIMEOUT 1000;

SELECT @message_body AS ReceivedPaymentRequestMsg;

--check message type
IF (@message_type_name = N'PaymentRequestMessage')
BEGIN
     DECLARE @reply_message_body XML;


--You can enter the logic to process the message here
     SELECT @reply_message_body = N'<PaymentResponseMessage>PAYREQ001</PaymentResponseMessage>';

     SEND ON CONVERSATION @conversation_handle
          MESSAGE TYPE [PaymentResponseMessage]
     (@reply_message_body);
END

SELECT @reply_message_body AS PaymentResponseMessage;

COMMIT TRANSACTION;
GO

You can then check for the PaymentResponseMessage sent to the InitiatorQueue by executing the below select query on the InitiatorQueue.

-- Check for the reply message in the initiator queue
SELECT *, CAST(message_body AS XML) AS message_body_xml
FROM PaymentProcess_InitiatorQueue;
GO

Understanding the relationship between DIALOG, CONTRACT, SERVICE and QUEUE

By sending the response message on the same DIALOG by using the same conversation handle, the contract PaymentProcessContract specified in the DIALOG will be used to send the message.
The PaymentProcessContract specifies PaymentResponseMessage is sent by Target to the Initiator and the DIALOG also specifies the conversation initiator is the PaymentProcess_InitiatorService.
The PaymentProcess_InitiatorService logically represents the PaymentProcess_InitiatorQueue and thus the response message arrives at the InitiatorQueue.

I hope this helps you understand the relationship between DIALOG, CONTRACT, SERVICE and QUEUE.

Ending the Conversation

Now that we have sent the PaymentResponseMessage back to the InitiatorQueue we are going to use the same method to RECEIVE the message from the queue.
If the received message is of the type PaymentResponseMessage we will use END CONVERSATION with the conversation handle received from the response message.

The idle conversations occupy space and are of no further use, so it is always a good practice to END CONVERSATION once you have received the desired response and clear out the conversation.

DECLARE @message_body XML;
DECLARE @conversation_handle UNIQUEIDENTIFIER;
DECLARE @message_type_name sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @conversation_handle = conversation_handle,
    @message_body = CAST(message_body AS XML),
    @message_type_name = message_type_name
  FROM PaymentProcess_InitiatorQueue
), TIMEOUT 1000;

IF (@message_type_name = N'PaymentResponseMessage')
BEGIN
    END CONVERSATION @conversation_handle;
END

SELECT @message_body AS ReceivedPaymentResponseMessage;

COMMIT TRANSACTION;
GO

We have ended the conversation on the Initiator side, this sends an End Dialog message to the TargetQueue. If you check the TargetQueue using the below query, you should see the EndDialog message.

--Check for EndDialog message in TargetQueue
SELECT *, CAST(message_body AS XML) AS message_body_xml
FROM PaymentProcess_TargetQueue;
GO

We also need to end the conversation from the Target side. We will do something similar to what we did with the InitiatorQueue but here we’ll look for a message type of EndDialog rather than PaymentResponseMessage.
Ending the Conversation here does nothing but just cleans up the conversation. There is no message sent back to the initiator.

-- Receive the End Dialog and clean up
DECLARE @conversation_handle UNIQUEIDENTIFIER;
DECLARE @message_body XML;
DECLARE @message_type_name sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @conversation_handle = conversation_handle,
    @message_body = CAST(message_body AS XML),
    @message_type_name = message_type_name
  FROM PaymentProcess_TargetQueue
), TIMEOUT 1000;

--check if message type is EndDialog
IF (@message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
     END CONVERSATION @conversation_handle;
END

COMMIT TRANSACTION;
GO

With this we have successfully end the CONVERSATION or DIALOG associated with the conversation_handle. We can have a look at both the queues, they should be empty unless a new PaymentRequestMessage is being processed.

-- Check for a message in the target queue
SELECT *, CAST(message_body AS XML) AS message_body_xml
FROM PaymentProcess_TargetQueue;
GO

-- Check for a  message in the initiator queue
SELECT *, CAST(message_body AS XML) AS message_body_xml
FROM PaymentProcess_InitiatorQueue;
GO

This is now the end of message processing between two systems that has happened asynchronously. The TargetQueue can have PaymentRequests added at any point of time and the Payment Processing System can dequeue and process messages at any point of time and respond back with the results.

Service Broker can also be used for batch processing of data wherein you can queue messages during business hours and process the whole chunk of messages during off-business hours or process it periodically during the day. One of the advantages of using SQL Server Service Broker is that as the messages and queues reside in the database, whenever you backup and restore the database all of your messages and queues are retained.

Thus, we have had a glimpse of both queueing and messaging technology that SQL Server Service Broker offers and also the basic components required for a simple asynchronous application. Service Broker has a lot more to offer and you can get more information here.

I hope you found this interesting. Thank you for reading!