Geeks With Blogs
My Place For SQL Lets Talk SQL

From past so many days I am trying to write something or the other..Bt some how started on this fundamental first DBA topic of YUKON


Is it like internal MSMQ of SQL ???Or a Messaging service between various SQL Components

what happened with the proliferation of web and Non Web applications was that it created a need for increased process management across database applications. 


All SQL 2005 papers give example of Order entry System for positioning this Service Broker.But I feel a very real time example of when I was working for a E-crm Dialer Application.How difficult it was to get each and every call logged woth the sales data and the notes from Agents..MSMQ would get stuck..Network Congestion..deadlock problems..SQL Tuning problem and then Dialer Components misbehaving..I wished to have a PIPE inside SQL that could store all transactions as messages asynchronously and pull them out once the system was connected back..

SQL Server 2005 Beta 2 provides a new scalable architecture for building asynchronous message routing.

“The Service Broker technology allows internal or external processes to send and receive guaranteed, asynchronous messages by using extensions to normal Transact-SQL data manipulation language (DML).

Messages are sent to a queue in the same database as the sender, to another database in the same instance of SQL Server, or to an instance of SQL Server either on the same server or on a remote server. Service Broker brings the best of asynchronous messaging with the best transactional support available, the SQL Server relational engine”

Thats how MS described it..Now I fail to understand whats new in SQL Service broker...We could have acheived the same using MSMQ and Data transfer technologies between Services where INter service data delivery goes like messages...

Why did we need it??Well The cumbersome must go and easy stay back..So here is the breif Introspection of what all I found about this Broker from a website..Well the architecture part is beautiful to understand

The Service Broker exposes the distributed application components as services holding conversations among them. It also manages these services in sending, receiving and processing messages. Within a typical Service Broker scenario a source service sends a message to a target service.  On its arrival, the Service Broker inserts it into a queue associated with a target service and then it can be processed by a service program. Once the message is processed, the target service responds. Due to the messaging potentialities of the Service Broker, services can communicate for days using an asynchronous communications approach

The Service Broker Architecture
Technically, the Service Broker can be viewed as a group of T-SQL extensions used to create components to develop message-based applications. The service functionality is provided for by a set of SQL Server objects:

  • Service Program: A service program may be a stored procedure written in a SQL/CLR manner used to process messages. It can send messages to be processed by other service programs.
  • Queue: A Queue is the component that stores the messages before they are processed by a service program associated with them. This queue provides asynchronous messaging between services and offers other potentialities like lock-related messages.
  • Message Type: A Message Type represents a message format used to communicate with services. Specifically, they can communicate with each other by interchanging message type instances.
  • Contract: A Contract is a set of names of message types that specifies the direction of a message in a conversation.
  • Service: A service is a logical endpoint that represents a collection of the above mentioned objects performing some specific task. Services are stored in SQL Server and have a name.

Service Broker applications base their communication on a conversation. A conversation is a component that correlates and orders the messages that services receive. Theoretically, a conversation is a message exchange pattern that represents the same idea as a human dialog. The conversation support is a technological innovation in the services technologies. Some Web Services technologies like Indigo and WebLogic Server address this issue in different ways. In the case of the Service Broker it relies on some SQL Server terms:

  • Dialogs: Dialogs represent a bidirectional conversation between two Service Broker endpoints.
  • Service Instances: The service instances identify conversations working together to achieve some tasks.
  • Routes: A route is used to direct messages to the correct services even between different instances of SQL Server.
  • Remote Service Bindings: Remote Service Bindings associate a remote service with a user in a local database.

Message Types

Inter-service communication is carried out by interchanging messages. Just as sentences follow a structure in real life conversations, messages sent to services must also follow some format dictated by the Message Type used to describe the message contents (binary or XML data). In the case of XML messages, the data Service Broker always checks whether the content is valid or not, that is, it checks if messages are appropriately structured or if they follow some XML Schema. Message Types always remain in the database.


The fact that a service exposes a contract to describe the operations it supports constitutes a pillar of the Service-Oriented Architecture. A Service Broker contract describes which messages may be used to perform a desired task and also which endpoints may use specific message types. For instance, a contract may specify that some messages may only be sent by the target service.


