Using Service Broker to Communicate With Other Database Servers

In this article, we are going to discuss the Service Broker, another new feature in SQL Server 2005. In simple terms, Service Broker is a distributed messaging framework, or MSMQ (Microsoft Message Queue), in SQL Server 2005. Using this new feature, you will be able to add reliable, scalable, asynchronous functionality to your database applications.

This article will focus on the benefits of this new feature and basic configuration for applications.

Benefits of Message-Based Application

Before detailing the features and functionalities of Service Broker, let us examine the benefits of message-based applications.

Message based applications are very much needed when several services are involved. For example, in an online trading application, before confirming the order of a client, you need to perform several processes like checking stock availability, shipping availability, delivery date, etc. These processes will need time. If you are validating all the processes before confirming the order, your client may have to wait for some time at his browser. Instead, you can take in ordering details from the client and process all the necessary processes by putting the information in messaging. After getting all the information from all the services, you can compile them into an e-mail and send it to the client. In this way, your client does not have to wait for a long time at your Web site, which will make it more user-friendly.

Message based applications can process several services at once. For example, shipping service and stock availability service can run parallel. By doing so, it will minimize the time required to process the entire order.

Microsoft Messaging Queue (MSMQ) is a framework that supports this kind of messaging. You can write an application in VB, C#, or C++ to implement MSMQ applications. However, a basic issue with traditional messaging applications is when one process fails you have to rollback all the other processes manually, which has to be handled carefully. Not an easy task.

SQL Server 2005 has come up with this new feature to get rid of traditional messaging issues.

Install

Service Broker needs minimum configuration, and its features are available in all SQL Server editions. In SQL Server Express edition, you get the client features of Service Broker.

There are a few concepts or objects that we have to understand in Service Broker: Queues, Messages, Dialogs, Contracts, Services, and Conversation Groups.

Queues

Well, queues are nothing but queues. Like traditional queues, this is a first-in-first-out (FIFO) storage system. Queues provide a link between the message sender and the message receiver. The message sender will send the message to the queue while the message receiver will read the message. Because of the queue technology, the sender does not have to wait for the receiver to finish processing the message before sending the next one. The sender can send any number of messages and the receiver can process them at a different rate or even a different time thus enabling equal resource distribution.

In Service Broker, queues use hidden tables. Because messages are saved in tables, they will not be removed even after the server restarts. Also, you can backup your messages by simply making a backup of the database, as those queues are part of the database. Another important feature is that you cannot execute INSERT, DELETE, or UPDATE statements against these queues. SELECT and RECEVE (which I will discuss later in this article) are the only operations that you can perform on these queues.

Messages

A message is placed in queues. The data type of a message is varbinary(max), which is a new data type in SQL Server 2005. This new data type gives you the option of saving messages of up to 2 GB capacity. You will not able to view the message by simply invoking a SELECT statement. Messages are always received in the order in which they are sent and are guaranteed to arrive, as they cannot be lost during transmission or to any other cause. This is a major advantage over MSMQ as many developers/users complain about the sequence of message sending and receiving in MSMQ. If the message is not sent for some reason, the service will try to resend it again later, which will reduce your need to monitor the messages.

When defining a message, you can define what type of message it should contain. This provides added security to Service Broker messages. The validation types are EMPTY, WELL_FORMED_XML and VALID_XML_WITH_SCHEMA_COLLECTION. With EMPTY, you are not imposing any validation on the message. WELL_FORMED_XML means that message should have valid XML format. VALID_XML_WITH_SCHEMA_COLLECTION is the same as the class XmlSchemaCollection. XmlSchemaCollection is a cache or library where XML-Data Reduced (XDR) and XML Schema Definition (XSD) language schemas can be stored and validated. XmlSchemaCollection improves performance by caching schemas in memory instead of accessing them from a file or URL.

Dialogs

