SQL Server Service Broker – Local Instance Example

Let us understand service broker concepts with an example. This is a simple local instance based example which explains how service broker can be used to communicate between two databases.

Create Database DB-Initiator

Use Master create database DBInitiator WITH TRUSTWORTHY ON; GO

 

When TRUSTWORTHY
ON is specified, database modules (for example, views, user-defined functions, or stored procedures) that use an impersonation context can access resources outside the database

Create a Master Key

use DBInitiator Go CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test1357'; GO

 

Create a logging Table for our Example

CREATE TABLE [dbo].[Entry_table](     [ID] [int] IDENTITY(1,1) NOT NULL Primary key,     [Data] [varchar](max) COLLATE Latin1_General_CI_AI NULL,     [Message] [xml] NULL) GO

 

So far so good! I am the master key creation would be puzzling. Hold on to it. It isn't that complicated.

Let us setup the second database.

We will create the similar entities in DBTarget as well.

create database DBTarget     WITH TRUSTWORTHY ON; GO   use DBTarget Go   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test1357'; GO     -- Creating Database table to be used for logging data CREATE TABLE [dbo].[Entry_table](     [ID] [int] IDENTITY(1,1) NOT NULL Primary key,     [Data] [varchar](max) COLLATE Latin1_General_CI_AI NULL,     [Message] [xml] NULL)   GO

 

Service Broker Setup on Both Databases

We need to setup service broker components on both the databases. These components are self-explanatory and easy to understand. You can create analogies to the normal conversation protocols human follow to understand these concepts.

How to say?

CREATE MESSAGE TYPE SendMessageType     VALIDATION = WELL_FORMED_XML;

 

How to Reply?

CREATE MESSAGE TYPE ReceiveMessageType     VALIDATION = WELL_FORMED_XML;

 

Auto Check Message?

VALIDATION : Specifies how Service Broker validates the message body for messages of this type. When this clause is not specified, validation defaults to NONE. WELL_FORMED_XML Specifies that the message body must contain well-formed XML.

 

Specifying Request Reply Handshake – Called Contract.

CREATE CONTRACT [MainContract] (     [SendMessageType] SENT BY INITIATOR,     [ReceiveMessageType] SENT BY TARGET );

 

Where to put the message? - Queue

Every message goes into the queue. Before we create the queues let us create a dummy stored procedure. This stored procedure will process the messages that come into the queue. After you create the procedure, create the Initiator queue.

use DBInitiator go   CREATE PROCEDURE dbo.ProcessMessage As declare    @msg_type int = 0 declare @dialog uniqueidentifier,         @ErrSave int = 0,         @ErrDesc nvarchar(150),         @msg_body xml                 SELECT @msg_type, @dialog GO   CREATE QUEUE InitiatorQueue WITH STATUS=ON, ACTIVATION ( PROCEDURE_NAME = ProcessMessage, MAX_QUEUE_READERS = 5,         Execute AS 'dbo') ;

 

Please note: The signature that is expected for the activation clause needs to match your stored procedure definition. Also, use the appropriate user for the execute as clause.

Do the same on the other database as well.

use DBTarget go   CREATE PROCEDURE dbo.ProcessMessage As declare    @msg_type int = 0 declare @dialog uniqueidentifier,         @ErrSave int = 0,         @ErrDesc nvarchar(150),         @msg_body xml                 SELECT @msg_type, @dialog GO   CREATE QUEUE TargetQueue WITH STATUS=ON, ACTIVATION ( PROCEDURE_NAME = ProcessMessage, MAX_QUEUE_READERS = 5,         Execute AS 'dbo') ;

 

Who will send to whom?

These will be the services that will communicate.

use DBInitiator go -- Create a Service to be used CREATE SERVICE SERVICE1     ON QUEUE [InitiatorQueue]     (     [MainContract]     ); GO use DBTarget go -- Create a Service to be used CREATE SERVICE SERVICE2     ON QUEUE [TargetQueue]     (     [MainContract]     ); GO

 

 

Stored Procedure that will process the message (thanks to Akram Hussein)

I created a dummy stored procedure ProcessMessage before. This was needed to create the queue. Now I will alter the stored procedure to put the actual logic.

The actual logic is simple.

1. Check the queue for message.
   
2. If you find a message and this is not an end of conversation message..
   
   1. Respond
      
3. Else
   
   1. Close the conversation.
      

In all the above process, log into the table.

use DBTarget go   Alter PROCEDURE dbo.ProcessMessage As declare    @msg_type int = 0 declare @dialog uniqueidentifier,         @ErrSave int = 0,         @ErrDesc nvarchar(150),         @msg_body xml             -- While loop to check the queue. while (1 = 1) begin     begin transaction -- Receive the next available message from the queue     WAITFOR (         RECEIVE top(1) -- Take the first message.             @msg_type=message_type_id, --the type of message received             @msg_body=message_body, -- the message contents             @dialog = conversation_handle -- the identifier of the dialog this message was received on             FROM InitiatorQueue -- or InitiatorQueue – This need to be changed based on which database you have the queue.     ), TIMEOUT 4000   -- If we didnt recieve anything, just rollback.     if (@@ROWCOUNT = 0)         BEGIN             Rollback Transaction             BREAK         END   -- look for errors.     SET @ErrSave = @@ERROR ;       IF (@ErrSave <> 0)     BEGIN         ROLLBACK TRANSACTION ;         SET @ErrDesc = N'An error has occurred.' ;         END CONVERSATION @dialog         WITH ERROR = @ErrSave DESCRIPTION = @ErrDesc ;           INSERT INTO Entry_table VALUES(@ErrDesc,NULL)     END     ELSE     -- Check for the End Dialog message.     If (@msg_type <> 2) -- End dialog message         BEGIN          -- Log Message in Database         INSERT INTO Entry_table VALUES('Successfully Received',@msg_body);     -- Send the message back to the sender.             SET @msg_body = '<msg> i got the message </msg>';             SEND ON CONVERSATION @dialog -- send it back on the dialog we received the message on                 MESSAGE TYPE ReceiveMessageType -- Must always supply a message type                 (@msg_body); -- the message contents are XML         END     ELSE         BEGIN               -- End Conversation and Notify other side of the conversation             END CONVERSATION @dialog           END     commit transaction end      GO

 

Let us try now…

The script snippet will send a message. We will read the table to find out how messages have transmitted. All conversations need to happen through a dialog
Service Broker dialog security lets your application use authentication, authorization, or encryption for an individual dialog conversation (or dialog). By default, all dialog conversations use dialog security. When you begin a dialog, you can explicitly allow a dialog to proceed without dialog security by including the ENCRYPTION = OFF clause on the BEGIN DIALOG CONVERSATION statement. However, if a remote service binding exists for the service that the conversation targets, the dialog uses security even when ENCRYPTION = OFF. This explains why I created the master key before.

use DBInitiator go   DECLARE @dialog_handle uniqueidentifier,         @msg XML   BEGIN DIALOG CONVERSATION @dialog_handle FROM SERVICE [SERVICE1] TO SERVICE 'SERVICE2' ON CONTRACT [MainContract] ;   set @msg = '<name> my is name - Vinod </name>';     -- Now Sending a message note we are using -- SendMessageType since it is defined in the contract -- that it is to be sent only by initiater   SEND ON CONVERSATION @dialog_handle     MESSAGE TYPE SendMessageType (@msg)