The role of a queue is to store messages. It is directly associated with a service. When a message arrives, the Service Broker inserts it into the queue. Likewise, when the application receives a message, the Service Broker deletes this message. A queue is also associated with the service program representing a stored procedure or program activated when the message is received. The maximum number of service programs to be activated when a message arrives is specified in the definition of the queue. This makes it possible to increase the message processing performance. The queue stores the message content and the information related to it, for example the contract, the relationship with other messages, etc.

Service Programs

The Service Program is the entity that processes the messages. Normally, it is either a stored procedure or a managed external program. As stated above, the Service Program is activated when a message arrives to a queue extracting the message and processing it. When it does this, it can send messages to other services.


The service identifies a set of tasks performed by various objects specifying the contracts and the queues that store the messages. A Service represents a logical endpoint grouping other objects. To create a service it is necessary to define the message types to be sent or received as well as the contracts, the service program to process the messages and the queue that is supposed to hold the messages.


Dialogs represent conversations that involve two endpoints: the initiator and the target. As part of the contract and based on its role, it is necessary to define which messages can be sent or received by a service. A key aspect in any conversation is the message reliability ensured by the receipt acknowledgments of the dialog messages. The Service Broker, in turn, saves all the messages in a conversation as soon as they are acknowledged by the other endpoint in the conversation.
Another important aspect in a conversation is its lifetime. When the initiating application begins the dialog, it may specify the lifetime of this dialog. The service programs at both endpoints must terminate the dialog when they finish their work.





A route identifies an address used by the broker to send messages to a service. It does so in a unique manner. It is used whenever the Service Broker needs to send a message during a conversation. More than one route can be defined for a service while the broker may choose one at random.

Remote Service Bindings

To communicate with remote services the Service Broker should use certificate-based security. A remote service binding objective is to associate a local database user with a remote service name. The messages sent to a remote service are encrypted using the public key of the local user certificate associating this public key to a user in the remote service with permission to store messages in the remote service queue.

Service Instances

The Service Broker uses Service Instances to identify a group of related dialogs. Technically, the Service Instance is represented by a unique identifier data type. When the broker receives a message, it adds the service instance identifier to the message before inserting it into the queue.
Another effect caused by the use of the Service Instance identifier is to provide exclusive access to the messages. An isolated Service Program can at a given time read messages with the same Service Instance Identifier. The Service Instance identifier remains valid until all conversations related to this identifier end. The Service Instance identifier is the primary key in all tables that maintain the state of the applications. This feature simplifies the retrieval of data associated with applications.

Further Remarks

The Service Broker represents a significant beginning to provide a queue-based messaging technology fully integrated to database applications. Its architecture is very interesting in regards to the way it merges concepts such as Service, Contract, etc from the “Service-Oriented Architecture” with others that make up the queue-based messaging technologies. Consequently, the resulting architecture is very powerful and simple. I believe that in the future, the Service Broker might upgrade itself with some other features from the messaging technologies like message-filtering, message header processing, maybe SOAP messaging, message sequencing, among others..

Using the Service Broker

Up to now we have been exploring the Service Broker concepts and architecture. Now we will implement a brief example to show the use of some of the main components of the Service Broker. Suppose there is a system that manages customer information provided by an external entity (another system perhaps). We have designed a very simple Service to receive an XML message with data identifying a customer, to insert that message in a database and to log a message indicating that the action was performed.

The first step can be to define the message structure. An XML Schema can define this. The following code adds the XML Schema to the database.

CREATE XMLSCHEMA '<xs:schema xmlns:xs="" elementFormDefault="qualified" attributeFormDefault="unqualified" targetNamespace="ServiceBroker/Samples/FirstSample/Contact">
      <xs:element name="ContactInfo">
               <xs:documentation>Comment describing your root Element                           
                  <xs:element name="Contact">
                          <xs:element name="email" type="xs:string"/>
<xs:restriction base="xs:string">
                              <xs:pattern value="[0-9\(\)\-]*"/>
                      xs:attribute name="name" type="xs:string"      
                                              <xs:element name="phone">
Now that we have a schema to define the message, we may create a message type and use the previously defined XML Schema for the encoding. 
 WITH 'ServiceBroker/Samples/FirstSample/Contact'

