Sql Service Broker
Tags: sql, sqlserver, database, asynchronous
Published: 2023-10-19 16:33:41

I would like to share my first experience with SQL Service Broker from Microsoft. While I have some experience with SQL as a programmer, I have primarily utilized SQL as a database component within various applications and systems. However, I had never delved into the realm of Service Broker until now. This article chronicles my initial exploration and efforts to understand and utilize this component.

## So, what exactly is a Service Broker?

I read a few introductory words and I understood it roughly like this. Service Broker is about communication. It is all. It is about communication between services. This communication can occur within a single database, between multiple databases, or even between different instances. Essentially, it serves as a technology for efficient queue management. Messages are placed in a queue and remain there until the system has available resources to process them. Importantly, this communication is asynchronous. When a sender dispatches a message, they don't have to wait for the receiver to process it. The sender can continue working while the message sits in the queue, and the processing occurs in the background. The possibilities of use are vast, ranging from leveraging asynchronous triggers for handling long-running tasks to distributing data between SQL instances. It is only limited by your imagination. It's worth noting that Service Broker is an internal service of SQL Server and is not listed among the Windows services.

![Broker Diagram](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4sl4zp59x3i50iwa1331.gif)

## What do I need to test?

In my particular case, I needed to implement asynchronous triggers to solve long-running tasks.

## What do I need to know?

As a first step, I needed to familiarize myself with the various objects and the overall broker architecture to gain a better understanding of how it all fits together. A Service Broker consists of a client service, which initiates the conversation, and a service that receives and processes the messages. Each service is associated with a queue. The relationship between services is defined by a contract, which determines the types of messages that the initiator and target services can send to the queue. The exchange of messages between two services is referred to as a dialog.

## What are the main objects?

The main objects involved in Service Broker are *Message type*, *Contract*, *Queue*, *Service*, and *Route*.

### Message type

Each message is represented by a record in the queue, and its format is determined by the Message Type. A Message Type contains the name of the message and the data type. The target service performs validation on the type of data received. Various validations can be applied, such as:

* **EMPTY:** The message must not contain any data.
    
* **NONE:** The message can contain any type of data (e.g., JSON, binary, etc.).
    
* **WELL\_FORMED\_XML:** The message must have a valid XML format.
    
* **VALID\_XML:** The message must be valid against a specified XML schema.
    

In addition to user-defined messages, there are also system messages provided by the Service Broker. These system messages are used by the broker to report the status of a dialog e.g. error messages, end dialog messages, and dialog timer messages.

### Contract

A contract represents an agreement between two services and defines the types of messages that they can send to each other during communication. The SEND BY parameter within a contract specifies which endpoint is allowed to send messages:

* **INITIATOR:** Only the initiator can send messages.
    
* **TARGET:** Only the target can send messages.
    
* **ANY:** Both the initiator and target can send messages.
    

### Queue

A queue is a storage location where incoming messages are stored for processing. A single queue can be assigned to multiple services, allowing them to send or receive messages from that queue.

### Service

A service is responsible for routing and delivering messages to the designated queue within a database. A service acts as an endpoint for a conversation and can be either an initiator (which starts a conversation by sending a message to a queue) or a target (which receives a message from a queue). Each service is associated with a queue and a contract that specifies the types of messages it can send to the queue.

### Route

A route is an object used for message routing, mapping a service name to a network address. If a service is not assigned a route, it will deliver the message within the current instance.

## What are the main commands I will need?

Next, I delved into learning more about the commands involved in Service Broker. The main commands in Service Broker are *Begin Dialog*, *Send*, *Receive*, and *End Conversation*.

### Dialog

A dialog represents a communication channel between two services and is used for sending messages. Service Broker ensures the guaranteed delivery of messages, and they are delivered to the queue in the order in which they were sent. *BEGIN DIALOG* is used to initiate a new conversation, and the conversation continues until one of the parties terminates it or sends an error message. It's important to note that both parties must properly terminate the conversation for it to be correctly ended; otherwise, the dialog will persist in the database.

### Send

The *SEND* command is used to send a message within a conversation. The message is placed in the destination queue. If the destination of the conversation is outside the instance, the message is placed in the *transmission\_queue*.

### End Conversation

