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 trigger. It was a for single database, here i will explain service broker for between databases. In 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 bi-directional 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 fo ahead this post, I would like to you read earlier post for single database.

Have you read it? What we do now?
1.Create a request & reply MESSAGE TYPE for target and initiator.
2.Create a CONTRACT that specify 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 require essentially 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 earlier example, we will setup service broker to process order data and complete conversion with confirmation as processed or not. So let us create an objects for order details in initiator database.

-- Using initiator database
USE SenderDB
GO

-- Creating in initiator database
IF (object_id('OrderDetails','U') > 0)
DROP TABLE OrderDetails

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
IF (object_id('OrderProcessed','U') > 0)
DROP TABLE OrderProcessed

CREATE TABLE OrderProcessed
(
BatchId int IDENTITY(1,1),
OrderIds VARCHAR(MAX),
ProcessedDate datetime DEFAULT GETDATE()
)
GO

To processed ther order database we need to create code in initiator database which will send a request message to target service on target database. So we will capture only those orders which are not processed. Let us create 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 received these requested message and send reply message to 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

Message arrived at target database and processed and also send a reply message for the process confirmation. Now initiator 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

-- 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 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 is turn to complete the process and end conversion for processes 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

Testing mode is completed and hope you liked this post, may be help you.

Nice conversion!