Introduction

One way to understand Service Broker is to think of it as a postal service. To hold a conversation with your distant relative, you can communicate by sending letter through the postal service. The postal service sorts and delivers the letters. Your relative retrieves the letter from his mailbox, reads it and sends a new letter in response. You and you relative retrieve the letters from your mailboxes, read them, write responses, and send new letters – until the conversation has ended. Letter delivery happens "asynchronously" – while you and your relative handle other tasks.

Two users exchange mail through a postal service

In the postal service analogy, the letters are messages. A Service Broker service is the address to which the post office delivers the letters. Queues are the mailboxes that hold the letters once they are delivered. Applications receive the messages, act on the messages, and send responses.

A program that uses Service Broker holds conversations with other programs in a manner similar to postal delivery.

Components that interface with Service Broker

Service Broker is a new technology in Microsoft SQL Server 2005 that helps database developers build secure, reliable, and scalable applications. Because Service Broker is part of the Database Engine, administration of these applications is part of the routine administration of the database.

Service Broker provides queuing and reliable messaging for SQL Server. Service Broker is used both for applications that use a single SQL Server instance and applications that distribute work across multiple instances.

Service Broker Architecture

Service Broker applications are made up of Service Broker database objects and one or more applications that use those objects.

There are three types of Service Broker components:

Service Components: These are design-time components that specify the basic structure of the conversations that the application uses.

Conversation Components: These form the run-time structure of the Service Broker application that exchange messages as part of a dialog conversation between two participants.

Networking and Remote Security: These components define the infrastructure for exchanging messages outside of a SQL Server instance and configured independently of the application code.

These components will be briefly discussed in the following sections.

Service Architecture

This section describes the database objects that for the design-time components of the basic structure of the conversation that the application uses.

Message Types: A Message Type is a named definition of the format for messages exchanged between services. Message types persist in the database where the type is created. Identical message type can be created in each database that participates in a conversation.

CREATE MESSAGE TYPE [SubmitExpense]
VALIDATION = NONE 

CREATE MESSAGE TYPE [AcceptDenyExpense]
VALIDATION = NONE 

CREATE MESSAGE TYPE [ReimbursementIssued]
VALIDATION = NONE 

-- Create message types of validation type as NONE

Contracts: A contract defines the message types used in a conversation and also determines which participant in the conversation can send messages of that type. Contract definitions persist in the database where the type is created. You create an identical contract in each database that participates in a conversation

CREATE CONTRACT [ProcessExpenses]
( 
[SubmitExpense] SENT BY INITIATOR, 
[AcceptDenyExpense] SENT BY TARGET, 
[ReimbursementIssued] SENT BY TARGET 
)
-- Create a contract between two services that uses SubmitExpense message type for the Initiator 
-- and AcceptDenyExpense & ReimbursementIssued for the Target.

Queues: A queue is a named container for holding message while they await processing. A queue may or may not be associated with a service program. The queuing mechanism allows asynchronous communication between services. Other benefit of this mechanism is related message locking.

CREATE QUEUE ExpenseQueue
-- Create a queue ExpenseQueue that is not associated with a service program

Service Programs: A service program can be stored procedures written in Transact-SQL or a CLR-compliant language, or they can be external programs that connect to an instance of SQL Server. The service that does not have contracts can only be the initiator of a dialog.

CREATE PROCEDURE [ExpenseProcessing] 
AS
BEGIN
-- Code for ExpenseProcessing comes here
END
-- Create a service program ExpenseProcess to process the messages

Services: Service is name of task or a set of tasks between which conversations occur. Service Broker uses the service name to route messages, deliver messages to the correct queue within a database, enforce the contract for a conversation, and determine the remote security for a new conversation.

CREATE SERVICE [Expenses] 
ON QUEUE [ExpenseQueue] ([ProcessExpenses])
-- Create a service with name Expense, using ExpenseQueue that will be listening for messages, 
-- and accepting only messages applying to ProcessExpense.

The following diagram shows a Service Broker service:

Service Broker service

