Service Broker Solution

Introduction

Service Broker was introduced in SQL Server 2005 to provide a reliable, scalable, and asynchronous message queuing system for local or distributed applications. Service Broker solutions can range from a simple application existing ion a single database to a complex application reaching across remote SQL Server instance.

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.

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.

Service Broker Working

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.

Enabling Service Broker

Check if service broker is enabled :

SELECT is_broker_enabled FROM sys.databases WHERE name = 'MyDatabaseName'

Enable service broker :

ALTER DATABASE <> SET ENABLE_BROKER
GO

New in SQL Server 2008, Service Broker is enabled by default in all user databases as well as the msdb database. By using Object Explorer in SSMS, you can view and modify the Service Broker related properties on the Option page of the Properties window of a particular database, as shown in figure below:

Service Broker Components

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.

Syntax:-

CREATE MESSAGE TYPE message_type_name

    [ AUTHORIZATION owner_name ]

    [ VALIDATION = {  NONE

                    | EMPTY 

                    | WELL_FORMED_XML

                    | VALID_XML WITH SCHEMA COLLECTION 

                                                    schema_collection_name

                   } ]

[ ; ]

MSDN Reference for Message Types syntax

• 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.

Syntax:-

CREATE CONTRACT contract_name

   [ AUTHORIZATION owner_name ]

      (  {   { message_type_name | [ DEFAULT ] }

          SENT BY { INITIATOR | TARGET | ANY } 

       } [ ,...n] ) 

[ ; ]

MSDN Reference for Contracts syntax

• 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.

Syntax:-

CREATE QUEUE