Getting Started with SQL Server Service Broker

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 the CREATE 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 at MikeG1@larkfarm.com.



Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Today's agile organizations pose operations teams with a tremendous challenge: to deploy new releases to production immediately after development and testing is completed. To ensure that applications are deployed successfully, an automatic and transparent process is required. We refer to this process as Zero Touch Deployment™. This white paper reviews two approaches to Zero Touch Deployment--a script-based solution and a release automation platform. The article discusses how each can solve the key …

  • On-demand Event Event Date: December 18, 2014 The Internet of Things (IoT) incorporates physical devices into business processes using predictive analytics. While it relies heavily on existing Internet technologies, it differs by including physical devices, specialized protocols, physical analytics, and a unique partner network. To capture the real business value of IoT, the industry must move beyond customized projects to general patterns and platforms. Check out this webcast and join industry experts as …

Most Popular Programming Stories

More for Developers

RSS Feeds