As shown in the illustration, the ProcessExpenses contract specifies three message types: SubmitExpense, AcceptDenyExpense, and ReimbursementIssued. The contract lists the message types required for a conversation that performs an expense reimbursement task. The ProcessExpenses contract governs all conversations between the ProcessExpense service and any services that initiate a conversation with the ProcessExpense service. The ProcessExpense service stores incoming and outgoing messages in the ExpenseQueue queue. The ExpenseProcessing stored procedure receives messages from this queue, processes the messages, and sends messages back to the queue for routing to the appropriate broker if a reply is necessary.

Conversation Architecture

This section describes messages, conversations, conversation groups that form the run-time structure of the conversation.

Messages: Messages are the data exchanged between services. Each message belongs to one conversation, and has a specific message type. An application typically processes the content of a message based on the contract and the message type. Each message has a unique conversation identity, as well as a sequence number within the conversation.

-- sending messages
SEND ON CONVERSATION @dialog_handle 
MESSAGE TYPE [SubmitExpense] ("message")
-- receiving messages
RECEIVE TOP(1) @msg = message_body, @dialog_handle = conversation handle
FROM ExpenseQueue

Dialog Conversations: A dialog conversation, or dialog, is a reliable, persistent stream of messages between two services. All messages sent by Service Broker are part of a conversation. A dialog conversation has two participants - an initiator who begins the conversation and a target that accepts a conversation begun by the initiator. Each dialog belongs to one conversation group, and follows a specific contract. Dialogs use the conversation identifier and sequence numbers that are contained in each message to identify related messages and deliver messages once only in the correct order (EOIO - exactly-once-in-order).

DECLARE @dialog_handle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [ExpenseClient]
TO SERVICE '[Expenses]'
ON CONTRACT [ExpenseSubmission]
-- Begin a dialog conversation between two services ExpenseClient and Expenses 
-- and specify the contract from the set of contracts listed as being supported by the TO service

Conversation Groups: Conversation groups identify conversations that work together to complete the same task. Service Broker uses conversation groups to manage message locking, which helps application developers manage concurrency. Application developers also use conversation groups to help with state management.

The following diagram shows the message flow of a dialog:

Message flow between initiator and target

Applications exchange messages as part of the dialog. When SQL Server receives a message for a dialog, SQL Server places the message in the queue for the dialog. The application 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.

Networking and Remote Security

This section describes objects that control how Service Broker communicates outside of SQL Server.

Remote Service Bindings: A remote service binding establishes a relationship between a local database user, the certificate for the user, and the name of a remote service. Service Broker uses the remote service binding to provide dialog security for conversations that target the remote service.

CREATE REMOTE SERVICE BINDING APBinding
TO SERVICE '[AccountsPayable]'
WITH USER = APUser

Routes: A route is required for Service Broker to deliver messages. By default, each database contains a route that specifies that services with no other route defined are delivered within the current instance.

CREATE ROUTE ExpenseRoute
WITH
SERVICE_NAME = '[Expenses]',
BROKER_INSTANCE = 'D8D4D268-00A3-4C62-8F91-634B89C1E315',
ADDRESS = 'TCP://192.168.10.2:1234' 

Service Broker Endpoints: An endpoint is a SQL Server object that represents the capability for SQL Server to communicate over the network. A Service Broker endpoint configures SQL Server to send and receive Service Broker messages over the network. Service Broker endpoints provide options for transport security and message forwarding. A Service Broker endpoint listens on a specific TCP port number.

CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) 

Code Walkthrough

This section provides a brief introduction to using Service Broker.

Enabling Service Broker

Service Broker must be enabled for the database to send and receive messages. Create two databases InitiatorDB and TargetDB with the TRUSTWORTHY option.

CREATE DATABASE InitiatorDB WITH TRUSTWORTHY ON
GO
CREATE DATABASE TargetDB WITH TRUSTWORTHY ON
GO
ALTER DATABASE InitiatorDB SET ENABLE_BROKER
GO
ALTER DATABASE TargetDB SET ENABLE_BROKER
GO
-- If conversation is across instances, activate network support for Service Broker by creating ENDPOINT

Once this is done, it is possible to begin sending and receiving messages.

Creating the services

