Dinesh Asanka
SQL Service Broker objects in the SQL Server Management Studio after creating them.

Using the SQL Server Service Broker for Asynchronous Processing

June 10, 2020 by

Introduction

In the real-world implementation of SQL Server of an enterprise system, it always needs to implement data in multiple databases. In most of the cases, a single transaction will span across multiple databases. If there are multiple databases, there will be performance issues and implementation issues during the cross-database transactions. However, with the use of SQL Service Broker that was introduced in SQL Server 2005, you can implement asynchronous transactions between databases.

What is Service Broker

The Service broker is similar to other message queuing technologies such as MSMQ. In the service broker, data can be processed automatically or on-demand. Data in the SQL service broker is consumed by SEND and RECEIVE, which is typically an XML format.

A scenario for Service Broker

Though there are a few other implementations of Service Broker, we will be looking at the usage of Service Broker for the Distributed Transaction System.

Let us assume that there is an Order System. When the order is received, inventory has to be processed. To maintain scalability and security concerns, these are handled by two processes.

We will start by enabling the service broker from the following code.

In the service broker, we will be using four main concepts: Message Type, Contract, Queue, and Services. The following figure shows different types of objects in Object Explorer for SQL service broker.

SQL Service Broker objects in the SQL Server Management Studio,

However, to create relevant service broker objects, you need to use T-SQL queries as there is no user interface for those object creations.

Let’s create a Message Type. A Message type will define what type of data you are sending.

Above created message type ReceviedOrders are owned by dbo, and validation is set to none. In the SQL service broker, there can be four types of validations, such as NONE, EMPTY, WELL_FORMED_XML, and VALID_XML WITH SCHEMA COLLECTION. In the NONE option, no validations are made, and typically NONE is used as validation left to the consumption.

The next service broker object that we create is the CONTRACT. The contract will create a logical grouping of one or more message types. This means that there is a one-to-many relationship between the CONTRACT and the MESSAGE TYPE.

In the above-created CONTRACT, it has specified that messages can be sent from any endpoints. However, if you want to restrict for security purposes, you still have the option of restricting it.

Next, we will be creating an important object called QUEUE that will hold the messages that you are sending.

When the status is set to OFF, you cannot send or receive messages from the queue. Another important configuration is that the RETENTION option. If the RETENTION is set to OFF, messages will be deleted from the queue. If you want to keep the messages for auditing purposes, you can set this to ON. However, setting the RETENTION to ON will impact the performance of the system. Therefore, it is recommended to set the RETENTION to OFF.

The final SQL Service broker object to cover for our scenario is the SERVICE. Service will ensure that messages are sending and receiving.

The service will contain the queue and the contract, as shown in the above code.

Now let’s see how you can see the created SQL Service Broker objects in the SQL Server Management Studio (SSMS).

SQL Service Broker objects in the SQL Server Management Studio after creating them.

Now we have set up the infrastructure to message queues, and let’s see how we can use them. We will create a table called Orders, as shown below.

We will create a Stored Procedure that will insert records to the Order table and send the message to the previously defined queue.

In the above-stored procedure, SEND command is used to send an XML formatted message to the ReceivedOrders queue.

Let us execute this stored procedure with the following queries.

When the above execution are completed you will see three records in the Orders table.

Order table after records are inserted.

Since we have sent three messages to the OrderQueue, we can verify those entries from the following query.

Message body cannot be viewed as it is encrypted as shown below.

Message Queue in the table.

Now let’s consume the queue from the following code.

The above code can consume the data in the queue and process them. With this approach, asynchronous processing can be done.

Additional Benefits

Apart from the asynchronous processing, other benefits can be achieved from the SQL service broker. One of the important features is that messages are within the database. If you backup and restore the database, messages of the queues are retained.

Further, in SQL Server, database mail uses the SQL service broker feature.

Conclusion

SQL Server Service broker is an important component in the SQL Server family that can be used for various activities. In this article, it was explained how SQL service broker was used for asynchronous processing using MESSAGE TYPE, CONTRACT, QUEUE, and SERVICES.

Dinesh Asanka
44,128 Views