In the previous article in this series I introduced Service Broker to you. You learned the core concepts of SQL Server Service Broker. You learned how to write simple Service Broker applications that are composed of message types, contracts, queues, and services. But what are the real advantages of message based Service Broker programming compared to other messaging technologies like MSMQ, and in which scenarios you can make an effective use of Service Broker?

Before we compare Service Broker to other message based technologies, I want to give you a short overview of why messaging is such an important concept and why Microsoft integrated a messaging framework directly into SQL Server. When you use messaging technologies in your applications it can have a very positive impact on performance and throughput. Messaging provides you several advantages. Mainly I want to look more into the following concepts:

• Asynchronous Message Processing
• Deferred Message Processing

Asynchronous Message Processing

Asynchronous message processing means that the sender (the Service Broker Initiator Service) can continue working while waiting for the receiver (the Service Broker Target Service) to process and eventually reply to the message. You can make advantage of asynchronous message processing when you want to decouple long-running business processes from a client application. The client application accepts the input from the user, creates a message from that input, and sends the message to another Service Broker service, which processes the message asynchronously in the background. One advantage of this approach is the fact that client applications are more responsive and users can do other things in the meantime within the client application.

Asynchronous message processing is a concept that you are using regularly in your day-to-day life when you are sending and receiving emails. As soon as you have sent an email to someone else, you are not just sitting around waiting until you get back a response from the other person. You work on other things in the meantime, you may even shut down and restart your computer, or you might even gooff on holiday for a few days. But the great thing about all those options is the fact that you will be able to receive a response email, even if you are not sitting at your computer when it arrives, or even if you are completely offline! Thisis one of the key aspects of asynchronous message processing: you don’t have to wait until you actually receive the result.

Deferred Message Processing

Deferred message processing goes hand in hand with asynchronous message processing, but there is an interesting difference between these two concepts: asynchronous message processing specifies only that work is done at a later time, while deferred message processing specifies when the work is actually done. Messages can be processed immediately, in a few hours, or in a few days – it depends only on the configuration of the messaging system. With deferred message processing you get the following advantages:

• The receiver controls when messages are processed
• Load balancing occurs between several servers and during peak times
• Fault tolerance exists on the receiving side

Let’s assume you run a messaging system based on Service Broker in a company where a high message load occurs during normal work hours. With deferred message processing, you can configure Service Broker so that messages are only put into the queue during the day and are processed overnight when more resources are available. This means that user requests can be processed more quickly, and applications are more responsive under high loads. This is similar to doing automatic database backups at night—another case where demand is less, and more resources are available at night.

With load balancing, you can define the server on which a message should be processed. This is useful when you have a large volume of messages that must be processed quickly. You can deploy several instances of a Service Broker service and have clients connect to one of them. If you must take an instance offline, perhaps to perform some maintenance work on the server, your messaging system will continue to work without any problems or changes in configuration, because new requests from clients will be distributed among the other available Service Broker instances.

Service Broker and SQL Server

As you can see from the previous description there are a lot of advantages with message processing. But why the heck did Microsoft integrate message processing directly into SQL Server? There are also other messaging products available on the Microsoft platform – like MSMQ (Microsoft Message Queuing). MSMQ has been available as part of Windows since the first version of Windows NT. MSMQ was the first messaging technology from Microsoft used to provide messaging capabilities for a wide range of business applications. One of the biggest advantages of MSMQ is that it is licensed and distributed with Windows, so you don’t have any additional licensing costs when you use it in your own applications. In addition, it’s not bound to any specific database product. If you want to use Oracle with MSMQ, you can do it without any problems. However, as with every product and technology, there are also some drawbacks, including the following:

