Working with SQL Server Service Broker (SSBS) When the Initiator and Target are on the Same Database

Introduction

In my previous article, I introduced you SQL Server Service Broker, what it is, how it works, what its different components are and how they are related to each other. Now it's time to roll up our sleeves and write some SSBS applications. In this article, I will be creating an application in which Initiator and Target both are in the same database. In the next couple of articles, I will be helping you to write SSBS applications if the Initiator and Target are not in the same database.

Problem Statement

There are two applications, one is Order application and the other one is Inventory application. Before accepting any order from the users, the Order application needs to make sure that the product, which 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 status asynchronously and will continue doing its 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.

All in one database

Depending on the arrangement of Initiator and Target, the architecture can be grouped into three categories:

                •Initiator and Target in same database

                •Initiator in one database and Target in another database on same instance

              •Initiator and Target in separate instances

In this example, I will demonstrate how to create an SSBS application if both Initiator and Target are in same database.

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

USE master;
GO
--Create a 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 COUNT(1) FROM sys.databases WHERE name = 'SSBSLearning')
 DROP DATABASE SSBSLearning
GO
CREATE DATABASE SSBSLearning
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 = 'SSBSLearning'
 
--If your database is not enabled for Service Broker becuase you have 
--changed the default setting in Model database, even then you can enable
--service broker for a database with this statement
ALTER DATABASE SSBSLearning
      SET ENABLE_BROKER;
      --WITH ROLLBACK IMMEDIATE
GO
----To disable service broker for a database
--ALTER DATABASE SSBSLearning
--      SET DISABLE_BROKER;
--GO

Once you have created a database or enabled the Service Broker for an existing database, you need to create the service broker objects; first Message Types then Contracts, which will use the created message types. Then you need to create Queues and finally you would be required to create Services, which are nothing but endpoints that sit on top of queues to send and receive messages.

USE SSBSLearning;
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;
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
 
--A Target can also send messages back to Initiator and hence
--you can create a queue for Initiator also
CREATE QUEUE dbo.SSBSLearningInitiatorQueue;
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

Once you are done creating all of the required SSBS objects for both Initiator (Order application) and Target (Inventory application), you will be sending messages between Initiator and Target. A dialog or conversation is always started by Initiator and therefore Initiator will be sending the request message first (to check the inventory stock for product as mentioned in problem statement) to the Target something
like this.

--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&g';
 --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

The request message sent using the above statements is stored in the Target queue until it is processed. You can query the Target queue using a SELECT statement.

--You can query the Target queue with SELECT statement
SELECT * FROM dbo.SSBSLearningTargetQueue;
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

Next the Target (Inventory application) will pick up the request messages from its queue, process it and respond back to the Initiator (Order application) with the product inventory status; at the end it will end the dialog conversation, which Initiator initiated as it is no longer required.

--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

The response message (which Target sends) comes and resides in the Initiator queue, which you can see by querying the Initiator queue with a SELECT statement as you did for Target queue above.

--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
--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

At this point, the Initiator (Order application) can retrieve the product status response message from its
queue, which was written by Target (Inventory application). Please note, if you use the above statement to see the Initiator queue, you will see two records; the first one is the response message from the Target and second one message is for dialog end, which was ended by Target after sending the response. To end a
dialog successfully, it has to be ended by both Target and Initiator. Hence, you need to execute the script given below twice; the first time to process the response and the second time to end the conversation as it is already ended by Target.

--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
 

Once you are done with the testing and want to drop all the objects which you created, you can use the clean-up scripts below.

--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 SERVICE [//SSBSLearning/ProductStockStatusCheck/TargetService]
DROP QUEUE dbo.SSBSLearningInitiatorQueue
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
IF EXISTS(SELECT COUNT(1) FROM sys.databases WHERE name = 'SSBSLearning')
 DROP DATABASE SSBSLearning
GO

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