WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
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:
- 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:
- Defining message types
- Defining contracts
- Creating queues
- Creating services
- 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
After defining messages, you can use the
CREATE CONTRACT statement to define a contract:
CREATE CONTRACT StockContract (StockMessage SENT BY INITIATOR)
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
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 MessagesTo 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)
To receive messages, you can use the RECEIVE statement:
RECEIVE CONVERT(NVARCHAR(max), message_body) AS message FROM StockReceiveQueue
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.