Service Broker - Conversation between Databases in SQL Server

Recently I have posted for Service Broker for single database in which I have explained it with table history example and it was invoked within a trigger. It was a for a single database, here I will explain service broker for between databases. In an earlier post of service broker with single database messages were traversed from INITIATOR to TARGET only. But with this post we will go for the communication in a bidirectional way, means from INITIATOR to TARGET and from TARGET to INITIATOR.

Service broker has two end called TARGET and INITIATOR, so target and initiator will be on different databases for this example. Before going ahead this post, I would like to you, read the earlier post for a single database.

Have we read it? What we do now?

1.Create a request & reply MESSAGE TYPE for target and initiator.
2.Create a CONTRACT that specifies the messages between target and initiator.
3.Create an QUEUE for target and initiator.
4.Create a SERVICE for for the created Queues above.
5. Send a message from initiator to target.
6. Reply a message from target to initiator.

An Example :
Configuring databases for initiator and target objects, enable for service broker too. Below script do all the stuffs which are essentially required to start the conversion through service broker.

USE MASTER;
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'SenderDB')
DROP DATABASE SenderDB;
GO
CREATE DATABASE SenderDB;
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'ReceiverDB')
DROP DATABASE ReceiverDB;
GO
CREATE DATABASE ReceiverDB;
GO

ALTER DATABASE SenderDB SET TRUSTWORTHY ON;
GO

ALTER DATABASE ReceiverDB SET TRUSTWORTHY ON;
GO


USE ReceiverDB
GO

