Traditional database applications work synchronously: a request is sent to the database, and the client must wait for the database to respond before processing can continue. Applications that rely on multiple databases would have to wait for each database to respond in turn to complete a transaction.

Service Broker allows requests to be handled asynchronously, by providing message queues for requests that cannot be serviced immeadiately. In a Service Broker application, databases are defined as communication end-points, each with an associated queue implemented as a table. Service Broker manages queues implemented as tables using a FIFO queuing system. For each queue a service will be defined with an associated contract that defines the defines the message types it handles in addition to the queue it belongs to. More than one service can be assigned to a queue and contracts can be assigned to more than one service, but a service can only be assigned to one queue.

Under Service Broker, instead of sending or requesting data from a datasource, applications start a conversation by sending a message to a service. The service then transfers the message on to its queue.

Implementing Service Broker

The steps for creating a Service Broker application are:

1. Enable service broker for the database
2. Create XML Schema collections for message validation - optional
3. Define message types
4. Create a contract defining the messages to be handled by each service
5. Define queues to store messages
6. Define the services associated to each queue

Messages can then be sent with a BEGIN DIALOG command, followed by successive SEND and RECEIVE commands

Use to the 'ALTER DATABASE' command to enable Service Broker:

ALTER DATABASE db_name SET ENABLE_BROKER
GO
USE db_name
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'password'
GO

Message types are defined in a 'contract' specifying the format for communication between endpoints:

CREATE MESSAGE TYPE msg_type_name
[AUTHORIZATION owner_name]
[VALIDATION = <validation_option>]

The validation options are:

• NONE
• EMPTY
• WELL_FORMED_XML
• VALID_XML WITH SCHEMA COLLECTION schema_collection_name

Schema_collections for message validation are created by:

CREATE XML SCHEMA COLLECTION collection_name 
AS
   -- XML Schema Definition --

SELECT * FROM sys.xml_schema.collections

For each message type defined, a reply message type will also need to be defined

With the messages defined, the next step is to define a contract specifying the messages to be handled by a service:

CREATE CONTRACT contract_name
[AUTHORIZATION owner_name]
( ( message_type_name | DEFAULT ] )
  SENT BY [ INITIATOR | TARGET | ANY ]),
( message_type_name 
  SENT BY ... ), ...

All messages are stored in a queue, implemented as a hidden table:

CREATE QUEUE queue_name
  WITH STATUS = ON
  RETENTION = OFF

The RETENTION = ON setting tells Service Broker to keep messages until the entire conversation is finished

Services need to be defined for a queue, specifying the types of conversations and contracts applicable:

CREATE SERVICE service_name
ON QUEUE queue name contract_name

Conversations are the mechanisms that enables communication between endpoints, specifying the service and contracts used:

BEGIN DIALOG [CONVERSATION] @dialogue_handle
  FROM SERVICE initiator_name
  TO SERVICE   target_name
ON CONTRACT contract_name
WITH { related_conversation_specification }
  LIFETIME = dialogue_lifetime
  ENCRYPTION = [ ON | OFF ]

The dialogue_handle is used to identify a dialogue and tag messages within the dialogue.

In a Service Broker application, all work is placed on the queues using SEND, and received from the queue using RECEIVE:

SEND
  ON CONVERSATION conversation_handle
  MESSAGE TYPE message_type_name
    (message_body)

RECEIVE [TOP N]
  <column_list>
FROM <queue>
[INTO table_variable]
[WHERE conversation_specifier ]

Priority Messages

The ACTIVATION clause of a Service Broker queue definition, allows you to assign a stored procedure to a queue that will execute as soon as a message hits the queue. As more messages arrive, new instances of the stored procedure are executed. Thus Service Broker dynamically allocates resources according to demand. High priority messages can be specified using the CREATE BROKER PRIORITY command, to circumvent the normal FIFO queue management