The *END CONVERSATION* command is used to close a conversation. It is crucial to ensure that both sides of the conversation properly close it.

### Receive

The *RECEIVE* command is used to retrieve one or more messages from the queue. Once a message is successfully retrieved, it is removed from the queue.

Alright, now that I have a basic understanding of how Service Broker works, it's time to set it up for testing purposes.

## How do I imagine my testing purposes?

First, I will create a database called ***Test\_ServiceBroker.*** Within this database, I will create a table named ***tbl\_DataStorage*** to simulate a production table. This table will collect data from users for further processing.

Next, I will create a ***trigger*** for the ***tbl\_DataStorage*** table that will execute after the data is inserted. This trigger will utilize Service Broker to send the data to a stored procedure named ***sp\_ProcessRequestMessage***. This procedure will handle the data processing. Once the data is processed, the Service Broker will send a message to another stored procedure named ***sp\_ProcessResponseMessage***, which will update the status of the record in the ***tbl\_DataStorage*** table to mark it as complete.

I will create and configure the necessary Service Broker objects. Firstly, I will create a ***Message Type*** for the request message and another ***Message Type*** for the response message. Following that, I will establish a ***Contract*** between these Message Types, defining the allowable communication between them.

Afterward, I will create a ***Queue*** for the initiator and another ***Queue*** for the target. These queues will serve as the storage location for incoming messages.

Finally, I will create two ***Services***: one for the initiator and another for the target. These services will act as the endpoints for the conversation and will be associated with their respective queues.

![SQL Diagram](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j6ytfgfbvchdewz9rufh.gif)

## Create a database playground

First, let's prepare the playground for our Service Broker implementation.

```sql
-- Create the 'Test_ServiceBroker' database
USE master
GO
IF db_id('Test_ServiceBroker') IS NOT NULL
	DROP DATABASE Test_ServiceBroker
CREATE DATABASE Test_ServiceBroker;
GO
```

```sql
-- Check if Service Broker is enabled
SELECT name, is_broker_enabled FROM sys.databases
```

We can check the status of the Service Broker by querying the ***is\_broker\_enabled*** column in the ***sys.databases*** table. Here are the possible values:

* **0:** Service Broker is disabled. Received messages will not be placed in the queue, and sent messages will remain in the transmission queue.
    
* **1:** Service Broker is enabled.
    

```sql
-- Enable Service Broker for the 'Test_ServiceBroker' database
ALTER DATABASE Test_ServiceBroker SET ENABLE_BROKER
GO
```

We can enable the Service Broker for the ***Test\_ServiceBroker*** database by using the ***ENABLE\_BROKER*** parameter. Similarly, the Service Broker can be disabled using the ***DISABLE\_BROKER*** parameter.

```sql
-- Use the 'Test_ServiceBroker' database
USE Test_ServiceBroker
GO
```

Let's switch to the ***Test\_ServiceBroker*** database for the following operations.

```sql
-- Create the 'tbl_DataStorage' table
IF object_id('tbl_DataStorage') IS NOT NULL
  DROP TABLE tbl_DataStorage
GO
CREATE TABLE tbl_DataStorage
(
	Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	InsertDate DATETIME NOT NULL DEFAULT (GETDATE()),
	DataStorage NVARCHAR(MAX),
	DataStatus NVARCHAR(256),
	CompletionDate DATETIME,
);
GO
```

Here, we create the ***tbl\_DataStorage*** table, which will store the data for processing. The table has the following columns:

* **Id:** A unique primary key for each record.
    
* **InsertDate:** The date and time of data insertion.
    
* **DataStorage:** User data that will be processed.
    
* **DataStatus:** The status of data processing.
    
* **CompletionDate:** The date and time of data processing completion.
    

## Configure the Service Broker

Next, let's configure the Service Broker for our playground.

```sql
-- Use the 'Test_ServiceBroker' database
USE Test_ServiceBroker
GO
```

```sql
-- Create the message type for the request
CREATE MESSAGE TYPE [RequestMessage]
VALIDATION = WELL_FORMED_XML
GO

-- Create the message type for the response
CREATE MESSAGE TYPE [ResponseMessage]
VALIDATION = WELL_FORMED_XML
GO
```

