Using mssql Service Broker in Service-Oriented Architecture

Main principle of SOA is separation between the service interface and it’s implementation. In order to achieve that separation on the database level we can use Mssql Service Broker messaging.

Using Service Broker allows us to send internal or external messages between or withing the mssql database. We can also use the massages to activate stored procedure and execute tasks or even external web service by invoking c# custom assembly method. Internal messaging lets us trigger asynchronous events that we can use to perform time consuming operations or integration tasks (for example by activation database trigger that starts the event chain).

In our example I will show you how to execute time consuming task asynchronously. It is especially useful when you need to calculate a lot of data when some table content changes (trigger events).

Below is the example of triggering time consuming procedure synchronously and asynchronously using Service Broker.

In order to implement our solution we need to first create self activating stored procedure that will be activated by service broker queue each time we send the message to.

 CREATE PROCEDURE proc_BrokerTargetActivProc
 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 TASK_QUEUE
    ), TIMEOUT 5000;

    IF (@@ROWCOUNT = 0)
    BEGIN
      ROLLBACK TRANSACTION;
      BREAK;
    END
     
    declare @targetpProcedure nvarchar(100);
    set @targetpProcedure = CAST(@RecvReqMsg AS nvarchar(100))
    
    IF LEN(@targetpProcedure) > 0
    BEGIN
        --execute target procedure (no need to send the reply)
       exec @targetpProcedure;
     
    END
    ELSE IF @RecvReqMsgName =
        N'https://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
       END CONVERSATION @RecvReqDlgHandle;
    END
    ELSE IF @RecvReqMsgName =
        N'https://schemas.microsoft.com/SQL/ServiceBroker/Error'
    BEGIN
       END CONVERSATION @RecvReqDlgHandle;
    END
      
    COMMIT TRANSACTION;

  END
GO

When we have the activation procedure ready, the next step is to create basic Service Broker objects we will use when sending the messages.

CREATE MESSAGE TYPE [//SBM/RequestMessage] VALIDATION=WELL_FORMED_XML; 
CREATE MESSAGE TYPE [//SBM/ReplyMessage] VALIDATION=WELL_FORMED_XML;

CREATE CONTRACT [//SBM/MSGContract]
(
 [//SBM/RequestMessage]
 SENT BY INITIATOR 
  ,[//SBM/ReplyMessage]
 SENT BY TARGET 
);  

CREATE QUEUE TASK_QUEUE
 WITH STATUS=ON,
 ACTIVATION (
  PROCEDURE_NAME = proc_BrokerTargetActivProc,
  MAX_QUEUE_READERS = 5,
  EXECUTE AS SELF);

CREATE SERVICE [//SBM/TargetService] ON QUEUE TASK_QUEUE([//SBM/MSGContract]);
CREATE SERVICE [//SBM/InitService] ON QUEUE TASK_QUEUE([//SBM/MSGContract]);

The final step is to create helper procedure that will be sending activating messages to the queue passing in the target procedure’s name to be executed.

CREATE PROCEDURE proc_ExecuteProcedureAsync
 @ProcedureName nvarchar(100) = ''
 AS

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg xml;
BEGIN TRANSACTION;
BEGIN DIALOG @InitDlgHandle
     FROM SERVICE
      [//SBM/InitService]
     TO SERVICE
      N'//SBM/TargetService'
     ON CONTRACT
      [//SBM/MSGContract]
     WITH
         ENCRYPTION = OFF;
 
 SELECT @RequestMsg = @ProcedureName;
       
--Send the Message
SEND ON CONVERSATION @InitDlgHandle MESSAGE TYPE [//SBM/RequestMessage](@RequestMsg);

--Close conversation
END CONVERSATION @InitDlgHandle;

COMMIT TRANSACTION;

I have included complete sql script below that will create all structures and procedures for you. Give it a try.
SetUpServiceBroker1