For an instructor lead, in-depth look at learning SQL click below.
The SQL Server Service Broker provides built-in support for message queuing applications and asynchronous programming in SQL Server. It will simplify the creating of scalable, robust, and effective distributed applications. Service Broker is a part of SQL Server, so you don’t have to install or configure anything additional to use it.
Understanding the Service Broker Architecture
Service Broker is built on several components, all intertwined: Message Types, Contract, Queues, Services, and Stored Procedures. Together, these components form the bedrock on which messaging and queuing are rendered.
Example 1: Creating a Queue and Service
|
1 2 3 4 5 6 7 8 9 10 |
/*Creates a queue where messages can be sent */ CREATE QUEUE TestQueue; GO /*Creates a service on the sql server that can send and receive these messages using the queue we created */ CREATE SERVICE TestService ON QUEUE TestQueue ([DEFAULT]); GO |
Using Dialogs
Dialogs are at the heart of communication in Service Broker. They represent a conversation between two services.
Example 2: Initiating a Dialog
|
1 2 3 4 5 6 7 8 9 |
DECLARE @dialog_handle UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @dialog_handle FROM SERVICE [TestService] TO SERVICE 'TestService' ON CONTRACT [DEFAULT] WITH ENCRYPTION = OFF; |
Sending and Receiving Messages
Once a dialog has started, messages can be sent from one service to the other.
Example 3: Sending and Receiving Messages
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/*Sending a message */ DECLARE @dialog_handle UNIQUEIDENTIFIER; /*Creating the message content */ DECLARE @message_body NVARCHAR(100); SET @message_body = N'Hello, Service Broker!'; DECLARE @message_type_name NVARCHAR(256); SET @message_type_name = N'DEFAULT'; /*Sending the message */ SEND ON CONVERSATION @dialog_handle MESSAGE TYPE @message_type_name (@message_body); |
Received messages are stored in a queue and can be processed sequentially.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
/*Receiving the message */ DECLARE @message_type_name SYSNAME; DECLARE @message_body NVARCHAR(MAX); DECLARE @dialog_handle UNIQUEIDENTIFIER; RECEIVE TOP(1) @message_type_name=message_type_name, @message_body=message_body, @dialog_handle=conversation_handle FROM TestQueue; |
These examples are highly simplified to illustrate the concepts. SQL Server Service Broker is an in-depth feature with a lot to explore. The samples above should be a good starting point in understanding this robust SQL Server feature.