We create two message types, one for the request and another for the response. Both message types have their validation set to ***WELL\_FORMED\_XML***, indicating that the user will send data in XML format.

```sql
-- Retrieve information about message types
SELECT * FROM sys.service_message_types
```

This query retrieves information about the registered message types in the Service Broker. The key attribute here is ***validation\_desc***, which provides details about the validation type. Additionally, there are several default message types, such as ***Error***, ***EndDialog***, and ***DialogTimer***.

```sql
-- Create the contract
CREATE CONTRACT [DataProcessContract]
(
	[RequestMessage] SENT BY INITIATOR,
	[ResponseMessage] SENT BY TARGET
)
GO
```

We create a contract named ***DataProcessContract*** between the ***RequestMessage*** (sent by the initiator) and the ***ResponseMessage*** (sent by the target).

```sql
-- Retrieve information about contracts
SELECT * FROM sys.service_contracts
```

This query retrieves information about the contracts registered in the Service Broker.

```sql
-- Retrieve information about contracts and message types
SELECT
	sc.name AS 'Contract',
	smt.name AS 'Message type',
	scmu.is_sent_by_initiator,
	scmu.is_sent_by_target,
	smt.validation
FROM sys.service_contract_message_usages scmu
	INNER JOIN sys.service_message_types smt ON scmu.message_type_id = smt.message_type_id
	INNER JOIN sys.service_contracts sc ON sc.service_contract_id = scmu.service_contract_id
```

This query provides data about the contracts, their associated message types, whether they are sent by the initiator or target, and the validation type.

```sql
-- Create the queue for the initiator
CREATE QUEUE [InitiatorQueue]
WITH STATUS = ON
GO

-- Create the queue for the target
CREATE QUEUE [TargetQueue]
WITH STATUS = ON
GO
```

We create two queues, one for the initiator and one for the target. Both queues are set to ***ON*** status, which means they are enabled and ready to accept messages.

```sql
-- Retrieve information about queues
SELECT * FROM sys.service_queues
```

This query retrieves information about the queues in the Service Broker. The main attributes here are the ***name***, ***max\_readers***, ***activation\_procedure***, and the enabled status of activation, ***receive***, and ***retention***.

```sql
-- Create the service for the initiator
CREATE SERVICE [InitiatorService]
ON QUEUE [InitiatorQueue]
(
	[DataProcessContract]
)
GO

-- Create the service for the target
CREATE SERVICE [TargetService]
ON QUEUE [TargetQueue]
(
	[DataProcessContract]
)
GO
```

We create two service endpoints: one for the initiator and another for the target. Both services are associated with the ***DataProcessContract*** contract.

```sql
-- Retrieve information about services
SELECT * FROM sys.services
```

This query retrieves information about the services in the Service Broker. It includes the name of the service and the associated service queue ID (*service\_queue\_id*).

## Create a Trigger and Stored Procedures

Now we need to create a trigger for the ***tbl\_DataStorage*** table and two stored procedures to process messages from the Service Broker.

```sql
-- Create the trigger
CREATE TRIGGER [dbo].[trg_DataStorage_insert]  
	ON [Test_ServiceBroker].[dbo].[tbl_DataStorage]
	AFTER INSERT
AS
BEGIN TRY
	BEGIN TRANSACTION ;
		DECLARE @DataContent NVARCHAR(MAX)
		DECLARE @Id int
		SET @DataContent = (SELECT [DataStorage] FROM inserted i)   
		SET @Id = (SELECT Id FROM inserted i)  
		If (@DataContent IS NOT NULL)
		BEGIN
			UPDATE [Test_ServiceBroker].[dbo].[tbl_DataStorage] SET [DataStatus] = 'pending processing' WHERE [Id] = @Id;
			SET @DataContent = @DataContent + '<Id_DataStorage>' + CAST(@Id AS NVARCHAR(10)) + '</Id_DataStorage>';
			DECLARE @DialogHandle UNIQUEIDENTIFIER;  
			BEGIN DIALOG CONVERSATION @DialogHandle  
				FROM SERVICE [InitiatorService]  
				TO SERVICE 'TargetService'  
				ON CONTRACT [DataProcessContract]  
				WITH ENCRYPTION = OFF;  
			SEND ON CONVERSATION @DialogHandle  
				MESSAGE TYPE [RequestMessage](@DataContent);
	  END
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO
```

