SQL Server 2005 – Service Broker

Introduction

The process of sending and receiving asynchronous messages by different processes in SQL Server 2005 is enabled by the Service Broker. The Transact SQL Data Manipulation Language is used to the send messages to a queue in the database of the sender or to another database in the SQL Server instance, or another server instance or remote server. Conceptually the process involves queues, dialogs, conversations groups and activation. In this tutorial we will briefly examines these concepts before having a practical look at what happens when messages are sent or received by the service broker.

Service Broker objects and architecture

The Service Broker consists of a number of SQL Server objects that represent the various components of the asynchronous messaging system. A service is a set of tasks that is available, while message types represent the messages exchanged to complete a task. The messages are held in a queue and these messages are processed at predefined intervals and sent. Once the message has been sent, the application updates the data to indicate that the message has been sent.

To facilitate this process, a number of additions have been made to SQL Server database. Message types, queues, services, contracts and conversations have been added to create the various capacities of the Service Broker.

The Message type object determines the structure of the messages that can be sent from one service to another. The message type is created using the CREATE MESSAGE TYPE statement. This statement allows developers specify the format of the messages and constrain messages in varying degrees. This can be a simple varbinary message type that will allow only messages of the varbinary type or xml Message type which will perform validation via XML and needs to include the VALIDATION clause and reference existing XML schema collections.

The Contract object is an agreement between two services that helps prevent various parts of the application from sending bad messages. Message types are used to establish this agreement between services. All messages bound by this contract must use the message type whatever its structure. The SENT BY clause indicates the sender of the message and mandates that the message can only be sent by the initiator of the conversation. INITIATOR defines the initiator of the conversation and the TARGET indicates the recipient of the conversation. ANY indicates that either service can send the message.

Queues

A queue is an object that holds the messages for the service. It is defined in the CREATE QUEUE statement. These queues can be defined to be accessed at the option of the user or can be set to respond as and when messages are received. The syntax would be as under:

CREATE QUEUE studentmsgqueue

If the messages have to be processed as and when received, a stored procedure will have to be defined as under:

CREATE PROCEDURE NewStudentlocator
AS
DECLARE @hConversation uidentifier,
…..@msgTypeName nvarchar(256),
…..@msg varbinary(max)
WHILE(1-1)
BEGIN
…..BEGIN TRANSACTION
…..WAITFOR
(
…..RECEIVE TOP(1)
……….@hConversation=conversation_handle,
……….@msgTypeName=message_type_name,
……….@msg=message_body
…..FROM dbo.Studentmsgqueue
), TIMEOUT=500
…..
IF @@ROWCOUNT=0
BEGIN
…..ROLLBACK TRANSACTION
…..BREAK
END
IF @msgTypeName=’Example/StudentID’
BEGIN
…..–Process incoming messages of type EXFORExample/StudentID here
…..COMMIT TRANSACTION
…..CONTINUE
END
IF @msgTypeName=’http://schemas.xxx.com/SQL/ServiceBroker/EndDialog’
BEGIN
…..–Process end of dialog messages here
……………END CONVERSATION @hConversation
……………COMMIT TRANSACTION
……………CONTINUE
……….END
……….ROLLBACK TRANSACTION
…..END
GO
CREATE QUEUE dbo.STUDENTMSGQUEUE
…..WITH ACTIVATION
…..(PROCDURE_NAME=NewStudentLocator,
…..STATUS=ON,
…..MAX_QUEUE_READERS=1,
…..EXECUTE AS SELF)

Microsoft recommends that developers should check for ‘Error’ and ‘DialogTimer’ also in addition to the above check for ‘ÉndDialog’.

Dialogs

Dialogs are implemented as bidirectional streams of messages between two endpoints. The messages in a dialog are ordered and delivered in the order of dispatch. This order is not disturbed across transactions, inputs, threads, or crashes and restarts. This is a unique feature of the Service Broker. Each message is given a unique identifier in form of a conversation handle. This helps identify the application that sent the message.

Conversation groups

Dialogs are grouped in accordance with the task they have to perform. This grouping is provided by conversation groups implemented by the conversation group identifier which is included in the messages. The conversation group is locked when a message is received from any of the dialogs in the group and is held by the receiving transaction for the duration of the transaction. This makes the application more scalable as a number of threads can be written and are processed only one at a time and the application need not be made resilient to problems caused by simultaneous processing of multiple threads.

The conversation group identifier labels the state associated with a process and can be used as the primary key in the state tables to enable quick retrieval of the state associated with the message.

