Designing a SQL Service Broker Control Bus

SQL Service Broker (SSB) is the new standard for distributed messaging solutions built on top of SQL Server 2005. Although SQL Server Management Studio provides some administration for SSB, its features are inadequate for complicated configuration and monitoring implementations. So, how do you build monitoring and configuration into a complicated SSB solution? You can start by building a control bus. A control bus serves the special configuration and monitoring needs of a distributed messaging solution by using the messaging infrastructure being monitored to implement control bus functions.

Using a sample application, this article shows you some techniques for implementing your own SSB control bus solution. First, it looks at what a control bus is and then explains why a control bus is better for complex SSB solutions than other configuration and monitoring solutions.

Features of a Control Bus

The book Enterprise Integration Patterns by Gregor Hohpe and Bobby Woolf outlines the requirements of a control bus. Because the book covers the finer points, I'm going to highlight some control bus features I implemented in the example.

A distributed messaging solution's asynchronous nature requires special care and feeding. Messaging solutions may be spread across multiple machines, making configuration difficult. A central place for all configuration simplifies administration. Also, components of a messaging solution may have only intermittent contact with one another, further complicating configuration and administration. Furthermore, all components may be configured and running but still be considered inoperable if messaging throughput is not at a particular sustained level.

The tools and techniques you use to implement a messaging solution make the perfect platform for implementing messaging solution monitoring and configuration. In SSB, you can build queues and messages to monitor other SSB queues and other pieces of the messaging infrastructure. You can perform configuration by submitting messages to the control bus queues. Monitoring and configuration queues built across systems resolve the intermittent contact issues.

Aside from being built on the messaging infrastructure, a control bus performs these other functions:

• A control bus provides ways to test the infrastructure and gather vital statistics on the messaging solution using heartbeat-like messages, testing messages, and statistic reporting messages.
• Configuration messages issued by a control bus can make adjustments to the components of a messaging solution.
• Typically, a control bus aggregates gathered information in a console-like application similar in look-and-feel to network-monitoring software.

So, how do you use SSB to implement the features described above? The sample application in this article shows you some of the techniques. First, look at what the sample does.

Sample Application Overview

The sample application contains two components: a desktop application hosting the console and a SQL database called ControlBus that hosts stored procedures and tables implementing all of the SSB TSQL code. Figure 1 shows a screen shot of the console.

Click here for a larger image.

Figure 1. Screen Shot of Console Desktop Application

The ControlBus database contains a set of queues built to simulate a messaging solution and a queue providing the platform for control bus features. A test stored procedure posts messages to the simulated messaging solution. All queues in the simulated messaging solution receive an XML message and activate a stored procedure, which empties the queue.

When you press the "Send Heartbeat" button, a stored procedure posts heartbeat request messages to all queues in the simulated messaging solution. "Refresh" reads statistics gathered by the control bus stored procedures.

Now, look at how the console desktop application has been implemented.

Console Desktop Application

Figure 2 depicts the application class dependencies.

Figure 2. Application's Class Dependencies

SSBGateway controls activity with Service Broker. It utilizes the SSBSQLCommandExec class to call stored procedures in the ControlBus database. The Transmit function calls stored procedures initiating outbound actions in SSB. The following is the submit function:

public void Transmit(ControlMessage msg)
{
   _exec.RunCmd(msg.ProcToRun);
}

Read reads and consumes inbound data from SSB. For each record consumed, Read invokes the delegate function parameter. The following is the Process function:

public bool Read(out ControlMessage msg)
{
   bool isMsg = true;
   msg = new ControlMessage();
   isMsg = _dr.Read();
   if (isMsg)
   {
      ... ...
   }
   else
   {
      _dr.Close();
   }

   return (isMsg);
}

SSBGatewayController maintains an SSBGateway class instance. The form invokes the appropriate function in the SSBGatewayController, depending on the button pushed.

All of the real functionality is handled by the p_SendControlBusMessage and p_ReceiveChannel_ControlBusData stored procedures. The p_SendControlBusMessage procedure handles the configuration, heartbeat, and statistics attributes of the control bus, and it is invoked in various parts of the example. The p_ReceiveChannel_ControlBusData procedure processes the posted XML message.

Now, delve into how p_SendControlBusMessage and p_ReceiveChannel_ControlBusData work.

p_SendControlBusMessage and p_ReceiveChannel_ControlBusData

Figure 3 depicts the queue layout in the ControlBus database using some notation from Enterprise Integration Patterns.

Figure 3. Queue Layout in the ControlBus Database

The ControlBusQueue accepts and processes all of the control bus messages. The p_SendControlBusMessage procedure posts all of its messages to the ControlBusQueue, and accepts two parameters:

• Queue Name, the name of the queue posting the message
• Message Type, the message type to create in the queue

By using the BEGIN DIALOG and SEND ON CONVERSATION TSQL statements, p_SendControlBusMessage creates the XML message and posts the message to the ControlBusQueue.

Meanwhile, p_ReceiveChannel_ControlBusData is set to activate when data is posted to ControlBusQueue. It reads the queue, extracts the message type, and calls TSQL statements to carry out the appropriate activity. Results of all control bus activities are stored in the CurrentQueueData table.

The following section explains how each control bus message is implemented. For brevity, it refers to the processed messages by the last word in the message name.

Looking for a Heartbeat

As stated previously, the Heartbeat function of a control bus determines whether queues are working properly. In the sample, Heartbeat simply determines whether the queue is enabled or disabled. In a more complete solution, a Heartbeat may collect more information about the queue and the machine or database on which the queue is running.

Heartbeat is invoked by the control bus desktop application.

Config

Typically, a configuration message allows the control bus to change something on a message queue. For example, you may want to enable or disable a message queue or change the stored procedure doing the activation on a message queue. The sample simply logs a message to the SQL Server event log.

Configuration is invoked by the control bus desktop application.

Collect Stats

Stats updates the number of processed messages each time it is invoked. Stats is invoked differently than Heartbeat and Config. For Stats, each time a message is processed by a particular simulated data queue, p_SendControlBusMessage is invoked.

Extending the Sample

As you think about your own control bus implementation, keep the following ideas in mind:

1. You probably should divide your solution into two applications, a Windows service that periodically initiates things such as Statistics collection and Heartbeat, and a desktop application that reads information and handles user-initiated actions. You can wrap much of the functionality in stored procedures rather than calling every TSQL statement from .NET code.
2. Like all good application development, your solution should be responsive. So, you probably should make it multithreaded. Exception handling also will be important inside the service application and inside the stored procedures. You need to consider how you will monitor that which does the monitoring. Tools such as Microsoft Operations Manager (MOM) can be helpful here.
3. Consider how you would deploy the application. Do you create one ControlBusQueue per database or per server? Much will depend on the size of your deployment.
4. Everything in SSB is generated using TSQL. So, you can store template scripts in a control bus administration database and create services from the template code.
5. The SQL Server 2005 CLR functionality can be leveraged to extend the functionality of your solution.

Accommodate Complex Messaging

A control bus built with the SQL Server Service Broker will nicely serve the special configuration and monitoring needs of a more complex SQL Server-based messaging solution. Examples of control bus functions are queue statistics, heartbeat-like messages, and even configuration messages. A proper control bus solution will include a Windows service, some sort of desktop application for user interaction, and stored procedures.

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.