Service Broker – Part II

In the first

part of this article, we saw the basic artifacts of Service Broker (hereinafter SSB) and the basic steps that are required to build a Service Broker application. Just to recap, SSB is made up of the following basic artifacts:

The example that we showed in the first part was a simple one-way messaging application that allowed one service to send a message to another service which picked it up. This sort of messaging may be useful in situations wherein you just want a service to take some action and you do not care about the results.

However, what if you want to establish a two-way messaging between two services wherein we want the two services to interact based on various message types? This is the more common usage of SSB and in this article; we will see how we can enable this sort of two-way communication.

To understand two-way messaging in Service Broker, we need to understand

few new concepts. These are:

1. Conversations
2. ConversationGroups
3. MessageTypes

Conversations are the basic method by which SSB works. All SSB applications will need to use conversations to establish a reliable, long-running, asynchronous exchange of messages. Conversations are akin to what humans do too. To talk to someone, you establish a conversation.

Conversation Groups allow the grouping of related messages together so that they can be acted upon to complete a task. Conversation group is also the locking scope for messages present in a queue. For example, all messages belonging to the same conversation group will be locked and read by a service program so that others cannot access the same. Conversation groups are similar to multiple people talking together in a conference. If there is no relation to what one person says and the other responds, then you will have chaos in the conference call! On the other hand, if each person listens to the set of items intended for him and then responds in that context, then you will have order and structure.

Message types are basically present to distinguish one message from the other. A service program might perform different logic of different message types. Since two services can exchange messages of different types, programming with the message type in mind is useful.

Ok, with this knowledge in place, let us build our simple two-way messaging solution.

The Problem

The problem that we are going to try to solve here involves two services (let’s call them BookPublisher and BookInventory). An application basically asks the BookPublisher about a book. The BookPublisher sends a message to the BookInventory service to check if the book is available. If a book is available, the BookInventory service responds likewise and this information is then sent back to the calling application. This can be represented by the following simple diagram.

As shown in the above diagram, the application calls some entry point asking for a book (this could be a procedure in the database). The procedure then begins a dialog between the publisher service and the inventory service about the availability of the book (as shown by the message envelopes).

Now, let’s see what how we will model this message exchange in Service Broker. The following are the various code snippets that solve the problem. You will need to execute them in order to see the complete solution working.

First, let us create two message types that will help identify the query and the response messages. The following script will create the message types.

CREATE MESSAGE TYPE QueryInventory
VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE QueryInventoryResponse
VALIDATION = WELL_FORMED_XML
GO

We create two message types. Note that we are expecting the messages to be well formed XML documents as indicated by the WELL_FORMED_XML validation type. Service Broker will ensure that only valid XML documents can be exchanged when we use these message types.

Next, let us create the contract required for the exchange of these messages. The following script creates the contract.

CREATE CONTRACT QueryInventoryContract
(QueryInventory SENT BY INITIATOR,
QueryInventoryResponse SENT BY TARGET
)
GO

Note that the contract specifies who will send what types of messages. Once the contract is established, we can create the necessary queues on which the messages will reside. The following script creates the queues.

CREATE QUEUE PublisherPostBox
CREATEQUEUE InventoryPostBox
GO

Note that we are using a very simplified syntax for queue creation. For example, we have not associated a service program to the queue. For now, we will resort to incrementally executing this solution and seeing it work before working with advanced concepts. Once the queues are created, we can then create the service points through which the interaction will occur. The following script creates the services required for this problem.

CREATE SERVICE BookPublisher
ONQUEUE PublisherPostBox (QueryInventoryContract)
CREATE SERVICE BookInventory
ONQUEUE InventoryPostBox (QueryInventoryContract)
GO

At this point, we are ready to begin a dialog between the two services. The following script initiates the dialog and sends the first message to the BookInventory service.

DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message XML
 BEGIN
SET @message = '<QueryInventory><Book>BK1001</Book></QueryInventory>'
BEGIN TRANSACTION
--Begin the dialog
BEGINDIALOG @conversationHandle
FROM SERVICE BookPublisher
TO SERVICE 'BookInventory'
ON CONTRACT QueryInventoryContract;
--Send a message on the dialog
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE QueryInventory (@message);
COMMIT TRANSACTION
END
GO