As a first step is to create two types of messages in both the databases

CREATE MESSAGE TYPE [InitiatorRequest] VALIDATION = NONE 
GO
CREATE MESSAGE TYPE [TargetResponse] VALIDATION = NONE 
GO

Then, setup the contract in both the databases that will be used and associate the message types with the initiator the target.

CREATE CONTRACT [MainContract]
( 
    [InitiatorRequest] SENT BY INITIATOR,
    [TargetResponse] SENT BY TARGET
)
GO

Create the queues and associated services in the respective databases.

USE InitiatorDB
GO
CREATE QUEUE [InitiatorQueue] WITH STATUS = ON, RETENTION = ON
GO
CREATE SERVICE [RequestService] ON QUEUE [InitiatorQueue]
(
    [MainContract]
)
GO
USE TargetDB
GO
CREATE QUEUE [TargetQueue] WITH STATUS = ON, RETENTION = ON
GO
CREATE SERVICE [ResponseService] ON QUEUE [TargetQueue]
( 
    [MainContract] 
)
GO

Sending Message

Now that services are created, start the conversation in the InitiatorDB. This walkthrough simply exchanges the name of the server.

USE InitiatorDB
GO
SET NOCOUNT ON
DECLARE @ConversationHandle uniqueidentifier;
BEGIN TRAN
-- Begin a dialog to the target service
BEGIN DIALOG  @ConversationHandle
    FROM SERVICE  [RequestService]
    TO SERVICE      'ResponseService'
    ON CONTRACT   [MainContract]
    WITH ENCRYPTION = OFF, LIFETIME = 600;
-- Send the message
SEND ON CONVERSATION @ConversationHandle 
   MESSAGE TYPE [InitiatorRequest] (N'Hello! Request from '+ @@servername);
COMMIT
GO

Receiving Message

Once the message is sent by the initiator, the target service in the TargetDB checks the message type and sends the appropriate message.

USE TargetDB
GO
-- SELECT CAST(message_body AS nvarchar(MAX)) AS msg_body, * FROM [TargetQueue]
SET NOCOUNT ON
DECLARE @ConversationHandle uniqueidentifier;
DECLARE @MsgBody nvarchar(MAX);
DECLARE @MsgTypeName sysname;
BEGIN TRAN
-- just get the first message
WAITFOR 
(
        RECEIVE TOP (1)
@MsgTypeName = message_type_name,  
@ConversationHandle = conversation_handle,    
@MsgBody = message_body
        FROM [TargetQueue]
), TIMEOUT 60;
PRINT @MsgBody 
-- if the message is from the initiator, then respond
IF @MsgTypeName = N'InitiatorRequest'
BEGIN 
        -- Send the response to the request initiator
        SEND ON CONVERSATION @ConversationHandle
   MESSAGE TYPE [TargetResponse] (N'Hi! Response from '+ @@servername );
        -- no more messages/end of conversation
        END CONVERSATION @ConversationHandle;
END
COMMIT
GO

-- USE TargetDB
-- GO
-- SELECT CAST(message_body AS nvarchar(MAX)) AS msg_body, * FROM [InitiatorQueue]

Advantages of Service Broker

Service Broker's features provide a number of significant benefits to database applications.

• Database integration enhances application performance and simplifies administration.
• Message ordering and coordination for simplified application development
• Loose application coupling provides workload flexibility.
• Related message locking allows more than one instance of an application to process messages from the same queue without explicit synchronization.
• Automatic activation allows applications to scale with the message volume.

Database Integration: Integration with SQL Server allows transactional messaging without the added overhead and complexity of an external distributed transaction coordinator.

An application receives one or more messages, processes the message or messages, and sends a reply message within a single database transaction so the application remains in consistent state.

With database integration, data, messages and application logic are all in the database which becomes part of the routine database administration task.

A common development environment helps developer to leverage their knowledge with the database programming techniques for message-based programming.

Service Broker runs in the context to SQL Server instance. This allows each database to maintain its own queues thus helping Service Broker to manage resource usage across the entire SQL Server instance.

