SQL Server 2005 Service Broker

Service Broker is a very nice feature of SQL Server 2005. Like most things, it’s quite simple after you’ve figured it all out, but when you’re first learning, it can be quite daunting. When it comes to learning about SQL Server the main problem is that all the words Microsoft uses to describe things are so overloaded it’s easy to get confused. Everything is an "application" and everything is a "service"... This rapidly becomes confusing... "Which application are they talking about now? Their’s? Mine? What service? My service? The service broker service? That stored procedure?"

It also becomes confusing because SQL Server has so many different "technologies" built into it. A lot of them seem to do the same thing, but in fact they’re different, some more than others. For example, there is Notification Services, Service Broker, Event Notification, Internal Activation, External Activation, etc. And then it gets even more confusing because some of these "technologies" are just combinations of other "technologies." External Activation, for example, is really just Event Notification applied to Service Broker and it uses Service Broker Queues to deliver its messages.

In this post I’m going to briefly go over some of the things I’ve learned about Service Broker. If you’re completely new to Service Broker and you want a quick detailed overview with code samples you may want to go read this tutorial or browse through MSDN’s service broker articles.

So then, what is Service Broker? It is simply a queued message delivery system. A message sent from one person to another is guaranteed to not only arrive but arrive in order, and in the event that something goes wrong, not be lost. Of course there are a lot of details to describe exactly how it behaves, but that’s really all there is to understanding it.

What would you use it for? Lots of things! The uses I’m most concerned with are "Asynchronous triggers" and "Large-scale batch processing." In other words you can drop a message into a queue and then forget about it, continuing with your own work and ultimately completing. In the background, you know that your message will at some point be received and handled. In this way you can offload work from stored procedures or triggers to be performed asynchronously.

So here is the big question, if we’re sending messages from person A to person B, who or what are these people? They are anything which is capable of executing SQL commands. Thus it can be a stored procedure, a C# application using dynamic SQL, a Windows Service, anything. Notice that any of these things can be either the sender or the receiver!

Here’s a simple example. The sender can be a stored procedure which you invoke. This stored procedure will simply execute the needed commands to send a message from himself to the receiver. The receiver can also be a stored procedure, but a slightly unusual one. This stored procedure will be configured to start when SQL Server starts and it will run infinitely in a loop, periodically checking for new messages. If this is beginning to sound a lot like Socket programming to you, then you already understand Service Broker.

Where is the queue in this example? Well, actually, there are two of them. The sender has a queue and the receiver has another. Why? So far I’ve been describing this as a dialog. Person A always sends messages to B who always receives them. B never sends anything to A. But, in fact, that’s not the way Service Broker works. Service Broker always assumes a dialog in which A and B are both sending and receiving messages to and from each other. That doesn’t mean you have to carry on a dialog, in fact when I’ve used Service Broker I’ve only had a monolog. But that’s simply in the way I’ve written the applications that are using Service Broker. I didn’t do anything different in the way I setup Service Broker to make it work that way.

Back to our example, since we have two stored procedures, could we put them in two different databases? Indeed we can! In fact, that is one of the main uses of Service Broker. You could put them in two different catalogs of the same server, or you could put them in two completely different servers. Now you can effectively have two different databases talk to each other.

Having a stored procedure that is always running and monitoring the queue in a loop works just fine and there is really nothing wrong with that approach. However, Service Broker comes with another option for receiving from a queue called "Internal Activation." Basically, SQL does the looping in the background for you and when a message arrives on the queue it invokes a stored procedure. This stored procedure pulls the message off the queue and does whatever it wants to do with it. Usually the stored procedure is written to then enter a loop where it pulls messages off the queue until the queue is empty, at which point the stored procedure ends. Internal Activation will also invoke multiple instances of your stored procedure if the currently running procedures aren’t able to keep up with the number of messages arriving at the queue. In this way Internal Activation can help your message handling scale based on demand.

