Should I use MSMQ or SQL Service Broker for transactions?

Question

I've been asked by my team leader to investigate MSMQ as an option for the new version of our product. We use SQL Service Broker in our current version. I've done my fair share of experimentation and Googling to find which product is better for my needs, but I thought I'd ask the best site I know for programming answers.

Some details:

• Our client is .NET 1.1 and 2.0 code; this is where the message will be sent from.
• The target in a SQL Server 2005 instance. All messages end up being database updates or inserts.
• We will send several updates that must be treated as a transaction.
• We have to have perfect message recoverability; no messages can be lost.
• We have to be asynchronous and able to accept messages even when the target SQL server is down.
• Developing our own queuing solution isn't an option; we're a small team.

Things I've discovered so far:

• Both MSMQ and SQL Service Broker can do the job.
• It appears that service broker is faster for transactional messages.
• Service Broker requires a SQL server running somewhere, whereas MSMQ needs any configured Windows machine running somewhere.
• MSMQ appears to be better/faster/easier to set up/run in clusters.

Am I missing something? Is there a clear winner here? Any thoughts, experiences, or links would be valued. Thank you!

EDIT: We ended up sticking with service broker because we have a custom DB framework used in some of our client code (we handle transactions better). That code captured SQL for transactions, but not . The client code was also all version 1.1 of .NET, so we'd have to upgrade all the client code. Thanks for your help!

Answer 1

Having just migrated my application from Service Broker to MSMQ, I would have to vote for using MSMQ. There are several factors to take into account, but most of which have to do with how you are using your data and where the processing lives.

• If processing is done in the database? Service Broker
• If it is just data move? Service Broker
• Is processing done in .NET/COM code? MSMQ
• Do you need remote distributed transactions (for example, processing on a box different than SQL)? MSMQ
• Do you need to be able to send messages while the destination is down? MSMQ
• Do you want to use nServiceBus, MassTransit, Rhino-ESB, etc.? MSMQ

Things to consider no matter what you choose

• How do you know the health of your queue? Both options handle failover differently. For example Service Broker will disable your queue in certain scenarios which can take down your application.
• How will you perform reporting? If you already use SQL Tables in your reports, Service Broker can easily fit in as it's just another dynamic table. If you are already using Performance Monitor MSMQ may fit in nicer. Service Broker does have a lot of performance counters, so don't let this be your only factor.
• How do you measure uptime? Is it merely making sure you don't lose transactions, or do you need to respond synchronously? I find that the distributed nature of MSMQ allows for higher uptime because the main queue can go offline and not lose anything. Whereas with Service Broker your database must be online or else you lose out.
• Do you already have experience with one of these technologies? Both have a lot of implementation details that can come back and bite you.
• No mater what choice you make, how easy is it to switch out the underlying Queueing technology? I recommend having a generic IQueue interface that you write a concrete implementation against. This way the choice you make can easily be changed later on if you find that you made the wrong one. After all, a queue is just a queue and should not lock you into a specific implementation.