CREATE MESSAGE TYPE [//Common/Databases/SendMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//Common/Databases/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT [//Common/Databases/DBContract]
      ([//Common/Databases/SendMessage]
         SENT BY INITIATOR,
       [//Common/Databases/ReplyMessage]
         SENT BY TARGET
      );
GO


CREATE QUEUE DestQueue;
GO

CREATE SERVICE [//DestDB/Databases/DestService]
       ON QUEUE DestQueue
       ([//Common/Databases/DBContract]);
GO


USE SenderDB
GO

CREATE MESSAGE TYPE [//Common/Databases/SendMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//Common/Databases/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO


CREATE CONTRACT  [//Common/Databases/DBContract]
      ( [//Common/Databases/SendMessage]
         SENT BY INITIATOR,
        [//Common/Databases/ReplyMessage]
         SENT BY TARGET
      );
GO


CREATE QUEUE SenderQueue
GO

CREATE SERVICE [//SenderDB/Databases/SenderService]
       ON QUEUE SenderQueue
GO

We had setup service broker to collect history for any table row change in the earlier example, we will set up service broker to process order data and complete conversion with confirmation as processed or not. So let us create an object for order details in initiator database.

-- Using initiator database
USE SenderDB
GO

-- Creating in initiator database
CREATE TABLE OrderDetails
(
OrderId bigint,
OrderQty int,
OrderVal numeric(18,2),
OrderDate datetime,
IsProcessed bit default 0
)
GO

-- Using target database
USE ReceiverDB
GO

-- Creating in target database
CREATE TABLE OrderProcessed
(
BatchId int IDENTITY(1,1),
OrderIds VARCHAR(MAX),
ProcessedDate datetime DEFAULT GETDATE()
)
GO

To process the order database, we need to create code in initiator database which will send a request message to target service on the target database. So we will capture only those orders which are not processed. Let us create a stored procedure for the same.

-- Using initiaror database
USE SenderDB
GO

-- Creating a procedure to send a request message
CREATE PROCEDURE SendOrderForProcess
AS
BEGIN
SET NOCOUNT ON

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg VARCHAR(MAX);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE [//SenderDB/Databases/SenderService]
     TO SERVICE N'//DestDB/Databases/DestService'
     ON CONTRACT [//Common/Databases/DBContract]
     WITH
         ENCRYPTION = OFF;

SELECT @RequestMsg = COALESCE(@RequestMsg + ', ', '') + 
CAST(OrderId AS VARCHAR(10))
FROM OrderDetails
WHERE IsProcessed = 0;

IF @RequestMsg is not null
BEGIN    
SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE [//Common/Databases/SendMessage]
      (@RequestMsg);

END

COMMIT TRANSACTION;

END
GO

As we completed the code generate and send request message, we will receive these requested message and send a reply message to the initiator after processing the requested message.

-- Using target database
USE ReceiverDB
GO

-- Creating a procedure to receive and replky message
CREATE PROCEDURE ReceiveOrderForProcess
AS
BEGIN
SET NOCOUNT ON

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg VARCHAR(MAX);
DECLARE @RecvReqMsgName sysname;

-- Creatig a loop to get the message one by one from queue
WHILE (1=1)
BEGIN

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM DestQueue
), TIMEOUT 1000;

 IF (@@ROWCOUNT = 0)
    BEGIN
      ROLLBACK TRANSACTION;
      BREAK;
    END

IF @RecvReqMsgName =
   N'//Common/Databases/SendMessage'
BEGIN
 IF @RecvReqMsg IS NOT NULL
 BEGIN
 INSERT INTO OrderProcessed(OrderIds)
 SELECT @RecvReqMsg

     DECLARE @ReplyMsg VARCHAR(max);
     SET @ReplyMsg = @RecvReqMsg ; 
 
     SEND ON CONVERSATION @RecvReqDlgHandle
          MESSAGE TYPE
            [//Common/Databases/ReplyMessage](@ReplyMsg);

     END CONVERSATION @RecvReqDlgHandle;
END
END

COMMIT TRANSACTION;
END

END
GO

A message arrived at target database and processed and also send a reply message for the confirmation process. Now initiate database will mark those orders as completed. Let us do the same.

-- Using initiator database
USE SenderDB
GO

-- Creating a procedure to complete the conversion of processed orders
CREATE PROCEDURE CompleteOrder
AS
BEGIN
SET NOCOUNT ON

DECLARE @RecvReplyMsg VARCHAR(MAX);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;
DECLARE @sqlProcess varchar(max)

-- Creatig a loop to get the message one by one from queue
WHILE (1=1)
BEGIN

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReplyDlgHandle = conversation_handle,
    @RecvReplyMsg = message_body
  FROM SenderQueue
), TIMEOUT 1000;

 IF (@@ROWCOUNT = 0)
    BEGIN
      ROLLBACK TRANSACTION;
      BREAK ;
    END ;

SET @sqlProcess = 
'UPDATE OrderDetails
 SET IsProcessed = 1
WHERE OrderId IN ('+@RecvReplyMsg+') '

EXEC (@sqlProcess)

END CONVERSATION @RecvReplyDlgHandle;

COMMIT TRANSACTION;
END
END
GO

Testing :
We have done all the code which are required for the testing service broker for two databases.

-- Inserting sample records in table and viewing records from both tables

INSERT INTO SenderDB.dbo.OrderDetails
(OrderId,
OrderQty,
OrderVal,
OrderDate,
IsProcessed)
SELECT 1,1,300,GETDATE(),0

INSERT INTO SenderDB.dbo.OrderDetails
(OrderId,
OrderQty,
OrderVal,
OrderDate,
IsProcessed)
SELECT 2,4,500,GETDATE(),0

INSERT INTO SenderDB.dbo.OrderDetails
(OrderId,
OrderQty,
OrderVal,
OrderDate,
IsProcessed)
SELECT 3,8,700,GETDATE(),0
UNION ALL
SELECT 4,9,800,GETDATE(),0
UNION ALL
SELECT 5,7,900,GETDATE(),0

-- Viewing data
SELECT 
* 
FROM ReceiverDB.dbo.OrderProcessed

SELECT 
* 
FROM SenderDB.dbo.OrderDetails
GO

Now execute the stored procedures which we have created in initiator and the target database for a conversion of request and reply message and reviewing records again in both tables.

-- Execting SP to send a request message
EXEC SenderDB.dbo.SendOrderForProcess

-- Execting SP to send a reply message
EXEC ReceiverDB.dbo.ReceiveOrderForProcess

-- Viewing data
SELECT 
* 
FROM ReceiverDB.dbo.OrderProcessed

SELECT 
* 
FROM SenderDB.dbo.OrderDetails
GO

It turns to complete the process and end conversion for processing orders and reviewing a confirmed processed orders in tables.

-- Execting SP to complete process
EXEC SenderDB.dbo.CompleteOrder

-- Viewing data
SELECT 
* 
FROM ReceiverDB.dbo.OrderProcessed

SELECT 
* 
FROM SenderDB.dbo.OrderDetails
GO

The testing mode is completed and hope you liked this post, maybe help you. Nice conversion!