Tag Archives: Service

Setup service broker between two databases in SQL Server.

Setup service broker between two databases in SQL Server.

My earlier article “Service Broker implementation between two tables in one database”  explained how to setup service broker between two table in one database. Today I am explaining how to setup service broker between two tables resides in two different databases in one SQL Server instance.

I will be using the same table, queues, contracts, services and message types given in “Service Broker implementation between two tables in one database” article so if you have not read this article, I would recommend to take a look there once.

Creating two different databases in one SQL Server 2012 instance.

USE MASTER;
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'ExtremeAdviceSBMain')
DROP DATABASE ExtremeAdviceSBMain;
GO

CREATE DATABASE ExtremeAdviceSBMain;
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'ExtremeAdviceSBReceiver')
DROP DATABASE ExtremeAdviceSBReceiver;
GO

CREATE DATABASE ExtremeAdviceSBReceiver;
GO

--making broker enable for database.
ALTER DATABASE ExtremeAdviceSBMain  SET ENABLE_BROKER
ALTER DATABASE ExtremeAdviceSBMain  SET TRUSTWORTHY ON;
ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBMain TO [sa];
GO

--making broker enable for database.
ALTER DATABASE ExtremeAdviceSBReceiver SET ENABLE_BROKER
ALTER DATABASE ExtremeAdviceSBReceiver  SET TRUSTWORTHY ON;
ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBReceiver TO [sa];
GO

Let us now create basic architecture of service broker like message type, contract, queue and services in both databases ExtremeAdviceSBMain & ExtremeAdviceSBReceiver (target and initiator). Please note that I have used the same service broker object I have used in earlier article. We may eliminate few things but I will cover it up later on.

USE ExtremeAdviceSBReceiver
GO

--Create Message Type in target database
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML
GO

