Service Broker where we used;

Service broker in SQL server is a messaging system. This can be used to pass messages from different servers and can be routed thru the defined path. We can use Service Broker as a Queue system as well, obviously in First In First Serve logic.

          In one of the project we have implemented this only for queuing purpose. As per technical requirement we had to communicate some information between some modules in a project (system). These modules in the system can be treated as ‘Stand alone’ modules.

Here is the requirement: There is some daily data process which is part of one module. These data process are heavy and may take hours to get complete.  But during this process we have to do some communication to another system thru WCF, which will finally reach to SSRS subscription. The message receiving system is designed to do many functionality routing.

So what we have done is, we have created one Service Broker Queue in the database and the data-process activities will put a row to the SB-Queue. For messaging we have used a template called xslt. So which ever activity (procedures) need to send a message to other system have to put its own message in the pre-defined format (xslt) to SB-Queue.

To listen this SB-Queue, we have developed a windows-service application. This service will read the SB-Queue within a specified frequency (lets say 40 seconds), and will receive the message. Once this service received the message, it will be removed from the SB-Q. Based on the data in the message, this functionality-routing application will call the required WCF service and will pass the required parameter. Then the application flow will be continuing.

The major step to start work with Service Broker is

1.    Service Broker should be enabled in the database. By default this will be off.
To check Service broker status we can do a query
SELECT is_broker_enabled FROM master.sys.databases where name = DB_NAME()
2.    To enable Service Broker
use TestDB
ALTER DATABASE TestDB  SET ENABLE_BROKERwith rollback immediate
If it is throwing any master key related error, you need to create the master key
Create MasterKey Encryption by password = ‘password@123’


Now again try to enable it. If you want to check whether it is enabled or not use the below query
SELECT is_broker_enabled FROM master.sys.databases where name = DB_NAME()
use TestDB
Go
–select is_broker_enabled from sys.databases where name=DB_NAME()
–alter database Testdb set enable_broker with rollback immediate
CREATE XML SCHEMA COLLECTION [Test_Schemacollection]
AS
N'<xs:schema elementFormDefault=”qualified” targetNamespace=”http://www.manupradeep.com/ServiceBroker/” xmlns=”http://www.manupradeep.com/ServiceBroker/” xmlns:xs=”http://www.w3.org/2001/XMLSchema”>
  <xs:element name=”Start_action”>
    <xs:complexType>
      <xs:sequence>
        <xs:element name=”request” type=”RequestData” minOccurs=”0″ nillable=”true” />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
 
  <xs:complexType name=”RequestData”>
    <xs:sequence>
      <xs:element name=”TemplateName” type=”xs:string” nillable=”true” />
      <xs:element name=”CurrentUser” type=”xs:string” nillable=”true” />
      <xs:element name=”NamedArguments” minOccurs=”0″ nillable=”true”>
     
        <xs:complexType>
          <xs:sequence>
            <xs:element name=”NamedArgument” type=”NamedArgument” maxOccurs=”unbounded” minOccurs=”0″ />
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name=”NamedArgument”>
    <xs:sequence>
      <xs:element name=”Key” type=”xs:string” nillable=”true” />
      <xs:element name=”Value” type=”ClrValue” nillable=”true” />
    </xs:sequence>
  </xs:complexType>
  <xs:element name=”StartResponse”>
    <xs:complexType>
      <xs:sequence>
        <xs:element name=”StartResult” type=”ResponseData” minOccurs=”0″ nillable=”true” />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:complexType name=”ResponseData”>
    <xs:sequence>
      <xs:element name=”WorkflowId” type=”ClrValue” minOccurs=”0″ />
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name=”ClrValue”>
    <xs:simpleContent>
      <xs:extension base=”xs:string”>
        <xs:attribute name=”typeName” type=”xs:string” use=”required” />
      </xs:extension>
    </xs:simpleContent>
  </xs:complexType>
</xs:schema>’;
go
create MESSAGE TYPETest_Messagetype
VALIDATION = VALID_XML WITH SCHEMA COLLECTION [Test_Schemacollection];
GO
create MESSAGE TYPETest_ResponseMessagetype
VALIDATION = VALID_XML WITH SCHEMA COLLECTION [Test_Schemacollection];
GO
CREATE CONTRACT Test_Contract
(
Test_Messagetype SENT BY INITIATOR,
Test_ResponseMessagetype SENT BY TARGET
);
GO
—————————————————————————
CREATE PROCEDURE [dbo].[Test_HandleSubmitWorkflowResult]
AS
BEGIN
  DECLARE@RecvReqDlgHandle UNIQUEIDENTIFIER;
  DECLARE@RecvReqMsg NVARCHAR(max);
  DECLARE@RecvReqMsgName sysname;
  WHILE (1=1)
  BEGIN
    BEGIN TRANSACTION;
     
    WAITFOR
    ( RECEIVE TOP(1)
        @RecvReqDlgHandle = conversation_handle,
        @RecvReqMsg =message_body,
        @RecvReqMsgName = message_type_name
      FROM dbo.SpRightWorkFlowSendQueue
    ), TIMEOUT 5000;
    IF (@@ROWCOUNT = 0)
    BEGIN
      ROLLBACK TRANSACTION;
      BREAK;
    END
    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
         INSERT INTO dbo.SERVICEBROKER_ERRORS (CONVERSATION, ERROR_TEXT)
         VALUES (@RecvReqDlgHandle, @RecvReqMsg)
        
       END CONVERSATION @RecvReqDlgHandle;
    END
     
    COMMIT TRANSACTION;
  END
