A Gentle Introduction to the Service Broker

By: Kent Tegels

In his book Zen Mind, Beginner's Mind, Shunryu Suzuki wrote that "In the beginner's mind there are many possibilities, but in the expert's mind there are few." For many developers this feels very true. When you first set your mind to understanding this complex product, you see a dizzying array of new features like CLR Integration, Integration Services, Reporting Services, the Service Broker and the XML data type. It is hard to know where to being learning how to make the most out of SQL Server. As you spending time working with SQL Server 2005, you might quickly learn what works and what does not in your situation. The list of many things to "grok" becomes a progressively smaller and smaller list while your reach as a database developer grows. In this issue of DevelopMents, I would like to introduce you to the Service Broker.

download sample code here

The concept of using messages to perform asynchronous data processing is certainly not new – indeed it has been the bedrock of writing transactions-based applications for many years. When you place an order with on-line retailer, for example, you do not expect to stay connected to that particular web site until your order arrives. Rather, you're simply telling that vendor the items you wish to buy and how you would prefer to pay for them. They receive your order and may reply quickly with an acknowledgement followed by your ordered items a few days later. Service Broker allows us to implement this message passing and queue pattern inside the database. There are many reasons you might want do that. First, Service Broker leverages the highly scalable and robust engine of SQL Server as the message store. You can easily integrate the message processing logical with native database transactions as well. Programming the Service Broker is relatively simple. You really only need to learn how to use a few objects and write some Transact-SQL code to get started.

The main object is Service Broker is the Queue. The Queue is a specialized form of a Table that holds messages. Messages are read from a Queue by a Service Program triggered to execute in a process known as Activation. Activation can occur internally, wherein SQL Server will use a Stored Procedure as the Service Program or externally, where an application can poll the database for notifications that there are messages awaiting processing. Internal activation is norm; external activation is well suited for long-processes that would otherwise bog down the hosting SQL Server.

Unlike some other messaging frameworks, Service Broker does not send messages from or accept messages directly into a Queue. Rather, Service Broker abstracts the delivery endpoints for messages as a Service. Services are typically named using Uniform Resource Names (URNs) so that they may occur on many different servers for fault tolerance and workload distribution. An exchange of messages between Services is known as a Conversation. SQL Server 2005 supports two-party conversations and calls these a Dialog Conversation. The idea here is to allow any instance of SQL Server on the network to send a message to the same or a different an instance of SQL Server without having tight host binding. Note that Services are bound to a Queue which is materialized in an instance.

Services are networked using another new SQL Server object known as a Route. Each instance of SQL Server participating in a Service Broker-based application maintains a routing table. Services can also be configured only accept certain classes of messages. These classes of messages are cataloged into SQL Server using another new object known as a Message Type. SQL Server also allows for message types to be composed into a Contract. When we create a service, we can optionally specify contracts that the service will adhere to. These contracts specify when a given message type may be sent: either by the service that starts the conversation (known as the initiator) or by the target of that conversation.

Microsoft has added three new statements to Transact-SQL to support the dispatch and processing of queued messages: BEGIN DIALOG CONVERSATION, which prepares the local Service Broker internals to accept a message for dispatch; SEND which specifies the message payload and optionally a Message Type and RECEIVE, which fetches the available messages from a given queue much like a SELECT statement would.

Hello, World, Service Broker style

Now that we have seen the elements of Service Broker, let’s see in action with a very simple application. Let’s start by creating an empty database to work with and use it:

use master;
create database [HelloWorld];
use [HelloWorld];

If you remember from the previous section, I noted that we could define a Message Type that would describe the payload contents of a message. In this case, we will define two message types: one to pass a greeting and one to pass back a response. The payload for each message will be well-formed XML, so we describe that as part of the message type:

