Writing an SSBS App When the Initiator and Target are on the Same Database


SQL Server Service Broker (SSBS), introduced with SQL Server 2005 and enhanced in SQL Server 2008, allows you to write queuing/message based applications within the database itself. This article discusses creating an application in which Initiator and Target both are in the same database.

Introduction

In my
previous couple of articles
, 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>';
	--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
IF EXISTS(SELECT TOP 1 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.

Conclusion

This article has demonstrated
how you can write your SSBS application when both Initiator and Target are in
same database. In the next couple of articles, I will demonstrate how you will
be writing SSBS applications when Initiator and Target are in different
databases in same SQL Server instance or in different instances altogether.

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

Latest Articles