You are familiar with Queues and Messages from other messaging applications. A Dialog is a fairly new concept in SQL Server 2005 Service Broker. Some experts are calling this a Conversation as well. Whatever the name, it forms the core of Service Broker. A Dialog is the mechanism by which you are able to put your messages in queues. In a Dialog, messages are ordered and delivered in the order that they were sent. Under normal circumstances, a dialog is created for a particular task and deleted when it is completed. A Dialog occurs between two end points. End points are used to communicate with Service Broker on different SQL Server instances. End points allow Service Broker to communicate by using HTTP, TCP, or SOAP protocols. These end points are not configured by default. So you need to configure them if you plan to communicate over different SQL Server instances. Service Broker often uses port 4022 for broker-to-broker communication. You can change this if you want to, when creating end points.

Contracts

Contracts define who can send what types of messages from the initiator or receiver. You can specify multiple message types in a contract and specify who (either initiator or receiver) can send it.

Services

Services read messages from a queue and process them. A Service can be a SQL Server stored procedure or a different program that is linked with a Dialog.

Conversation Groups

As some messages are related to others, Conversation groups are used to keep them together. If one message process fails, the service will automatically roll back the related message processes.

Service Broker Architecture

Here are all the concepts and objects of Service Broker together in pictorial format:

Source: MSDN

Applications exchange messages as part of a dialog. When SQL Server receives a message for a dialog, it places the message in the queue from the service for the dialog. The application or stored procedure receives the message from the queue and processes the message as necessary. As part of the processing, the application may send messages to the other participant in the dialog.

Practice

Now it is time to get your hands dirty. First, let's define some simple business logic to demonstrate Service Broker. Let's assume that we are doing a design for online purchasing. Our requirement is such that whenever a user places an order the details are sent to the inventory system. From the inventory system, the service will read a queue and update necessary tables. (For simplicity, I assume the trading system and inventory system to be in one database. In the real world, the two systems will be on two different servers and you will have to create end points to facilitate communication.)

First we create a new database called SSSB.

