Six Key SQL Server Service Broker Components and How They Relate

Arshad Ali discusses the SQL Server Service Broker Components (SSBS), Conversation, Conversation group, SEND and RECEIVE commands, Route and how these components relate to each other.

Introduction

In my last article (SQL Server Service Broker Components –
Message Types, Contracts and Queue)
,
I discussed the Initiator, Target, Message Types, Contract and Queue components
of SSBS. In this article I will be talking about the Service, Conversation,
Conversation group, SEND and RECEIVE commands, Route and how these components
are related to each other.

Service Broker – Components – Cont’d

Services

A service represents an endpoint
for message sending/receiving and is associated with a queue. It enforces the
defined contract for the conversation and plays a role for routing and
delivering the message to the target queue. On target, the Target service picks
up the messages from the target queue and processes them. The general syntax to
create a service is given below:

CREATE SERVICE <service_name>
   [ AUTHORIZATION <owner_name> ]
   ON QUEUE <queue_name>
   [ ( <contract_name> | [DEFAULT] [ ,...n ] ) ]

<service_name> is the name of the service that you
want to create. <owner_name> owns
the service and if not specified will be owned by the current user. <queue_name> is the name of the queue with which
you want this service to be associated and it must exist in the same database
as of the service. Then you specify one more <contract_name>, which this service will be using to validate the
messages. [DEFAULT]
is the name of default contract, which exists in every database and which has
DEFAULT message type whose validation is set to NONE. If you omit the
specification of contract then service might only initiate a conversation.

