Introducing Distributed Messaging using Service Broker in SQL Server 2005

Download source - 10.3 Kb

Introduction

Microsoft SQL Server 2005 introduces a new distributed messaging framework that allows for asynchronous programming support. The Service Broker is a new platform for building asynchronous � distributed database applications. In this article I will give a brief overview of the definitions of the most important aspects in the Service Broker platform, and give a sample application that leverages some of its features.

There will be a part 2 for this article which is currently being developed, that will talk about more of the advanced features in the Service Broker platform, and also providing an enterprise sample solution.

What the Service Broker provides

With the new Service Broker platform, internal or external SQL Server instances can send and receive guaranteed, asynchronous messages by using Transact T-SQL. Messages can be sent from within the same database, different database, or even remotely located SQL Server instances.

How the Service Broker works

The Service Broker communicates with a newly developed protocol called the Dialog, which allows for bi-directional communication between two endpoints. The Dialog protocol specifies the logical steps required for a reliable conversation, and makes sure that messages are received in the order they were sent. The following diagram below from Microsoft SQL Server 2005 books online, illustrates how the Dialog protocol is being utilized in the Service Broker platform:

The following is another overview diagram of the Service Broker, it shows the main entities of the Service Broker such as a Message, Contract and Service which will be explained further in the next section. However let's look at the dialog conversation part which is a high level protocol used by the Service Broker to make sure messages are received in order and with no loss. The dialog conversation protocol as we see is not a transportation protocol as it is an application protocol to maintain the message Exactly-Once-In-Order (EOID). The EOID, is the concept of ordering messages no matter what order they were received in from the transportation channel.

The Service End Points as we see below are the communication end points between two Service Brokers. They are the ones who support the transportation protocols such as TCP/IP, HTTP, SOAP etc�

The end points are created on each Service Broker, to allow for remote communication between different SQL Server instances. There will be further explanation for each of the following in the next section.

Service Broker Concepts

Messages

A message is an entity that is exchanged between Server Brokers. A message must have a name and data type. Optionally, a message can have a validation on that type of data. A message is part of a conversation and it has a unique identifier as well as a unique sequence number to enforce message ordering.

Note that all messages sent by a Service Broker are part of a conversation.

Dialog - Conversations

A dialog is a conversation between two Service Brokers it defines, the initiator service, the target service and the contract that will be used for their conversation. It furthermore defines encryption options, and lifetime for a single conversation.

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.

Conversation Groups

The conversation group is a collection of related dialog conversations. Let's take the example of airline ticketing system. As you can imagine a conversation group is like a family that needs to check-in together. The same concept also applies for messages relating to each other, they must be received and processed in order.

SQL Server 2005 provides locks on a conversation group to provide Exactly-Once-In-Order (EOID) functionality. That allows for messages of the same conversation group to be received in order. Thus, only one session at a time can receive messages for the conversation group. Because a conversation group can contain more than one conversation, an application can use conversation groups to identify messages related to the same business task, and process those messages together.

End Point

SQL Server 2005 uses end points to communicate with Service Brokers on different SQL Server instances. An end point allows for a Service Broker to communicate over the network using transport protocols such as HTTP, TCP, and SOAP. An end point for each transportation protocol has to be defined using T-SQL DDL.

Please note, by default, SQL Server does not contain any end points, and hence must be first created to enable communication.

Furthermore, security between SQL Server instances must be enabled for such communication to be allowed.

Service Broker Security

Service Broker Security allows for services to communicate securely, even if they are located on different computers. Server instances can be located on different networks, or even over the internet and still communicate securely together.

Service Broker security relies on certificates that are shared among remote databases, however no other information is shared.

Service Broker allows for two types of security:

Dialog Security
Encrypts messages in an individual dialog conversation and verifies the identities of participants in the dialog. Dialog security also provides remote authorization and message integrity checking. Dialog security establishes an authenticated and encrypted communication between two services.
Transport Security
Prevents unauthorized databases from sending Service Broker messages to databases in the local instance. Transport security establishes an authenticated network connection between two databases.

Service Broker from the inside

The Service Broker consists of the following six main entities within each database:

Message Types
Describes the name, data type and validation of a message, this can be a variant type or an XML data that is specified by a schema.
Contracts
A contract, is like what the word says, is a contract between two services describing what message types will be included, and who should be authorized to send them. For example you can specify multiple message types in a contract and specify whether the sender or the receiver is allowed to send it.
Queues
A queue is the primary storage for messages transferred between two services. Furthermore it can be associated with more than one service, and provides an activation mechanism. The activation mechanism allows for stored procedures to be called once a message is received to handle the message. This can be viewed like a pipeline for a message. A queue must be set to be active to be able to send and receive messages. Please note, you can query a queue using a SELECT statement.
Services
A Service is used by the Service Broker to deliver messages to the correct queue within a database, to route messages, to enforce the contract for a conversation, and to determine the remote security for a new conversation.
Routes
A Service Broker uses routes to determine where to deliver the message. This can be used for distributed messaging within the Service Broker, allowing either for remote or even local instances to communicate. When creating routes, you specify the service it will route to, the address and protocol. By default, each database has a AutoCreatedLocal route that is used to define the local instance of the database.
Remote Service Bindings
Creates a binding that defines the security credentials to use to initiate a conversation with a remote service.

The diagram bellows shows the Service Broker for a (MyDB) database, and its six main entities:

From the above diagram you can see that for each database we have a tree with six main items that you can view. For the administration and management part, until the April CTP release of SQL Server 2005, there is no user interface to manage those items, they are all created and edited using T-SQL.

