Designing a SQL Service Broker Control Bus, Page 2
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.
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.
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:
- 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.
- 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.
- 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.
- 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.
- 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 email@example.com.