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.
—Dejan Nakarada-Kordic,
ASB Bank (New Zealand)
End of Article