The trigger is executed after data is inserted into the ***tbl\_DataStorage*** table. It stores the ID and user data in variables. Then, it updates the ***DataStatus*** to "***pending processing"*** and adds the ***&lt;Id\_DataStorage&gt;*** tag with the ID value to the ***DataContent*** variable. The important part is creating a dialog from the ***InitiatorService*** to the ***TargetService*** using the ***DataProcessContract*** contract. The dialog handle is stored in the ***DialogHandle*** variable as a ***UNIQUEIDENTIFIER***, a unique identifier composed of a hexadecimal string. Next, a message of type ***RequestMessage*** is sent with the ***DataContent*** as the message body. This message will be processed by the Service Broker.

The ***WITH ENCRYPTION*** option in the ***BEGIN DIALOG*** statement determines whether encryption will be used. It encrypts all messages sent outside the SQL Server instance. The message body is encrypted with a symmetric key generated for the conversation, and the keys are encrypted using the private key with the specified certificate. In my case, the value is set to OFF. I don't need to encrypt messages where they are sent within one instance and one database.

```sql
-- Create the stored procedure sp_ProcessRequestMessage
CREATE PROCEDURE [dbo].[sp_ProcessRequestMessage]
AS
BEGIN
	DECLARE @DialogHandle UNIQUEIDENTIFIER
	DECLARE @MessageName VARCHAR(256)
	DECLARE @MessageBody XML
	DECLARE @Id_DataStorage INT
		WHILE (1 = 1)
			BEGIN
				BEGIN TRY
					BEGIN TRANSACTION;
						WAITFOR (
							RECEIVE TOP(1)
								@DialogHandle = conversation_handle,
								@MessageName = message_type_name,	
								@MessageBody = CAST(message_body AS XML)
							FROM dbo.TargetQueue ), TIMEOUT 5000   
							IF (@@ROWCOUNT = 0)
							BEGIN
								ROLLBACK TRANSACTION
								BREAK
							END
							IF ( @MessageName = 'RequestMessage' )
							BEGIN
								-- any data processing here... replace WAITFOR DELAY ;
								WAITFOR DELAY '00:01:00';
								SET @Id_DataStorage =  @MessageBody.value('/Id_DataStorage[1]', 'INT');
								SET @MessageBody = '<Id_DataStorage>' + CAST(@Id_DataStorage AS VARCHAR(10)) + '</Id_DataStorage>' + '<ProcessStatus>Complete</ProcessStatus>';
								SEND ON CONVERSATION @DialogHandle MESSAGE TYPE [ResponseMessage] (@MessageBody);
							END
							ELSE IF ( @MessageName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' )
							BEGIN
								END CONVERSATION @DialogHandle ;
							END
							ELSE IF ( @MessageName = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' )
							BEGIN
								END CONVERSATION @DialogHandle ;
							END
					COMMIT TRANSACTION;
				END TRY
				BEGIN CATCH
				  ROLLBACK TRANSACTION
				END CATCH
			END
END
GO
```

This stored procedure waits in a loop for messages from the queue and processes them. If there are no messages in the queue to process (***@@ROWCOUNT*** = ***0***), the procedure terminates. The ***WAITFOR*** statement waits for a return value or a timeout. The ***TIMEOUT*** parameter determines how long the ***WAITFOR*** statement waits for messages, specified in milliseconds. If the value is -1, the wait time is unlimited. If the timeout expires, ***RECEIVE*** returns an empty result set. ***RECEIVE*** retrieves a table containing zero or more rows, where each row represents one message from the queue. In this case, we expect messages from the ***dbo.TargetQueue*** table and wait for a maximum of 5000 milliseconds. If a message of the type ***RequestMessage*** (sent by the trigger) is received, data processing is simulated. For testing purposes, the ***WAITFOR DELAY*** command is used to introduce a delay of one minute. After processing the data, a new message is created with the processed record's ID in the ***&lt;Id\_DataStorage&gt;*** tag and the completion status in the ***&lt;ProcessStatus&gt;*** tag. The message is sent using the Service Broker to the second procedure, ***sp\_ProcessResponseMessage***, which updates the status of the record in the ***tbl\_DataStorage*** table. If an ***EndDialog*** or ***Error*** message type is received, the conversation is ended using the ***END CONVERSATION*** command.

