A simple tutorial on SQL Server 2005 Beta 2 Service Broker

lakers009

A simple tutorial on SQL Server 2005 Beta 2 Service Broker

Posted by: Rickie Lee (www.iyunv.com/rickie)

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
  

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
('Welcome to Rickie Lee''s blog, www.iyunv.com/rickie');

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
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'
On Contract XMLContract;

Send on Conversation @handle
Message Type XMLMessage
('Welcome to Rickie Lee''s blog, www.iyunv.com/rickie');

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: ……………..

Select Cast(message_body as XML) From ReceivingQueue
Result: Welcome to Rickie Lee's blog, www.iyunv.com/rickie

Once the message is there, then we can pick it up with the following script:
Receive Cast(message_body as XML) From ReceivingQueue
Result: Welcome to Rickie Lee's blog, www.iyunv.com/rickie


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, http://mtaulty.com/blog/archive/2005/02/14/1484.aspx