Disadvantages of SQL Server Service Broker
I have been doing r&d for the scope of SQL Server Service Broker to replace current messaging solution MSMQ. I want to know disadvantages of SQL Server Service Broker in contrast to MSMQ for following criteria.
- Development
- Troubleshooting
- Performance (let's say we need to process 100,000 messages daily, having avg size around 25 KB)
- Scalability
Answer
I've used Service Broker in my current project, having previously used MSMQ (brokered by MassTransit) with great success. I was initially dubious about using Service Broker, but I have to admit it has performed very well.
If you're using the Publish / Subscribe model, then I would use Message Queueing every time (though I would use RabbitMQ over MSMQ if the project allowed) but when you just want to chew through a stack of data and persist it to Sql Server, then Service Broker is a great solution: the fact it's so 'close to the metal' is a big advantage.
Development
Service Broker requires a lot of boilerplate, which is a pain, but unless you're planning on having lots of different queues it's manageable. Sql Server projects in Visual Studio take a lot of the pain of deploying away.
Troubleshooting
Service Broker is a black box - messages go in, and they usually come out, but if they don't then troubleshooting can be problematic, and all you can do is query the system views - and sometimes you simply can't find out what has gone wrong. This is annoying, but MSMQ has the same kind of issues..
Performance
Service Broker performance is excellent. We are processing a lot more than 100,000 messages per day, more than 30,000 per hour at our SLA load, and our message sizes are large. I would estimate we process close to 100,000 messages per hour during heavy load testing.
For best performance I would advise you to use a Dialog Pool like this one 1 as creating a Service Broker dialog can be an expensive operation.
You will also want to use the Error Handling procedures detailed by Remus Rusanu. (If you do use Service broker, you might as well read everything Remus has written on the subject before you start, as you'll end out reading it eventually!)
Scalability
You can certainly use more than one server to scale up if required, though we haven't had to do so, and from the load size you mention I don't think you would need to either.
I don't think I have really managed to answer your question, as I haven't highlighted enough disadvantages of Service Broker queues. I would say the impenetrable nature of its internal workings is the thing that most annoys me - when it works, it works very well, but when it stops working it can be very hard to figure out why. Also, if you have a lot of messages in a queue, using ALTER QUEUE takes a very long time to complete.
Not knowing how you are using MSMQ also makes it different to fairly compare the two technologies.
1 Recreated in a gist as the original url is now "disabled" and the page isn't in the internet archive. Eventually found a copy here