```sql
-- Create the stored procedure sp_ProcessResponseMessage
CREATE PROCEDURE [dbo].[sp_ProcessResponseMessage]
AS
BEGIN
	DECLARE @DialogHandle UNIQUEIDENTIFIER
	DECLARE @MessageName VARCHAR(256)
	DECLARE @MessageBody XML
	DECLARE @Id_DataStorage INT
	DECLARE @ProcessStatus NVARCHAR(64)
		WHILE (1 = 1)
			BEGIN
				BEGIN TRY
					BEGIN TRANSACTION;
						WAITFOR (
							RECEIVE TOP(1)
								@DialogHandle = conversation_handle,
								@MessageName = message_type_name,	
								@MessageBody = CAST(message_body AS XML)
							FROM dbo.InitiatorQueue ), TIMEOUT 5000   
							IF (@@ROWCOUNT = 0)
							BEGIN
								ROLLBACK TRANSACTION
								BREAK
							END
							IF ( @MessageName = 'ResponseMessage' )
							BEGIN
								SET @Id_DataStorage =  @MessageBody.value('/Id_DataStorage[1]', 'INT');
								SET @ProcessStatus =  @MessageBody.value('/ProcessStatus[1]', 'NVARCHAR(64)');
								UPDATE [Test_ServiceBroker].[dbo].[tbl_DataStorage] SET [DataStatus] = @ProcessStatus, [CompletionDate] = GETDATE() WHERE [Id] = @Id_DataStorage;
								END CONVERSATION @DialogHandle;
							END
							ELSE IF ( @MessageName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' )
							BEGIN
								END CONVERSATION @DialogHandle ;
							END
							ELSE IF ( @MessageName = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' )
							BEGIN
								END CONVERSATION @DialogHandle ;
							END
					COMMIT TRANSACTION;
				END TRY
				BEGIN CATCH
				  ROLLBACK TRANSACTION
				END CATCH
			END
END
GO
```

This procedure is almost identical to the ***sp\_ProcessRequestMessage*** stored procedure. The difference is that the ***RECEIVE*** command waits for data in the ***InitiatorQueue*** queue where the previous procedure sent it. The message name must be ***ResponseMessage***. This procedure parses the XML content of the received message, updates the record in the ***tbl\_DataStorage*** table with the status provided in the message, and finally terminates the conversation with the ***END CONVERSATION*** command.

## I stumbled a little bit

Hmmm, here is one small problem. How will my procedures be called? Triggers are understandable; if a record is added to the table, a trigger will be executed, and a dialog will start. But what about procedures? Who triggers their execution? I discovered a useful functionality called ***Internal Activation***. By enabling Internal Activation, the service broker begins monitoring the queue. Whenever messages become available in the queue for processing, the procedure associated with that queue will be automatically triggered. Let's take a look at the parameters for Internal Activation:

**WITH STATUS:** This parameter determines whether the queue is enabled and ready to receive messages (ON) or disabled (OFF).

**RETENTION:** If set to ON, messages in the queue are retained and marked as managed. Conversely, setting it to OFF deletes messages from the queue after processing. The default value is OFF.

**ACTIVATION:** This parameter controls the activation behavior after receiving a message in the queue.

**ACTIVATION parameters:**

* **STATUS:** ON, the activation automatically.
    
* **PROCEDURE\_NAME:** Specifies the name of the stored procedure associated with the queue.
    
* **MAX\_QUEUE\_READERS:** Determines the maximum number of instances of the stored procedure that can be initiated simultaneously. The maximum allowed value is 32767.
    
* **EXECUTE AS:** Specifies the security context under which the procedure runs. It can be set to SELF (indicating the currently logged-in user) or OWNER (indicating the account that owns the queue), or any other account name.
    

Now, let's proceed with modifying both queues accordingly:

```sql
-- Altering the InitiatorQueue as a queue with an activation procedure
ALTER QUEUE [InitiatorQueue]
WITH ACTIVATION
(
	STATUS = ON,
	PROCEDURE_NAME = [sp_ProcessResponseMessage],
	MAX_QUEUE_READERS = 10,
	EXECUTE AS SELF
)
GO

-- Altering the TargetQueue as a queue with an activation procedure
ALTER QUEUE [TargetQueue]
WITH ACTIVATION
(
	STATUS = ON,
	PROCEDURE_NAME = [sp_ProcessRequestMessage],
	MAX_QUEUE_READERS = 10,
	EXECUTE AS SELF
)
GO
```

## Let's test it

Okay, that could be all for now. It's time to give it a try. I will simulate the user application by using a regular SQL *INSERT INTO* command to send test data to the ***tbl\_DataStorage*** table.

```sql
-- Sending test data.
INSERT INTO [Test_ServiceBroker].[dbo].[tbl_DataStorage] ([DataStorage]) VALUES ('<test>fooo</test>')
```

```sql
-- Viewing the table
SELECT * FROM [Test_ServiceBroker].[dbo].[tbl_DataStorage]
```

If I manage to send the data and observe the output within one minute (since I have set a DELAY of 1 minute in the ***sp\_ProcessRequestMessage*** procedure), I should see the value "***pending processing"*** in the ***DataStatus*** column, which was inserted by the trigger. Also, send trigger data for processing.

![Pending Processing](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zvg57mwhbqcekoh5x3jj.jpg)

After one minute, the data should be processed, and the ***DataStatus*** column should show ***"Complete"*** in the output. The difference between the ***InsertData*** and ***CompletionDate*** times is approximately one minute, as determined by the 1-minute delay set in the ***sp\_ProcessRequestMessage*** procedure.

![Complete](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/td3eg5v1gt1mgvoq56gy.jpg)

Of course, it is possible to INSERT many different rows simultaneously or at the same time and observe what happens. I tried entering multiple rows at once.

```sql
-- insertion of the 15th line (trash code)
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar01</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar02</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar03</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar04</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar05</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar06</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar07</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar08</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar09</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar10</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar11</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar12</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar13</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar14</test>')
INSERT INTO [tbl_DataStorage] ([DataStorage]) VALUES ('<test>bar15</test>')
```

![Test multiple rows](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cslhtics5ihkfm0fwrel.jpg)

I noticed that the data is processed sequentially in packages. This can be seen from the timestamp in the ***CompletionDate*** column, which is determined by the ***MAX\_QUEUE\_READERS*** setting of the queue. If I set ***MAX\_QUEUE\_READERS*** to **1**, the data will be processed sequentially in 1-minute intervals instead of processing several rows at once. But this process is still asynchronous, the data insert does not wait for the job to complete. See the next section.

![Test single row](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lp0h72z87no1eqsu8uo0.jpg)

## Comparison of asynchronous vs. synchronous

It may seem unnecessarily complicated to enable the service broker, create all those objects, start a dialog and send messages. Catch the messages with procedures and they will process them. Plus create a trigger to start the communication. Instead of all this, one trigger would be enough, which after inserting data into ***tbl\_DataStirage*** processes the data. This is true, but with the difference that the data will be processed synchronously. This means that the user has to wait until the data is processed.

Here is a small demonstration. First, I will create a new trigger for the ***tbl\_DataStorage*** table called ***trg\_DataStorage\_insert\_sync***. The trigger is activated after the data is inserted and all it does is update the ***DataStatus*** column to ***"pending processing"***. It then waits for one minute before updating the DataStatus to ***"Complete"***. Essentially, the trigger performs the same actions as my service broker implementation.

```sql
-- Create the trigger for synchronous test
CREATE TRIGGER [dbo].[trg_DataStorage_insert_sync]  
	ON [Test_ServiceBroker].[dbo].[tbl_DataStorage]
	AFTER INSERT
AS
BEGIN TRY
	BEGIN TRANSACTION ;
		DECLARE @DataContent NVARCHAR(MAX)
		DECLARE @Id int
		SET @DataContent = (SELECT [DataStorage] FROM inserted i)   
		SET @Id = (SELECT Id FROM inserted i)  
		If (@DataContent IS NOT NULL)
		BEGIN
			UPDATE [Test_ServiceBroker].[dbo].[tbl_DataStorage] SET [DataStatus] = 'pending processing' WHERE [Id] = @Id;
			WAITFOR DELAY '00:01:00'; -- any data processing here... replace WAITFOR DELAY ;
            UPDATE [Test_ServiceBroker].[dbo].[tbl_DataStorage] SET [DataStatus] = 'Complete' WHERE [Id] = @Id;
	  END
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO
```

