SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Getting the Message

By Phil Factor,

All the large-scale database applications that I’ve ever been involved with have had some sort of message queuing systems built into them. Queues are, and always have been, fundamental to any concurrent or distributed processing. All the largest websites have them: Flickr, for example, uses queues to process over 12 million tasks a day. Steve Huffman of Reddit says "If you need to do something, do it while the user isn’t waiting for you. Put it in a queue". So why is there so little interest in Service Broker? On SQL Server Central, for example, it has just 36 threads compared with 4696 for Reporting Services. Articles on Service Broker seem to attract little interest. This is all the more surprising since it has nearly ten years of development behind it, and has a reputation for being solid.

When designing any server-based system, there usually comes a time when you need to 'think parallel' and 'asynchronous' about your work processes, regardless of fast are your SQL routines. The simplest way of making this happen is to loose-couple your services so that they communicate requests, notifications and data via connectionless message queues. The driving motivation for embracing the increased complications of making your system queue-based is resilience under unpredictable peak loads, but it helps scalability and reliability too.

In the traditional client-server model, there wasn’t much that the user could do after 'submitting' the 'form' for processing other than for the results to be returned, so one could afford to stick to the model of the client calling a stored procedure, which did all the necessary processing before returning with a result. Even then, you had to use queues for lengthy jobs such as printing, validating payments, or sending emails. As the size and complexity of a system grows, the more advantage there is to the loose-coupled approach. Nowadays, websites try to return pages = as soon as possible, by delegating any process that doesn't contribute information immediately to that page.

Homebrew non-volatile queues aren't easy. Messages have to be transactional. They have to be resilient, and there have to be ways of prioritizing them. You need to support connectionless messaging so as to tolerate limited outage. There have to be ways of monitoring, purging, and routing messages. Just the security issues alone can be complex enough. Service Broker has the natural advantage for any database application of being written as a SQL Server database. So why don’t we hear more about it? My guess is that it is because it just quietly gets on and works, but I'd be fascinated to hear what you think.

Phil Factor

Total article views: 225 | Views in the last 30 days: 0
Related Articles

SQL server Service Broker -- Reading same Message by multiple Users in the queue

SQL server Service Broker -- Reading same Message by multiple Users in the queue


Service Broker queue design

Setting message precedence for message processing


Get Your Service Broker Messages in Order. Always.

Learn how you can guarantee the ordering of all messages in a Service Broker queue, regardless of co...


SQL Server Service Broker Explained

What is Service Broker? Service Broker is a native SQL Server implementation of message queues. ...


Service Broker Error Handling

How to handle errors raised during Service Broker Queue Processing.