A simple tutorial on SQL Server 2005 Beta 2 Service Broker

Date: April 6, 2005

Service Broker is a new technology in Microsoft SQL Server 2005 Beta 2 that helps database developers build secure, reliable, and scalable applications. Service Broker provides queuing and reliable messaging as part of the Database Engine. These features provide the infrastructure necessary to build high-performance applications that easily scale-up or scale-out.

1. Service Broker is part of each database and is enabled by default.

Disable service broker for a given database:

Alter Database [DatabaseName] Set Disable_Broker

Enable service broker for a given database:

Alter Database [DatabaseName] Set Enable_Broker

2. Service Broker Objects

Let's take a look at the objects that Service Broker makes use of.

• Message Types

Message types define the type of data a message must contain (or not

contain). Four are available: None, Empty, Well Formed XML and Valid XML with Schema Collection.

For example:

Create Message Type XMLMessage

Validation = WELL_FORMED_XML

• Contracts

A contract defines the message types used in a conversation and also determines which side of the conversation can send message of that type. Each conversation follows a contract that the initiating service (initiator) specifIEs when the conversation begins. Both sides of a conversation must define the same contract.

Each contract must have at least one Message Type associated with it.

For example:

Create Contract XMLContract

(XMLMessage Sent by ANY)

• Queues

Queues store messages. When a message arrives for a service, Service Broker places the message on the queue associated with the service.

You can optionally configure a queue to automatically execute a stored procedure (Service Program) when messages arrive (called Activation).

For example:

Create Queue SendingQueue With Status=ON, Retention=OFF

Create Queue ReceivingQueue With Status=ON, Retention=OFF

• Service Programs

Service Broker Service Programs are the applications that perform the work of sending (to Services) and receving messages (from Queues). A Service Program can be in the form of a Stored Procedure and/or external application (Windows applications in C# or others)

• Services

A Service Broker is the interface through which messages can be sent. A service is bound to exactly one queue; however, a queue can hold messages for more than one service.

For example:

Create Service SendingService On Queue SendingQueue

(XMLContract)

Create Service ReceivingService On Queue ReceivingQueue

(XMLContract)

• Dialogs

  l>

  A Dialog is a conversation between two Service Broker Services. A dialog is used to send messages from one Service to another using a compatible Contract.

  Remember that, you don’t send messages to a Queue directly. Rather, you send them to a Service.For example:

  Declare @handle uniqueidentifIEr

  Begin Dialog Conversation @handle

  From Service SendingService

  To Service 'ReceivingService'

  On Contract XMLContract;

   

  Send on Conversation @handle

  Message Type XMLMessage

  ('<hello>Welcome to Rickie Lee''s blog, www.cnblogs.com/rickIE</hello>');

   

  End Conversation @handle with cleanup;

  3. Relationships along with Service Broker Objects
  
  

  
  

  Each message sent must conform to one Message Type and that Message Type must be part of matching Contract. Contracts are tIEd to dialogs and each message sent is done so through the means of a Dialog Conversation.

  • Dialog conversations are made between two Service Broker services, or Conversation Endpoints.
  • Dialog conversations between two services are uniquely tracked by means of a conversation_id – a UNIQUEIDENTIFIER value. This identifIEr exists at both Conversation Endpoints of a Dialog Conversation.
  • Each Message is held in a Queue, which is essentially a table in the database.
  • A message sent using a message type in one service must have the same name and type in the target service.

  4. Service Broker Application Demo

  For simplicity, all the following statements will be run in one database.

  -- Create Message Type

  Create Message Type XMLMessage

  Validation = WELL_FORMED_XML

  -- Create Contract

  ONT-SIZE: 9pt; FONT-FAMILY: 'CourIEr New'">Create Contract XMLContract

  (XMLMessage Sent by ANY)

  -- Create Two Queues

  Create Queue SendingQueue With Status=ON, Retention=OFF

  Create Queue ReceivingQueue With Status=ON, Retention=OFF

  -- Create two Service Endpoints

  Create Service SendingService On Queue SendingQueue

  (XMLContract)

  Create Service ReceivingService On Queue ReceivingQueue

  (XMLContract)

  -- Begin Dialog Conversation

  Declare @handle uniqueidentifIEr

  Begin Dialog Conversation @handle

  From Service SendingService

  To Service 'ReceivingService'

  IZE: 9pt; FONT-FAMILY: 'CourIEr New'">On Contract XMLContract;

  Send on Conversation @handle

  Message Type XMLMessage

  ('<hello>Welcome to Rickie Lee''s blog, www.cnblogs.com/rickIE</hello>');

  End Conversation @handle with cleanup;

  It’s very simple. I think no exception will occur. Next let’s check if the message is being transmitted to the target queue.

  Select * From ReceivingQueue

  Result: …………….. <One record will be displayed in the query result panel.>

  Select Cast(message_body as XML) From ReceivingQueue

  Result: <hello>Welcome to Rickie Lee's blog, www.cnblogs.com/rickIE</hello>

  Once the message is there, then we can pick it up with the following script:

  Receive Cast(message_body as XML) From ReceivingQueue

  Result: <hello>Welcome to Rickie Lee's blog, www.cnblogs.com/rickIE</hello>

  References:

  1. A First Look at SQL Server 2005 Service Broker, Roger Wolter,

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlsvcbroker.asp?frame=true

  2. Michael G., Service Broker Architecture, http://www.dotnetfun.com/articles/sql/sql2005/SQL2005ServiceBrokerBasicArchitecture.ASPx

  3. Mike Taulty’s Weblog, SQL Server 2005 Service Broker & WSE 2.0,