SQL Server 2005 Service Broker Plays Well with Older Relatives

SQL Server 2005 Service Broker (SSB) is an asynchronous messaging technology built into SQL Server. With SSB, you essentially get the power of messaging technologies like Microsoft Message Queue (MSMQ) combined with the relational database capabilities of SQL Server, all controlled with Transact SQL statements.

However, because SSB ships only with SQL Server 2005, harnessing its power with other technologies such as SQL Server 2000 may be a bit of a mystery to you. You could get a third technology such as BizTalk involved to link the technologies. Maybe an alternative called external activation is the way to go, but SQL Server Books Online doesn't provide a clear way to implement external activation and it's complicated besides.

This article shows you a third, simpler approach to integrating SSB with other systems: using some new features in .NET 2.0 to build a simple polling solution.

Solution Overview

You could have many reasons for including non-SSB systems in a SSB messaging solution. For example, you may be transitioning portions of an application from SQL Server 2000 to SQL Server 2005, or making SQL Server 2005 part of a larger MSMQ messaging solution. In the interim, you may have a mix of SQL Server 2005, MSMQ, and SQL Server 2000.

In SQL Server 2005 Books Online, SSB queue external activation specifies monitoring and taking action on a SSB data queue based on a separate event activation SSB queue. External activation can be complicated. You must overcome a number of issues with this approach, including the following:

You must process the event message and the data messages in the same transaction. Because of distributed transaction SSB limitations (discussed later), processing multiple messages from multiple queues may not preserve data integrity.

You may need to process events from multiple queues if you dont use an event queue for every SSB data queue.

For these reasons, a simple polling solution makes more sense than external activation. This article prescribes such a solution, which periodically polls a SSB queue. It reads messages, one at a time, and writes them to the external target system. Figure 1 provides an overview of the solution.

Related Articles

Now you're ready to see how to implement the solution. Youll first look at the classes in the solution and then trace a message through the classes.

Class Overview

The key classes in the solution are RouterController, ServiceMessageMapper, SSBConversationMediator, and SSBConversation. The following are short explanations of the roles the classes plays in the application:

RouterController handles the starting and stopping of the application. All of the classes are contained in either RouterController or a container inside of a class contained in RouterController.

ServiceMessageMapper handles the initialization, starting, and stopping of the SSBConversationMediator.

SSBConversationMediator handles the interaction between the SSBConversation and a class derived from the ITransactedMessageMediator interface. It also controls the transaction between Service Broker and the target application.

SSBConversation manages the interaction with Service Broker. It sends TSQL commands to Service Broker and reads messages from a SSB queue.

The best way to understand how the classes interact is to trace the path of a message through the program, which the following sections do.

ServiceMessageMapper

RouterController controls object construction and determines whether the application will write to SQL Server 2000 or MSMQ, but all the execution starts in ServiceMessageMapper. ServiceMessageMapper controls how frequently polling occurs. The Run function executes on a separate thread inside the RouterController class. The Run function looks like this:

When a message appears in a SSB queue, ServiceMessageMapper calls the Process function in SSBConversationMediator to read the message and move it to the external system.

SSBConversationMediator

As stated previously, SSBConversationMediator coordinates copying messages from SSB to the external system. It houses the coordination inside a TransactionScope (the next section discusses how the TransactionScope works), as you can see in the following process function:

In this example, _conv is an SSBConversation class. SSBConversation issues the RECEIVE TSQL command to read and remove a XML message from an SSB queue. As previously noted, the ITransactedMessageMediator is an interface. Implementing an interface allows a developer to tailor message writing to the technical requirements of the external system.

TransactionScope and Distributed Transactions

Distributed transactions are all-or-nothing actions performed on multiple systems. A message passing from one server to another should be handled in a distributed transaction to ensure that data is not lost or duplicate messages are not processed. In SQL Server 2000 and MSMQ, Distributed Transaction Coordinator (DTC) coordinates the actions using DTC resource managers.

In the .NET 1.1 world, distributed transactions are implemented using a ServicedComponent class, some custom attributes, and DTC. DTC requires significant overhead to employ, and using ServicedComponent can be clumsy. The .NET 2.0 world is more streamlined and flexible. In .NET 2.0, transactionsdistributed or notcan be handled by a single object called TransactionScope. DTC is present in .NET 2.0, along with a lower-overhead technology called Lightweight Transaction Manager (LTM). Using a technique called promotion, TransactionScope negotiates between DTC and LTM, picking the most appropriate technology for the transaction.

Certain SQL Server operations (e.g., savepoints) inhibit the ability to execute distributed transactions. In my testing, SSB appeared to use savepoints, thus limiting my use of distributed transactions. So if you model your solution on the example, keep in mind that your transaction may not be completely distributed. How this can impact a solution is discussed further in the following section.