SQL Server Service Broker

Overview

SQL Server Service Broker is a robust, reliable, and scalable messaging system built directly into SQL Server. It enables applications to communicate by sending and receiving messages. This allows for decoupled architectures, where different parts of an application can operate independently, improving performance, scalability, and fault tolerance.

Key concepts include:

• Services: An endpoint for sending and receiving messages.
• Queues: A table that holds messages until they are processed.
• Message Types: Define the format and structure of messages.
• Contracts: Define the set of message types that can be exchanged between services.
• Conversations: A logical dialog between two services for exchanging messages.
• Brokers: The underlying infrastructure that routes and manages messages.

Core T-SQL Commands

Service Broker introduces several new T-SQL commands for managing its features:

Creating Services and Queues

Use the CREATE SERVICE and CREATE QUEUE statements to set up communication endpoints.

-- Create a queue to hold messages
CREATE QUEUE MyQueue;

-- Create a service that uses the queue
CREATE SERVICE MyService
AUTHORIZATION dbo
ON QUEUE MyQueue
([http://schemas.microsoft.com/sqlserver/Soap/ServiceBroker/DefaultCollection]); -- Example of a message contract

Sending and Receiving Messages

The SEND ON CONVERSATION and RECEIVE FROM statements are central to message exchange.

DECLARE @ConversationHandle UNIQUEIDENTIFIER;
DECLARE @Message NVARCHAR(4000);

-- Initiate a conversation (simplified example)
BEGIN DIALOG CONVERSATION @ConversationHandle
FROM SERVICE MyService
TO SERVICE 'http://schemas.microsoft.com/sqlserver/Soap/ServiceBroker/MyService'
ON CONTRACT [http://schemas.microsoft.com/sqlserver/Soap/ServiceBroker/MyContract]
WITH ENCRYPTION = OFF;

-- Send a message
SET @Message = N'Hello, Service Broker!';
SEND ON CONVERSATION @ConversationHandle
MESSAGE_BODY = @Message,
MESSAGE_TYPE = 'http://schemas.microsoft.com/sqlserver/Soap/ServiceBroker/MyMessageType';

-- Receive messages (typically in a stored procedure triggered by the queue)
-- RECEIVE ... FROM MyQueue;

Ending Conversations

Conversations must be explicitly ended to release resources.

END CONVERSATION @ConversationHandle;

Service Broker Objects

Understanding the different components is crucial for effective implementation:

Object	Description	T-SQL Command
Queue	A table that stores messages waiting to be processed.	CREATE QUEUE, ALTER QUEUE, DROP QUEUE
Service	An endpoint for sending and receiving messages. It is associated with a queue.	CREATE SERVICE, ALTER SERVICE, DROP SERVICE
Message Type	Defines the format and structure of messages.	CREATE MESSAGE TYPE, ALTER MESSAGE TYPE, DROP MESSAGE TYPE
Contract	Specifies the set of message types allowed in a conversation.	CREATE CONTRACT, ALTER CONTRACT, DROP CONTRACT
Conversation	A logical dialog between two services for exchanging messages.	BEGIN DIALOG CONVERSATION, SEND ON CONVERSATION, RECEIVE FROM, END CONVERSATION

Use Cases and Benefits

Service Broker offers several advantages:

• Reliability: Built-in transaction support ensures messages are delivered exactly once.
• Scalability: Handles high volumes of messages efficiently.
• Asynchronous Processing: Allows applications to remain responsive while tasks are executed in the background.
• Simplicity: Integrated into SQL Server, simplifying deployment and management.
• Security: Supports encryption and authentication for message exchange.

Further Reading

Explore these related topics for a comprehensive understanding:

• T-SQL Stored Procedures
• Database Mirroring
• SQL Server Replication