END
———————————————————————–
GO
CREATE QUEUE Test_SendQueue
      WITH ACTIVATION
      (
            STATUS = ON,           
            PROCEDURE_NAME = Test_HandleSubmitWorkflowResult,
            MAX_QUEUE_READERS = 1,
            EXECUTE AS N’dbo’
      );
GO
————————————–
CREATE QUEUE Test_ReceiveQueue;
————————————–
GO
CREATE SERVICE Test_SendService ONQUEUE Test_SendQueue (Test_Contract);
GO
CREATE SERVICE Test_ReceiveService ONQUEUE Test_ReceiveQueue(Test_Contract);
GO
————————————–
go
CREATE TYPE [dbo].[SB_Parameters] AS TABLE(
      [PARAMETER_NAME] [varchar](100) NOT NULL,
      [PARAMETER_CLR_TYPE] [varchar](20) NULL,
      [PARAMETER_VALUE] [varchar](4000) NULL,
      PRIMARY KEY CLUSTERED
(
      [PARAMETER_NAME] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
————————————–
go
CREATE FUNCTION [dbo].[SB_CreateXMLMessage] 
( 
 @templateName nvarchar(100), 
 @CurrentUser varchar(50),
 @namedArguments [dbo].[SB_Parameters] READONLY 
) 
RETURNS nvarchar(max) 
AS 
BEGIN 
 DECLARE@message nvarchar(max); 
 
 WITHXMLNAMESPACES (DEFAULT ‘http://www.manupradeep.com/ServiceBroker/’) 
  SELECT 
   @message = 
   ( 
    SELECT 
     @templateName AS“request/TemplateName”,
     @CurrentUser AS“request/CurrentUser”, 
     ( 
      SELECT 
       PARAMETER_NAME AS“Key”, 
       PARAMETER_CLR_TYPE AS “Value/@typeName”, 
       ISNULL(PARAMETER_VALUE, ) AS “Value” 
      FROM 
       @namedArguments 
      FOR XML PATH (‘NamedArgument’), type 
     ) AS “request/NamedArguments” 
    FOR XML PATH (‘Start_action’) 
   )  
 RETURN@message; 
end;
go
————————————————————–
CREATE PROCEDURE [dbo].[SB_SendActionRequest]
@Message NVARCHAR (MAX),@Conversation UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
       BEGINDIALOG CONVERSATION  @Conversation FROMSERVICE Test_SendService TO SERVICE ‘Test_ReceiveService’ ONCONTRACT Test_Contract WITHENCRYPTION = OFF;
       SENDON CONVERSATION@Conversation MESSAGE TYPETest_Messagetype (@Message);            

END
—*********************************************************************************

declare@ParameterValues dbo.SB_Parameters
declare@Conversation UNIQUEIDENTIFIER
declare @Message NVARCHAR(4000)
INSERT INTO @ParameterValues VALUES
(‘key1’, ‘System.String’, ‘Key1 value’)
,(‘key2’, ‘System.String’, ‘Key2 value’)
,(‘key3’, ‘System.String’, ‘Key3 value’)
EXEC  @message =dbo.SB_CreateXMLMessage‘Action Template1’,‘userid’,@ParameterValues
select cast(@message as xml)       
EXEC dbo.SB_SendActionRequest @Message , @Conversation OUTPUT
go
select * fromTest_ReceiveQueue
receive * fromTest_ReceiveQueue
To remove all the objects for this test

use TestDB
go
drop PROCEDURE [dbo].[SB_SendActionRequest]
go
drop function  [dbo].[SB_CreateXMLMessage]
go
drop type [SB_Parameters]
go
drop SERVICE Test_ReceiveService
go
drop SERVICE Test_SendService
go
drop QUEUE Test_SendQueue
go
drop QUEUE Test_ReceiveQueue
go
drop PROCEDURE [dbo].[Test_HandleSubmitWorkflowResult]
go
drop CONTRACT Test_Contract
go
drop MESSAGE TYPETest_ResponseMessagetype
go
drop MESSAGE TYPETest_Messagetype
go
DROP XML SCHEMA COLLECTION [Test_Schemacollection]