SQL Server offers a powerful feature called Service Broker that allows for guaranteed, asynchronous message sending and receiving using extensions to the Transact-SQL Data Manipulation Language (DML). In this article, we will explore the different components of Service Broker and provide a step-by-step guide on how to set it up for a single database.

Configuring the Service Broker

Before we can start using Service Broker, we need to configure it by setting up a few components:

1. Enable the Service Broker on the database
2. Create valid Message Types
3. Create a Contract for the conversation
4. Create Queues for the Communication
5. Create Services for the Communication

To enable the Service Broker, use the following code:

USE master
ALTER DATABASE YourDatabaseName
SET ENABLE_BROKER;

Next, we need to create valid Message Types. These are specific types of messages that will be sent and received. For example, we can create a Message Type called “RequestMessage” and another called “ReplyMessage” using the following code:

USE YourDatabaseName
CREATE MESSAGE TYPE [//YourDatabaseName/Sample/RequestMessage]
VALIDATION=WELL_FORMED_XML;

CREATE MESSAGE TYPE [//YourDatabaseName/Sample/ReplyMessage]
VALIDATION=WELL_FORMED_XML;

After creating the Message Types, we need to create a Contract. A Contract specifies which Message Types will be used for sending and receiving messages between the Initiator and the Target. Here’s an example of creating a Contract:

USE YourDatabaseName
CREATE CONTRACT [//YourDatabaseName/Sample/Contract]
(
[//YourDatabaseName/Sample/RequestMessage]
SENT BY INITIATOR,
[//YourDatabaseName/Sample/ReplyMessage]
SENT BY TARGET
);

Now, let’s create Queues for the communication. Queues are used to store messages while they are being sent and received. We can create two queues, one for the Initiator and one for the Target, using the following code:

USE YourDatabaseName
CREATE QUEUE InitiatorQueue;
CREATE QUEUE TargetQueue;

Finally, we need to create Services for the communication. Services are responsible for routing messages to the appropriate queues. Here’s an example of creating Services:

USE YourDatabaseName
CREATE SERVICE [//YourDatabaseName/Sample/InitiatorService]
ON QUEUE InitiatorQueue;

CREATE SERVICE [//YourDatabaseName/Sample/TargetService]
ON QUEUE TargetQueue
([//YourDatabaseName/Sample/Contract]);

Sending and Receiving Messages

Now that we have configured the Service Broker, let’s explore how to send and receive messages between the Initiator and the Target.

To send a message from the Initiator to the Target, we need to determine the Services and Contract, prepare the message, and then send it. Here’s an example:

USE YourDatabaseName
DECLARE @InitDlgHandle UNIQUEIDENTIFIER
DECLARE @RequestMessage VARCHAR(1000)

BEGIN TRAN

BEGIN DIALOG @InitDlgHandle
FROM SERVICE [//YourDatabaseName/Sample/InitiatorService]
TO SERVICE '//YourDatabaseName/Sample/TargetService'
ON CONTRACT [//YourDatabaseName/Sample/Contract]
WITH ENCRYPTION=OFF;

SELECT @RequestMessage = N'Send a Message to Target';

SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE [//YourDatabaseName/Sample/RequestMessage]
(@RequestMessage);

SELECT @RequestMessage AS SentRequestMessage;

COMMIT TRAN;

To receive a message from the Initiator and send a reply, we can use the following code:

USE YourDatabaseName
DECLARE @TargetDlgHandle UNIQUEIDENTIFIER
DECLARE @ReplyMessage VARCHAR(1000)
DECLARE @ReplyMessageName Sysname

BEGIN TRAN;

RECEIVE TOP(1)
@TargetDlgHandle=Conversation_Handle,
@ReplyMessage=Message_Body,
@ReplyMessageName=Message_Type_Name
FROM TargetQueue;

SELECT @ReplyMessage AS ReceivedRequestMessage;

IF @ReplyMessageName=N'//YourDatabaseName/Sample/RequestMessage'
BEGIN
DECLARE @ReplyMsg VARCHAR(1000)
SELECT @ReplyMsg =N'Send a Message to Initiator';

SEND ON CONVERSATION @TargetDlgHandle
MESSAGE TYPE [//YourDatabaseName/Sample/ReplyMessage]
(@ReplyMsg);
END

END CONVERSATION @TargetDlgHandle;

SELECT @ReplyMsg AS SentReplyMessage;

COMMIT TRAN;

To receive a reply message from the Target, we can use the following code:

USE YourDatabaseName
DECLARE @InitiatorReplyDlgHandle UNIQUEIDENTIFIER
DECLARE @ReplyReceivedMessage VARCHAR(1000)

BEGIN TRAN;

RECEIVE TOP(1)
@InitiatorReplyDlgHandle=Conversation_Handle,
@ReplyReceivedMessage=Message_Body
FROM InitiatorQueue;

END CONVERSATION @InitiatorReplyDlgHandle;

SELECT @ReplyReceivedMessage AS ReceivedRepliedMessage;

COMMIT TRAN;

Conclusion

Service Broker in SQL Server provides a powerful mechanism for sending and receiving guaranteed, asynchronous messages. By following the steps outlined in this article, you can configure Service Broker for a single database and start the conversation cycle between the Initiator and the Target. Remember, the same steps can be applied to enable communication between multiple databases.

For more information and to explore additional features and capabilities of Service Broker, refer to the SQL Server documentation.