Tag Archives: Service Broker

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.

Enable Service Broker in SQL Server database

Enable Service Broker in SQL Server database

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)

Once you understand what Service Broker is and finally decides to implement it in your database, you have to enable Service Broker for you database. Let us have a look at it.

 --Enable service broker in SQL Server database for Adventureworks2012 database
 ALTER DATABASE Adventureworks2012 SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

-- TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the
 --database and the contents within it. setting it ON if service broker is connecting outside database/instance
 ALTER DATABASE Adventureworks2012 SET TRUSTWORTHY ON;
 GO
 

Once you enable service broker in your SQL Server database, you can confirm whether Service Broker enabled in your database or not. Execute following TSQL in your SQL Server instance.

 SELECT is_broker_enabled,* FROM sys.databases
 WHERE name='Adventureworks2012'
 GO
 

If you get “1” in “is_broker_enabled” field in above SELECT query than you are fine as Service Broker is already enabled now.

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.

 

Importance of Service Broker in SQL Server

Importance of Service Broker in SQL Server

Sooner or later every business process needs some asynchronous processes to cater the business needs and keep their application free from the heavy processes which can be tedious for the user if it directly processed from the application. It may decrease the performance of the application.

There are many asynchronous processes (like MSMQ, Web services, WCF and many more) but Service Broker is one of my favorite if I have setup of SQL Server for the application.

Service broker manages the queue as a database object and hence we get native support of SQL Server so that if we take a backup of SQL Server database, queue will be backed up with it.

Let me give you some situation where I have recently set up Service Broker.

I was working with one home health care management system of New York. Many agencies working with that SAAS (Software As A Service) product and we used to find thousands of concurrent user actively using that application in business hours. Every Wednesday each agency used to generate Invoice and billing for the insurance company as well as for staff because Friday is a payment day so invoice, pay slip etc. should be ready and calculated on Wednesday.

We used to get heavy database & network pressure at that time, out application keeps hanging and it shows time out error to many of the agencies too which is very annoying. We used to help them by creating invoices/bills manually directly from the backend but it is not good to approach client like this so we have finally decided to process all these calculations in form of service broker, as soon as client generates the request, we call Service broker and as soon as Service Broker completes process, it used to send an email to client so that they can get their bills/invoices from the application without making a pressure in application and getting timeout error.

This is really a power of asynchronous message processing.

I would like to share one more business requirement which forced me to set up service broker recently.

I was in involved in one project of one of the environmental laboratory in New Jeary, USA where they used to perform test for Air, Soil and Water. They have many different types of test used to perform on different types of samples. They define a special way to perform the test based on government norms as well as client’s demand. That special way (chemical to process the test) needs a combination of many different chemicals and other required stuff. Once they define this way, they named it “Recipe”. One Recipe may have many different chemicals as well as other Recipe too. While generating the report of Recipe, we needs to go infinite inside the content of Recipe as it may be single chemical or other Recipe and that other Recipe could be created from other Recipe. This Recipe report and calculation take too much time and hence intranet based .NET web application used to give time out error so many times and user gets frustrate so in this situation we have decided to use Service Broker so that user can put request for Recipe report from .NET application, Service Broker completes the process and send an email to user about completion.

This is how Services broker can be used. I will come up with one more article based on this topic very soon with the introduction of important part of Service Broker… stay tuned!!!

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.