This sample merely includes the sentence meaning and its use but not its full syntax. The “CREATE MESSAGE TYPE” sentence defines a message type object by setting the message_type_name and by providing an ENCODING type to define the way in which all messages that belong to the same message type are encoded. The ENCODING type may be XML, VARBINARY or EMPTY and in the case of XML it can be associated to a Schema definition. If we need to change a message type definition, we may use the “ALTER MESSAGE TYPE” sentence. 

Now that we have defined the message we need to describe a contract to specify what messages the service can send-receive. The following code shows how we developed this part in our example. 


Using the “CREATE CONTRACT” sentence we provide the name of the contract and the message type that this service can send-receive. Contracts are created and persist in each database participating in the conversation. In the “SENT BY” clause we define which conversation endpoint sends the message. The possible arguments are: INITIATOR, TARGET and ANY.

When we define a dialog we are ready to send and receive messages in it. We send a message using the SEND sentence where we set the message type, the message body and the conversation handle returned in the “BEGIN DIALOG” sentence. In the following code we create an XML message and later create a dialog specifying the ContactService as a target. Finally, we send the message to the service.
In our sample we specify that the INITIATOR of the conversation is sending a message of the message type ServiceBroker/Samples/FirstSample/AddContact that we declared above. However, if we want to provide the service with a response message as part of the conversation the code would have to appear like this.

[ServiceBroker/Samples/FirstSample/AddContactContract] [ServiceBroker/Samples/FirstSample/AddContact]

In this case ServiceBroker/Samples/FirstSample/AddContactResponse represents another message type that we had previously declared. 

Now we have both messages and the way to interchange them. The next step would be to define the queues to store the messages that the conversation endpoints might send. The following code creates the queue of our example.

    PROCEDURE_NAME = AddContact,
When we create a queue we need to set a valid SQL Server identifier as its name. Then, using the STATUS clause, we have to specify if the queue will be created in a disabled state or not. Afterwards, we set the Service Program that will be activated when a message is received in the queue. Previously, we had to define a stored procedure to act as a Service Program and to process the message.
In our example, we define two stored procedures: AddContact and ProcessMessage. The AddContact uses the ProcessMessage and acts as a Service Program for the ContactQueue. The code of these stored procedures is provided with the sample code of this paper.  

Another issue to consider would be the number of instances of the Service Program to be activated when processing the messages. When a message arrives to the queue, the Service Program will be activated, and when another message arrives, another instance of the Service Program will be activated. The number of instances that can be active at a time is limited by MAX_QUEUE_READERS. The “EXECUTE AS” clause determines the SQL Server login for the Service Program to run.  

Next, a definition of the Service follows. It represents an endpoint in a Service Broker application and its specific role is defined in the contract. In our sample the service definition may look like this: 

CREATE SERVICE [ServiceBroker/Samples/FirstSample/Services/ContactService]
  ON QUEUE [ContactQueue]([ServiceBroker/Samples/FirstSample/AddContactContract])

As part of the service definition we specified the Service Name, its contract and its associated queue. We may also specify other attributes like the service owner and whether the messages should be kept in the queue until the conversation is over. 
To use the service, all we need to do is to begin a conversation with it. We do this using dialogs. As mentioned above, dialogs represent message exchange conversation patterns that we use for inter-service communication. We create a Dialog using the “BEGIN DIALOG” sentence. As part of the Dialog definition we obtain a dialog identifier that identifies a dialog in a unique way. We indicate the services that act as INITIATOR and as TARGET in the dialog. Besides, we specify the contract exposed by the target services and we define the message sequence in the dialog. We can also set other attributes like the dialog lifetime, the encryption of messages -whether they are encrypted or not- the related dialogs and the related service instances among others. The following code shows the creation of a simple dialog. 

DECLARE @dh uniqueidentifier
  FROM SERVICE [ServiceBroker/Samples/FirstSample/Services/FirstService]
TO SERVICE   'ServiceBroker/Samples/FirstSample/Services/SecondService'
  ON CONTRACT  [ServiceBroker/Samples/FirstSample/SampleContract]

When we define a dialog we are ready to send and receive messages in it. We send a message using the SEND sentence where we set the message type, the message body and the conversation handle returned in the “BEGIN DIALOG” sentence. In the following code we create an XML message and later create a dialog specifying the ContactService as a target. Finally, we send the message to the service.

