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

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

• 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

• 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

ServiceMessageMapper

            SSBConversationMediator convMed = new SSBConversationMediator
(_mediator, _mySignal);
 
            convMed.OpenChannels(ConfigInfo.Source(),
ConfigInfo.Destination(this.MessageMapperID));
 
            //Run until the queue is empty then pause and check again.
            while (_mainSignal.ContinueProcessing)
            {
                if (convMed.IsConversationToProcess())
                {
                    convMed.Process();
                }
 
                Thread.Sleep(3000);
            }
 

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

            TransactionScope ReadMsgScope;
 
            while (IsConversationToProcess() && _signal.ContinueProcessing)
            {
                ReadMsgScope = new TransactionScope();
 
                //Begin transaction
                using (ReadMsgScope)
                {
                    _conv.ReadMessage();
 
 
                    if (_conv.IsTermMessage())
                    {
                        //Do nothing commit tran and move on
                    }
                    else
                    {
                        _mediator.Translate(_conv.CurrentMessage);
                    }
 
                    //Commits the changes
                    ReadMsgScope.Complete();
                }
 
            }

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

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

            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);
 
            xml.Load(msg.Body);
 
            param.Value = xml.OuterXml;
 
            cmd.ExecuteNonQuery();
 
            tran.Commit();

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();
            xml.Load(msg.Body);
 
            _msgQueue.Send(xml.OuterXml);
 

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

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.