CODEWITHSUNDEEP
sqlsql-servermessage-queueservice-brokerloose-coupling
TharunRaja
TharunRaja

Reputation: 707

Async procedure call using service broker in Sql Server

I have a backup procedure (production) running daily on a schedule and the backups generated are used periodically to refresh the lower environments. I'm building an automated solution to download .bak files to lower environments (different server). I'm reluctant to use MIRROR TO option in backup as it may effect the normal backup operation if something fails in mirror to . I here want to use service broker and asynchronously trigger file copy from prod to lower environment at the end of the backup procedure. I've had put lot of effort in understanding service broker , message type, service , queue and contract. Everything's fine until here. Now I just want to understand on how to trigger filecopy procedure with some custom logic at the end of backup using service broker. I've played around with the following script to understand service broker. Someone kindly guide me on how to build a solution out of this. 

------------------------------------------------------------SETUP--------------------------------------------

CREATE MESSAGE TYPE [//SBTest/SBSample/RequestMessage] VALIDATION=WELL_FORMED_XML;

CREATE MESSAGE TYPE [//SBTest/SBSample/ReplyMessage] VALIDATION=WELL_FORMED_XML; 
-------------------------------------------------------------------------------------------------------------
CREATE CONTRACT [//SBTest/SBSample/SBContract]
(
[//SBTest/SBSample/RequestMessage] SENT BY INITIATOR ,
[//SBTest/SBSample/ReplyMessage] SENT BY TARGET 
);
-------------------------------------------------------------------------------------------------------------
CREATE QUEUE SBInitiatorQueue; 

CREATE QUEUE SBTargetQueue; 
-------------------------------------------------------------------------------------------------------------
CREATE SERVICE [//SBTest/SBSample/SBInitiatorService] ON QUEUE SBInitiatorQueue; 

CREATE SERVICE [//SBTest/SBSample/SBTargetService] ON QUEUE SBTargetQueue ([//SBTest/SBSample/SBContract]); 

-------------------------------------------------------------------------------------------------------------





-------------------------------------------------------INITIATE QUE-----------------------------------------
DECLARE @InitDlgHandle UNIQUEIDENTIFIER
DECLARE @RequestMessage VARCHAR(1000) 

BEGIN TRAN 

--Determine the Initiator Service, Target Service and the Contract 

BEGIN DIALOG @InitDlgHandle
FROM SERVICE [//SBTest/SBSample/SBInitiatorService] TO SERVICE'//SBTest/SBSample/SBTargetService'
ON CONTRACT
[//SBTest/SBSample/SBContract]
WITH ENCRYPTION=OFF; 


--Prepare the Message
SELECT @RequestMessage = N'HAHA'; 


--Send the Message
SEND ON CONVERSATION @InitDlgHandle 
MESSAGE TYPE
[//SBTest/SBSample/RequestMessage]
(@RequestMessage);

SELECT @RequestMessage AS SentRequestMessage;

COMMIT TRAN 
---------------------------------------------------------------------------------------------------------------






-------------------------------------------------------READ QUE-------------------------------------------------

\DECLARE @TargetDlgHandle UNIQUEIDENTIFIER
DECLARE @ReplyMessage VARCHAR(1000)
DECLARE @ReplyMessageName Sysname 

BEGIN TRAN; 

--Receive message from Initiator
RECEIVE TOP(1)
@TargetDlgHandle=Conversation_Handle, @ReplyMessage=Message_Body, @ReplyMessageName=Message_Type_Name
 FROM SBTargetQueue; 

SELECT @ReplyMessage AS ReceivedRequestMessage; 

-- Confirm and Send a reply
IF @ReplyMessageName=N'HAHA'

BEGIN
DECLARE @RplyMsg VARCHAR(1000)

SELECT @RplyMsg =N'HI'; 

SEND ON CONVERSATION @TargetDlgHandle
MESSAGE TYPE
[//SBTest/SBSample/ReplyMessage]
(@RplyMsg);
END CONVERSATION @TargetDlgHandle;

END 

SELECT @RplyMsg AS SentReplyMessage; 

COMMIT TRAN;

-------------------------------------------------------------------------------------------------------------------

Upvotes: 0

Views: 1677

Answers (1)

Richard Cupples
Richard Cupples

Reputation: 196

The Triggering occurs using "Internal Activation". You need to create a stored procedure that will be triggered when a message enters the queue, reads from the queue, then does the work. Adapted from the link:

CREATE PROCEDURE TargetActivProc
AS
  DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
  DECLARE @RecvReqMsg NVARCHAR(100);
  DECLARE @RecvReqMsgName sysname;

  WHILE (1=1)
  BEGIN

    BEGIN TRANSACTION;

    WAITFOR
    ( RECEIVE TOP(1)
        @RecvReqDlgHandle = conversation_handle,
        @RecvReqMsg = message_body,
        @RecvReqMsgName = message_type_name
      FROM TargetQueueIntAct
    ), TIMEOUT 5000;

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

    IF @RecvReqMsgName =
       N'//SBTest/SBSample/RequestMessage'
    BEGIN

      --do work here
    END
    ELSE IF @RecvReqMsgName =
        N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
       END CONVERSATION @RecvReqDlgHandle;
    END
    ELSE IF @RecvReqMsgName =
        N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
    BEGIN
       END CONVERSATION @RecvReqDlgHandle;
    END

    COMMIT TRANSACTION;

  END
GO

The next part is to enable activation on the queue

ALTER QUEUE SBTargetQueue
    WITH ACTIVATION
    ( STATUS = ON,
      PROCEDURE_NAME = TargetActivProc,
      MAX_QUEUE_READERS = 1,
      EXECUTE AS SELF
    );

The procedure will be activated when a message arrives in the queue and will remain activated and looping until the queue is empty.

Upvotes: 2

Related Questions