Ordering and Coordination Messages: Service Broker solves the traditional messaging system problems by handling message order, unique delivery, and conversation identification automatically. Once a conversation is established between two service broker endpoints, an application receives each message only once, and in the order in which the message was sent.

Support for Loosely Coupled Applications: Service Broker provides loose coupling between the initiating application and the target application. An application can send a message on a queue and then continue with application processing, relying on Service Broker to ensure that the message reaches its destination. This loose coupling provides scheduling flexibility.

Service Broker also allows systems to distribute processing more evenly, reducing the peak capacity required by a server which can improve overall throughput and performance in database applications. The entry application uses Service Broker to send information about the transaction to the applications that perform background processing reliably over a period of time, while the main entry application continues to receive new business transaction.

Related Message Locking: Service Broker associates related conversations in a conversation group and automatically locks all messages in the same conversation group, so that these messages can only be received and processed by one application instance. Meanwhile, other instances of the application can continue to de-queue and process messages in other conversation groups. This allows multiple parallel application instances to work reliably and efficiently, without requiring complicated locking code in the application.

Automatic Activation: One of the most useful features of Service Broker is activation. Activation allows an application to dynamically scale itself to match the volume of messages that arrive in the queue.

Typical uses of Service Broker

Service Broker can be useful for any application that needs to perform processing asynchronously, or that needs to distribute processing across a number of computers. Typical uses of Service Broker include:

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

Asynchronous Triggers: Many applications that use triggers, such as online transaction processing (OLTP) systems, can benefit from Service Broker. A trigger creates a message containing information about the work to be done and sends this message to a service that performs the requested work. The trigger then returns. When the original transaction commits, Service Broker delivers the message to the destination service. The program that implements the service performs the work in a separate transaction. The application thus avoids system slowdowns that result from keeping the original transaction open while performing the work.

Reliable Query Processing: Some applications must reliably process queries, without regard to computer failures, power outages, or similar problems. An application that needs reliable query processing can submit queries by sending messages to a Service Broker service. The application that implements the service reads the message, runs the query, and returns the results. All three of these operations take place in the same transaction. If a failure occurs before the transaction commits, the entire transaction rolls back and the message returns to the queue. When the computer recovers, the application restarts and processes the message again.

Reliable Data Collection: Applications that collect data from a large set of sources can take advantage of Service Broker to reliably collect data. For example a retail application with multiple sites can use Service Broker to send transaction information to a central data store. Because Service Broker provides reliable, asynchronous message delivery, each site can continue to process transactions even if the site temporarily loses connectivity to the central data store. Service Broker security helps to ensure that messages are not misdirected, and helps to protect the data in transit.

Distributed Server-Side Processing for Client Applications: Large applications that access multiple SQL Server databases can benefit from Service Broker. For example…

A Web application for ordering books could use Service Broker on the server side to exchange information between the different databases that contain data on ordering, customer, inventory, and credit. Because Service Broker provides message queuing and reliable message delivery, the application can continue to accept orders even when one of the databases is unavailable or heavily loaded. In this scenario, Service Broker functions as a framework for a distributed OLTP system.

Data Consolidation for Client Applications: Applications that must use or display information simultaneously from multiple databases can take advantage of Service Broker. For example a customer service application that consolidates data from multiple locations onto one screen can use Service Broker to run these multiple requests in parallel, rather than sequentially, and in so doing significantly shorten application response time. The customer service application sends requests to different services in parallel; as the services respond to the requests, the customer service application collects the responses and displays the results.

Large-Scale Batch Processing: Applications that must perform large-scale batch processing can take advantage of the queuing and parallel processing offered by Service Broker to handle large volumes of work quickly and efficiently. The application stores data to be processed in a Service Broker queue. A program periodically reads from the queue and processes the data. An application can take advantage of the reliable messaging provided by Service Broker to perform batch processing on a computer other than the computer from which the request originates.

In a Nutshell

All Service Broker applications communicate through conversations -- reliable, long-running, asynchronous exchanges of messages. Service Broker handles the most difficult tasks involved in writing messaging applications -- such as message coordination, reliable message delivery, locking, and starting queue readers -- leaving database developers free to concentrate on solving business problems.