Simulating Monolog Conversations with SQL Server 2005 Service Broker

• Download demo project - 37.2 KB

Introduction

SQL Server 2005 comes with a new service that is very useful for creating robust Enterprise applications: the Service Broker. Service Broker is useful to implement guaranteed and in-order delivery of messages between two services. This is modeled as a dialog between two services. Currently, Service Broker lacks the support for monolog, or simple queuing of messages.

This article shows how to implement a monolog in SQL Server 2005 Service Broker.

Background

You can read Akram Hussein's excellent introduction to Service Broker in Introducing Distributed Messaging using Service Broker in SQL Server 2005. To give a very quick overview here, Service Broker deals with Services communicating with each other using messages. Each service has a queue associated with it. An application can read (receive) messages from the queue and send messages to other services or respond to the originating service.

The way message orchestration is modeled is through the notion of a conversation. Each message is part of a conversation and messages are ordered within a conversation. This is how in-order delivery is implemented.

SQL Server 2005 has only one of type of conversation: the dialog. A dialog is a conversation between two services, each having its own queue. This type of conversation is very useful if you want to implement reliable communication between two applications. The two services can even sit on two different databases.

SQL Server 2005 doesn't include other conversation types. This is why there is a bit of confusion around the terms "conversation" and "dialog," which seem to be used interchangeably in the documentation. This is because dialog is the only implementation of a conversation.

Lack of Monologs

Another conversation type was modeled but never implemented in SQL Server 2005: the monolog. A monolog is basically a publisher/subscriber model where one service publishes messages and any number of services can receive the messages. SQL Server 2008 won't include the monolog feature either, as mentioned by a member of the SQL Server product team on MSDN forum. We propose, in this article, to look at a way of implementing a monolog using what is currently available. That is, a dialog.

Modeling Monologs with Dialogs

Our approach is to define only one service and one queue. Then we will define dialogs from that service to itself. Different "consumers" will be represented by different dialogs. Each dialog behaves like a sub-queue: a transaction locks the reception of messages and messages are stored in order.

In order to to broadcast a message to a set of consumers, we will simply send the message through all the dialogs associated with the consumers.

Challenge of the Approach

One of the biggest difficulties we met was to manage those dialogs. Service Broker dialogs are designed to loosely couple the two services dialoging. One of the consequences of the loose coupling is that the dialog ID of the dialog for the initiator service isn't the same as the dialog ID of the other service.

This makes things difficult, since when you are creating a dialog, the dialog ID you have isn't the one identifying the dialog at the reception side. Fortunately, those two dialog IDs are related in the system view sys.conversation_endpoints. Here is a database script illustrating this point. It creates a TestDialog database, a contract, a queue and a service. It then starts a dialog on that service and sends one message.

CREATE DATABASE TestDialogID
GO

USE TestDialogID
GO

CREATE SCHEMA mlg
GO

CREATE CONTRACT [mlg.MonologContract]
(
    [DEFAULT] SENT BY INITIATOR
)

CREATE QUEUE mlg.MonologQueue

CREATE SERVICE [mlg.MonologService]
    ON QUEUE mlg.MonologQueue ([mlg.MonologContract])
GO

DECLARE @dialog_handle AS UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [mlg.MonologService]
TO SERVICE 'mlg.MonologService'
ON CONTRACT [mlg.MonologContract]
WITH
ENCRYPTION = OFF

SELECT @dialog_handle AS dialog_handle;

SEND ON CONVERSATION @dialog_handle
    MESSAGE TYPE [DEFAULT] ('Test message');

SELECT * FROM mlg.MonologQueue

SELECT * FROM sys.conversation_endpoints
GO

Here is a sample of what this script could output. All IDs are generated GUIDs; therefore they will be different on your machine:

The first result set is the dialog handle created by the BEGIN DIALOG CONVERSATION command. The second result set is the content of the queue after the message was sent. The third result set is the content of sys.conversation_endpoints after the message is sent. You can notice that the conversation_handle in the second result set isn't the same as the dialog_handle in the first. This is because the first dialog handle is the handle on the initiator side, while the second is the handle on the receiver side. The third result set actually resolves the confusion: you see the dialog_handle from the first result set in the column conversation_handle of the first row and you see the second one in the second row. What is common to both is the conversation_id.

Using the Code

The little SQL libraries available with this article basically wrap the concept of the previous sections into 6 stored procedures:

We use the conversation ID instead of the conversation handle, since this one is unique on both ends of the communication. We also included C# API wrapping calls to those SQL stored procedures. Here is the object model.

A basic test case would look like this:

using (Queue queue =
    Queue.FromConnectionString(
    "Data Source=.;Initial Catalog=MyDataBase;Integrated Security=True"))
{
    Conversation conversation = queue.CreateConversation();

    try
    {
        byte[] data = new byte[] { 1, 2, 3, 4 };

        conversation.PushMessage(data);

        ConversationMessage message = 
            conversation.PopMessage(TimeSpan.FromSeconds(1));

        Console.WriteLine("ID:  {0}", message.MessageID);
        Console.WriteLine("Sequence Number:  {0}", 
            message.MessageSequenceNumber);
        Console.WriteLine("Message body length:  {0}", 
            message.MessageBody.Length);
    }
    finally
    {
        conversation.EndConversation();
    }
}

Finally, we included a Windows Forms application demonstrating the usage of the API.

This demo demonstrates both the send/receive one message at a time (left-hand side) and the send/receive all messages (right-hand side) stored procedures.

Broadcasting

What is intentionally missing in this library is the ability to broadcast a message. The reason for this is that this is application-dependent. In your application, you simply need to add a layer identifying the receiver group and the criteria for that receiver group. When you want to broadcast a message, you then have to look at the criteria of all groups, select the groups matching the criteria and send the message to each member of each group using the API provided here.

Next Steps

The current implementation of the send message sends one message at a time. This can be quite slow for massive sends. For instance, a quick test of sending 10 000 messages of 10 kilobytes each took up to 2 minutes on a virtual PC running on a laptop. In a broadcasting scenario where you would like all those sends to be wrapped in one SQL transaction, this would be too slow. We would need to implement a batch send message. This could be accomplished easily by packaging a bunch of message into an XML parameter.

Conclusion

Monolog conversation doesn't exist yet in SQL Server 2005, but there is a way to simulate it with a dialog conversation, as shown in this article. SQL Server 2005 Service Broker can therefore be used to implement a reliable publish/subscribe pattern.

History

• 12 October, 2007 -- Original version posted