Setup service broker between two databases in SQL Server.
My earlier article “Service Broker implementation between two tables in one database” explained how to setup service broker between two table in one database. Today I am explaining how to setup service broker between two tables resides in two different databases in one SQL Server instance.
I will be using the same table, queues, contracts, services and message types given in “Service Broker implementation between two tables in one database” article so if you have not read this article, I would recommend to take a look there once.
Creating two different databases in one SQL Server 2012 instance.
USE MASTER; GO IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'ExtremeAdviceSBMain') DROP DATABASE ExtremeAdviceSBMain; GO CREATE DATABASE ExtremeAdviceSBMain; GO IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'ExtremeAdviceSBReceiver') DROP DATABASE ExtremeAdviceSBReceiver; GO CREATE DATABASE ExtremeAdviceSBReceiver; GO --making broker enable for database. ALTER DATABASE ExtremeAdviceSBMain SET ENABLE_BROKER ALTER DATABASE ExtremeAdviceSBMain SET TRUSTWORTHY ON; ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBMain TO [sa]; GO --making broker enable for database. ALTER DATABASE ExtremeAdviceSBReceiver SET ENABLE_BROKER ALTER DATABASE ExtremeAdviceSBReceiver SET TRUSTWORTHY ON; ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBReceiver TO [sa]; GO
Let us now create basic architecture of service broker like message type, contract, queue and services in both databases ExtremeAdviceSBMain & ExtremeAdviceSBReceiver (target and initiator). Please note that I have used the same service broker object I have used in earlier article. We may eliminate few things but I will cover it up later on.
USE ExtremeAdviceSBReceiver GO --Create Message Type in target database CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML GO --Create Contract in target database CREATE CONTRACT [EXTREME://EX/Contract/Order] ( [EXTREME://EX/Message/Order/Request] SENT BY INITIATOR, [EXTREME://EX/Message/Order/Response] SENT BY TARGET ); GO --Create Target Queue CREATE QUEUE OrderTargetQueue WITH STATUS = ON GO CREATE SERVICE [EXTREME://EX/Service/Order/Target] ON QUEUE [OrderTargetQueue] ([EXTREME://EX/Contract/Order]) GO USE ExtremeAdviceSBMain GO --Create Message Type in initiator database CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML GO --Create Contract in initiator database CREATE CONTRACT [EXTREME://EX/Contract/Order] ( [EXTREME://EX/Message/Order/Request] SENT BY INITIATOR, [EXTREME://EX/Message/Order/Response] SENT BY TARGET ); GO --Create initiator Queue CREATE QUEUE OrderInitiatorQueue WITH STATUS = ON GO CREATE SERVICE [EXTREME://EX/Service/Order/Initiator] ON QUEUE [OrderInitiatorQueue] ([EXTREME://EX/Contract/Order]) GO
Now we will create two tables. One table in initiator database which is OrderDetail. Sales person will generate sales order and data will falls under that table. If sales person inserting duplicate order, it will caught by trigger and will be inserted in OrderHistory table. For more detail about this concept, have a look at my earlier article.
USE ExtremeAdviceSBMain GO --two sample table for demonstration CREATE TABLE OrderDetail ( OrderID INT IDENTITY(1,1), ClientID VARCHAR(10), ProjectID VARCHAR(50), OrderDate DATETIME DEFAULT GETDATE() ) GO USE ExtremeAdviceSBReceiver GO CREATE TABLE OrderHistory ( ID INT IDENTITY(1,1), OrderID INT, DuplicateWith INT, CheckDate DATETIME DEFAULT GETDATE() ) GO
Now we will create main logic which will send and process original message along with trigger on OrderDetail table which will activate service broker as soon as new record inserted in OrderDetail table.
USE ExtremeAdviceSBMain GO --Create this SP in Initiator database --Create SP which will be called from app --or trigger or by any other way --and activate service broker CREATE PROCEDURE [SendOrderRequestMessage] ( @data AS XML ) AS SET XACT_ABORT OFF; BEGIN TRY BEGIN TRANSACTION DECLARE @DialogHandle UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @DialogHandle FROM SERVICE [EXTREME://EX/Service/Order/Initiator] TO SERVICE N'EXTREME://EX/Service/Order/Target' ON CONTRACT [EXTREME://EX/Contract/Order] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @DialogHandle MESSAGE TYPE [EXTREME://EX/Message/Order/Request](@data); --END CONVERSATION @DialogHandle COMMIT ; END TRY BEGIN CATCH ROLLBACK TRAN; DECLARE @msg VARCHAR(MAX); SELECT @msg = ERROR_MESSAGE(); PRINT @msg END CATCH; GO USE ExtremeAdviceSBReceiver GO --Create this SP in target database -- Create actual business logic SP CREATE PROCEDURE [CheckOrderMessage] ( @data XML ) AS BEGIN TRY DECLARE @MessageType VARCHAR(50) ,@OrderID INT ,@ClientID VARCHAR(10) ,@ProjectID VARCHAR(50) SELECT @MessageType = @Data.value('(OrderMessage/Header/@MessageType)[1]','VARCHAR(50)'), @OrderID = @data.value('(OrderMessage/Header/@OrderID)[1]','INT'), @ClientID = @Data.value('(OrderMessage/Header/@ClientID)[1]','VARCHAR(10)'), @ProjectID = @data.value('(OrderMessage/Header/@ProjectID)[1]','VARCHAR(50)') SELECT @MessageType = ISNULL(@MessageType,'') IF @MessageType = 'OrderHistory' AND @OrderID IS NOT NULL BEGIN DECLARE @OrderIDOld INT ,@ClientIDOld VARCHAR(10) ,@ProjectIDOld VARCHAR(50) SELECT @ClientIDOld=ClientID, @ProjectIDOld=ProjectID, @OrderIDOld=OrderID FROM ExtremeAdviceSBMain.dbo.OrderDetail WHERE ClientID=@ClientID AND ProjectID=@ProjectID AND OrderID<>@OrderID IF(@OrderIDOld IS NOT NULL) BEGIN INSERT INTO OrderHistory (OrderID,DuplicateWith) SELECT @OrderIDOld,@OrderID END RETURN END END TRY BEGIN CATCH DECLARE @msg VARCHAR(MAX) SELECT @msg = ERROR_MESSAGE() END CATCH RETURN GO --Create Target queue reader SP CREATE PROC [OrderTargetQueueReader] AS SET NOCOUNT ON; DECLARE @XML XML, @MessageBody VARBINARY(MAX), @MessageTypeName SYSNAME, @ConversationHandle UNIQUEIDENTIFIER, @Response XML BEGIN TRY WHILE (1 = 1) BEGIN WAITFOR ( RECEIVE TOP(1) @MessageTypeName = message_type_name, @MessageBody = message_body, @ConversationHandle = [conversation_handle] FROM dbo.OrderTargetQueue ), TIMEOUT 60000 IF (@@ROWCOUNT = 0) BEGIN BREAK END SET @XML = CAST(@MessageBody AS XML); IF @MessageTypeName = 'EXTREME://EX/Message/Order/Request' BEGIN --PRINT 'executing CheckOrderMessage @XML' EXECUTE CheckOrderMessage @XML --PRINT 'sending response' SELECT @xml = 'OK'; SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE [EXTREME://EX/Message/Order/Response](@xml); --PRINT 'ending cnversation' END CONVERSATION @ConversationHandle; END END END TRY BEGIN CATCH DECLARE @msg VARCHAR(MAX) SELECT @msg = ERROR_MESSAGE() DECLARE @line INT, @proc VARCHAR(MAX) SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE() SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg SELECT @msg END CATCH GO USE ExtremeAdviceSBMain GO --create following SP to handle response of target in initiatordatabase --Create Initiator queue reader SP CREATE PROC [OrderInitiatorQueueReader] AS SET NOCOUNT ON; DECLARE @XML XML, @MessageBody VARBINARY(MAX), @MessageTypeName SYSNAME, @ConversationHandle UNIQUEIDENTIFIER, @Response XML BEGIN TRY WHILE (1 = 1) BEGIN BEGIN TRANSACTION WAITFOR ( RECEIVE TOP(1) @MessageTypeName = message_type_name, @MessageBody = message_body, @ConversationHandle = [conversation_handle] FROM dbo.OrderInitiatorQueue ), TIMEOUT 60000 IF (@@ROWCOUNT = 0 AND @@TRANCOUNT>0) BEGIN ROLLBACK TRANSACTION BREAK END IF @MessageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN END CONVERSATION @ConversationHandle; END COMMIT TRANSACTION END END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION DECLARE @msg VARCHAR(MAX) SELECT @msg = ERROR_MESSAGE() DECLARE @line INT, @proc VARCHAR(MAX) SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE() SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg END CATCH GO -------service broker call from trigger. CREATE TRIGGER trg_OrderDetail_Ins ON OrderDetail AFTER INSERT AS BEGIN SET NOCOUNT ON; DECLARE @Data XML SELECT @Data = ( SELECT 'OrderHistory' AS 'Header/@MessageType', OrderID AS 'Header/@OrderID', ClientID AS 'Header/@ClientID', ProjectID AS 'Header/@ProjectID' FROM (SELECT * FROM inserted) AS T FOR XML PATH(''), ROOT('OrderMessage'), TYPE ) EXEC [SendOrderRequestMessage] @Data END GO
Once you are ready with this structure, you are all set to go. Let us insert one record in OrderDetail table.
USE ExtremeAdviceSBMain GO --Testing SB by inserting one record in OrderDetail Table --which will execute Trigger and trigger will activate SB INSERT INTO OrderDetail(ClientID,ProjectID) SELECT 'TC01','NY Healthcare' GO --you will get one record which we have inserted above. SELECT * FROM OrderDetail GO --you will get no record in OrderHistory table --as we have not inserted duplicate record yet. SELECT * FROM ExtremeAdviceSBReceiver.dbo.OrderHistory GO
Since we have not inserted any duplicate records yet, we won’t get any record in OrderHistory table. Let us inserting duplicate record in OrderDetail table.
--inserting same record again so SB will detect it as duplicate order --that will be inserted in ORderHistory table INSERT INTO OrderDetail(ClientID,ProjectID) SELECT 'TC01','NY Healthcare' GO --you will get one record in OrderHistory table --as we have inserted duplicate record SELECT * FROM ExtremeAdviceSBReceiver.dbo.OrderHistory GO
We, now, suppose to get record in OrderHistory table but unfortunately we won’t get it. There are two possibilities that either our initiator queue doesn’t have sent message to target or target have received the record but it has not processed it. Right???? Let us check it.
SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBMain.DBO.OrderInitiatorQueue SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBReceiver.DBO.OrderTargetQueue GO
You can see that our “OrderTargetQueue” has record to process. Then question is why it has not processed?
Well, we have had Trigger on OrderDetail table which has activated our initiator queue and hence record reached to target queue but target queue has not been activated and hence it has not processed target message.
We can process message with following SP either manually or by sql server job as we have not activate it automatically.
USE ExtremeAdviceSBReceiver GO EXEC [OrderTargetQueueReader] GO
Now let us see whether record is processed or not.
SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBMain.DBO.OrderInitiatorQueue SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBReceiver.DBO.OrderTargetQueue GO
You can see “OrderTargetQueue”has not record now as our message is processed and you can also see the entry in “OrderHistory” table also as we had one duplicate record.
Still “OrderInitiatorQueue” has record to process. You can see that it is just a blank message for end dialog with “OK” status which has sent back by target queue after processing messages.
We can run following SP to process end dialog in initiator queue.
USE ExtremeAdviceSBMain GO EXEC [OrderInitiatorQueueReader] GO
I will shortly come up with article which will run full service broker cycle by its own without manual interaction.
If you wish to refer my other Service Broker related article, have a look at following list:
- Importance of Service Broker in SQL Server (Click Here)
- Introduction of Service Broker in SQL Server (Click Here)
- Enable Service Broker in SQL Server database (Click Here)
- Small demonstration of Service broker for beginners (Click Here)
- Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
- Find Service Broker Queue Count in SQL Server (Click Here)
- Get Transmission queue row count in Service Broker (Click Here)
- Purge target Transmission queue of Service Broker in SQL Server (Click Here)
- Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
- Service Broker implementation between two tables in one database (Click Here)
- Service Broker Queue Activation and Thread in SQL Server (Click Here)
If you like this article, do like “Extreme-Advice” page in Facebook.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.