Writing SQL Server Service Broker Applications Across Databases

Introduction

SQL Server Service Broker (SSBS) is a new architecture (introduced with SQL Server 2005 and enhanced further in SQL Server 2008 and SQL Server 2008 R2), which allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message based applications within the database itself. This integration of queuing and message based infrastructure into the database provides several advantages from other message/queuing based applications outside of the database. SSBS provides:

• simplified administration
• inbuilt security
• improved performance (as it writes the messages directly to target queue if it is in same database or in a different database on the same SQL Server instance)
• transaction support without making it distributed
• simplified recovery process as queued data are backed up as part of the normal database back up
• parallel processing
• etc.

In my previous couple of articles, I introduced you SQL Server Service Broker, what it is and how it works, what its different components are and how they are related to each other. Then I talked about writing an SSBS application when both Initiator (Sender) and Target (Receiver) are in same database. Now let’s move on and see how to write SSBS applications when Initiator and Target are in two different databases on the same SQL Server instance.

Problem Statement

In this example I will take the same problem statement as my last article but this time Initiator and Target will be in different databases on the same SQL Server instance.

There are two applications, one is Order application (uses SSBSInitiatorDB database) and another one is Inventory application (uses SSBSTargetDB database). Before accepting any order from the users, the Order application needs to make sure that the product that is being ordered is available in the store but for that, the Order application does not want to wait. The Order application will request to check the product stock availability status asynchronously and will continue doing other work. On the other side, the Inventory application will listen to the request coming into the queue, process it and respond back to the Order application, again asynchronously.

When Initiator and Target are in different databases on the same SQL Server instance

As I said before, depending on the arrangement of Initiator and Target, the architecture can be categorized into three categories:

The behavior in scenario A and B above is almost the same. In these cases, SSBS optimizes performance by writing messages directly to the Target Queue. If, while writing message to the Target Queue, it encounters any problem (for example the Target service is not available, Target Queue is disabled, etc.) it keeps that message in the sys.transmission_queue table temporarily so that it can push the message to Target Queue once it is available. To resolve some of the exceptions when Initiator and Target are in different databases on the same SQL Server instance, see the end of this article.

In this example, I will demonstrate how you will be creating an SSBS application when Initiator and Target are in different databases on the same SQL Server instance.

Although we can enable Service Broker for an existing database and create SSBS objects in it, for simplicity I will be creating new databases for this demonstration.

What I am doing is creating a database, which will act like an Initiator. Then I am enabling Service Broker on it. Next I am setting the TRUSTWORTHY property of the database to ON to ensure it can access resources outside database and then I am creating two message types and a contract. Finally, I am creating an Initiator queue that will hold the responses (messages) sent back by the Target to the Initiator and Initiator service, which will be tied to this queue and will act like an initiator endpoint.

Setting up Initiator

USE master;
GO
--Create Initiator database for this learning session, it will help you to do 
--clean up easily, you can create SSBS objects in any existing database
--also but you need to drop all objects individually if you want to do
--clean up of these objects than dropping a single database
IF EXISTS(SELECT TOP 1 1 FROM sys.databases WHERE name = 'SSBSInitiatorDB')
       DROP DATABASE SSBSInitiatorDB
GO
CREATE DATABASE SSBSInitiatorDB
GO
 
--By default a database will have service broker enabled, which you can verify 
--with is_broker_enabled column of the below resultset
SELECT name, service_broker_guid, is_broker_enabled, is_honor_broker_priority_on 
FROM sys.databases WHERE name = 'SSBSInitiatorDB'
 
--If your database is not enabled for Service Broker because you have 
--changed the default setting in Model database, even then you can enable
--service broker for a database with this statement
ALTER DATABASE SSBSInitiatorDB
      SET ENABLE_BROKER;
      --WITH ROLLBACK IMMEDIATE
GO
----To disable service broker for a database
--ALTER DATABASE SSBSInitiatorDB
--      SET DISABLE_BROKER;
--GO
--You need to mark database TRUSTWORTHY so that it can 
--resource beyond the scope of this database
ALTER DATABASE SSBSInitiatorDB SET TRUSTWORTHY ON
GO
 
