SQL Server 2005 Service Broker Plays Well with Older Relatives

Friday Sep 8th 2006 by Jeffrey Juday

You can leverage SQL Server 2005 Service Broker with existing technologies such as SQL Server 2000 and MSMQ. BizTalk and SSB external activation are viable options, but a simple polling solution is easier.

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.

Click here for a larger image.

Figure 1. Overview of Polling Solution

For the following reasons, the solution also opts for a separate application rather than using the new CLR features for SQL Server 2005:
  • Connections and other communication constructs to the external target system can be initialized and cached for quick execution.
  • A CLR assembly in SQL Server 2005 communicating with external resources requires special permissions and configuration.

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.

Figure 2 provides an overview of all the classes.

Click here for a larger image.

Figure 2. Overview of Key Classes in Polling Solution

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.


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:
            SSBConversationMediator convMed = new SSBConversationMediator
(_mediator, _mySignal);
            //Run until the queue is empty then pause and check again.
            while (_mainSignal.ContinueProcessing)
                if (convMed.IsConversationToProcess())

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.


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:
            TransactionScope ReadMsgScope;
            while (IsConversationToProcess() && _signal.ContinueProcessing)
                ReadMsgScope = new TransactionScope();
                //Begin transaction
                using (ReadMsgScope)
                    if (_conv.IsTermMessage())
                        //Do nothing commit tran and move on
                    //Commits the changes

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.

ITransactedMessageMediator—SQL Server 2000 and MSMQ

ITransactedMessageMediator requires a Translate and an Init function. Translate accepts an SSBMessage class. TransactedMessageMediator_SQL2000 implements the ITransactedMessageMediator interface and writes SSBMessage to a table on the external SQL Server 2000 database. The following is an excerpt from the Translate function:
            SqlParameter param;
            SqlCommand cmd;
            string query;
            XmlDocument xml = new XmlDocument();
            SqlTransaction tran;
            tran = _conn.BeginTransaction();
            cmd = _conn.CreateCommand();
            cmd.Transaction = tran;
            query = "EXEC InsertTestSSB @body ";
            cmd.CommandText = query;
            param = cmd.Parameters.Add("@body", SqlDbType.Text);
            param.Value = xml.OuterXml;

Ideally, the SqlConnection would enlist the existing transaction rather than implementing its own transaction. Unfortunately, for reasons discussed previously, enlisting was not possible. Therefore, if you build on the example, consider the impact of duplicate messages in your solution.

Like SQL Server 2000, MSMQ can perform transactions. TransactedMessageMediator_MSMQ implements ITransactedMessageMediator for MSMQ. The following is the Translate function implementation:

            XmlDocument xml = new XmlDocument();

You can also construct more complicated implementations of ITransactedMessageMediator. For example, given a message’s contents or header information, you could build another layer of classes to route a message to one of many tables, databases, message queues, or servers.

New Asynchronous Messaging Technology

See, you can leverage SQL Server 2005 Service Broker with existing technologies such as SQL Server 2000 and MSMQ. BizTalk and SSB external activation are certainly options to consider, but an easier solution is using some of the new features in .NET 2.0 to build a simple polling solution. Whichever solution you choose, pay close attention to distributed transactions and duplicate messages.

Download the Code

To download the accompanying source code for the example, click here.

About the Author

Jeffrey Juday is a software developer with Crowe Chizek in South Bend, Indiana. He has been developing software with Microsoft tools for more than 12 years in a variety of industries. Jeff currently builds solutions using BizTalk 2004, ASP.NET, SharePoint, and SQL Server 2000. You can reach Jeff at jjuday@crowechizek.com.

Please enter your content here.
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved