How to Communicate Between SSBS Applications Across Instances

Arshad Ali demonstrates how to verify the SQL Server Service Broker (SSBS) configuration when both the Initiator and Target are in different SQL Server instances, how to communicate between them and how to monitor the conversation.

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.

In Writing SSBS Applications Across Instances – Getting
Environments Ready
I demonstrated how to set
up the Initiator and Target and how to create routes between them. In this
article I will demonstrate how to verify the configuration when both the
Initiator and Target are in different SQL Server instances. We will also
examine how to communicate between them and how to monitor the conversation
status between them.

Verifying environment configuration

SSBS
provides a utility called ssbdiagnose, which is used to troubleshoot a problem
in conversation between two services or a configuration problem in either one
or the other service, which you can verify in a newly configured SSBS
application or after any changes in the configuration of an existing SSBS
application. To learn more about this command see the MSDN doc, ssbdiagnose
Utility
.

The command given below provides
details about the configuration of the two services that
we created in earlier article of this series.

ssbdiagnose -E CONFIGURATION FROM SERVICE 
//SSBSLearning/ProductStockStatusCheck/InitiatorService -S ARSHAD-LAPPY -d 
SSBSInitiatorDB TO SERVICE 
//SSBSLearning/ProductStockStatusCheck/TargetService -S MKTARALIW2K8R2 -d 
SSBSTargetDB ON CONTRACT //SSBSLearning/ProductStockStatusCheckContract

Since
I have not created the SSBS objects for the Initiator and Target, it gives
following error message:

Verifying environment configuration

Figure 1 – Verifying environment configuration 1

Now
when I have created all of the SSBS objects for the Initiator and Target and
have done the entire required configuration, it gives following message:

Verifying environment configuration

Figure 2 – Verifying environment configuration 2

The next command monitors the status of the conversation
for our Initiator and Target and reports any errors. To learn more about this
command see
ssbdiagnose Utility
.

ssbdiagnose RUNTIME -ID F31BFCAA-F110-E011-BF3C-70F395CE1DFE -TIMEOUT 10 
CONNECT TO -E -S ARSHAD-LAPPY -d SSBSInitiatorDB CONNECT TO -E -S 
MKTARALIW2K8R2 -d SSBSTargetDB 

Monitoring status of the communication

Figure 3 – Monitoring status of the communication

Communication between Initiator and Target

Once
the environments are setup, you are all set to send messages back and forth
between the Initiator and Target, which we will do next. In the Initiator
database, create a dialog (conversation) handle with the BEGIN DIALOG command
by specifying the Initiator and Target service names and contract to be used by
them during communication. Finally, use the SEND command to send a message to
target service. Notice, this time I am using encryption (WITH ENCRYPTION = ON)
while sending messages as a network is involved in this communication.

When
an Initiator issues the SEND command to send a message, that message is stored
in the sys.transmission_queue queue (Transmission Status column shows the
reason of failure if there is any). Then SSBS sends that message over the
network to the Target and at same time, it marks the status of this message as
waiting for acknowledgement from the Target. On the Target, when the message is
received successfully in the Target queue, it sends the acknowledgement back to
the Initiator. At this point only, the message is deleted from the sys.transmission_queue
queue at the Initiator. If the Target issues a SEND command to send a response
message back to the sender, the response message goes into the sys.transmission_queue
queue at the Target; again SSBS sends that response message over the network to
the Initiator and at same time, it marks the status of this response message as
waiting for acknowledgement from the Initiator. On the Initiator, when the
response message is received successfully in the Initiator queue, it sends the
acknowledgement back to the Target. At this point only, the message is deleted
from the sys.transmission_queue queue at the Target.

Communication
across instances – 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 = ON;
       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 becuase
--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

Now that we
have sent the message to the target service running on a remote SQL instance
and if there was no problem in communication,  the message has been
written successfully to the target queue. Next we need to retrieve the message
(using the RECEIVE command) from the queue and process it and finally send the
response back to Initiator

Now
the response will be written to the Initiator queue, assuming there is no
problem in communication or else it will reside in the sys.transmission_queue
until it is delivered to the Initiator queue. At the end, the Target ends the
conversation initiated by the Initiator using the END CONVERSATION command as
shown below.

Communication across
instances –
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 5000;
 
       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 
--its delivery to Initiator queue
SELECT * FROM sys.transmission_queue
GO

Now on the Initiator, we again use the RECEIVE command
to retrieve the response message sent by the Target. For
a conversation to be successfully ended, it needs to be ended by the Initiator
also. The END CONVERSATION command is below.

Communication
across instances – 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 5000;
       --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 master;
GO
IF EXISTS (SELECT * FROM sys.endpoints WHERE name = N'SSBSInitiatorEP')
     DROP ENDPOINT SSBSInitiatorEP;
GO
USE msdb
GO
IF EXISTS (SELECT * FROM sys.routes WHERE name = N'SSBSInitiatorRoute')
     DROP ROUTE SSBSInitiatorRoute;
GO
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 ROUTE SSBSTargetRoute;
DROP REMOTE SERVICE BINDING SSBSRemoteServiceBindingForTarget;
DROP CERTIFICATE SSBSTargetCert;
DROP USER SSBSTargetUser;
GO
DROP SERVICE [//SSBSLearning/ProductStockStatusCheck/InitiatorService];
DROP QUEUE dbo.SSBSLearningInitiatorQueue;
DROP CONTRACT [//SSBSLearning/ProductStockStatusCheckContract];
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckRequest];
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckResponse];
DROP CERTIFICATE SSBSInitiatorCert;
DROP MASTER KEY;
DROP USER SSBSInitiatorUser;
GO
--Drop the Initiator database which you created above
USE master
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 master;
GO
IF EXISTS (SELECT * FROM sys.endpoints WHERE name = N'SSBSTargetEP')
     DROP ENDPOINT SSBSTargetEP;
GO
USE msdb
GO
IF EXISTS (SELECT * FROM sys.routes WHERE name = N'SSBSTargetRoute')
     DROP ROUTE SSBSTargetRoute;
GO
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 ROUTE SSBSInitiatorRoute;
DROP REMOTE SERVICE BINDING SSBSRemoteServiceBindingForInitiator;
DROP CERTIFICATE SSBSInitiatorCert;
DROP USER SSBSInitiatorUser;
GO
DROP SERVICE [//SSBSLearning/ProductStockStatusCheck/TargetService]
DROP QUEUE dbo.SSBSLearningTargetQueue
DROP CONTRACT [//SSBSLearning/ProductStockStatusCheckContract]
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckRequest]
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckResponse]
DROP CERTIFICATE SSBSTargetCert;
DROP MASTER KEY;
DROP USER SSBSTargetUser;
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

Conclusion

In
the first article of this series of writing SQL Server Service Broker (SSBS)
Applications across Instances I stepped through how to set up the Initiator and
Target and how to create routes between them. In this article I demonstrated
how to verify the configuration when both Initiator and Target are in different
SQL Server instances, how to communicate between them and how to monitor the
conversation status between them. In my next article I will discuss internal
activation in detail.

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

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