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.

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.

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read