--Create Contract in target database
CREATE CONTRACT [EXTREME://EX/Contract/Order]
(
[EXTREME://EX/Message/Order/Request] SENT BY INITIATOR,
[EXTREME://EX/Message/Order/Response] SENT BY TARGET
);
GO

--Create Target Queue
CREATE QUEUE OrderTargetQueue
WITH STATUS = ON
GO

CREATE SERVICE [EXTREME://EX/Service/Order/Target]  ON QUEUE [OrderTargetQueue] ([EXTREME://EX/Contract/Order])
GO

USE ExtremeAdviceSBMain
GO

--Create Message Type in initiator database
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML
GO

--Create Contract in initiator database
CREATE CONTRACT [EXTREME://EX/Contract/Order]
(
[EXTREME://EX/Message/Order/Request] SENT BY INITIATOR,
[EXTREME://EX/Message/Order/Response] SENT BY TARGET
);
GO
--Create initiator Queue

CREATE QUEUE OrderInitiatorQueue
WITH STATUS = ON
GO

CREATE SERVICE [EXTREME://EX/Service/Order/Initiator]  ON QUEUE [OrderInitiatorQueue] ([EXTREME://EX/Contract/Order])
GO

Now we will create two tables. One table in initiator database which is OrderDetail. Sales person will generate sales order and data will falls under that table. If sales person inserting duplicate order, it will caught by trigger and will be inserted in OrderHistory table. For more detail about this concept, have a look at my earlier article.

USE ExtremeAdviceSBMain
GO

--two sample table for demonstration
CREATE TABLE OrderDetail
(
OrderID INT IDENTITY(1,1),
ClientID VARCHAR(10),
ProjectID VARCHAR(50),
OrderDate DATETIME DEFAULT GETDATE()
)
GO

USE ExtremeAdviceSBReceiver
GO

CREATE TABLE OrderHistory
(
ID INT IDENTITY(1,1),
OrderID INT,
DuplicateWith INT,
CheckDate DATETIME DEFAULT GETDATE()
)
GO

Now we will create main logic which will send and process original message along with trigger on OrderDetail table which will activate service broker as soon as new record inserted in OrderDetail table.

USE ExtremeAdviceSBMain
GO
--Create this SP in Initiator database
--Create SP which will be called from app
--or trigger or by any other way
--and activate service broker
CREATE PROCEDURE [SendOrderRequestMessage]
(
@data AS XML
)
AS
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRANSACTION

DECLARE @DialogHandle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @DialogHandle
FROM SERVICE [EXTREME://EX/Service/Order/Initiator]
TO SERVICE N'EXTREME://EX/Service/Order/Target'
ON CONTRACT [EXTREME://EX/Contract/Order]
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Request](@data);
--END CONVERSATION @DialogHandle
COMMIT ;

END TRY

BEGIN CATCH
ROLLBACK TRAN;
DECLARE @msg VARCHAR(MAX);
SELECT @msg = ERROR_MESSAGE();
PRINT @msg
END CATCH;
GO

USE ExtremeAdviceSBReceiver
GO

--Create this SP in target database
-- Create actual business logic SP
CREATE PROCEDURE [CheckOrderMessage]
(
@data XML
)
AS
BEGIN TRY

DECLARE @MessageType VARCHAR(50)
,@OrderID INT
,@ClientID VARCHAR(10)
,@ProjectID VARCHAR(50)

SELECT  @MessageType = @Data.value('(OrderMessage/Header/@MessageType)[1]','VARCHAR(50)'),
@OrderID = @data.value('(OrderMessage/Header/@OrderID)[1]','INT'),
@ClientID = @Data.value('(OrderMessage/Header/@ClientID)[1]','VARCHAR(10)'),
@ProjectID = @data.value('(OrderMessage/Header/@ProjectID)[1]','VARCHAR(50)')

SELECT @MessageType = ISNULL(@MessageType,'')

IF @MessageType = 'OrderHistory' AND @OrderID IS NOT NULL BEGIN
DECLARE @OrderIDOld INT
,@ClientIDOld VARCHAR(10)
,@ProjectIDOld VARCHAR(50)

SELECT @ClientIDOld=ClientID, @ProjectIDOld=ProjectID, @OrderIDOld=OrderID
FROM   ExtremeAdviceSBMain.dbo.OrderDetail
WHERE ClientID=@ClientID AND ProjectID=@ProjectID AND OrderID<>@OrderID

IF(@OrderIDOld IS NOT NULL)
BEGIN
INSERT INTO OrderHistory (OrderID,DuplicateWith)
SELECT @OrderIDOld,@OrderID
END
RETURN
END
END TRY

BEGIN CATCH
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
END CATCH
RETURN
GO

--Create Target queue reader SP
CREATE PROC [OrderTargetQueueReader]
AS
SET NOCOUNT ON;

DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY
WHILE (1 = 1)
BEGIN
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderTargetQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0)
BEGIN
BREAK
END
SET @XML = CAST(@MessageBody AS XML);
IF @MessageTypeName = 'EXTREME://EX/Message/Order/Request'
BEGIN
--PRINT 'executing CheckOrderMessage @XML'

EXECUTE CheckOrderMessage @XML

--PRINT 'sending response'

SELECT @xml = 'OK';

SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Response](@xml);
--PRINT 'ending cnversation'
END CONVERSATION @ConversationHandle;
END
END
END TRY
BEGIN CATCH

DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()

SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg
SELECT @msg
END CATCH
GO

USE ExtremeAdviceSBMain
GO

--create following SP to handle response of target in initiatordatabase
--Create Initiator queue reader SP
CREATE PROC [OrderInitiatorQueueReader]
AS
SET NOCOUNT ON;
DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY

WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderInitiatorQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0 AND @@TRANCOUNT>0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END

IF @MessageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN
END CONVERSATION @ConversationHandle;
END
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()
SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg
END CATCH
GO

-------service broker call from trigger.
CREATE TRIGGER trg_OrderDetail_Ins
ON  OrderDetail
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Data XML
SELECT @Data =
(
SELECT
'OrderHistory' AS 'Header/@MessageType',
OrderID AS 'Header/@OrderID',
ClientID AS 'Header/@ClientID',
ProjectID AS 'Header/@ProjectID'
FROM
(SELECT * FROM inserted) AS T
FOR XML PATH(''), ROOT('OrderMessage'), TYPE
)

EXEC [SendOrderRequestMessage] @Data
END
GO

Once you are ready with this structure, you are all set to go. Let us insert one record in OrderDetail table.

USE ExtremeAdviceSBMain
GO

--Testing SB by inserting one record in OrderDetail Table
--which will execute Trigger and trigger will activate SB
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record which we have inserted above.
SELECT * FROM OrderDetail
GO

--you will get no record in OrderHistory table
--as we have not inserted duplicate record yet.
SELECT * FROM  ExtremeAdviceSBReceiver.dbo.OrderHistory
GO

Since we have not inserted any duplicate records yet, we won’t get any record in OrderHistory table. Let us inserting duplicate record in OrderDetail table.

--inserting same record again so SB will detect it as duplicate order
--that will be inserted in ORderHistory table
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record in OrderHistory table
--as we have inserted duplicate record
SELECT * FROM ExtremeAdviceSBReceiver.dbo.OrderHistory
GO

We, now, suppose to get record in OrderHistory table but unfortunately we won’t get it. There are two possibilities that either our initiator queue doesn’t have sent message to target or target have received the record but it has not processed it. Right???? Let us check it.

SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBMain.DBO.OrderInitiatorQueue
SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBReceiver.DBO.OrderTargetQueue
GO

You can see that our “OrderTargetQueue” has record to process. Then question is why it has not processed?

Well, we have had Trigger on OrderDetail table which has activated our initiator queue and hence record reached to target queue but target queue has not been activated and hence it has not processed target message.

We can process message with following SP either manually or by sql server job as we have not activate it automatically.

 USE ExtremeAdviceSBReceiver
 GO

EXEC [OrderTargetQueueReader]
GO

Now let us see whether record is processed or not.

SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBMain.DBO.OrderInitiatorQueue
SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBReceiver.DBO.OrderTargetQueue
GO

You can see “OrderTargetQueue”has not record now as our message is processed and you can also see the entry in “OrderHistory” table also as we had one duplicate record.

Still “OrderInitiatorQueue” has record to process. You can see that it is just a blank message for end dialog with “OK” status which has sent back by target queue after processing messages.

We can run following SP to process end dialog in initiator queue.

USE ExtremeAdviceSBMain
GO

EXEC [OrderInitiatorQueueReader]
GO

I will shortly come up with article which will run full service broker cycle by its own without manual interaction.

If you wish to refer my other Service Broker related article, have a look at following list:

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
  • Service Broker implementation between two tables in one database (Click Here)
  • Service Broker Queue Activation and Thread in SQL Server (Click Here)

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.

Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server

Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server

After reading one of my recent article regarding “Small demonstration of Service broker for beginners”, one of the blog reader has asked me very interesting question. He asked me that though he has created all queues, message types etc. demonstrated in “Small demonstration of Service broker for beginners” article but how can he see list of all objects related to the particular contract? Is there any DMV which gives all these information?

I have created one small SELECT script based on the following system catalog view which caters the need of the question given above.

  • sys.service_contract_message_usages
  • sys.service_message_types
  • sys.service_contracts
  • sys.service_contract_usages
  • sys.services
  • sys.service_queue_usages
  • sys.service_queues

Here is the query I have created based on all the system catalog view given above.


SELECT

ServiceContract.name AS 'ContractName',

ServiQueue.name AS 'QueueName',

Servi.name AS 'ServiceName',

MessageType.name AS 'MessageType',

MessageUsage.is_sent_by_initiator,

MessageUsage.is_sent_by_target,

MessageType.validation,

MessageType.validation_desc

FROM sys.service_contract_message_usages AS MessageUsage

INNER JOIN sys.service_message_types AS MessageType ON MessageUsage.message_type_id =MessageType.message_type_id

INNER JOIN sys.service_contracts AS ServiceContract ON ServiceContract.service_contract_id =MessageUsage.service_contract_id

INNER JOIN sys.service_contract_usages ServContractUse ON ServContractUse.service_contract_id =ServiceContract.service_contract_id

INNER JOIN sys.services AS Servi ON Servi.service_id=ServContractUse.service_id

INNER JOIN sys.service_queue_usages AS SerQueueUse ON SerQueueUse.service_id = Servi.service_id

INNER JOIN sys.service_queues AS ServiQueue ON ServiQueue.object_id=SerQueueUse.service_queue_id

--you can query your own contract name or even remove WHERE clause

--to see complete list of Service Broker objects in database

WHERE ServiceContract.name like '%Extreme%'

ORDER By MessageType,QueueName

GO

Here is the list I have received:

ServiceBrokerObjectList

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.

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:

1ServiceBrokerObjectTree

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:

2TargetQueueHiddenTable

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.