It is still necessary to temporarily disable the ***trg\_DataStorage\_insert*** trigger to prevent it from sending messages through the service broker.

```sql
-- Disable trigger trg_DataStorage_insert
ALTER TABLE [tbl_DataStorage] DISABLE TRIGGER [trg_DataStorage_insert]
```

Similarly to the previous example, I will insert 15 rows into the ***tbl\_DataStorage*** table.

Here, you can observe that the execution of the insert took 15 minutes, which corresponds to the time it takes for 15 rows to be processed, considering the 1-minute DELAY for each row. The session waits for all the tasks to complete and only unblocks after they have been processed.

![Status line](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q6za1c4uqgq2jstrzrlt.jpg)

Now, I will revert everything back to its original state. I will enable the ***trg\_DataStorage\_insert*** trigger again and disable the ***trg\_DataStorage\_insert\_sync*** trigger (or alternatively, I can delete it since it is no longer needed).

```sql
-- Enable trigger trg_DataStorage_insert
ALTER TABLE [tbl_DataStorage] ENABLE TRIGGER [trg_DataStorage_insert]

-- Disable trigger trg_DataStorage_insert_sync
ALTER TABLE [tbl_DataStorage] DISABLE TRIGGER [trg_DataStorage_insert_sync]
```

Finally, I will insert 15 rows into the ***tbl\_DataStorage*** table once again.

![Status line](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/coqajvtwplwfx5pkl9ex.jpg)

Perfect! The data insertion occurred instantly, and the entire processing is now happening asynchronously in the background. The session is now free, and the service broker will take care of managing everything.

## Controlling message priority

SQL Service Broker offers the capability to assign priorities, allowing you to control the processing order of messages. While not strictly necessary in my particular use case with a single communication, it is valuable to have an understanding of priority settings. By default, all conversations have the same priority. Message priority is defined at the conversation level and influences the sequence in which messages are sent and received.

Priority levels range from **0** to **10**, with 0 being the lowest and 10 being the highest. Messages with higher priority levels are processed before those with lower priority levels, enabling you to manage the message processing order. However, it's important to note that message priority does not guarantee immediate or real-time execution, as processing time depends on factors such as system resources and workload.

To begin, the first step is to enable the ***HONOR\_BROKER\_PRIORITY*** option for the database, as it is disabled by default. To determine whether the *HONOR\_BROKER\_PRIORITY* setting is enabled, you can execute a simple query that retrieves the relevant information from the *sys.databases*. The query provides you with the current status of the *honor\_broker\_priority* setting for each database.

```sql
-- Check if is_honor_broker_priority_on is enabled
SELECT name, is_honor_broker_priority_on FROM sys.databases;
```

Enabling the *honor\_broker\_priority* setting is crucial if you want the SQL Service Broker to honor message priorities during processing.

```sql
-- Enable priority management
ALTER DATABASE [Test_ServiceBroker] SET HONOR_BROKER_PRIORITY ON;
```

To create a priority level, you can use the ***CREATE BROKER PRIORITY*** command.

```sql
-- Create a priority
CREATE BROKER PRIORITY ConversationPriority
FOR CONVERSATION
SET (CONTRACT_NAME = [DataProcessContract],
PRIORITY_LEVEL = 1 );
```

In this example, I have set a lower priority level for the ***DataProcessContract*** to illustrate the flexibility of message prioritization. By utilizing the ***ALTER*** command, you can easily adjust the priority level.

```sql
-- Modify priority to level 10
ALTER BROKER PRIORITY ConversationPriority
FOR CONVERSATION  
SET (PRIORITY_LEVEL = 10);
```

Here, I have adjusted the priority level to 10, which represents the highest priority. This allows you to tailor the priority settings based on your specific requirements.

Now, let's take a closer look at the message queues and conversation endpoints to observe the impact of the priority changes. Upon examining the queues, you will notice that the **priority** column reflects the updated priority level of 10. However, it's important to note that to view the data within the ***InitiatorQueue*** and ***TargetQueue*** tables, the *RETENTION* attribute needs to be set to *ON*. This ensures that the messages persist in the queues even after being processed.

To view the messages priority within the InitiatorQueue and TargetQueue, you can execute the following query:

```sql
-- Viewing messages in the InitiatorQueue
SELECT * FROM InitiatorQueue

-- Viewing messages in the TargetQueue
SELECT * FROM TargetQueue
```

Similarly, for exploring the conversation endpoints priority, you can execute the following query:

```sql
-- Viewing conversation endpoints
SELECT * FROM sys.conversation_endpoints
```

By examining these message queues and conversation endpoints, you can gain valuable visibility into the status of your messages, and observe the effect of priority changes.

By utilizing different priority levels, you can effectively influence the sequence in which messages are processed within the SQL Service Broker. This capability proves particularly valuable when dealing with diverse message types or various scenarios that require distinct levels of attention or urgency. With higher priority levels, you can ensure that critical messages receive prompt processing. On the other hand, lower priority can be assigned to messages that are less time-sensitive or do not require immediate action. By carefully tailoring the priority settings, you can optimize the overall message processing flow, enabling the system to efficiently allocate resources and manage workloads according to the importance and urgency of each message.

## A few insights

A few notes that helped me understand how the service broker works and how to monitor it. I was particularly interested in understanding what happens in the initiator queue, target queue, and conversation endpoints.

```sql
-- Viewing messages in the InitiatorQueue
SELECT * FROM InitiatorQueue

-- Viewing messages in the TargetQueue
SELECT * FROM TargetQueue
```

Here, I can see the messages waiting to be processed in the queues. Once a message is processed, it is automatically deleted. However, if we want to keep processed messages in the queue for testing purposes, we can modify the ***RETENTION*** parameter.

```sql
-- Viewing conversation endpoints
SELECT * FROM sys.conversation_endpoints
```

Each side of the conversation is represented by an endpoint, and the ***sys.conversation\_endpoints*** table contains a catalog of these endpoints. Closed conversations remain in the table for 30 minutes, indicated by the ***security\_timestamp***. This helps prevent replay attacks. The service broker ensures that a conversation is not reopened if it has the same handle and originates from the same host with the same encryption key. It's important to correctly close the dialog on both the initiator and target sides to avoid the accumulation of conversation endpoints in ***sys.conversation\_endpoints***.

And here's one more small tip for improving performance. It's beneficial to consider creating and closing a conversation if I have to process many small messages within a short time. In such cases, creating and closing a conversation repeatedly in a short period would unnecessarily burden the system. Instead, it's better to create and close a conversation after sending several messages, rather than after each individual message.

Additionally, there is no requirement to receive messages one by one using ***RECIVE TOP(1)***. Instead, you can retrieve all the messages, store them in a variable of type TABLE, and utilize a ***CURSOR*** to process them before committing the transaction. There are various optimization possibilities available, and the approach chosen should align with the specific use case at hand.

## Conclusion

Service Broker offers a robust and powerful messaging framework within the SQL Server ecosystem. It provides a reliable and scalable solution for asynchronous message-based communication between database components and instances. Throughout this article, we have explored the key concepts and components of SQL Service Broker, including messages, contracts, queues, services, and dialogs. We have seen how these elements work together to facilitate secure and efficient communication, enabling the exchange of information asynchronously. The built-in reliability and fault-tolerance mechanisms ensure message delivery and processing even in the presence of network interruptions or system failures.

In the future, I intend to broaden my knowledge and leverage the Service Broker to establish communication between two SQL instances, each deployed on a separate server. This communication between instances will use encryption and a certificate for secure data transfer.

> **Source SQL codes can be found on my [GitHub](https://github.com/mortylen/sql-service-broker).**

> _Cover photo by [Ryoji Iwata](https://unsplash.com/photos/person-holding-red-jigsaw-puzzle-5siQcvSxCP8?utm_content=creditCopyText&utm_medium=referral&utm_source=unsplash)_