create message type [http://develop.com/examples/greeting] validation = well_formed_xml;
create message type [http://develop.com/examples/response] validation = well_formed_xml;

Note that there are not Web (or another other) services at the URL http://develop.com/examples. These are just URNs used to distinguish from one service from another. Our next step is to create queues to hold and messages. We do this with the following lines of Transact-SQL:

create queue dbo.initiator_queue;
create queue dbo.target_queue;

Normally when we create Queues we would specify how we want to deal with activation. In this simple example, however, we will by-pass that. The activation clause is covered in Books On-Line and in our Essential SQL Server class. With our queues created, we can next create a Service for each of these queues using the following statements:

create service [http://develop.com/examples/helloRequestor] on queue dbo.initiator_queue( [http://develop.com/examples/helloContract]);
create service [http://develop.com/examples/helloRespondent] on queue dbo.target_queue( [http://develop.com/examples/helloContract]);

Again, these are just names are URNs. We now have all of the objects in place we need to get started. Before we can send a message, we need to start a conversation and before we can start a conversation, we need to get a Conversation Handle. The Conversation Handle is unique value associated with each message sent in a conversation for grouping purposes. Once we have that value, as a UniqueIdentifier, we can ask the Service Broker to prepare to receive and transmit a message for us using the BEGIN DIALOG CONVERSATION statement. In that same batch, we can also use the SEND statement to pass the message type and payload off via Service Broker:

declare @ch uniqueIdentifier;
declare @mt nvarchar(256);
declare @msg xml;
begin try
begin transaction;
receive @ch = conversation_handle,@mt = message_type_name,@msg = message_body from dbo.target_queue;
select @msg;
-- Do message processing
send on conversation @ch
message type [http://develop.com/examples/response]
('<msg>Nice hearing from you.</msg>');
commit;
end try
begin catch
print ERROR_MESSAGE()
if xact_state() <> 0 rollback;
end catch
if xact_state() = 1 commit;

The conversation started here is directed the current database as no routing is needed in this case. When Service Broker accepts the message, it normally holds in a specialized system queue known as the Transmission Queue. In this case, however, our message will be immediately dispatched to the respondent service and then entered into the target queue. We can read the message type and payload easily with the receive statement. This is normally done within a transaction scope so that if SQL Server crashes during the processing of a message, we will not lose it. Once the message has been fully processed, we can remove it from the queue by committing the transaction. We can also send a response message back Here’s the code for doing that:

declare @ch uniqueIdentifier;
declare @mt nvarchar(256);
declare @msg xml;
begin try
begin tran;
receive @ch = conversation_handle,@mt = message_type_name,@msg = message_body from dbo.target_queue;
select @msg;
-- Do message processing
send on conversation @ch
message type [http://develop.com/examples/response]
('<msg>Nice hearing from you.</msg>');
commit;
end try
begin catch
print ERROR_MESSAGE()
if xact_state() <> 0 rollback;
end catch
if xact_state() = 1 commit;

And since we’ve send a response message back, we can read that using an almost identical coding pattern:

declare @ch uniqueIdentifier;
declare @mt nvarchar(256);
declare @msg xml;
begin try
begin tran;
receive @ch = conversation_handle,@mt = message_type_name,@msg = message_body from dbo.initiator_queue;
select @msg;
end conversation @ch;
end try
begin catch
print ERROR_MESSAGE()
if xact_state() <> 0 rollback;
end catch
if xact_state() = 1 commit;

Obviously this is a very limited example of the fundamentals of Service Broker, but it does give a fair example of how easy it is to program. Stop to consider that all we really did not show here is message routing between instances and using activation. The code shown here for sending and receiving message is very similar to what we would write in activation Stored Procedures and external applications.

When you combine the message framework that Service Broker offers with the XML processing and Common Language Runtime integration features in SQL Server 2005, your reach as a database developer is dramatically expanded. While, indeed, there is much more to learn, keeping a beginner’s mind that is open to the possibilities certainly helps.

Contact Kent Tegels DevelopMentor's Database Curriculum Lead with any questions or comments at: ktegels@develop.com

To learn more about Service Broker, see our Essential Sql Server for Developers course.

CLICK HERE to get a detailed course outline and put "SQL Server" in the subject line or call 800.699.1932.

Upcoming Guerrilla Events

What You Can Expect: - Multiple instructors (2-5) per event - Extended course hours - A team of industry leading experts - Leading-edge technologies - Innovative course materials, including slides, labs, books, demos - Your own PC and development platform - Peer-to-peer collaboration and competition - An exciting, multi-media learning environment All Inclusive Price includes: 5-NIGHTS HOTEL, ALL MEALS AND BEVERAGES

ABOUT GUERRILLA EVENTS

back to top

Upcoming Courses