USE [master]
GO
/****** Object: Database [SSSB] Script Date: 02/19/2007 22:55:11 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SSSB')
DROP DATABASE [SSSB]

CREATE DATABASE [SSSB]

Then we enable the Service Broker for this database.

ALTER DATABASE [SSSB] SET ENABLE_BROKER

Now we create an order table that will be in the inventory system.

CREATE TABLE [dbo].[tblOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ItemCode] [varchar](15) NULL,
[Qty] [int] NULL,
[OrderDate] [smalldatetime] NULL
CONSTRAINT [PK_tblOrders] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)

We have to create Service Broker objects. First we need to create a MESSAGE TYPE and a CONTRACT.

CREATE MESSAGE TYPE SendStockDetails
VALIDATION = WELL_FORMED_XML;

The SendStockDetails Message Type will send the information about the entered stock. This will have WELL_FORMED_XML as the messages are supposed to be sent in XML format.

CREATE CONTRACT [MainContract]
(
SendStockDetails SENT BY INITIATOR
);

CONTRACT specifies in what direction these message types are used.

The next step is to create a Queue (queSendStockSend) to send the stock details.

CREATE QUEUE queSendStockSend WITH STATUS= ON, ACTIVATION ( PROCEDURE_NAME = usp_updatetocks,
MAX_QUEUE_READERS = 5, EXECUTE AS 'dbo' ) ;

The queue above has two important parameters. One is the procedure name. Whenever a message is sent to the queue, the given stored procedure (usp_updatetocks) will be activated. This sp should be created before the CREATE QUEUE statement. However, for the sake of presentation, sp will be discussed later.

The other parameter, MAX_QUEUE_READERS, specifies the maximum number of instances of the activation stored procedure that the queue starts at the same time. The value of max_readers must be a number between 0 and 32,767.

We create the SERVICE by combining the QUEUE and CONTRACT.

CREATE SERVICE svrStockUpd ON QUEUE queSendStockSend ([MainContract])

Now we have to write a sp to input data into the queue. This sp will have two parameters: itemcode and qty. inside the sp, XMLmessage will be formatted and sent to the queue.

CREATE PROCEDURE [dbo].[usp_StockInfo]
@ItemCode [varchar](15),
@Qty [int]
AS
BEGIN
DECLARE @OrdDate AS SMALLDATETIME
SET @Orddate = GETDATE() -- We assume current date and time as the order date
DECLARE @Message XML
CREATE TABLE #XMLMessage
(
[ItemCode] VARCHAR(15),
[Qty] INT,
[OrderDate] SMALLDATETIME,
)

INSERT INTO #XMLMessage
(
[ItemCode],
[Qty],
[OrderDate]
)
VALUES (
@ItemCode,
@Qty,
@ORddate
)

SELECT @Message = ( SELECT * FROM #XMLMessage
FOR XML PATH('Order'),
TYPE
) ;
-- Above will fomulate valid XML message
DECLARE @Handle UNIQUEIDENTIFIER ;

-- Dialog Conversation starts here

BEGIN DIALOG CONVERSATION @Handle FROM SERVICE svrStockUpd TO
SERVICE 'svrStockUpd' ON CONTRACT [MainContract] WITH ENCRYPTION = OFF ;

SEND ON CONVERSATION @Handle MESSAGE TYPE SendStockDetails (@Message) ;
END
GO

You can observe that the service is used for DIALOG CONVERSATION.

The message will be in the following XML format.

2001
60
2007-02-21T00:32:00

First let's input some date by using usp_stockinfo.

[usp_StockInfo] 'A-200',12

You will notice that you will be returned immediately after the above executes.

If you execute SELECT * FROM dbo.queSendStockSend you can view the message data. However, you will see that message body is encrypted. (Download the code in the example above.)

Next we should create a sp to read the queue. In practice, this should be created before the queue is created, as you need to specify the activation stored procedure while creating the queue.

CREATE PROCEDURE usp_updatetocks
AS
BEGIN

SET NOCOUNT ON ;
DECLARE @Handle UNIQUEIDENTIFIER ;
DECLARE @MessageType SYSNAME ;
DECLARE @Message XML
DECLARE @OrdDate SMALLDATETIME
DECLARE @Qty INT
DECLARE @ItemCode VARCHAR(15) ;

RECEIVE TOP ( 1 )
@Handle = conversation_handle,
@MessageType = message_type_name,
@Message = message_body FROM dbo.queSendStockSend;

IF ( @Handle IS NOT NULL
AND @Message IS NOT NULL
)
BEGIN
SELECT @OrdDate = CAST(CAST(@Message.query('/Order/OrdDate/text()') AS NVARCHAR(MAX)) AS SMALLDATETIME)
SELECT @Qty = CAST(CAST(@Message.query('/Order/Qty/text()') AS NVARCHAR(MAX)) AS INT)
SELECT @ItemCode = CAST(CAST(@Message.query('/Order/ItemCode/text()') AS NVARCHAR(MAX)) AS VARCHAR(15))

INSERT INTO dbo.tblOrders
(
ItemCode,
Qty,
OrderDate
)
VALUES (
@ItemCode,
@Qty,
@OrdDate
) ;
END
END

To read the queue, we have used the RECEIVE command. With the RECEIVE command you are deleting the queue record after receiving it. If you use SELECT, it will not be removed from the queue. As the message is in a binary format, we need to convert it using the CAST function. We will convert them to three separate variables and insert them into the table.

After this, if you run select * from tblOrders, you can see that data has been logged in the tblorder table by Service Broker.

Using Service Broker in SQL Server 2005

A typical example that you can provide for the Service Broker is Database Mail in SQL Server 2005. Whenever a mail request (a Message in the context of Service Broker) is placed on a queue, an external application, Databasemail90.exe activates by means of Service Broker. The main advantage of Database Mail is that the mail process is done by an external application, which reduces SQL Server overhead. This was achieved because of the Service Broker architecture.

Here are some other places where you can use Service Broker to your advantage.

• Asynchronous triggers
• Reliable query processing
• Reliable data collection
• Distributed server-side processing for client applications
• Data consolidation for client applications
• Large-scale batch processing

You can find full details in "Typical Uses of Service Broker."

Conclusion

Service Broker of SQL Server 2005 is not simply a replacement for MSMQ because more features have been added that will benefit system developers. I tried to keep this introductory example simple. The next article on Service Broker with cover advanced features such as security and endpoints.

Your feedback on this article is most welcome at dineshasanka@gmail.com.