SQL: Implementing queues in SQL Server the right way

There are many times when I need to use a queue in my database designs. Service Broker is a transacted queue that lives right in your SQL Server database. If you haven’t used it, or don’t even know why you should, read on.

The primary reason that I like to use queues is that they let you decouple different applications or different parts of applications. Let me give you some examples:

The report that would never run successfully

When you need to get data from another SQL Server system, what do you do? The obvious option (and what most people do) is to create a linked server, and then just query it. That’s all well and good, but what happens if you have ten servers, or fifty servers?

I was at a hospital that had 73 SQL Server instances. They wanted to report on some statistics from each server, and had created all the linked servers, and a query that ran against all those servers. Good solution? Not even close.

The problem is that it was rare for all 73 instances to be running at the time the query was executed. And if even one of them was down, the query failed.

This is a core problem that’s often called tight coupling. The more servers you depend upon to be up at the same time, the more you’re building a fragile system i.e. a house of cards.

A far more reliable system would be based on sending messages between the servers and collecting the reponses. It really doesn’t have to all be done synchronously.

[caption id=“attachment_5193” align=“alignnone” width=“540”] Awesome image by Nathan Dumlao[/caption]

Unintended side-effects

There’s another common issue with tight coupling. Imagine you have a database that stores details of stock items. A new marketing manager asks for an email every time a new stock item is added. There is a stored procedure that inserts the new stock items, so you decide to change it, by making it send an email. But what now happens if the email server isn’t working? Can you still add stock items? By joining two functions together, you might have broken the primary function. (Fortunately, SQL Server’s email system sends asychronously so it won’t have that issue but the concept is important).

Asynchronous triggers

Imagine that you have a database with customer addresses, and you want to geocode them every time one gets updated. You can add a trigger to the table, work out if the address was changed, and then call a web service to do the geolocation. But do you really want to wait for it to respond? And again, what if it isn’t working right now? A much better solution is to get the trigger to send a message asking for the geocoding to be done, and then just let the trigger finish.

[caption id=“attachment_5195” align=“alignnone” width=“449”] Awesome image by Neil Thomas[/caption]

Service Broker to the rescue

For any of these types of issues, you need a queue, and preferably, a transactional queue. And that’s exactly what Service Broker is. I see customers trying to roll their own queues in SQL Server databases by building logic around a table, but why do this when someone who understands queues better than you has already done the work? For example, have you thought about things like poison messages (i.e. a message in the queue that cannot be processed)?

In SQL Server 2005, the SQL Server team did wonderful plumbing work for Service Broker. It was enhanced again in 2008, and yet again in 2012. The main pain point is that the team have never added decent tooling for it into SSMS, and they haven’t provided much proscriptive guidance. Yet what they’ve provided is awesome.