A Simple Service Broker Example

The following examples show how you can create simple messaging between two databases inside a single server instance, using T-SQL and the Service Broker platform. The samples exclude routing, security, and remote service binding; they will be presented in another article.

Creating a Message Type

-- Create a Type for Send Message type

CREATE MESSAGE TYPE SendMessageType
    VALIDATION = WELL_FORMED_XML;

-- Create a Type for Receive Message Type

CREATE MESSAGE TYPE ReceiveMessageType
    VALIDATION = WELL_FORMED_XML;

The above code creates a message that is of a validation type as a WELL_FORMED_XML. This is a built in type inside SQL Server 2005. It allows only to send messages that conforms to XML standards. Of course you can use your own XML schema, to validate against.

Creating a Contract

-- Create Contract to be used

CREATE CONTRACT [MainContract]
(
    [SendMessageType] SENT BY INITIATOR,
    [ReceiveMessageType] SENT BY TARGET
);

The following creates a contract between two services, it basically says that you must use SendMessageType for the Initiator (Sender) and ReceiveMessageType for the Target. This only allows a sender to use the SendMessageType while the Receiver to use ReceiveMessageType.

Creating a Queue

CREATE QUEUE QUEUE1
    WITH STATUS=ON,
    ACTIVATION (
        PROCEDURE_NAME = OnReceiveMessage,
        MAX_QUEUE_READERS = 5,
        Execute AS 'dbuser') ;

You need to replace dbuser with your own username.

The queue created creates activation on a stored procedure that will handle the message when it arrives. The stored procedure name is OnReceiveMessage. The maximum number of queue readers is 5. Furthermore you must specify the user it will be using to run from as a security context.

Creating a Service

CREATE SERVICE SERVICE1
    ON QUEUE [QUEUE1]
    (
    [MainContract]
    );

The following creates a service with name Service1, using Queue1 that will be listening for messages, and accepting only messages applying to MainContract.

Beginning a Dialog Conversation

BEGIN DIALOG CONVERSATION @dialog_handle
   FROM SERVICE [SERVICE1]
   TO SERVICE 'SERVICE2'
   ON CONTRACT [MainContract] ;

A Dialog Conversation is created with a dialog_handle which is a GUID ID for each conversation, this is created on starting a dialog. We specify the From Service and To Service. Furthermore we specify the contract to be used.

Sending and Receiving Messages

Sending Messages

    SEND ON CONVERSATION @dialog_handle
    MESSAGE TYPE SendMessageType (@msg)

Sending a message is easy. We give it the dialog_handle and specify the type of message to be sent, since we have multiple message types in our contract. We then add the message which is a local XML variable.

Receiving Messages

    RECEIVE TOP(1) @msg = message_body, 
        @dialog_handle = conversation_handle
    FROM QUEUE2

The recipient receives the message by selecting the result from QUEUE2. Of course you can select based on the conversation identifier handle. However, for simplicity, we select the first message and assign a message to it. We can then do anything with the message according to the business logic of our application.

Ending a Conversation

    END CONVERSATION @dialog_handle
    WITH cleanup

The Sample Application

The sample application is a series of SQL scripts that creates two databases that work on the same instance of SQL Server.

The application includes a script for each database that creates message types, contracts, queues and services required. The application runs in the following manner:

Send Script sends a message from Service Broker on db1 to Service Broker on db2.
Service Broker on db2 receives message and a stored procedure picks up the message and logs the message data and status to a log table.
Service Broker on db2 sends back acknowledgement to db1 with another message.
Service Broker db1 logs the message and status.
Conversation is ended on both sides.

The sample application is fairly simple, it doesn�t provide routing functionality over different instances, security, or even end point creation and communication. This functionality will be provided in a separate article with more in-depth details on the Service Broker security, and remote abilities.

Service Broker Tips and Tricks

SQL Server 2005 has a collection of templates that can be used to create all kinds of SQL queries in almost all T-SQL structures. It can further be used for the Service Broker, to create queues, services etc� and also other Service Broker functionality, please look at the diagram below:

You need to go to the solution file and right click and add new items. This will provide the ability to add templates of the Service Broker functionality right into your application.
Another tip is when creating a database and you may require it to communicate with another database using Service Broker, you need to create the database using:
```
create database localsb_db1
    WITH TRUSTWORTHY ON;
```
The authorization allows for communication between two server databases on the same SQL Server instance. This is a must if you need both Service Brokers to communicate.

When to use a Service Broker

The following list provides a typical list of Service Broker uses; this list is provided from MS SQL Server 2005 Online books provided by Microsoft SQL Server 2005 CTP Release in April.

Asynchronous Triggers
Reliable Query Processing
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 a Service Broker. A trigger queues a message that requests that work from a Service Broker service. The trigger does not actually perform the requested work. Instead, the program that implements the service performs the work in separate transaction. By performing this work in a separate transaction, the original transaction can commit immediately. The application 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 interruptions from 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, the entire transaction rolls back and the message returns to the queue. When the computer recovers, the application restarts and processes the message again.

Data Collection

Applications that collect data from a large set of sources can take advantage of a Service Broker to reliably collect data. For instance, a retail application with multiple sites can use a Service Broker to send transaction information to a central data store. Because a 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 for information are good candidates for use with 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. Service Broker provides message queuing and reliable message delivery, and so 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 serially, and significantly shorten application response time. A 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.

What�s next?

I will be providing another article talking about the advanced features such as security, routing and service end points in the Service Broker, and providing an enterprise sample application example soon.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here