When I first heard about this a little red flag went up in my brain. I thought to myself, "Self! I thought messages were guaranteed to be delivered in order with Service Broker. If Service Broker is going to launch multiple "handler" stored procedures in parallel, how is it that the messages will be handled in order?" It turns out the answer lies in another Service Broker concept I haven’t mentioned yet called a conversation. Messages have to be sent on a conversation. Messages within the same conversation will arrive AND be handled in the order they were sent, guaranteed. But messages between conversations can be handled in parallel. This behavior is performed magically in the background without you having to do anything. It happens because whoever receives a message on a new conversation automatically obtains a lock on that conversation and they will only be able to receive messages from that conversation, until the conversation ends. But again, if no one had told you, you’d scarcely be aware of it at all.

If there is Internal Activation, there must be External Activation as well, right? Right. Internal Activation launches a stored procedure inside SQL Server, External Activation is for launching arbitrary programs outside SQL Server. .NET programs, C++ programs, Ruby programs, whatever. At least, that’s what you’d expect. In fact it’s kind of a misnomer. The Internal Activator launches the internal stored procedures. Internal Activation refers more to when the activator should do its work. The same is true for External Activation. The difference is that SQL Server 2005 doesn’t come with an External Activator. So you have to write your own. You can do this, because SQL Server 2005 does have External Activation, which tells you when a Queue has been activated.

External Activation is performed with SQL Server Event Notification. In the parlance, you’d write an external application which receives the external activation event (called the Queue Activation event). Okay... So how does the external app "receive an event?" Through Service Broker! That’s right, you create another Service Broker Queue and the Queue Activation event is placed in that queue as a message. Then you write an external application that monitors this new queue and creates new application processes which behave just like the activated stored procedures from Internal Activation. I’m not going to go into more details of how this works. It is a bit more complicated of course. However, Microsoft does have a sample.

How does this external application monitor the Service Broker queue? Exactly the same way as our first example stored procedure did. It just sits there in an infinite loop waiting for messages to arrive on the Queue. In other words, it just executes a SQL statement inside a loop.

So, what’s the difference between using an External Activator and just writing an application that monitors the actual queue in question (not the "queue activation" queue)? The only difference is that the External Activator will spawn off multiple handlers applications and therefore scale better with demand. Just writing a monitoring application will only handle one message at a time (unless you get fancy, which could get you into trouble in some circumstances due to conversation queuing as mentioned earlier). However, there are circumstances where this is all you need. In fact, all the ways I’ve used Service Broker to date have worked this way.

This is partly because all my Service Broker "applications" have been monologs and the processing they have performed wouldn’t really go any faster if it were done in parallel. In fact, I have never used more than one conversation. So far, I always create a single conversation. I cache the conversation handle in a database table so I can reuse it whenever I send a message. This is because most of the performance impact when using Service Broker comes from creating a conversation. That makes sense since the conversation is actually the unit that has guaranteed in order message delivery. By caching the conversation and never ending it or starting a new one, you improve the performance. This is discussed in an article called Reusing Conversations. You have to consider some scalability concerns before deciding to use exactly one conversation. For me however, I felt that was good enough (not to mention simple).

For me, the receiving application is a Windows Service written in C#. It creates a background thread that loops indefinitely issuing a WAITFOR( RECEIVE ... ) command to SQL Server with a half second or so timeout. This setup allows me to send messages serially and receive and handle them serially as well. If I needed to send messages in parallel I’d have to use more than one conversation. I believe I could still receive the messages serially, but I’d have to be very careful with when the conversation lock was released, as discussed in this msdn article, Conversation Group Locks.

Hopefully this summary has shown how flexible and powerful Service Broker is. And also how easy it is, despite the fact that the learning curve is pretty high and the setup curve is pretty high too (you have to create at least 6 different new database objects at the bare minimum to get a service broker conversation going). With luck SQL Server 2008 wont deprecate all this information.