SQL Server Service Broker – An introduction

Service Broker was introduced in SQL Server 2005 as a way to provide a messaging solution integrated into the database so that it becomes very easy to incorporate messaging and especially transactional messaging into your database applications. These so-called “Messages” are always sent asynchronously, and can be sent between services on the same database, different databases on the same server instance or even different server instances.

This article is a tutorial on how to use Service Broker to build a very simple messaging application. For simplicity, I am considering both the services to be in the same database.

Creating the Message types:-

create message type [//Fund/FundAdministrator/RequestMsg]
validation = well_formed_xml;
GO

This will create a message type which forms the request message which is sent from the initiator service (the requestor) to the target service (the responder).

create message type [//Fund/FundAdministrator/ResponseMsg]
validation = well_formed_xml;
GO

This will create the response message type. This is the message type which is sent from the Target to the Initiator in response to //Fund/FundAdministrator/RequestMsg.

Here, both the request message type and response message type are in URI format. That is merely a convention and definitely not necessary. If you are implementing a service broker application where both the Initiator and the Target are on the same database instance, you are better-off giving simpler meaningful names.

Creating the Contracts:-

CREATE CONTRACT [//Fund/FundAdministrator/FundContract]
([//Fund/FundAdministrator/RequestMsg]
SENT BY INITIATOR,
[//Fund/FundAdministrator/ResponseMsg]
SENT BY TARGET
);
GO

This defines a contract which associates what message type is sent by the Initiator and which message type is a contract.
Thus, a conversation implements a contract which in turn identifies the types of message exchanged by the Initiator and Target.

Creating the Queues:-

CREATE QUEUE FAInitiatorQueue;
GO

CREATE QUEUE FATargetQueue;
GO

This creates the Initiator and Target queues. Queues are merely implemented as tables in SQL Server to the support the Service Broker architecture. They implementing ordering of the messages. They also protect the messages from direct modification by any database users.

Creating the Services:-

CREATE SERVICE
[//Fund/FundAdministrator/FAInitiatorService]
ON QUEUE FAInitiatorQueue;
GO

CREATE SERVICE
[//Fund/FundAdministrator/FATargetService]
ON QUEUE FATargetQueue
([//Fund/FundAdministrator/FundContract]);
GO

This creates the Initiator and Target services. Note, the Target service, besides specifying which queue does it receive the messages in, it also specifies the Contract defined earlier. This restricts the Target Service to allow conversations pertaining only to that defined contract.

Send a request from Initiator:-

BEGIN

DECLARE @DialogHandle UNIQUEIDENTIFIER;

BEGIN DIALOG @DialogHandle
FROM SERVICE
[//Fund/FundAdministrator/FAInitiatorService]
TO SERVICE
‘//Fund/FundAdministrator/FATargetService’
ON CONTRACT
[//Fund/FundAdministrator/FundContract]

DECLARE @RequestMsg NVARCHAR(1000)
set @RequestMsg = ‘<Fund>XYZ</Fund>';

SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [//Fund/FundAdministrator/RequestMsg]
(@RequestMsg);

END
GO

This sends a request from the Initiator to the Target. Note the Request Dialog handle that is required. It is a UniqueIdentifier. Before starting the conversation (sending the request), a dialog needs to be established describing the Initiator, Target and Contract. The “Send On Conversation” statement sends the request.

Receive a Request at the Target:-

DECLARE @DialogHandle UNIQUEIDENTIFIER;
DECLARE @ReceivedMsg NVARCHAR(1000);

BEGIN

RECEIVE TOP(1)
@DialogHandle = conversation_handle,
@ReceivedMsg = message_body
FROM FATargetQueue;

BEGIN

–Process Fund here

DECLARE @ResponseMsg NVARCHAR(1000);
SELECT @ResponseMsg = ‘<ResponseMsg>Fund Processed successfully</ResponseMsg>';

SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE
[//Fund/FundAdministrator/ResponseMsg]
(@ResponseMsg);

END CONVERSATION @DialogHandle

END

END
GO

The Target receives the request message (in this case the Fund name) and the converstaion handle. It then processes the fund and sends the response message back to the same conversation handle. Note, it also ends the Target side of the Conversation.

Initiator receives the response:-

DECLARE @ResponseMsg NVARCHAR(1000);
DECLARE @DialogHandle UNIQUEIDENTIFIER;

BEGIN

RECEIVE TOP(1)
@DialogHandle = conversation_handle,
@ResponseMsg = message_body
FROM FAInitiatorQueue

END CONVERSATION @DialogHandle

END
GO

Here, the Initiator receives the response and finally closes the conversation at its end.

This demonstrates a very simple scenario of request from the Initiator to the Target and a response back from the Target to the Initiator.

There are tremendous benefits and uses of the Service broker. Here are a few references to the same:-

  1. http://msdn.microsoft.com/en-us/library/ms345108%28SQL.90%29.aspx
  2. http://msdn.microsoft.com/en-us/library/ms166071.aspx