DECLARE @xdoc xml('ServiceBroker/Samples/FirstSample/Contact')
SET @xdoc = '<ci:ContactInfo xmlns:ci="ServiceBroker/Samples/FirstSample/Contact">
                 <ci:Contact name="">
--convert the message to a binary format
DECLARE @binmsg varbinary(max)
SET @binmsg = cast(nchar(0XFEFF) + cast(@xdoc as nvarchar(max)) as varbinary
--init the conversation
DECLARE @dh uniqueidentifier
  FROM SERVICE [ServiceBroker/Samples/FirstSample/Services/ContactService]
  TO SERVICE   'ServiceBroker/Samples/FirstSample/Services/ContactService'
  ON CONTRACT  [ServiceBroker/Samples/FirstSample/AddContactContract];
--sends a message
MESSAGE TYPE[ServiceBroker/Samples/FirstSample/AddContact]
At the other end of the conversation we must receive the messages. We perform this action using the RECEIVE or SELECT sentence. Both sentences retrieve messages from a queue. The main difference being that on the one hand when we use RECEIVE a message is removed from the queue and on the other hand, with SELECT the message remains in the queue. As part of both sentences we specify the WHERE clause in which we can set some filters to the receiving action. We also accomplish the receiving action with a WAITFOR clause to set the time that the receiving operation needs when waiting for a message to arrive to the queue. It will need this time if the queue is empty or if no message matches the filter in the WHERE clause.

The following code shows the receive part of the Service Program associated with the ContactQueue.

DECLARE @dh uniqueidentifier
     DECLARE @msg varbinary(max)
    -- retrieve the message from the queue
        RECEIVE TOP(1) @dh = conversation_handle, @msg = message_body
        FROM ContactQueue

       TIMEOUT 15000
In this case the Service Program waits 15 seconds for a message and retrieves both the message body and the conversation handle.
By mixing all these features and others that are part of the SQL Server YUKON we may add messaging capabilities to our database applications.


The SQL Server Service Broker technology represents a powerful bridge into the database and messaging-based applications. Using this technology we can develop robust services that perform the messaging aspects of some SQL Server applications. Before the Service Broker we could only incorporate this type of capabilities when using external technologies like MSMQ but now, it is possible to develop messaging applications using native SQL Server technology. Personally, I think that the Service Broker may bring about other potentialities to increase the strength of the applications. However, the fact is that the Service Broker offers a set of benefits never dreamed of before by database application developers.

Posted on Tuesday, October 19, 2004 10:49 PM | Back to top

Comments on this post: SQL SERVICE BROKER

Requesting Gravatar...
Great I like the article...
Left by Sanjay Gupta on Oct 20, 2004 10:06 AM

Requesting Gravatar...
Did you manage to get it to work with multiple instances of sqlserver ?
Left by ALZDBA on Dec 13, 2006 4:54 AM

# Designer lingerie at best price
Requesting Gravatar...
Valentines Day is just a few weeks away, so what better time to give some sexy
lingerie to your special person. 

At the La Peches Lingerie online lingerie shop,

you can buy sexy lingerie without embarrassment.

Designer Lingerie, French lingerie and Italian Lingerie from La Peches.
Left by SensuousAna on Jan 30, 2009 10:10 PM

# Alte Kriegsrelikte gefunden
Requesting Gravatar...

Yarid A. aus Kalifornien hat diese vier mexikanischen Real-Silbermünzen von 1849 mit seinem Garrett ACE 250 und seinem 23 x 30,5 cm-ACE-Ring gefunden.<img></img> garret garet schatzsuche
Left by KaleescalkSag on Nov 17, 2009 4:14 AM

Requesting Gravatar...
wholesale Lingerie. luxury and designer lingerie [url=]wholesale Lingerie[/url] online lingerie shop .including bra sets, bridal nightwear.
Left by Magiceve on Nov 24, 2010 3:40 AM

Requesting Gravatar...
brilliant....nice lead!! . Ravida luxury and designer
lingerie wholesale Lingerie online lingerie shop .including bra sets, bridal nightwear.
Left by Magiceve on Nov 24, 2010 3:41 AM

Requesting Gravatar...
I get the error message -
The broker is disabled in the sender's database.
Left by Shiva Naru on Jun 09, 2011 5:59 PM

Your comment:
 (will show your gravatar)

Copyright © Veer Ji Wangoo | Powered by: