SQL Server Broker Part 1

SQL server Broker

Demo on how to send messages between two databases.

Create a database source & destination

CREATE DATABASE source;

GO

ALTER DATABASE Source SET TRUSTWORTHY ON;

GO

CREATE DATABASE destination ;

GO

** Make sure you set TRUSTWORTHY on Source.

Next we need to make objects, as we all know that these objects need to be setup on both source database.

1. Create Message Types
2. Create Contract
3. Create Queue
4. Create Service

Creating Message Type on Source db

CREATE MESSAGE TYPE [RequestMessage_1]

      VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [ReplyMessage_1]

      VALIDATION = WELL_FORMED_XML;

GO

Creating Contract on Source db

CREATE CONTRACT [SimpleContract]

     ([RequestMessage_1]

        SENT BY initiator,

      [ReplyMessage_1]

        SENT BY target

     );

GO

Creating Queue on Source Db

CREATE QUEUE SourceQueue;

Create Service on the Queue on the Source Db

CREATE SERVICE SourceService

      ON QUEUE Sourcequeue;

GO

Now Let’s Switch to Destination Db.

Here on Destination also we need to create Message types, Contract, Queue and Service.

Create Messagetypes on Destination

CREATE MESSAGE TYPE RequestMessage_1

      VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE ReplyMessage_1

      VALIDATION = WELL_FORMED_XML;

GO

Create Contract on Destination Db.

CREATE CONTRACT SimpleContract

    ( RequestMessage_1

        SENT BY INITIATOR,

     ReplyMessage_1

        SENT BY TARGET

     );

GO

Create Queue on Destination db

CREATE QUEUE DestinationQueue;

Create Service on Destination db

CREATE SERVICE destinationservice

      ON QUEUE destinationqueue

     (SimpleContract);

GO

When creating service on destination give the contract name as well.

Now Transacting messages between databases using broker service :

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;

DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle

    FROM SERVICE SourceService

    TO SERVICE N'destinationservice'

    ON CONTRACT SimpleContract

    WITH

        ENCRYPTION = OFF;

SELECT @RequestMsg =

  N'Hello World_2';

SEND ON CONVERSATION @InitDlgHandle

    MESSAGE TYPE [RequestMessage_1]

     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;

GO

** If the message was not sent and had some error you can always check in source queue. The message along with error will be there.

Receiving the message on destination database.

We can check the queue before checking by query as well.

In the above screenshot you see that the status is 0 for first message and 1 for second. The status column shows that the message in the queue is read or not. 0 means read, and 1 means yet to be read.

#* If Retention for Destination queue was FALSE then the read message with status 0 would have not been there and would have got purged after reading. As RETENTION is True the message even after reading still exists.

Reading the message from a query :

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;

DECLARE @RecvReqMsg NVARCHAR(100);

DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR

( RECEIVE TOP(1)

   @RecvReqDlgHandle = conversation_handle,

   @RecvReqMsg = message_body,

   @RecvReqMsgName = message_type_name

 FROM destinationqueue

), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

COMMIT TRANSACTION;

GO

Now if we check the queue we can see that both messages have been read and status for both is 0.

Thanks for reading this. We will see the issues/errors/Troubleshooting etc related to broker service in next article