Tag Archives: Queue

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.

Introduction of Service Broker in SQL Server

Introduction of Service Broker in SQL Server

Before I actually start making you understand about what Service Broker is, I have explained some practical scenario where I have introduced Service Broker recently. If you would like to know those scenarios, have a look at my earlier article “Importance of Service Broker”.

Service Broker is an asynchronous message processing framework build directly into the SQL Server. Following are four important objects of Service Broker.

1.) Message Types

2.) Contracts

3.) Queue

4.) Service Program

Message Types:  Message Types is the first and one of the important objects of SQL Server Service Broker. Message Type do the important job of validating message before sending it to queue. There are four type of message types available.

  • XML Validation against XML Schema
  • Well Formed XML
  • No Validation (no validation at all, especially for binary data)
  • Empty (there is no message body required)

Contracts: Contracts decides which message type suppose to be used by Service Broker for a particular task. In service broker we used to send message to the other service to process the message and Contract decides message type suppose to be used between two services. If you send the message type from one service to another service which is not defined in contract, message will be rejected. You can easily find the message types being used from the sender to receiver by looking at Contract definition.

Queue: Queue is a native SQL Server database object and very important part of SQL Server service broker. Queue stores message (not matter whether it is from target service or initiator service).  When Service Broker receives a message, It first gets validated by Message Types and after successful validation check, message used get stored in queue for further process. Queue is like a SQL Server table where you can even query to see the message but can’t execute DML statements explicitly to manipulate data for queue table manually. Since queue is basically a table of SQL Server, it comes with SQL Server database backup and restore.

Service Program: Service Program used to process message from the queue. When a new message arrives, Service Broker automatically calls service program defined. If internal activation is used, SQL Server Stored Procedure can be the service program whereas, when external activation is used, you can have your own separate application for service broker’s service program.

Well, these are some basic objects with brief introduction of Service Broker. I will come up with more information and practical stuff related to Service Broker soon.

Till than Stay tuned and enjoy SQL!!!!!

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.