Note that the output from the BEGIN DIALOG statement is a conversation identifier that can be used to identify a particular conversation. This conversation identifier is used in the SEND ON CONVERSATION command later on to send a message on the particular conversation.

At this time, you can do a SELECT * FROM InventoryPostBox and see that the message has reached the inventory queue. Now, the receiver can pick this message off the queue and do some associated processing. In a typical SSB application, this is typically done by a service program that is associated with a queue, but in our case, we will write a small script that will process the message one-by-one (thereby understanding how basic processing occurs). The following script shows how the message can be retrieved from the queue and then processed.

DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message XML
DECLARE @messageType NVARCHAR(256)
BEGIN
BEGINTRANSACTION;
--Receive a message from the queue
RECEIVE TOP(1) @conversationHandle = conversation_handle,
@message = CONVERT(XML, message_body),
@messageType = message_type_name
FROM InventoryPostBox;
-- Check the message type and take appropriate action
IF (@messageType = 'QueryInventory')
 BEGIN
-- Do some processing
SET @message.modify('insert <status>Available</status> as last into (QueryInventory)[1] ');
-- Respond back to the conversation
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE QueryInventoryResponse (@message)
 END
IF (@messageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
  BEGIN
END CONVERSATION @conversationHandle
 END
COMMIT TRANSACTION
END
GO

The receiving logic is pretty simple. We pick the first message off the queue by using the RECEIVE method. Note that we are picking three columns of interest: the conversation identifier, the message and the message type. We also do some little processing with the message by just adding a new node to the XML that is present. Typically, in a practical SSB application, you would do some complex processing logic here.

The conversation handle is required for us to reply to a specific conversation (we don’t want to send a response to some other conversation, do we? ;). The message type field indicates the type of the message that the queue has received. You can see a usage for this field in the above script. We will get back to the specifics for this later in this article. At this point, if you do a SELECT * FROM PublisherPostBox, you should see a response message from the BookInventory service.

Now, the publisher service can examine its queue and pick the response and process the same. The following script shows the processing logic and it is very similar to the above script.

DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message XML
 DECLARE @messageType NVARCHAR(256)
BEGIN
BEGIN TRANSACTION;
-- Receive a message from the queue
RECEIVE TOP(1) @conversationHandle = conversation_handle,
@message = CONVERT(XML, message_body),
@messageType = message_type_name
FROM PublisherPostBox;
-- Check the message type and take appropriate action
IF (@messageType = 'QueryInventoryResponse')
 BEGIN
-- Do some processing and end the conversation
SELECT @message
 END CONVERSATION @conversationHandle
 END
 COMMIT TRANSACTION
END
GO

The logic again is pretty simple. We just pick the message from the queue and print it based on the message type. At this point, we are done with the conversation and the END CONVERSATION command does the trick of telling this dialog that all is done. Or does it?

Post the above script, we may expect that both the queues will be empty, but on the contrary, there is one more message in the InventoryPostBox. What is this message? If you examine the result set from this queue, you will notice that the message type of this message is: http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog.

What is this message type? This message type is SSB’s way of saying that the source is asking for the destination to end the conversation. Now, the destination has to respond back with the acknowledgment and if you refer to the earlier script that we wrote on the inventory side, we handled this message type and issued a corresponding END CONVERSATION. If you now run that script again and then query both the queues, they will be empty, which is what we want.

At this stage, you are done with implementing a simple two-way conversation and here is cleanup script that you can execute to get things back to where we started.

DROP SERVICE BookPublisher
DROP SERVICE BookInventory
DROP QUEUE PublisherPostBox
DROP QUEUE InventoryPostBox
DROP CONTRACT QueryInventoryContract
DROP MESSAGE TYPE QueryInventory
DROP MESSAGE TYPE QueryInventoryResponse
GO

Ok, that brings us to the end of this article, where we learned something more than the first part. But, have we now covered it all? No, there is still more to go. For example:

1. Implementing conversation groups so that we handle related messages and responses.
2. Implementing services across databases in the same instance or in different instances.
3. Securing conversations so that no tampering occurs.

These will be the focus of future articles that I will be writing on this exciting new subject of SSB. Till then, hold on and play around with this code. I’ll be back!