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

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.

–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.

–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

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. 

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