Activation

The activation feature of the Service Broker can be used to specify a stored procedure and handle messages destined for a particular service. On receipt of the message the service broker checks whether there is a stored procedure running for the message and hands over the message to the procedure to process. If there is no procedure running a procedure is started. This procedure processes the messages until the queue is empty. In instances where messages are queued faster than they are processed, the Service Broker starts additional instances of the procedure to keep the processing of messages optimal. The maximum number of procedures to be run has to be configured for this purpose.

Use of Asynchronous, Queued Messaging

Scheduling of work is enabled by queues and this, results in scalability and enhanced performance. Some parts of the message such as the header will have to be processed before the message can be committed. The other parts of the message can be processed in an asynchronous manner if the core is processed in the beginning and it can provide opportunities for increased parallel processing for improved response time. Queuing also enable systems distribute processing evenly across the server time and reducing the peak capacity required by the server.

Use of Transactional Messaging

Transactional messaging is supported by the Service Broker. When a transaction fails the sends and receives are rolled back and will not take effect until the transaction is processed and the messages are successfully committed. An advantage of this messaging application is that sends and receives can be scattered and nothing happens till the transaction is committed. If the transaction is rolled back both sends and receives are relegated to the queue for reprocessing.

Solutions to the Hard Problems in Messaging

Messaging issues include message ordering, coordination, multithreading and receiver management. Each of these areas presents their own problems.

Message Ordering is the process of placing the messages in the order they are sent or received for processing. In traditional scenarios, message ordering was cumbersome and it was easier to deliver messages out of order. Programmers undertook elaborate coding to ensure that message 2 was received before message 3 was processed and so on by caching the messages and processing them in that order. Service Broker makes the whole process transparent by sending out the messages in sequence and processing them in the sequence sent.

Another associated problem of traditional messaging, namely the acknowledgement of messages received, is also resolved by Service Broker. In traditional applications if there is a power failure during a message send operation, the application assumes that the message was not sent and sends it again. There was always a possibility that the message would be received twice or acknowledged twice. Service Broker ensures that messages are not sent twice or acknowledged twice even if power is lost in the middle of a transaction.

Coordination is an essential feature as messages are viewed as stand alone entities and are processed individually. To identify the conversation from which the message is received, is sometimes rendered difficult as the processing of the request and the response may differ from one message in a conversation to another. When a number of conversations are initiated and responses are awaited the problem becomes further complicated. The Service Broker provides each message with a dialog handle and a unique conversation group identifier which enables the identification of the message with the conversation group.

Multithreading is the processing of multiple threads simultaneously. In traditional application multithreading often resulted in message threads being processed out of order. This problem has been solved by putting a lock on a conversation group as soon as the message is read, so that associated message threads are not received until the transaction is committed. This methodology is simple yet reliable.

Receiver management

Developers had to take care of two very important issues in a messaging system. They had to ensure that the receiving application is started before the sending application and they had to decide the number of threads or application instances must run in each queue. However, fixing these numbers presented their own problems. There may be too many or too few applications running as against the determined need. Service Broker simply activates the queue readers as required when the messages arrive. If the queue reader crashes or the system is rebooted the readers are automatically restarted to read the messages in the queue.

Messaging and the Database.

The advantages of building messaging into the database are many. The first of these is the conversation group locking mechanism. This a new type of database lock that is deciphered by the Service Broker commands. Secondly it enables the support for remote transactional receives from servers that can connect to the database. Thirdly, it synchronizes the message store and the database store by placing both the messages and the data in the same database. Backup and security issues also become easier to handle. Fourthly, since Service Broker is integrated into the database engine, messages addressed to another queue in any database on the same SQL Server instance can be directly put into the receive queue without routing it through the send queue. This improves performance.

The Common Language Support leverages the developer’s familiarity with Microsoft ActiveX Data Objects and other database programming techniques for message based programming. Stored procedures are now available to implement a Service Broker service which can be written in a variety of languages and take advantage of the common administration benefits of Service Broker.

A single connection and execution capability implies that the messaging transaction does not have to be a distributed transaction.

Since the Service Broker messages are internally handled by the database, the sender’s access permissions can be easily checked. This ensures greater security.

The database context enables the Service Broker to maintain an aggregate view of all messages that are in queue for transmission. The database is therefore, empowered to maintain its own transmission queues for backup and administration while optimizing on resources across databases in the SQL Server instance.

In short the database integration of the Service Broker makes it possible to bring queued messaging services to database applications.