Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 1, 2010

Six Key SQL Server Service Broker Components and How They Relate

By Arshad Ali

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date