• Message size is limited to 4MB.
• MSMQ is not installed by default. Furthermore, you need the Windows installation disk to install MSMQ.
• You need distributed transactions if you want to run the message processing and data-processing logic in one Atomic, Consistent, Isolated, and Durable (ACID) transaction. This requires installation of the Microsoft Distributed Transaction Coordinator (MS DTC).
• Message ordering is not guaranteed.
• Message correlation is not supported out of the box.
• You must implement queue readers manually.
• You must conduct synchronization and locking between several queue readers manually.
• Backup and restoration can be a challenge, because message data and transactional data are stored in different places.

One of the biggest disadvantages of MSMQ is the fact that you are working with two resource managers: the database that stores your data, and MSMQ messages that contains requests that must be fulfilled. This means that you have to use distributed transactions (through the use of MSDTC) to guarantee the ACID properties of your transactions. But distributed transactions management is not always straightforward and easy to setup and configure, and mismanaged distributed transactions can have a big negative impact on the overall performance of your application.

With Service Broker the data and the messages are stored and manipulated within the context of the database. There is only one resource manager involved – SQL Server. Therefore you don’t have the need for distributed transactions - everything is done within the context of a local SQL Server transaction:

• Message Receiving/Sending
• Message Processing

The same applies to backups and restores. With MSMQ you have to ensure that the backup of your messaging data and transactional data is consistent and synchronized. This is a really difficult task and very hard to accomplish when you are running a 24x7 production application. As you can see, there are several disadvantages when you use messaging systems that are not integrated directly into the database. This is the point where Service Broker comes into the picture, and where the usage of Service Broker makes a lot of sense.

Service Broker Scenarios

After you have learned in the previous section why Service Broker makes sense, I want to give you a more concrete example of how you can use Service Broker within your own database applications. The scenario that I’m describing comes from a customer engagement, where I helped my customer to improve the performance of their database driven application. One of their main problems was the fact that they had to write a file to a remote network share when an INSERT occurred on a specific database table. In the first step this functionality was implemented with a database trigger on that table – and the performance of their INSERT statements were extremely slow. After we reviewed their requirements we decided to implement an asynchronous database trigger through the use of Service Broker. Figure 1 illustrates this interesting concept.

Figure 1: Trigger processing may be partly synchronous and partly asynchronous

As you can see from Figure 1, the only thing that the trigger does is create a new Service Broker message and send that message to the Service Broker target service. This means that the work that was done prior that implementation synchronously inside the trigger is now done outside of the trigger in an asynchronous fashion. Therefore the trigger will have less of a performance impact. As you can also see, this Service Broker solution implements a one-way messaging between the database trigger and the message processing logic that is implemented in a Service Broker service. With this pattern the target service never sends any message back to the initiator service. The only question that arises from this pattern is whether you want to start a new conversation for each message sent or if you want to reuse Service Broker conversations? The recommendation is to reuse conversations wherever it is possible, for the following reasons:

• Reusing conversations has a significant positive performance impact. The cost of setting up and tearing down a conversation for each message can influence the performance by a factor of four.
• The advantage of having multiple messages on the same conversation can speed up processing on the receivers side by a factor of about 10.

The asynchronous trigger in this example uses the SessionConversations table to store opened Service Broker conversations so that they can reused at a later time. Listing 1 shows the creation of our sample database and the definition of this table.

CREATE DATABASE AsynchronousTrigger
GO
-- Setting the Trustworthy property for assemblies with
-- EXTERNAL ACCESS permissions
ALTER DATABASE AsynchronousTrigger SET TRUSTWORTHY ON
GO
USE AsynchronousTrigger
GO
CREATE TABLE SessionConversation
(
   SPID INT NOT NULL,
   FromService SYSNAME NOT NULL,
   ToService SYSNAME NOT NULL,
   OnContract SYSNAME NOT NULL,
   ConversationHandle UNIQUEIDENTIFIER NOT NULL,
   PRIMARY KEY (SPID, FromService, ToService, OnContract),
   UNIQUE (ConversationHandle)
)
GO

Listing 1: Creating a table to store open Service Broker conversations

