.: SQL Server Service Broker one usage

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_BROKER with rollback immediate

If it is throwing any master key related error, you need to create the master key

Create Master Key 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

--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]

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>';

create MESSAGE TYPE Test_Messagetype

VALIDATION = VALID_XML WITH SCHEMA COLLECTION [Test_Schemacollection];

create MESSAGE TYPE Test_ResponseMessagetype

VALIDATION = VALID_XML WITH SCHEMA COLLECTION [Test_Schemacollection];

CREATE CONTRACT Test_Contract

(

Test_Messagetype SENT BY INITIATOR,

Test_ResponseMessagetype SENT BY TARGET

);

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

CREATE PROCEDURE [dbo].[Test_HandleSubmitWorkflowResult]

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

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

CREATE QUEUE Test_SendQueue

WITH ACTIVATION

(

STATUS = ON,

PROCEDURE_NAME = Test_HandleSubmitWorkflowResult,

MAX_QUEUE_READERS = 1,

EXECUTE AS N'dbo'

);

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

CREATE QUEUE Test_ReceiveQueue;

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

CREATE SERVICE Test_SendService ON QUEUE Test_SendQueue (Test_Contract);

CREATE SERVICE Test_ReceiveService ON QUEUE Test_ReceiveQueue (Test_Contract);

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

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)

)

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

CREATE FUNCTION [dbo].[SB_CreateXMLMessage]

(

@templateName nvarchar(100),

@CurrentUser varchar(50),

@namedArguments [dbo].[SB_Parameters] READONLY

)

RETURNS nvarchar(max)

BEGIN

DECLARE @message nvarchar(max);

WITH XMLNAMESPACES (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;

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

CREATE PROCEDURE [dbo].[SB_SendActionRequest]

@Message NVARCHAR (MAX), @Conversation UNIQUEIDENTIFIER OUTPUT

BEGIN

BEGIN DIALOG CONVERSATION @Conversation FROM SERVICE Test_SendService TO SERVICE 'Test_ReceiveService' ON CONTRACT Test_Contract WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @Conversation MESSAGE TYPE Test_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

select * from Test_ReceiveQueue

receive * from Test_ReceiveQueue

To remove all the objects for this test

use TestDB

drop PROCEDURE [dbo].[SB_SendActionRequest]

drop function [dbo].[SB_CreateXMLMessage]

drop type [SB_Parameters]

drop SERVICE Test_ReceiveService

drop SERVICE Test_SendService

drop QUEUE Test_SendQueue

drop QUEUE Test_ReceiveQueue

drop PROCEDURE [dbo].[Test_HandleSubmitWorkflowResult]

drop CONTRACT Test_Contract

drop MESSAGE TYPE Test_ResponseMessagetype

drop MESSAGE TYPE Test_Messagetype

DROP XML SCHEMA COLLECTION [Test_Schemacollection]

.

Tuesday, 3 April 2012

SQL Server Service Broker one usage

No comments:

Post a Comment