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.
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.
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
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)
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
–Create service and bind them to the queues
CREATE SERVICE Sender ON QUEUE SenderQ
CREATE SERVICE Receiver ON QUEUE ReceiverQ (MessageContract)
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)
–Receive a message from the queue
RECEIVE CONVERT (NVARCHAR(max), message_body) AS message FROM ReceiverQ
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
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.