Small demonstration of Service broker for beginners

Small demonstration of Service broker for beginners

If you are new to Service Broker and want to know what service broker is and where can we use service broker, you can have a look at my two earlier articles.

Importance of Service Broker in SQL Server (Click Here)

Introduction of Service Broker in SQL Server (Click Here)

If your database is not already enabled for Service Broker, have a look at following link to know how to enable Service Broker for database in SQL Server.

Enable Service Broker in SQL Server database (Click Here)

Once you are ready with basic understanding of Service broker, we can, now, focus on developing architecture for service broker. This article will show you very basic architecture of service broker without much complexity so that even beginners can understand it easily, later on I will come up with some exciting and complex architecture of Service Broker.

Creating sample database and enable service broker for it.

USE MASTER
GO

CREATE DATABASE ExtremeAdviceSB
GO

ALTER DATABASE ExtremeAdviceSB SET Enable_Broker WITH ROLLBACK IMMEDIATE
GO

Now, creating two different message type. One for request message for initiator service and another one for response message for target service.

Note: If you are not aware with “Message Type” and other service broker related terms, please click here.

USE ExtremeAdviceSB
GO

CREATE MESSAGE TYPE [http://Extreme-Advice.com/SBDemo01/RequestMessage]
VALIDATION = WELL_FORMED_XML
GO

CREATE MESSAGE TYPE [http://Extreme-Advice.com/SBDemo01/ResponseMessage]
VALIDATION = WELL_FORMED_XML
GO

Now, we will create one contract which will define which message type will be used from initiator as well as from target.

CREATE CONTRACT [http://Extreme-Advice.com/SBDemo01/ExtremeAdviceContractDemo]
(
[http://Extreme-Advice.com/SBDemo01/RequestMessage] SENT BY INITIATOR,
[http://Extreme-Advice.com/SBDemo01/ResponseMessage] SENT BY TARGET
)
GO

Once, you are ready with Contract, it is a time to define two queue 1.) Initiator Queue 2.) Target Queue. Which will be used by core logic or service program. Initiator queue will generate message/request which will be sent to target queue after validation. Target queue will process message/request and generate response message and send to initiator queue.

CREATE QUEUE SBDemoInitiatorQueue
WITH STATUS = ON
GO

CREATE QUEUE SBDemoTargetQueue
WITH STATUS = ON
GO

After having both the queue, need to define service which will work for the same contract we have created above in our example.

CREATE SERVICE SBDemoInitiatorService
ON QUEUE SBDemoInitiatorQueue
(
[http://Extreme-Advice.com/SBDemo01/ExtremeAdviceContractDemo]
)
GO

CREATE SERVICE SBDemoTargetService
ON QUEUE SBDemoTargetQueue
(
[http://Extreme-Advice.com/SBDemo01/ExtremeAdviceContractDemo]
)
GO

Well now we are ready with basic architecture of service broker for our first demonstration for beginners. Here is the screen capture from my SSMS which shows every object I have created above. Have a look:

We will generate two message from initiator and send it to target with “BEGIN DIALOG CONVERSATION” command.

--Message 1
BEGIN TRY
BEGIN TRANSACTION;

DECLARE @UniId UNIQUEIDENTIFIER
DECLARE @SBMessage NVARCHAR(MAX);

BEGIN DIALOG CONVERSATION @UniId
FROM SERVICE SBDemoInitiatorService
TO SERVICE 'SBDemoTargetService'
ON CONTRACT [http://Extreme-Advice.com/SBDemo01/ExtremeAdviceContractDemo]
WITH ENCRYPTION = OFF;
SET @SBMessage =
'<SBRequest>
Message 1: Extreme-Advice.com on Service Broker
</SBRequest>';

SEND ON CONVERSATION @UniId MESSAGE TYPE
[http://Extreme-Advice.com/SBDemo01/RequestMessage]
(
@SBMessage
);
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--Message 2
BEGIN TRY
BEGIN TRANSACTION;

DECLARE @UniId UNIQUEIDENTIFIER
DECLARE @SBMessage NVARCHAR(MAX);

BEGIN DIALOG CONVERSATION @UniId
FROM SERVICE SBDemoInitiatorService
TO SERVICE 'SBDemoTargetService'
ON CONTRACT [http://Extreme-Advice.com/SBDemo01/ExtremeAdviceContractDemo]
WITH ENCRYPTION = OFF;
SET @SBMessage =
'<SBRequest>
Message 2: Extreme-Advice.com on Service Broker
</SBRequest>';

SEND ON CONVERSATION @UniId MESSAGE TYPE
[http://Extreme-Advice.com/SBDemo01/RequestMessage]
(
@SBMessage
);

COMMIT;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

Since we have generated two message from TSQL given above, we can confirm the same with “SBDemoTargetQueue” hidden table of target queue.

SELECT CAST(message_body as XML) AS Message, * FROM SBDemoTargetQueue
GO

Here is the screen capture of above SELECT statement:

Now, we have to message received in target queue which we have seen above. Now, question is, how to process it?

Well, we have “RECEIVE” command to process the record by reading “SBDemoTargetQueue” hidden table. Here is the code which will process one record from target queue table. Since we have two records in target queue, we have to execute following TSQL two times to get both record processed.

DECLARE @ConvGroupID UNIQUEIDENTIFIER
DECLARE @ConvHandleID UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE @messagebody XML;
BEGIN TRY
BEGIN TRANSACTION;

RECEIVE TOP (1)
@ConvGroupID = conversation_group_id,
@ConvHandleID = conversation_handle,
@messagetypename = message_type_name,
@messagebody = CAST(message_body AS XML)
FROM SBDemoTargetQueue

IF (@@ROWCOUNT > 0)
BEGIN
PRINT 'MessageType: ' + @messagetypename
PRINT 'MessageXML: ' + CAST(@messagebody AS NVARCHAR(MAX))
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

Once you are done with both the message from target queue, you can confirm that there is nothing left in your target queue’s hidden table.

SELECT CAST(message_body as XML) AS Message, * FROM SBDemoTargetQueue
GO

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.