USE SSBSInitiatorDB;
GO
--Create message types which will allow valid xml messages to be sent
--and received, SSBS validates whether a message is well formed XML 
--or not by loading it into XML parser
--Please use XML validation only when required as it has performance overhead 
CREATE MESSAGE TYPE
       [//SSBSLearning/ProductStockStatusCheckRequest]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//SSBSLearning/ProductStockStatusCheckResponse]
       VALIDATION = WELL_FORMED_XML;
GO
 
--Create a contract which will be used by Service to validate
--what message types are allowed for Initiator and for Target.
--As because communication starts from Initiator hence 
--SENT BY INITIATOR or SENT BY ANY is mandatory
CREATE CONTRACT [//SSBSLearning/ProductStockStatusCheckContract]
      ([//SSBSLearning/ProductStockStatusCheckRequest]
       SENT BY INITIATOR,
       [//SSBSLearning/ProductStockStatusCheckResponse]
       SENT BY TARGET
      );
GO
--A Target can also send messages back to Initiator and hence
--you can create a queue for Initiator also
CREATE QUEUE dbo.SSBSLearningInitiatorQueue WITH STATUS = ON;
GO
 
--Likewsie you would need to create a service which will sit 
--on top of Initiator queue and used by Target to send messages
--back to Initiator
CREATE SERVICE
       [//SSBSLearning/ProductStockStatusCheck/InitiatorService]
       ON QUEUE dbo.SSBSLearningInitiatorQueue;
GO

What I am doing next is creating a database which will act like a Target. Then I am enabling Service Broker on it. Next I am setting the TRUSTWORTHY property of the database to ON to ensure it can access resources outside the database boundary and then I am creating two message types and a contract. Finally I am creating the Target queue, which will hold the request (messages) sent by the Initiator to the Target and Target service, which will be tied to this queue and will act like a target endpoint.

Please note, as because this message types and contract will be shared by both Initiator and Target, they will have same definitions at both places.

Setting up Target

USE master;
GO
--Create target database for this learning session, it will help you to do 
--clean up easily, you can create SSBS objects in any existing database
--also but you need to drop all objects individually if you want to do
--clean up of these objects than dropping a single database
IF EXISTS(SELECT TOP 1 1 FROM sys.databases WHERE name = 'SSBSTargetDB')
       DROP DATABASE SSBSTargetDB
GO
CREATE DATABASE SSBSTargetDB
GO
 
--By default a database will have service broker enabled, which you can verify 
--with is_broker_enabled column of the below resultset
SELECT name, service_broker_guid, is_broker_enabled, is_honor_broker_priority_on 
FROM sys.databases WHERE name = 'SSBSTargetDB'
 
--If your database is not enabled for Service Broker because you have 
--changed the default setting in Model database, even then you can enable
--service broker for a database with this statement
ALTER DATABASE SSBSTargetDB
      SET ENABLE_BROKER;
      --WITH ROLLBACK IMMEDIATE
GO
----To disable service broker for a database
--ALTER DATABASE SSBSTargetDB
--      SET DISABLE_BROKER;
--GO
--You need to mark database TRUSTWORTHY so that it can 
--resource beyond the scope of this database
ALTER DATABASE SSBSTargetDB SET TRUSTWORTHY ON
GO
USE SSBSTargetDB;
GO
--Create message types which will allow valid xml messages to be sent
--and received, SSBS validates whether a message is well formed XML 
--or not by loading it into XML parser 
CREATE MESSAGE TYPE
       [//SSBSLearning/ProductStockStatusCheckRequest]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//SSBSLearning/ProductStockStatusCheckResponse]
       VALIDATION = WELL_FORMED_XML;
GO
 
--Create a contract which will be used by Service to validate
--what message types are allowed for Initiator and for Target.
--As because communication starts from Initiator hence 
--SENT BY INITIATOR or SENT BY ANY is mandatory
CREATE CONTRACT [//SSBSLearning/ProductStockStatusCheckContract]
      ([//SSBSLearning/ProductStockStatusCheckRequest]
       SENT BY INITIATOR,
       [//SSBSLearning/ProductStockStatusCheckResponse]
       SENT BY TARGET
      );
GO
--Create a queue which is an internal physical table to hold 
--the messages passed to the service, by default it will be 
--created in default file group, if you want to create it in 
--another file group you need to specify the ON clause with 
--this statement. You can use SELECT statement to query this 
--queue or special table but you can not use other DML statement
--like INSERT, UPDATE and DELETE. You need to use SEND and RECEIVE
--commands to send messages to queue and receive from it
CREATE QUEUE dbo.SSBSLearningTargetQueue WITH STATUS = ON;
GO
 
--Create a service, which is a logical endpoint which sits on top 
--of a queue on which either message is sent or received. With 
--Service creation you all specify the contract which will be
--used to validate message sent on that service
CREATE SERVICE
       [//SSBSLearning/ProductStockStatusCheck/TargetService]
       ON QUEUE dbo.SSBSLearningTargetQueue
       ([//SSBSLearning/ProductStockStatusCheckContract]);
GO

Once the environments are setup as above, you are all set to send messages back and forth between Initiator and Target; this is what I am doing next. In the Initiator database I am creating a dialog (conversation) handle with the BEGIN DIALOG command by specifying Initiator and Target service names and contract to be used by them during communication and finally using the SEND command to send message to target service. If for some reason, the messages are not being sent to target queue, it’s written into local sys.transmission_queue (Transmision Status column shows the reason of failure, see the end of this article to resolve a few of the common exception scenarios) until it gets delivered to the Target queue.

Communication – Sending message from Initiator

USE SSBSInitiatorDB;
GO
--To send message, first you need to initiate a dialog with 
--BEGIN DIALOG command and specify the Initiator and Target
--services which will be talking in this dialog conversation
DECLARE @SSBSInitiatorDialogHandle UNIQUEIDENTIFIER;
DECLARE @RequestMessage XML;
BEGIN TRANSACTION;
       BEGIN DIALOG @SSBSInitiatorDialogHandle
               FROM SERVICE
                [//SSBSLearning/ProductStockStatusCheck/InitiatorService]
               TO SERVICE
                N'//SSBSLearning/ProductStockStatusCheck/TargetService'
               ON CONTRACT
                [//SSBSLearning/ProductStockStatusCheckContract]
               WITH ENCRYPTION = OFF;
       SELECT @RequestMessage =
                 N'<Request>
                           <ProductID>316</ProductID>
                           <LocationID>10</LocationID>
                     </Request>';
       --To send message you use SEND command and specify the dialog
       --handle which you got above after initiating a dialog        
       SEND ON CONVERSATION @SSBSInitiatorDialogHandle
               MESSAGE TYPE 
               [//SSBSLearning/ProductStockStatusCheckRequest]
               (@RequestMessage);
       SELECT @RequestMessage AS RequestMessageSent;
COMMIT TRANSACTION;
GO
--If in case message cannot be put into Target queue because
--Target queue is not enabled or because of any other reasons
--it will be temporarily put into transmission queue until 
--its delivery to Target queue
SELECT * FROM sys.transmission_queue
GO

Okay so now we have sent the message to target service and assuming there was no problem in communication and message has been written successfully to target queue, we need to retrieve the message (using the RECEIVE command) from the queue and process them and finally send the response back to Initiator. The response will be written to the Initiator queue assuming there is no problem in communication or else it will reside in sys.transmission_queue until it is delivered to Initiator queue. At the end, the Target is ending the conversation initiated by the Initiator using the END CONVERSATION command as shown below.

Communication – Receiving message by Target and sending reply back

USE SSBSTargetDB;
GO
--You can query the Target queue with SELECT statement
SELECT * FROM dbo.SSBSLearningTargetQueue;
GO
--To retrieve a message from a queue you use RECEIVE command,
--With every message you also get dialog handle which you can 
--use to reply back to sender of the message
DECLARE @SSBSTargetDialogHandle UNIQUEIDENTIFIER;
DECLARE @RecvdRequestMessage XML;
DECLARE @RecvdRequestMessageTypeName sysname;
BEGIN TRANSACTION;
       --WAITFOR command is used to wait for messages to arrive
       --on the queue, TIMEOUT is specified in miliseconds
       WAITFOR
       ( RECEIVE TOP(1)
              @SSBSTargetDialogHandle = conversation_handle,
              @RecvdRequestMessage = CONVERT(XML, message_body),
              @RecvdRequestMessageTypeName = message_type_name
         FROM dbo.SSBSLearningTargetQueue
       ), TIMEOUT 1000;
 
       SELECT @RecvdRequestMessage AS RequestMessageReceived;
       --If the message type is request from Initiator, process the request.
       IF @RecvdRequestMessageTypeName = N'//SSBSLearning/ProductStockStatusCheckRequest'
       BEGIN
               DECLARE @ReplyMessage NVARCHAR(max);
               DECLARE @Quantity smallint
               SELECT @Quantity = Quantity 
                     FROM AdventureWorks.Production.ProductInventory
                     WHERE ProductID = @RecvdRequestMessage.value('(/Request/ProductID)[1]', 'int')  
                     AND LocationID = @RecvdRequestMessage.value('(/Request/LocationID)[1]', 'int') 
               SELECT @ReplyMessage = 
               N'<Reply>
                     <Quantity>' + CONVERT(VARCHAR(10), @Quantity) + '</Quantity>
               </Reply>';
               --To send message back to sender you again use SEND command and specify the dialog
               --handle which you got above while retrieving the message from the queue
               SEND ON CONVERSATION @SSBSTargetDialogHandle
                       MESSAGE TYPE 
                       [//SSBSLearning/ProductStockStatusCheckResponse]
                       (@ReplyMessage);
               --To end a dialog you use END CONVERSATION command, here the dialog
               --is being ended by Target, and then a message of 
               -- http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog message type 
               --will be sent to Initiator
               END CONVERSATION @SSBSTargetDialogHandle;
       END
       SELECT @ReplyMessage AS ReplyMessageSent;
COMMIT TRANSACTION;
GO
--Again if there is any error during transmission for messages from Target to 
--Initiator, the messages will be temporarily put into transmission queue until 
--it's delivery to Initiator queue
SELECT * FROM sys.transmission_queue
GO

Now on the Initiator we again use the RECEIVE command to retrieve the message sent by the Target. For a conversation to be successfully ended, it needs to be ended by Initiator also and this is what you can see I am doing with END CONVERSATION command.

Communication – Receiving reply back by Initiator

USE SSBSInitiatorDB
GO
--You can query the Initiator queue with SELECT statement
--Here you will see two records, one is the response and another
--one is the end dialog intimation with NULL in its message_body column
SELECT * FROM dbo.SSBSLearningInitiatorQueue
GO
--At this point the Initiator queue will hold two records, first
--one is a response message for the request and another one is for 
--intimation that dialog has been ended by the Target.
--You need to execute below piece of code twice to retrive both the 
--records from Initiator queue, if the message is of type 
-- http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog the dialog will
--be ended by Intiator also or else response message will be processed. 
DECLARE @RecvSSBSInitiatorDialogHandle UNIQUEIDENTIFIER;
DECLARE @RecvReplyMessage NVARCHAR(100);
DECLARE @RecvMessageType SYSNAME
BEGIN TRANSACTION;
       WAITFOR
       ( RECEIVE TOP(1)
              @RecvSSBSInitiatorDialogHandle = conversation_handle,
              @RecvReplyMessage = message_body,
              @RecvMessageType = message_type_name
         FROM dbo.SSBSLearningInitiatorQueue
       ), TIMEOUT 1000;
       --If the message is of type http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog 
       --the dialog will be ended by Intiator also or else response message will be processed.
       IF (@RecvMessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
              END CONVERSATION @RecvSSBSInitiatorDialogHandle;
       ELSE
              SELECT @RecvReplyMessage AS ReplyMessageRecived;
COMMIT TRANSACTION;
GO

To clean up your Initiator environment, you can directly drop the database that we created or drop the objects individually as shown below:

Cleaning up –Initiator

USE SSBSInitiatorDB;
GO
--Cleanup code to drop SSBS objects individually which 
--you created above, notice the order of dropping the objects,
--its reverse of the order in which you created the objects
DROP SERVICE [//SSBSLearning/ProductStockStatusCheck/InitiatorService]
DROP QUEUE dbo.SSBSLearningInitiatorQueue
DROP CONTRACT [//SSBSLearning/ProductStockStatusCheckContract]
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckRequest]
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckResponse]
GO
--Drop the Initiator database which you created above
USE master
GO
IF EXISTS(SELECT TOP 1 1 FROM sys.databases WHERE name = 'SSBSInitiatorDB')
       DROP DATABASE SSBSInitiatorDB
GO

Likewise to clean up your Target environment, you can directly drop the database that we created or drop the objects individually as shown below:

Cleaning up –Target

USE SSBSTargetDB;
GO
--Cleanup code to drop SSBS objects individually which 
--you created above, notice the order of dropping the objects,
--its reverse of the order in which you created the objects
DROP SERVICE [//SSBSLearning/ProductStockStatusCheck/TargetService]
DROP QUEUE dbo.SSBSLearningTargetQueue
DROP CONTRACT [//SSBSLearning/ProductStockStatusCheckContract]
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckRequest]
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckResponse]
GO
--Drop the database which you created above
USE master
GO
IF EXISTS(SELECT TOP 1 1 FROM sys.databases WHERE name = 'SSBSTargetDB')
       DROP DATABASE SSBSTargetDB
GO

Note: Needless to say, you must learn and do thorough testing of your SSBS application (or the scripts demonstrated above) on your development box first, before going to your production environment.

When SSBS, for some reason, cannot write the message to the target queue, it stores that message to sys.transmission_queue locally until it is delivered to target. Some of the exceptions and their resolutions are provided below (when you have Initiator and Target in two different databases):

Conclusion

In this second article of this series of SSBS application writing, I demonstrated, step-by-step, how to write your SSBS application when both the Initiator and Target are in different databases on the same SQL Server instance. In the next article, I will demonstrate how to write an SSBS application when the Initiator and Target are in different databases in different SQL Server instances altogether (now with the complexity of network coming on the way), so stay tuned for even more fun coming ahead. J

Resources

MSDN SQL Server Service Broker
MSDN Creating Service Broker Objects
MSDN Creating Service Broker Message Types
MSDN CREATE QUEUE (Transact-SQL)
MSDN Contracts

» See All Articles by Columnist Arshad Ali