You are here:Home » tsql » Working with Service Broker for single database in SQL Server

Working with Service Broker for single database in SQL Server

You may work with Service Broker in SQL Server. I am just learning Service Broker and implemented practical with an example which I evaluated. Before moving on it, I would like to introduce service broker, not in the deep but having some overview. Service broker applies to SQL Server 2005 and newer version.

Overview :
Per msdn, with Service Broker, internal or external processes can send and receive guaranteed, asynchronous messaging by using extensions to Transact-SQL. It provides queuing and reliable messaging for SQL Server. It can be used for single database communication, the two databases of single SQL server instance and two databases of different SQL server instances for exchanging messages between them.

Here we have a small demonstration with an example of the single databases conversation only. Here we will create database objects and start conversion between them within a single database only. Service broker has two end called TARGET and INITIATOR, so target and initiator will be on the same database for our example.

What will we do?
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.

So as per above list we will enable the database for Service Broker which will be used for this demo. After that we will create message types for the request and reply. The contract will be created after that. Finally, we will create a queue for target and initiator and services will be created for those queues.
-- Using master database
USE MASTER
GO

IF EXISTS(SELECT name FROM sys.databases where name = 'SourceDB')
DROP DATABASE SourceDB
GO

CREATE DATABASE SourceDB
GO

-- Enabling SourceDB database for service broker
ALTER DATABASE SourceDB
      SET ENABLE_BROKER;
GO

-- Using service broker enabled database.
USE SourceDB;
GO

-- Creating requst and reply message types 
CREATE MESSAGE TYPE
       [//DB/SourceDB/RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//DB/SourceDB/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO


-- Creating contract that specify the conversion
CREATE CONTRACT [//DB/SourceDB/DBContract]
      ([//DB/SourceDB/RequestMessage]
       SENT BY INITIATOR,
       [//DB/SourceDB/ReplyMessage]
       SENT BY TARGET
      );
GO

-- Creating queue on target 
CREATE QUEUE DestinationQueue;

-- Creating query on initiator
CREATE QUEUE SourceQueue;
GO

-- Creating a target service for above created queue 
CREATE SERVICE
       [//DB/SourceDB/TargetService]
       ON QUEUE DestinationQueue
       ([//DB/SourceDB/DBContract]);

-- Creating a initiator service for above created queue 
CREATE SERVICE
       [//DB/SourceDB/InitiatorService]
       ON QUEUE SourceQueue;
GO
These are the objects which will be used for service broker. We will also create some other objects for which service broker used. We will create one table and capture data change track in another table which will be created as below script.
-- Creating table
CREATE TABLE TransactionTable
(
TranId int,
TranType varchar(20),
TranDate datetime,
)
GO

-- Creating a history table of above main table
CREATE TABLE TransactionHistory
(
HistoryId INT IDENTITY(1,1),
XMLHistory XML
)
GO
Now we need to capture the data change tracking history in another table with service broker, so we will create a trigger on the main table and the service broker code will be used in trigger. It is just capturing the old and new data from magic tables and send from initiator to target and use a dialog handler to send a request message on conversion.
-- Creating a trigger on main table 
-- for insert, update and delete operation
CREATE TRIGGER TR_TrnHistory
ON TransactionTable
FOR INSERT, UPDATE, DELETE
AS
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg XML;
DECLARE @TranId UNIQUEIDENTIFIER;

-- Create a unique id for each conversion
SET @TranId = NEWID()

-- Begin dialog
BEGIN DIALOG @InitDlgHandle
     FROM SERVICE
      [//DB/SourceDB/InitiatorService]
     TO SERVICE
      N'//DB/SourceDB/TargetService'
     ON CONTRACT
      [//DB/SourceDB/DBContract]
     WITH
         ENCRYPTION = OFF;

-- Capture data change from magic table in xml format 
-- and assign to variable
SET @RequestMsg =
  
    (SELECT sender.* FROM
       (
       SELECT @TranId as Id,'Inserted' as ope,* FROM inserted 
       UNION ALL
       SELECT @TranId as Id,'Deleted' as ope,* FROM deleted 
       ) sender FOR XML AUTO );


-- Start conversion and send request message to target
IF @RequestMsg is not null
BEGIN

;SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE 
     [//DB/SourceDB/RequestMessage]
     (@RequestMsg);
END
To receive the messages from the initiator and process them, we need receive dialog handler which will receive the messages from the queue and process them. After it will insert into history table. Let us create one stored procedure to read the queue to receive the incoming messages.
-- Creating stored procedure
CREATE PROCEDURE SB_ReceiveData 
AS
BEGIN
SET NOCOUNT ON 

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg XML;
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 DestinationQueue
), TIMEOUT 1000;

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

-- Inserting records in history table
IF @RecvReqMsg IS NOT NULL
BEGIN
INSERT INTO TransactionHistory(XMLHistory)
SELECT @RecvReqMsg
END

COMMIT TRANSACTION;
END
END

GO
Congratulations, we have done all above stuff and now out of developmental mode. But we need to enter in testing mode now to review how service broker works. So we will make some transaction in table to test it actually.

Testing:
-- Inserting a record
INSERT INTO TransactionTable
SELECT 2,'Insert',Getdate()

-- Updating a records
UPDATE TransactionTable
SET TranType = 'Update',
TranDate = GETDATE()
WHERE TranId = 2

-- Deleting a record
DELETE FROM TransactionTable
WHERE TranId = 2

GO
Now Trigger will be fired when we perform above DML operation and data send to queue as a request message. To read the message we need to call stored procedures. So let us call it. Before executing a stored procedure, we will check the rows from both tables and the same will review after executing stored procedure.
SELECT 
* 
FROM TransactionTable

SELECT 
* 
FROM TransactionHistory
GO

EXEC SB_ReceiveData
GO

SELECT 
* 
FROM TransactionTable

SELECT 
* 
FROM TransactionHistory
GO


I hope you liked this post. I will post more about the Service Broker as the next.