As you can see from listing 1, the SessionConversations table stores the conversation handle tied to the combination of a SPID, the initiator service, the target service, and the contract. Figure 2 shows the Service Broker objects that are needed for this example.

Figure 2: The necessary Service Broker objects for that solution

The [http://ssb.csharp.at/CustomerInsertedRequestMessage] message type is sent to the target service as soon as the trigger is fired and the initiator service pushes the inserted data asynchronously to the target service. The [http://ssb.csharp.at/EndOfMessageStream] message type is also sent from the initiator service to the target service as soon as the set up dialog timer message arrives after the configured expire timeout (5 seconds), indicating that the current conversation between the initiator service and the target service should be closed. Listing 2 shows you the definition of those Service Broker objects that you have to create in the AsynchronousTrigger database.

-- Create the request message types
CREATE MESSAGE TYPE 
   [http://ssb.csharp.at/CustomerInsertedRequestMessage]
   VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE 
   [http://ssb.csharp.at/EndOfMessageStream]
   VALIDATION = WELL_FORMED_XML
GO
-- Create the contract based on the previous 2 message types
CREATE CONTRACT [http://ssb.csharp.at/CustomerInsertContract]
(
   [http://ssb.csharp.at/CustomerInsertedRequestMessage] SENT BY INITIATOR,
   [http://ssb.csharp.at/EndOfMessageStream] SENT BY INITIATOR
)
GO
-- Create the service queue
CREATE QUEUE [CustomerInsertedServiceQueue]
GO
-- Create the client queue
CREATE QUEUE [CustomerInsertedClientQueue]
GO
-- Create the service
CREATE SERVICE [CustomerInsertedService] 
   ON QUEUE [CustomerInsertedServiceQueue]
(
   [http://ssb.csharp.at/CustomerInsertContract]
)
GO
-- Create the client service
CREATE SERVICE [CustomerInsertedClient]
   ON QUEUE [CustomerInsertedClientQueue]
(
   [http://ssb.csharp.at/CustomerInsertContract]
)
GO

Listing 2: Creating all the necessary Service Broker objects

After you have created the needed Service Broker objects, you also have to register some SQLCLR assemblies that are implementing the asynchronous message processing of the database trigger. You have to change the location of the .DLL files accordingly to your local setup. Listing 3 shows the necessary DDL statements that import the SQLCLR assembly from the local file system and register the Managed Stored Procedure ProcessInsertedCustomers.

-- Create the request message types
CREATE MESSAGE TYPE 
   [http://ssb.csharp.at/CustomerInsertedRequestMessage]
   VALIDATION = WELL_FORMED_XML
GO
-- Import assembly into the database
CREATE ASSEMBLY [CustomerManagement]
FROM ‘c:\AsynchronousTrigger.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
-- Add the debug information about the assembly
ALTER ASSEMBLY [CustomerManagement]
ADD FILE FROM 'c:\AsynchronousTrigger.pdb'
GO
-- Add the debug information about the ServiceBrokerInterface assembly
ALTER ASSEMBLY [ServiceBrokerInterface]
ADD FILE FROM 'c:\ServiceBrokerInterface.pdb'
GO
-- Create the managed stored procedure
CREATE PROCEDURE [ProcessInsertedCustomer]
AS EXTERNAL NAME [CustomerManagement].[AsynchronousTrigger.TargetService].[ServiceProcedure]
GO

Listing 3: Importing SQLCLR assemblies

Listing 4 shows the T-SQL code for a database trigger called OnCustomerInserted, which is defined on the Customers table that is also created in Listing 4. As we’ll see, as soon as the database trigger starts a new Service Broker conversation with the target service, it also inserts the current conversation handle into this table for further use.

-- This table stores our customers
CREATE TABLE Customers
(
   ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
   CustomerNumber VARCHAR(100) NOT NULL,
   CustomerName VARCHAR(100) NOT NULL,
   CustomerAddress VARCHAR(100) NOT NULL,
   EmailAddress VARCHAR(100) NOT NULL
)
GO
CREATE TRIGGER OnCustomerInserted ON Customers FOR INSERT
AS
   DECLARE @conversationHandle UNIQUEIDENTIFIER
   DECLARE @fromService SYSNAME
   DECLARE @toService SYSNAME
   DECLARE @onContract SYSNAME
   DECLARE @messageBody XML
   SET @fromService = 'CustomerInsertedClient'
   SET @toService = 'CustomerInsertedService'
   SET @onContract = 'http://ssb.csharp.at/CustomerInsertContract'
   -- Check if there is already an ongoing conversation with the TargetService
   SELECT @conversationHandle = ConversationHandle FROM SessionConversations
      WHERE SPID = @@SPID
      AND FromService = @fromService
      AND ToService = @toService
      AND OnContract = @onContract
   IF @conversationHandle IS NULL
   BEGIN
      -- We have to begin a new Service Broker conversation with the
      -- TargetService
      BEGIN DIALOG CONVERSATION @conversationHandle
         FROM SERVICE @fromService
         TO SERVICE @toService
         ON CONTRACT @onContract
         WITH ENCRYPTION = OFF;
      -- Create the dialog timer for ending the ongoing conversation
      BEGIN CONVERSATION TIMER (@conversationHandle) TIMEOUT = 5;
      -- Store the ongoing conversation for further use
      INSERT INTO SessionConversations
      (SPID, FromService, ToService, OnContract, 
         ConversationHandle)
      VALUES
      (
         @@SPID,
         @fromService,
         @toService,
         @onContract,
         @conversationHandle
      )
   END
   -- Construct the request message
   SET @messageBody = (SELECT * FROM INSERTED FOR XML AUTO, ELEMENTS);
   -- Send the message to the TargetService
   ;SEND ON CONVERSATION @conversationHandle
   MESSAGE TYPE [http://ssb.csharp.at/CustomerInsertedRequestMessage] 
      (@messageBody);
GO

Listing 4: Creating a trigger to insert the current conversation handle into the table storing conversation data

If there is no opened conversation available, a new Service Broker conversation is started with the BEGIN DIALOG CONVERSATION T-SQL statement. Before the conversation handle of the created conversation is inserted into the SessionConversations table, a dialog timer is instantiated on the current conversation. A dialog timer is a component inside Service Broker that sends you a timer message in the specified amount of time – in this case after 5 seconds. The received dialog timer message is then used to indicate to the target service that the current conversation should be closed – in other words, deleting the corresponding conversation record from the SessionConversations table. If this is the case at the next time the database trigger creates a new conversation between the initiator service and the target service and a new conversation is recreated at the initiator service.

Now let’s take a as soon as a dialog timer message or EndDialog message arrives. Listing 4shows the detailed look at the ProcessCustomerInsertedClientQueue stored procedure that gets automatically activated at the initiator’s queue CustomerInsertedClientQueue implementation of this stored procedure.

CREATE PROCEDURE ProcessCustomerInsertedClientQueue
AS
   DECLARE @conversationHandle UNIQUEIDENTIFIER;
   DECLARE @messageTypeName SYSNAME;
   BEGIN TRANSACTION;
   RECEIVE TOP(1) 
      @conversationHandle = conversation_handle,
      @messageTypeName = message_type_name
   FROM CustomerInsertedClientQueue;
   IF @conversationHandle IS NOT NULL
   BEGIN
      DELETE FROM SessionConversations
      WHERE ConversationHandle = @conversationHandle;
      IF @messageTypeName = 
         'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer'
      BEGIN
         SEND ON CONVERSATION @conversationHandle MESSAGE TYPE 
            [http://ssb.csharp.at/EndOfMessageStream];
      END
      ELSE IF @messageTypeName = 
         'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
      BEGIN
         END CONVERSATION @conversationHandle;
      END
   END
   COMMIT TRANSACTION;
GO
-- Activate internal activation on the InitiatorService
ALTER QUEUE [CustomerInsertedClientQueue]
WITH ACTIVATION 
(
   STATUS = ON,
   PROCEDURE_NAME = ProcessCustomerInsertedClientQueue,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS SELF
)
GO

Listing 4: Creating the stored procedure that gets executed when the EndDialog or timer message arrives

After you set up the whole Service Broker infrastructure, you’ll need a service program that processes the incoming message from the CustomerInsertedClient and executes the required business functionality. For this sample, you can create the SQLCLR based stored procedure, ProcessInsertedCustomer, which gets activated automatically as soon as a new message arrives on the CustomerInsertedServiceQueue.

The stored procedure is able to consume and process the incoming [http://ssb.csharp.at/CustomerInsertedRequestMessage] and [http://ssb.csharp.at/EndOfMessageStream] message types. Listing 5 shows how both message types are processed within the managed stored procedure that you have deployed in Listing 3 through the SQLCLR assembly. You can find the C# code for the SQLCLR assembly in the file TargetService.cs in the folder AsynchronousTrigger.

[BrokerMethod("http://ssb.csharp.at/CustomerInsertedRequestMessage")]
public void OnCustomerInsertedRequestMessage(
   Message ReceivedMessage,
   SqlConnection Connection,
   SqlTransaction Transaction)
{
   WriteCustomerDetails(ReceivedMessage.BodyAsString);
}
private static void WriteCustomerDetails(string xmlMessage)
{
   // Loading the message into a XmlDocument
   XmlDocument xmlDoc = new XmlDocument();
   xmlDoc.LoadXml(xmlMessage);
   // Appening data to the text file
   using (StreamWriter writer = new StreamWriter(@"c:\InsertedCustomers.txt", true))
   {
      // Writing the message to the file system
      writer.WriteLine("New Customer arrived:");
      writer.WriteLine("=====================");
      writer.WriteLine("CustomerNumber: " + 
         xmlDoc.SelectSingleNode("//CustomerNumber").InnerText);
      writer.WriteLine("CustomerName: " + 
         xmlDoc.SelectSingleNode("//CustomerName").InnerText);
      writer.WriteLine("CustomerAddress: " + 
         xmlDoc.SelectSingleNode("//CustomerAddress").InnerText);
      writer.WriteLine("EmailAddress: " + 
         xmlDoc.SelectSingleNode("//EmailAddress").InnerText);
      writer.Close();
   }
}
[BrokerMethod("http://ssb.csharp.at/EndOfMessageStream")]
public void EndConversation(
   Message ReceivedMessage,
   SqlConnection Connection,
   SqlTransaction Transaction)
{
   // Ends the current Service Broker conversation
   ReceivedMessage.Conversation.End(Connection, Transaction);
}

Listing 5: The implementation code of the SQLCLR assembly

By now you have implemented the whole functionality to make the asynchronous database trigger work. You can now test the functionality of the trigger by inserting a new record into the table Customers.

INSERT INTO Customers
(
   ID,
   CustomerNumber,
   CustomerName,
   CustomerAddress,
   EmailAddress
)
VALUES
(
   NEWID(),
   'AKS',
   'Aschenbrenner Klaus',
   'A-1220 Vienna',
   'Klaus.Aschenbrenner@csharp.at'
)

In this case the trigger establishes a new Service Broker conversation with the target service, and sends a message to the service for further processing. You can now process the sent message by calling the SQLCLR based stored procedure ProcessInsertedCustomer. After calling the stored procedure it will write the text file c:\InsertedCustomers.txt to the file system, which contains the data of the inserted record.

Conclusion

As you have seen with this example it is very easy to decouple the message sending and message processing steps to introduce asynchronous message processing into your database applications.