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.
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:
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:
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
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.
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)', 'int') AND LocationID = @RecvdRequestMessage.value('(/Request/LocationID)', '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
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.