Service Broker in SQL

Service Broker

Service Broker is a feature in Microsoft SQL Server that enables asynchronous messaging and queuing between database instances or applications. It allows you to build scalable and reliable applications by decoupling the processing of requests from the actual execution of those requests.

When to use Service Broker?

Use Service Broker factors to apply native in- database asynchronous communication processing functionalities. operation inventors who use Service Broker can distribute data workloads across several databases without programming complex communication and messaging internals. Service Broker reduces development and test work because Service Broker handles the communication paths in the environment of a discussion. It also improves performance.

For illustration, frontal- end databases supporting Web spots can record information and shoot process ferocious tasks to line in back- end databases.Service Broker ensures that all tasks are managed in the environment of deals to assure trustability and specialized thickness.

• Conversation: Service Broker uses a concept called "conversations." A conversation is a two-way, asynchronous exchange of messages between two endpoints. Endpoints can be database instances or external applications.
• Message Types: Service Broker supports different message types, each with its own message format. These message types define the structure of the data being exchanged.
• Queues: Messages are sent to queues, which act as message stores. Queues hold the messages until they are processed by the receiving application or service.
• Services: Services represent logical endpoints in the Service Broker architecture. Services provide the kinds of messages they may process as well as the queues from which they will receive messages.
• Initiator and Target: In a Service Broker conversation, one endpoint initiates the conversation (initiator), and the other endpoint responds to it (target).
• Activation: service Broker enables activation, which means that when a communication arrives in a line, it can automatically spark a stored procedure or external operation to reuse that communication.
• Reliable Delivery: Service Broker ensures reliable delivery of messages, even in the event of system failures or outages. Messages are persisted in the database, and a conversation's state is maintained to guarantee message delivery.

Now let's create a sample where we imeplement our service broker objects. For that first we need to enable Service Broker on the database

                                
                                    
  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
                                
                            

MESSAGE TYPE

The providing a SQL Server Transact-SQL statement to create a message type named "ReceivedOrders." In the SQL Server Service Broker, message types are used to define the format and structure of messages that can be sent between services. This specifies the proprietor or the schema that will enjoy the communication type. In this case, the communication type is possessed by the" dbo" schema, which generally represents the database proprietor. his clause indicates the message validation setting for the message type.In this case, it's set to" None," which means that no confirmation will be performed on the communication content when dispatches of this type are transferred or entered.

                                
                                    
  MESSAGE TYPE ReceivedOrders
  AUTHORIZATION dbo
  VALIDATION = None;
                                
                            

Contract

SQL Server Service Broker, a contract defines the allowed message types that can be sent and received by a service. When you produce a contract, you specify the communication types that can be transferred( transferred in) and the communication types that can be entered( Admit in) by the service.

                                
                                    
  CREATE CONTRACT postmessages
  (
      ReceivedOrders SENT BY ANY
  );
                                
                            

Queue

SQL Server Service Broker, a queue is a storage object used to hold messages that are sent to a service. The messages in the queue are processed asynchronously, allowing for decoupled communication between services. This option specifies that the queue is enabled and ready to receive messages. When the queue is created with STATUS = ON, it is activated, and messages can be sent to it immediately. This option specifies that message retention is turned off for the queue. When retention is off, messages are removed from the queue as soon as they are delivered to the receiving service. If you set retention to ON (which is the default), messages are retained in the queue until they are explicitly removed or purged.

                                
                                    
  CREATE QUEUE OrderQueue
  WITH STATUS = ON,
  RETENTION = OFF;
                                
                            

Service

This option specifies that message retention is turned off for the queue. Messages are removed from the queue as soon as they are delivered to the receiving service when retention is turned off. If you set retention to ON (which is the default), messages are retained in the queue until they are explicitly removed or purged. This specifies the owner or the schema that will own the service. In this case, the service is owned by the "dbo" schema, which typically represents the database owner. This option associates the service with the "OrderQueue" queue. Messages sent to the "OrderService" will be stored in the "OrderQueue" and processed asynchronously.

                                
                                    
  CREATE SERVICE OrderService
  AUTHORIZATION dbo
  ON QUEUE OrderQueue
  ([postmessages]);
                                
                            

Let's now look at how the SQL Server Management Studio (SSMS) displays the created SQL Service Broker objects.

Below image displays the list of Service Broker Objects that we have created

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.

                                
                                    
  CREATE TABLE [dbo].[Orders](
  [ID] [int] NOT NULL,
  [Date] [date] NULL,
  [Code] [varchar](50) NOT NULL,
  [Quantity] [numeric](9, 2) NULL,
  [Price] [numeric](9, 2) NULL,
  CONSTRAINT [PK__Orders] PRIMARY KEY CLUSTERED 
  (
    [ID] ASC,
    [Code] ASC
  )
  ON [PRIMARY]
  ) ON [PRIMARY]
  GO
                                
                            

To produce a stored procedure that inserts records into the" Order" table and sends a communication to the" OrderQueue" line using the SQL Garçon Service Broker. Assuming you already have the "Order" table created in your database, you can skip this step. If not, make a table called "Order" with the necessary columns to hold order data.

Creating the Stored Procedure

Use the following SQL code to create the stored procedure that inserts records into the "Order" table and sends a message to the "OrderQueue" queue

                                
                                    
  CREATE PROCEDURE UserCreateOrders (
    @ID INT
    ,@Code VARCHAR(50)
    ,@Quantity NUMERIC(9, 2)
    ,@Price NUMERIC(9, 2)
    )
  AS
  BEGIN
    DECLARE @OrderDate AS SMALLDATETIME
    SET @OrderDate = GETDATE()
    DECLARE @XMLMessage XML

    CREATE TABLE #Message (
      ID INT PRIMARY KEY
      ,OrderDate DATE
      ,Code VARCHAR(50)
      ,Quantity NUMERIC(9, 2)
      ,Price NUMERIC(9, 2)
      )

    INSERT INTO #Message (
      ID
      ,OrderDate
      ,Code
      ,Quantity
      ,Price
      )
    VALUES (
      @ID
      ,@OrderDate
      ,@Code
      ,@Quantity
      ,@Price
      )
    --Insert to Orders Table
    INSERT INTO Orders (
      ID
      ,OrderDate
      ,Code
      ,Quantity
      ,Price
      )
    VALUES (
      @ID
      ,@OrderDate
      ,@Code
      ,@Quantity
      ,@Price
      )
      --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, An XML-formatted message can be sent to the ReceivedOrders queue using the SEND command. Now let's execute the stored procedure using the following commands

                                
                                    
  userCreateOrders 100,'s01',1,100.50
  userCreateOrders 100,'s02',2,1000.75
  userCreateOrders 100,'s010',1.5,100.00
                                
                            

After running the stored procedures we can see the entry in the table

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

                                
                                    
  SELECT service_name
  ,priority,
  queuing_order,
  service_contract_name,
  message_type_name,
  validation,
  message_body,
  message_enqueue_time,
  status
  FROM dbo.OrderQueue
                                
                            

Service Broker is a important point handed by Microsoft SQL Garçon that enables asynchronous, communication- grounded communication between database factors, known as services. It is designed to facilitate scalable, reliable, and loosely coupled interactions among various components within a database or across distributed databases. Service Broker enables asynchronous communication, allowing services to shoot and admit dispatches singly without staying for immediate responses. This decoupled architecture enhances application responsiveness and scalability. Service Broker acts as a message-oriented middleware, providing a reliable and efficient messaging framework for communication between services. It ensures message delivery, ordering, and transactional integrity.