SQL Server 2008 R2 Service Broker Changes

  • Article

Having been involved with a large SQL Server Service Broker deployment and having to deal with the challenges of having to process conversations across thousands of endpoints and the requirement to process messages from multiple conversations in parallel as efficiently as possible whilst still dealing with poison messages efficiently.

Poison Message Handling

As part of this challenge there is a requirement to mange message receive batch sizes across multiple processes and the associated processing failures and rollbacks whilst still trying to ensure that no more than 5 consecutive rollbacks on the queue does not happen and as a consequence disable the queue. I say try, because this is very complicated and often involves sub-optimal design trade offs between performance and reliability.

SQL Server 2008 R2 introduces the capability to execute the CREATE QUEUE or ALTER QUEUE with the addition of POISON_MESSAGE_HANDLING ( [ STATUS = { ON | OFF } ) syntax through which it is possible to disable the default poison message handling behavior. See SQL Server 2008 R2 Books Online.

This does not eliminate the requirement of ensuring that you have a well thought out poison message handling routine, however it does allow this routine to be defined in a manner focused on performance.

In a future blog I will describe some of the alternative approaches to managing conversations, conversation aggregation and parallel message processing in the presence of poison messages that I devised in addition to those documented on the Service Broker blog post on the subject.

Conversation Priorities

One of the feature enhancements previously released with SQL Server 2008  that greatly enhance the capabilities of Service Broker to deal with complex messaging scenarios are conversation priorities where user-defined rules are used to specify a priority level and the criteria for determining which Service Broker conversations to assign the priority level. Messages with higher priority levels are typically sent or received before messages of conversations with lower priority levels. For additional details see SQL Server 2008 R2 Books online.

<Gary>