Getting Started with SQL Server Service Broker

http://www.codeguru.com/cpp/data/mfc_database/sqlserver/article.php/c12783/Getting-Started-with-SQL-Server-Service-Broker.htm

Service Broker is a new feature in SQL Server 2005 that brings queuing and reliable messaging to SQL Server. Service Broker provides the "plumbing" to let you pass messages between applications, using SQL Server as the transport mechanism. Applications can use a single shared SQL Server database for this purpose or distribute their work across multiple databases. Service Broker handles all of the details of message passing, including:

• locking
• retries
• rejecting ill-formed messages
• ensuring "exactly once in-order" (EOIO) delivery

In this article I'll introduce you to the basics of Service Broker, including the terminology that it uses and the SQL statements that you'll need to implement a Service Broker application. This is just a quick survey; for details, refer to SQL Server Books Online.

Service Broker Terminology

Service Broker introduces a number of new terms to the SQL Server lexicon. These include:

• A message is a piece of information exchanged between applications that use Service Broker. A message can optionally be validated to match a particular XML schema.
• A conversation is a reliable, long-running, asynchronous exchange of messages.
• A dialog is a conversation between two services. All Service Broker conversations are dialogs.
• The initiator is the participant that begins a dialog.
• The target is the participant that accepts the dialog begun by the initiator.
• A conversation group is a group of related conversations. Every conversation belongs to exactly one conversation group.
• A contract is an agreement between two services about the message types allowed in a conversation.
• A queue stores the messages for a particular service.
• A service is a particular business task that can send and receive messages.

Creating a Service Broker Application

The basic steps involved in creating any Service Broker application include:

1. Defining message types
2. Defining contracts
3. Creating queues
4. Creating services
5. Sending and receiving messages

Each of these tasks has a corresponding T-SQL extension.

Defining Message Types

To define a message type for a Service Broker application, you use theCREATE MESSAGE TYPE statement. As part of this statement, you can specify whether a message must conform to a particular XML schema or be otherwise validated.

CREATE MESSAGE TYPE StockMessage
VALIDATION = NONE

Defining Contracts

After defining messages, you can use the CREATE CONTRACT statement to define a contract:

CREATE CONTRACT StockContract
(StockMessage SENT BY INITIATOR) 

Creating Queues

Every service managed by Service Broker requires queues to hold messages sent and received by that service. You can create these with the CREATE QUEUE statement:

CREATE QUEUE StockSendQueue

CREATE QUEUE StockReceiveQueue

Creating Services

Now that the queues exist, you can use CREATE SERVICE to build services to use them:

CREATE SERVICE StockSendService
   ON QUEUE StockSendQueue (StockContract)

CREATE SERVICE StockReceiveService
   ON QUEUE StockReceiveQueue (StockContract)

Sending and Receiving Messages

Now that all the pieces are in place, you can test sending and receiving messages between the two services. To do this, you'll use three T-SQL statements:

• BEGIN DIALOG CONVERSATION sets up the conversation between the two services.
• SEND sends a message.
• RECEIVE receives a message.

Sending Messages

To start a conversation between two services with a common contract, use the  BEGIN DIALOG CONVERSATION statement, which will return a unique dialog handle. After you've created the dialog and stored the dialog handle, you're ready to send messages. For this, you use the  SEND statement. Here's how it looks when you put the pieces together:

 

DECLARE @StockDialog uniqueidentifier
DECLARE @Message nvarchar(128)
BEGIN DIALOG CONVERSATION @StockDialog
   FROM SERVICE StockSendService
   TO SERVICE 'StockReceiveService'
   ON CONTRACT StockContract
   WITH ENCRYPTION = OFF
SET @Message = N'Add 12 widgets to inventory';
SEND ON CONVERSATION @StockDialog 
   MESSAGE TYPE StockMessage (@Message)
SET @Message = N'Remove 4 springs from inventory';
SEND ON CONVERSATION @StockDialog 
   MESSAGE TYPE StockMessage (@Message)
SET @Message = N'Add 7 twonkies to inventory';
SEND ON CONVERSATION @StockDialog 
   MESSAGE TYPE StockMessage (@Message)

Receiving Messages

To receive messages, you can use the RECEIVE statement:

RECEIVE CONVERT(NVARCHAR(max), message_body) AS message
   FROM StockReceiveQueue

What's Next?

Now you've seen how simple it is to set up queues and send messages using Service Broker, but you can go much further with it.

In many applications, you'll want to process incoming messages automatically. For example, you might want to take those inventory messages and automatically change rows in an inventory table. You can automate the response to an incoming message by taking advantage of the ability to associate an activation stored procedure with a queue in the CREATE QUEUE statement.

You can also use Service Broker to build distributed applications, where the sending and receiving queues are in different databases, even on different physical machines. If you do this, you'll want to be familiar with the CREATE ROUTE statement, which tells Service Broker how to find services on other computers. With this statement, you can store and forward queues in practically any architecture, as long as a TCP/IP route exists between the databases.

In older versions of SQL Server, developing asynchronous, reliable, message-based applications was difficult or impossible. With Service Broker, Microsoft has given you all the infrastructure you need to make the basics easy - so you can concentrate on your business needs. Keep this tool in mind and you're bound to find a use for it.

About the Author

Mike Gunderloy is the Senior Technology Partner for Adaptive Strategy, a Washington State consulting firm. You can read more of Mike's work at his Larkware Web site, or contact him atMikeG1@larkfarm.com