Service Broker is a process of sending and receiving asynchronous messages by using T-SQL commands.
It’s similar to other queuing technologies. Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server.
Data is usually sent as an XML data and you can also encrypt your message during transmission
As per my point of view Service broker can use to achieve following functionality
- Asynchronous process
- Decoupling execution from caller
- Distributed Server-Side Processing
Asynchronous provide you event-driven model. Asynchronous processes do not depend on other process. Its work independently on different threads simultaneously.
Decoupled execution allows components to remain completely autonomous, unaware of each other and process by itself.
Large application use multiple SQL Server to manage client application, Service broker can be used to communicate and pass data between those multiple SQL Server
Real world example where you can use SQL service broker
- In database we use trigger to perform particular task after insert, update or delete. Those task will execute synchronously and if trigger has lots of T-SQL statement then it’s become time consuming work. So in this situation we can use service broker for asynchronous execution.
- Service broker doesn’t support publish-subscribe framework but you can play around service broker component to implement publish-subscribe pattern
- Ordering web application could use service broker on the server side to send information to different database that might contain data related to inventory, customer and update other database based on message.
There are two part of the service broker end to end process
- Configuring the Service broker component
- Sending and Receiving Messages
Configuring the Service Broker
Below image help you to find out service broker component on SQL Server
Service broker components need to configure to start working on service broker. Below are step by step guideline to configure service broker component.
- Enable the Service Broker on the database
- Create valid Message Types.
- Create a Contract for the Conversation.
- Create Queues for the Communication.
- Create Services for the Communication.
1. Enabling Service Broker
Service broker is database level feature. It is not a SQL server instance level feature so that you need to enable it on the particular database on which you want to sue service broker feature.
Following is SQL Query which can be used to enable service broker on particular database
USE master
ALTER DATABASE [DatabaseName]
SET ENABLE_BROKER;
To verify whether service broker is enable or not on particular database you can use following query
SELECT is_broker_enabled FROM sys.databases
WHERE name = [DatabaseName]
Following screenshot can also useful to know whether service broker is enable or not
2. Create Valid Message Types
Message Types need to specific to send and receive messages. The initiator and the target will use the same message type to communicate between them. You can create as much as message type in database that participant in a conversation.
Following query will use to create Message Type of service broker
USE [DatabaseName]
CREATE MESSAGE TYPE [MessageTypeName]
VALIDATION=WELL_FORMED_XML;
Validation can be specified upon the content of the Message Type, most commonly used validation is WELL_FORMED_XML
3. Create a Contract for the Conversation
Service broker requires a contract to send and receive message. It also define Message type that is used during service broker conversion and also define which side of conversion can be send a message.
Following T-SQL statement used to create contract
USE [DatabaseName]
CREATE CONTRACT [ContractName] ([MessageTypeName] SENT BY INITIATOR);
4. Create Queues for the Communication
Service broker queue used for storing message during sending and receiving. There should be two queue one sender and other for receiver
Following T-SQL statement used to create queue one for initiator and one for target
USE [DatabaseName]
CREATE QUEUE [InitiatorQueueName];
CREATE QUEUE [TargetQueueName];
5. Create Services for the Communication
Service broker Services route the message to particular queue. When the initiator or the target send a message, it will route the message to specific queue. So, we will define each service with their appropriate queue.
Following T-SQL statement is used for create service for initiator and target
USE [DatabaseName]
CREATE SERVICE [InitiatorServiceName]
ON QUEUE [InitiatorQueueName] ([ContractName]);
CREATE SERVICE [TargetServiceName]
ON QUEUE [TargetQueueName] ([ContractName]);
Sending and Receiving Messages
Once service broker is setup on you SQL server now you can send message between initiator and target queue using service broker component
For sending message to target you need to first determine the service and contract then prepare message and send the message.
Following T-SQL statement is used to send message to receiver queue
Determine service and contract using @Dialog on initiator side and send message
DECLARE @Dialog UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @Dialog
FROM SERVICE [InitiatorServiceName]
TO SERVICE [TargetServiceName]
ON CONTRACT [ContractName]
WITH ENCRYPTION = OFF
DECLARE @Message NVARCHAR (128)
SET @Message = ‘Hello World’;
SEND ON CONVERSATION @Dialog
MESSAGE TYPE [MessageTypeName] (@Message)
Receive Message
RECEIVE CONVERT (NVARCHAR (MAX), message_body) AS Message
FROM [TargetQueueName]
SQL Example: Send and Receive “Hello World” service broker example
--------------------------------------------------------------------------------------------------------------------
-- This example configure service broker and performing sending and receiving “Hello World” message
-------------------------------------------------------------------------------------------------------------------
CREATE DATABASE SBHelloWorld
GO
USE SBHelloWorld
GO
-------------------------------------------
-- Configure service broker
--------------------------------------------
-- 1. Enable the Service Broker on the database
ALTER DATABASE SBHelloWorld SET ENABLE_BROKER
GO
-- 2. Create valid Message Types
CREATE MESSAGE TYPE SBMessageTest VALIDATION = NONE
GO
-- 3. Create a Contract for the Conversation
CREATE CONTRACT SBContractTest (SBMessageTest SENT BY INITIATOR)
GO
-- 4.a. Create Sender Queues for the Communication
CREATE QUEUE SBSenderQueueTest
GO
-- 4.b. Create receiver queue for the communication
CREATE QUEUE SBReceiverQueueTest
GO
-- 5.a. Create sender services for the communication
CREATE SERVICE SBSenderServiceTest ON QUEUE SBSenderQueueTest (SBContractTest)
GO
-- 5.b. Create services for the communication
CREATE SERVICE SBReceiverServiceTest ON QUEUE SBReceiverQueueTest (SBContractTest)
GO
------------------------------------------------------------
-- Send and receiver message over service broker
------------------------------------------------------------
DECLARE @SBDialogTest uniqueidentifier
DECLARE @Message NVARCHAR(128)
BEGIN DIALOG CONVERSATION @SBDialogTest
FROM SERVICE SBSenderServiceTest
TO SERVICE 'SBReceiverServiceTest'
ON CONTRACT SBContractTest
WITH ENCRYPTION = OFF
-- Sending Message
SET @Message = N'Hello World';
SEND ON CONVERSATION @SBDialogTest MESSAGE TYPE SBMessageTest (@Message)
-- View messages from Receive Queue
SELECT CONVERT(NVARCHAR(MAX), message_body) AS Message FROM SBReceiverQueueTest
GO
-- Receive Message
RECEIVE TOP(1) CONVERT(NVARCHAR(MAX), message_body) AS Message FROM SBReceiverQueueTest
GO
-- Drop database
USE master
GO
DROP DATABASE SBHelloWorld
GO
Some useful information that will help you out while working on service broker
—- Checking out queue
SELECT TOP 1000 *, casted_message_body =
CASE message_type_name WHEN ‘X’
THEN CAST(message_body AS NVARCHAR(MAX))
ELSE message_body
END
FROM [QueueName] WITH(NOLOCK)
—- Checking out transmission queue
SELECT * FROM sys.transmission_queue
Service Broker uses a transmission queue as a holding area for messages. Outgoing messages are added to the transmission queue in the database that sends the message. The message remains in the transmission queue until the destination has acknowledged receipt of the message.
References:
- Microsoft TechNet
- SQL Authority with pinal dave