Example – The statement below will
create a target service called [//SSBSLearning/ProductStockStatusCheck/TargetService]which
is associated with dbo.SSBSLearningTargetQueue
and uses the ([//SSBSLearning/ProductStockStatusCheckContract] contract.

CREATE SERVICE
       [//SSBSLearning/ProductStockStatusCheck/TargetService]
       ON QUEUE dbo.SSBSLearningTargetQueue
       ([//SSBSLearning/ProductStockStatusCheckContract]);
GO

Conversation and Conversation Groups

A conversation is the exchange of messages between two endpoints; the service
that initiates the conversation is called the Initiator and the service, which
receives the conversation request, is called the Target. A conversation group
is an ordered set of related conversations. Every message of the conversation
is assigned a sequence number and SSBS ensures the messages are processed in order.
Starting with SQL Server 2008, you can define the conversation group priority; it
means a high priority conversation group will be processed before a low
priority conversation group but conversations inside a conversation group are
still processed in order. SSBS uses a new type of lock i.e. ‘conversation group
lock’ to ensure that only one activation procedure processes the conversations
of a conversation group in order.

DECLARE @dialog_handle AS UNIQUEIDENTIFIER
BEGIN DIALOG [ CONVERSATION ] @dialog_handle
   FROM SERVICE <initiator_service_name>
   TO SERVICE '<target_service_name>'
       [ , { 'service_broker_guid' | 'CURRENT DATABASE' } ]
   [ ON CONTRACT <contract_name> ]
   [ WITH
   [  { RELATED_CONVERSATION = <related_conversation_handle>
      | RELATED_CONVERSATION_GROUP = <related_conversation_group_id> } ]
   [ [ , ] LIFETIME = <dialog_lifetime> ]
   [ [ , ] ENCRYPTION = { ON | OFF }  ] ]

When
you start a conversation with the BEGIN DIALOG command, it returns a dialog
handle of the type uniqueidentifier, which you place into a variable to later
use while sending messages. FROM SERVICE and TO SERVICE clauses are used to
specify the initiator and target service name respectively. Please note that
the name of the target service is specified as a string literal. Then with the
ON CONTRACT clause, you specify the contract, which will be used to validate
the messages being sent in this conversation. RELATED_CONVERSATION and
RELATED_CONVERSATION_GROUP are somewhat the same and used to combine related
conversations together to form a conversation group. The LIFETIME clause allows
you to specify the maximum number of seconds a conversation can exist. For your
conversation to be successfully completed it must be ended by both end-points
within the specified number of seconds or else an error is returned and
rollback is done. If you are sending messages across SQL Server instances, you
can set ENCRYPTION = ON to encrypt the messages. Please note encryption does
not happen inside a SQL Server instance. I will talk more about it in later
article on SSBS security.

Example – The statement below will
start a dialog conversation between SSBSLearning/ProductStockStatusCheck/InitiatorService
and SSBSLearning/ProductStockStatusCheck/TargetService
services and will validate the messages of this conversation with SSBSLearning/ProductStockStatusCheckContract
contract. Please note the dialog conversation handle is being returned in @SSBSInitiatorDialogHandle variable, which
is of type uniqueidentifier.

DECLARE @SSBSInitiatorDialogHandle UNIQUEIDENTIFIER;
BEGIN DIALOG @SSBSInitiatorDialogHandle
       FROM SERVICE
        [//SSBSLearning/ProductStockStatusCheck/InitiatorService]
       TO SERVICE
        N'//SSBSLearning/ProductStockStatusCheck/TargetService'
       ON CONTRACT
        [//SSBSLearning/ProductStockStatusCheckContract]
 WITH ENCRYPTION = OFF;

SEND and RECEIVE

SEND
and RECEIVE are two new T-SQL commands for putting messages into the queue and
retrieving them from the queue. The SEND command is used to send and transfer
the messages to the target queue. To send a message you need a dialog
conversation handle, which you created in the above example with the BEGIN
DIALOG command, to send messages in that particular conversation. Additional information
about the SEND command can be found here.

Example
– The statement below will send a message on the provided conversation handle.

DECLARE @RequestMessage XML;  
SELECT @RequestMessage =
         N'<Request>
                  <ProductID>316</ProductID>
                  <LocationID>10</LocationID>
            </Request>';
SEND ON CONVERSATION @SSBSInitiatorDialogHandle
       MESSAGE TYPE 
       [//SSBSLearning/ProductStockStatusCheckRequest]
(@RequestMessage);

The
RECEIVE command reads messages from a queue and returns a result set for
processing. You can use the where clause to filter out the result set but only
on the basis of conversation_handle or conversation_group_id. The WAITFOR
command has been extended to be used with the RECEIVE command and to have a
TIMEOUT clause, which specifies to wait for a message to arrive in the queue
for the given period of time in milliseconds if the result set is not
available. Once you are done with the received message you would usually end
the conversation with the END CONVERSATION command. Additional information
about RECEIVE command can be found here.

Example
– The statement below will retrieve a message for the dbo.SSBSLearningTargetQueue queue. If the result set is not
already available then it will wait for 1000 milliseconds.

WAITFOR
( RECEIVE TOP(1)
    @SSBSTargetDialogHandle = conversation_handle,
    @RecvdRequestMessage = CONVERT(XML, message_body),
    @RecvdRequestMessageTypeName = message_type_name
  FROM dbo.SSBSLearningTargetQueue
), TIMEOUT 1000;

When
you send a message into a database on the same SQL Server instance, the message
is directly put into the target queue whereas if the database is on another SQL
Server instance then the message is first kept in the transmission queue
(sys.transmission_queue) until it is transmitted to the target queue. However, there
might be some situations when the message is put into the transmission queue
even if the target queue is in the database on the same SQL Server. This
happens if the message cannot be put into the target queue because the target
queue is in a disabled state or because of any other errors; in that case the
message is stored in the transmission queue until its delivery.

Routes

As long as you have an Initiator
and Target, either in same database or in different databases on the same
instance, you would not be creating a route as there is a default “AutoCreatedLocal”
route in each database, which matches the service name and ensure messages are
delivered within the SQL instance.

A
route is created explicitly if the Initiator and Target are on different SQL
Server instances and lets SSBS know where to deliver the messages. In other
words, a route is a mapping or a means to locate the target service while
sending messages and to locate the initiating service while sending the
response back. You create a route using the CREATE ROUTE command and bind it
with the Service; along with that you also specify the ServerName:PortNo with
the Address clause of this command. I will be talking about thiks in detail
when I will demonstrate writing SSBS applications across SQL Server instances.

So far, I talked about the
different components of SSBS individually. This diagram depicts how these
components are related to each other and how they interact with each other. A
service is bonded with a queue, a queue can have messages conforming to one or
more contracts, and likewise a contract can have one or more message types
within it.

how the SSBS components are related to each other

Conclusion

In my last article (SQL Server Service Broker Components –
Message Types, Contracts and Queue)
,
I discussed a few of the components of SSBS; for example Initiator, Target,
Message Types, Contract and Queue. This article has covered the remaining SSBS
components; for example Service, Conversation, Conversation group, SEND and
RECEIVE commands, Route and how these components are related to each other. In
the next couple of articles, I will be helping you to write SSBS applications.

Additional Resources

Getting Started with SQL Server Service Broker
MSDN SQL Server Service Broker
MSDN Creating Service Broker Objects
MSDN Creating Service Broker Message Types
MSDN CREATE QUEUE (Transact-SQL)
MSDN Contracts
MSDN: Microsoft Message Queuing

»


See All Articles by Columnist

Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles