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.
|
1 2 |
ALTER DATABASE OrderDB SET ENABLE_BROKER; |
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.
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.
|
1 2 3 |
CREATE MESSAGE TYPE ReceivedOrders AUTHORIZATION dbo VALIDATION = None |
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.
|
1 2 |
CREATE CONTRACT postmessages (ReceivedOrders SENT BY ANY) |
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.
|
1 2 |
CREATE QUEUE OrderQueue WITH STATUS = ON, RETENTION = OFF |
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.
|
1 2 3 4 |
CREATE SERVICE OrderService AUTHORIZATION dbo ON QUEUE OrderQueue (postmessages) |
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).
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE [dbo].[Orders]( [OrderID] [int] NOT NULL, [OrderDate] [date] NULL, [ProductCode] [varchar](50) NOT NULL, [Quantity] [numeric](9, 2) NULL, [UnitPrice] [numeric](9, 2) NULL, CONSTRAINT [PK__Orders] PRIMARY KEY CLUSTERED ( [OrderID] ASC, [ProductCode] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO |
We will create a Stored Procedure that will insert records to the Order table and send the message to the previously defined queue.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
CREATE PROCEDURE usp_CreateOrders ( @OrderID INT ,@ProductCode VARCHAR(50) ,@Quantity NUMERIC(9, 2) ,@UnitPrice NUMERIC(9, 2) ) AS BEGIN DECLARE @OrderDate AS SMALLDATETIME SET @OrderDate = GETDATE() DECLARE @XMLMessage XML CREATE TABLE #Message ( OrderID INT PRIMARY KEY ,OrderDate DATE ,ProductCode VARCHAR(50) ,Quantity NUMERIC(9, 2) ,UnitPrice NUMERIC(9, 2) ) INSERT INTO #Message ( OrderID ,OrderDate ,ProductCode ,Quantity ,UnitPrice ) VALUES ( @OrderID ,@OrderDate ,@ProductCode ,@Quantity ,@UnitPrice ) --Insert to Orders Table INSERT INTO Orders ( OrderID ,OrderDate ,ProductCode ,Quantity ,UnitPrice ) VALUES ( @OrderID ,@OrderDate ,@ProductCode ,@Quantity ,@UnitPrice ) --Creating the XML Message SELECT @XMLMessage = ( SELECT * FROM #Message FOR XML PATH('Order') ,TYPE ); DECLARE @Handle UNIQUEIDENTIFIER; --Sending the Message to the Queue BEGIN DIALOG CONVERSATION @Handle FROM SERVICE OrderService TO SERVICE 'OrderService' ON CONTRACT [postmessages] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @Handle MESSAGE TYPE ReceivedOrders(@XMLMessage); END GO |
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.
|
1 2 3 |
usp_CreateOrders 202003,'PD0001',1,10.50 usp_CreateOrders 202003,'PD0002',2,100.75 usp_CreateOrders 202003,'PD0010',1.5,20.00 |
When the above execution are completed you will see three records in the Orders table.
Since we have sent three messages to the OrderQueue, we can verify those entries from the following query.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT service_name ,priority, queuing_order, service_contract_name, message_type_name, validation, message_body, message_enqueue_time, status FROM dbo.OrderQueue |
Message body cannot be viewed as it is encrypted as shown below.
Now let’s consume the queue from the following code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
DECLARE @Handle UNIQUEIDENTIFIER ; DECLARE @MessageType SYSNAME ; DECLARE @Message XML DECLARE @OrderDate DATE DECLARE @OrderID INT DECLARE @ProductCode VARCHAR(50) DECLARE @Quantity NUMERIC (9,2) DECLARE @UnitPrice NUMERIC (9,2) WAITFOR( RECEIVE TOP (1) @Handle = conversation_handle, @MessageType = message_type_name, @Message = message_body FROM dbo.OrderQueue),TIMEOUT 1000 SET @OrderID = CAST(CAST(@Message.query('/Order/OrderID/text()') AS NVARCHAR(MAX)) AS INT) SET @OrderDate = CAST(CAST(@Message.query('/Order/OrderDate/text()') AS NVARCHAR(MAX)) AS DATE) SET @ProductCode = CAST(CAST(@Message.query('/Order/ProductCode/text()') AS NVARCHAR(MAX)) AS VARCHAR(50)) SET @Quantity = CAST(CAST(@Message.query('/Order/Quantity/text()') AS NVARCHAR(MAX)) AS NUMERIC(9,2)) SET @UnitPrice = CAST(CAST(@Message.query('/Order/UnitPrice/text()') AS NVARCHAR(MAX)) AS NUMERIC(9,2)) PRINT @OrderID PRINT @OrderDate PRINT @ProductCode PRINT @Quantity PRINT @UnitPrice |
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.
View all posts by Dinesh Asanka
- Testing Type 2 Slowly Changing Dimensions in a Data Warehouse - May 30, 2022
- Incremental Data Extraction for ETL using Database Snapshots - January 10, 2022
- Use Replication to improve the ETL process in SQL Server - November 4, 2021