Service Broker Example #1 - Configuration, Send, Receive & Respond

Service Broker Example #1 - Configuration, Send, Receive & Respond

This example uses AdventureWorks and AdventureWorksTarget (an empty AdventureWorks db).

1 Initial Setup of databases -

-- Allow databases access to external object 

USE Master
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
ALTER DATABASE AdventureWorksTarget SET TRUSTWORTHY ON

-- Enable Databases for Service Broker

ALTER DATABASE AdventureWorks SET NEW_BROKER
ALTER DATABASE AdventureWorksTarget SET NEW_BROKER

-- Needed these as had downloaded Adventureworks and user that created it was not present.
-- Good practice anyway...
ALTER AUTHORIZATION ON DATABASE::AdventureWorks TO sa;
ALTER AUTHORIZATION ON DATABASE::AdventureWorksTarget TO sa;

2 Configuration of Target DB (do before source!) -

USE AdventureWorksTarget
GO
-- Set Key for Service Broker Communication
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PASSWORD2008'

-- Configure Message Types on target
CREATE MESSAGE TYPE [http://www.servicebroker.com/SendType]
    VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [http://www.servicebroker.com/ResponseType]
    VALIDATION = WELL_FORMED_XML;
GO

-- Configure Contract
-- Contract defines a message broker conversation
CREATE CONTRACT [http://www.servicebroker.com/Contract]
   ([http://www.servicebroker.com/SendType]
   SENT BY INITIATOR,
    [http://www.servicebroker.com/ResponseType]
   SENT BY TARGET
   );
GO

-- Configure Queue
CREATE QUEUE [AdventureWorksTargetQueue];

-- Turn Queue on
ALTER QUEUE [AdventureWorksTargetQueue]
WITH STATUS = ON

-- Configure Service
CREATE SERVICE [AdventureWorksTargetQueueTargetService]
    ON QUEUE [AdventureWorksTargetQueue]
    ([http://www.servicebroker.com/Contract]);
GO

3 Configuration of the Source DB -

USE AdventureWorks
GO

-- Set Key for Service Broker Communication
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PASSWORD2008'

-- Configure Message Types on source
CREATE MESSAGE TYPE [http://www.servicebroker.com/SendType]
    VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [http://www.servicebroker.com/ResponseType]
    VALIDATION = WELL_FORMED_XML;
GO

-- Configure Contract
-- Contract defines a message broker conversation
CREATE CONTRACT [http://www.servicebroker.com/Contract]
   ([http://www.servicebroker.com/SendType]
   SENT BY INITIATOR,
    [http://www.servicebroker.com/ResponseType]
   SENT BY TARGET
   );
GO

-- Configure Queue
CREATE QUEUE [AdventureWorksSourceQueue];

-- Turn Queue on
ALTER QUEUE [AdventureWorksSourceQueue]
WITH STATUS = ON

-- Configure Service
CREATE SERVICE [AdventureWorksSourceQueueSourceService]
   ON QUEUE [AdventureWorksSourceQueue];
GO

4 Examining the contents of the message queues.
Run this, no records will be returned just yet though -

-- Monitoring Queue contents
select * from AdventureWorks..[AdventureWorksSourceQueue]
select * from AdventureWorksTarget..[AdventureWorksTargetQueue]

-- Where is my message? (Look at transmission_status for error information)
select transmission_status,* from AdventureWorks.sys.transmission_queue
select transmission_status,* from AdventureWorksTarget.sys.transmission_queue

5 Sending a Message -

-- Send the Message
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

 BEGIN DIALOG @InitDlgHandle
   FROM SERVICE [AdventureWorksSourceQueueSourceService]
   TO SERVICE N'AdventureWorksTargetQueueTargetService' 
   ON CONTRACT [http://www.servicebroker.com/Contract]
   WITH ENCRYPTION = OFF;

 SELECT @RequestMsg =
    N'Initial Service Broker Message from AdventureWorks';

 SEND ON CONVERSATION @InitDlgHandle
   MESSAGE TYPE [http://www.servicebroker.com/SendType]
    (@RequestMsg);

 SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO

-- Examine the queues to see the message just transmitted in the target one >
select * from AdventureWorks..[AdventureWorksSourceQueue]
select * from AdventureWorksTarget..[AdventureWorksTargetQueue]

6 Read & Respond to the message -

-- Read the message on the target server and respond to it
USE AdventureWorksTarget

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 [AdventureWorksTargetQueue]
 ), TIMEOUT 1000;

 SELECT @RecvReqMsg AS ReceivedRequestMsg;

 IF @RecvReqMsgName =
    N'http://www.servicebroker.com/SendType'
 BEGIN
   DECLARE @ReplyMsg NVARCHAR(100);
   SELECT @ReplyMsg =
   N'Reply Service Broker Message from AdventureWorksTarget';
  
   SEND ON CONVERSATION @RecvReqDlgHandle
     MESSAGE TYPE
    [http://www.servicebroker.com/ResponseType] (@ReplyMsg);

   END CONVERSATION @RecvReqDlgHandle;
 END

 SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;
GO

-- Examine the queues to see the response message back on the source queue, 
-- along with an EndDialog message for it. >
-- It is no longer in the target queue.
select * from AdventureWorks..[AdventureWorksSourceQueue]
select * from AdventureWorksTarget..[AdventureWorksTargetQueue]

7 Send message response & close the conversation -

-- read the response on the sender & close conversation.
use AdventureWorks
go

DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;

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

END CONVERSATION @RecvReplyDlgHandle;

-- Display recieved request.
SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;
GO

-- Examine the queues once more.
-- Having verified receipt of the message. the Source queue is empty again.
select * from AdventureWorks..[AdventureWorksSourceQueue]
select * from AdventureWorksTarget..[AdventureWorksTargetQueue]