Service Broker - Blogs
X

Service Broker

 

SQL Server Service broker provides asynchronous queuing functionality to SQL Server. So now the end client will not have to wait. SQL Server Service broker uses both queues and asynchronous communication to fulfill its functionality. Queues are used because there may be instances that the other application with which we are interacting may not available. With the help of asynchronous communication, client sends message to the other end application and he can continue with some other task without waiting for any notifications from the end client.

Essential Components

Following are the essential components of SQL Server Service Broker:-

 

1.       End-Points – The endpoints can be two applications running on different or same servers.

 

2.       Message – A message is an entity that is exchanged between Server Brokers. A message must have a name and data type. Optionally, a message can have a validation on that type of data. A message is part of a conversation and it has a unique identifier as well as a unique sequence number to enforce message ordering.

 

3.       Dialog- Dialog ensures proper ordered sequence of events at both ends for a message. If we want to encrypt our message then we need to create the dialog using “WITH ENCRYPTION” clause. It will create a session key that’s used to encrypt the messages sent between dialog.

 

4.       Conversation Group- Conversation Group is a logical grouping of Dialog. To complete a task we can need one or more dialog. The most difficult thing in an asynchronous message system is to maintain states. There is huge delay between arrivals of two messages. So conversation group maintains state using stable state using state table. There are two main purposes of having conversation group. Firstly, we can lock a conversation group during reading, so that no other process can read those queue entries. Secondly, its uses instance ID to identify messages in a group.

 

5.       Message Transport- Message transport defines how the messages will be send across networks. Message transport is based on TCP/IP and FTP. There are two basic protocols “Binary Adjacent Broker Protocol” which is like TCP/IP and “Dialog Protocol” which like FTP.

 

Implementation

1.       Create a Messagetype which describes how the message is formed. If the message type is XML we can also associate a schema with it

Image1

CREATE MESSAGE TYPE MessageType VALIDATION = NONE

GO

2.       Further we have to assign these Messagetype to Contract. Messagetype is grouped in Contracts. Contract is an entity which describes messages for a particular Dialog. So a contract can have multiple messagetype’s.

Image2

--Create message contract what type of users can send these messages at this moment we are defining current as an initiator

CREATE CONTRACT MessageContract (MessageType SENT BY INITIATOR)

  GO

 

3.       Contract are further grouped in service. Service has all the dialogs needed to complete one process.

 

4.       Service can further be attached to multiple queues. Service is the basic object from SQL Server Service broker point of view.

Image3

--Declare the two end points that's sender and receive queues

CREATE QUEUE SenderQ

CREATE QUEUE ReceiverQ

GO

Image4

--Create service and bind them to the queues

CREATE SERVICE Sender ON QUEUE SenderQ

CREATE SERVICE Receiver ON QUEUE ReceiverQ (MessageContract)

GO

 

1.       So when any client wants to communicate with a queue he opens a dialog with the service.

 

--Send message to the queue

DECLARE @conversationHandle UNIQUEIDENTIFIER

DECLARE @message NVARCHAR(100)

 

   BEGIN DIALOG @conversationHandle FROM SERVICE Sender TO SERVICE 'Receiver' ON CONTRACT MessageContract

   WITH ENCRYPTION = OFF

-- Sending message

   SET @message = N'Blog by Abhishek';

   SEND ON CONVERSATION @conversationHandle MESSAGE TYPE MessageType(@message)

GO

 

--Receive a message from the queue

RECEIVE CONVERT (NVARCHAR(max), message_body) AS message FROM ReceiverQ

GO

 

If the Service Broker is not enabled in the database you will get an error message. To enable the service broker you need to write the below query.

ALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER

 GO

 

Here, ServiceBrokerTest is the database name.

Client who wants to use the queues do not have to understand the complexity of queues. They only communicate with the logical view of SQL Server Service broker objects. 

ServiceBroker

 

Below is the query of the same which can be run directly.

CREATE DATABASE ServiceBrokerTest
 GO
USE ServiceBrokerTest
 GO
-- Enable Service Broker
ALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER
 GO
 
CREATE MESSAGE TYPE MessageType VALIDATION = NONE 
GO
 
--Create message contract what type of users can send these messages at this moment we are defining current as an initiator
CREATE CONTRACT MessageContract (MessageType SENT BY INITIATOR)
  GO
 
--Declare the two end points that's sender and receive queues
CREATE QUEUE SenderQ
CREATE QUEUE ReceiverQ
GO
  
--Create service and bind them to the queues
CREATE SERVICE Sender ON QUEUE SenderQ
CREATE SERVICE Receiver ON QUEUE ReceiverQ (MessageContract)
GO
 
--Send message to the queue
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message NVARCHAR(100)
 
BEGIN DIALOG @conversationHandle FROM SERVICE Sender TO SERVICE 'Receiver' ON CONTRACT MessageContract
WITH ENCRYPTION = OFF
-- Sending message
SET @message = N'Blog by Abhishek';
SEND ON CONVERSATION @conversationHandle MESSAGE TYPE MessageType(@message)
GO
 
--Receive a message from the queue
RECEIVE CONVERT (NVARCHAR(max), message_body) AS message FROM ReceiverQ
GO
USE master
 GO
DROP DATABASE ServiceBrokerTest
 GO
 

Related

Connect apps and integrate data with workflows using Azure Logic Apps

Implementing complex business process has been made easy. Logic Apps in azure provide a way to imple...

Read More >

TFS to VSTS Migrations

The next evolution of TFSWhether you’re a startup or Enterprise or you’re a team of just...

Read More >

CfMD services -Dynamics NAV

CfMD services help ISVs minimize the time and effort needed to get their software solution through t...

Read More >

Upcoming Webinar - Offshore support to North American Dynamics NAV partners

Offshore support to North American Dynamics NAV partners whose customers are moving to NAV '16Wh...

Read More >

Microsoft Azure Consultant

·         Executing technical feasibility as...

Read More >

Customer Feedback

Your feedback is important to us. Please share your experience working with Canarys & the journe...

Read More >

Threading

Multi-taskingIt is a concept of performing multiple tasks over a certain period of time by executing...

Read More >

Partnership Program

We understand that the world of technology is changing rapidly from day to day and businesses have m...

Read More >

Partnership Program

The Canarys Partner Program:Canarys is all set to expand its business reach across the Globe and is ...

Read More >

Upcoming Webinar US - Plugging for Cloud, Right or Wrong

Plugging for Cloud, Right or Wrong?Cloud computing continues to be one of the most advertising topic...

Read More >

Share

Try DevOpSmartBoard Ultimate complete Azure DevOps End-to end reporting tool

Sign Up

  • Recent
  • Popular
  • Tag
Monthly Archive
Subscribe
Name

Text/HTML
Contact Us
  • *
  • *