Don't Let Poison Messages Kill Processing in SQL Server 2005's Service Broker

I recently worked on a project in which a large number of messages arriving from various servers had to be centrally processed on a single SQL Server 2005 instance. I decided to use the asynchronous message queuing capabilities in SQL Server 2005’s Service Broker so that I didn’t have to write a lot of code.

After some trial and error, I achieved the best performance with the XML validation and encryption switched off. This was acceptable because all our communications were occurring between trusted clients on a secure network.

I tried to use the default stored procedure that Service Broker’s activation feature uses when a custom stored procedure isn’t provided. However, I encountered a problem with the way in which the poison messages (i.e., messages containing information that an application can’t process successfully) were handled. Service Broker’s default error-handling behavior is to retry five times, then disable the queue. Because one of the project requirements was to process each message as quickly as possible, this default behavior wasn’t acceptable. I needed a process that would put a poison message aside in an error table and allow the processing of good messages to continue.

To obtain the process I needed, I wrote an activation stored procedure, usp_qTarget_Activation, that uses a RECEIVE statement to cache up to 500 messages in a table variable for batch processing. (The clients are using a single conversation to send many messages.) This stored procedure performs message processing inside a TRY…CATCH construct. If an error is encountered, the transaction is rolled back and another stored procedure is called to remove the poison message from the queue. (Most of the errors encountered during XML message processing put the transaction into an uncommittable state, so the only way to deal with an error is to roll back the entire transaction and start another process to perform a cleanup.)

The usp_SB_ErrorManager stored procedure performs the poison message cleanup. When an error is encountered, usp_qTarget_Activation calls usp_SB_ErrorManager, with the conversation handle and poison message’s queuing order number as parameters. First, usp_SB_ErrorManager disables activation on the queue to briefly pause message processing during its run. Next, it processes the queued messages from the offending conversation up to the poison message. Nonpoison messages are sent back to the queue. Re-queuing is acceptable because the order of messages isn’t important. If the order is important, usp_SB_ErrorManager could be written to process good messages instead of re-queuing them. I chose the re-queuing approach to avoid duplicating message processing code in the error handler.

When usp_SB_ErrorManager reaches the poison message, no XML parsing or validation is performed to avoid the error that caused the problem in the first place. Instead, usp_SB_ErrorManager inserts the entire message into in a VARBINARY field in a special error table for troubleshooting later. Finally, the stored procedure enables queue activation and exits, enabling the regular service broker processing to continue. If another poison message is encountered, the whole process is repeated.

Another way to ensure continuous processing of good messages would be to avoid errors by validating every message. However, this approach would place significant processing overhead on the system. In addition, even the most extensive validation can overlook some error conditions, thereby exposing the system to a risk of downtime. My approach ensures that all errors are handled only when they actually happen. In a wellwritten system, errors rarely occur, so this approach improves overall system performance because you aren’t wasting resources on validating every message.

You can download the code needed to implement this approach on your system. Go to www.sqlmag.com, enter 100521 in the InstantDoc ID text box, and click the 100521.zip hotlink. The .zip file contains the following seven scripts: 01CreateDatabase.sql, 02CreateTables.sql, 03CreateErrorManagerProc.sql, 04CreateInitiatorQueueActivationProc.sql, 05Create- TargetQueueActivationProc.sql, 06CreateService- BrokerObjects.sql, and 07SendMessages.sql.

After you’ve downloaded the scripts, run them on a SQL Server 2005 instance in the order depicted by their filenames to create a database and deploy all the Service Broker components. The last script sends some sample messages to Service Broker. One of the messages contains invalid data to illustrate the error handling.