25

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!

Ed Schwehm
  • 2,163
  • 4
  • 32
  • 55
  • 3
    If your client code runs on a machine other than the database server, make sure you test the rollback scenario. I ran into issues where when I would process the queue and I needed to fail something due to the remote endpoint being down, I would end up taking down the queue in service broker because it can only handle 5 failures before it goes offline. In order to get around this I had to disable transactions for Service Broker and rely on exception handling in code, which means that under certain failures I would completely lose the message. – Aaron Weiker Mar 23 '10 at 21:49
  • What was the final choice? That's if it's not too late to ask :). – Marcel N. Jun 26 '12 at 15:34
  • 1
    @the coon: We ended up using SQL Service Broker, and it worked fine (as I mentioned in the edit above). It was the better choice for us at the time, though I think (between these two) I'd use MSMQ if I were to rebuild the system from scratch. – Ed Schwehm Jun 28 '12 at 15:44

4 Answers4

34

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.
Aaron Weiker
  • 2,523
  • 2
  • 21
  • 22
  • 1
    "Do you need to be able to send messages while the destination is down? MSMQ" - you can do this with Service Broker, you just need to have a local database to hold the send queue. – Martin Brown Jan 13 '14 at 09:47
  • I know this answer is from 4 years ago... but you've always been able to process queues remotely and transitionally with Service Broker. (and as long as you aren't using routing you can do it for free with SQL express.) Yeah, the .Net client wrapper from Microsoft is kinda sucks but it's really easy to wrap the SQL commands with ADO.Net or Entity Framework. The Queue shutting down after 5 rollbacks can easily be resolve by putting an "enable queue" statement at the top of you main loop. – Matthew Whited Dec 22 '14 at 11:46
4

I've used MSMQ before and the only item I'd add to your list is a prerequisite check for versioning. I ran into an issue where one site had Win 2000 Server and therefore MSMQ v.2, versus Win 2003 Server and MSMQ v3. All my .NET code targeted v.3 and they aren't compatible... or at least not easily so.

Just a consideration if you go the MSMQ route.

Mike L
  • 4,693
  • 5
  • 33
  • 52
3

The message size limitation in MSMQ has halted my digging in that direction. I am learning Service Broker for the project.

Dan
  • 31
  • 1
1

Do you need to be able to send messages while the destination is down? MSMQ

I don't understand why? SSB can send messages to disconnected destination without any problem. All this messages going to transmission queue and would be delivered when destination stay reachable.

user368415
  • 21
  • 1
  • 2
    What if SSB is down? Or a network connection goes? With MSMQ it will still write locally and will be available across the public queue once a connection is restored. If the network connection goes down, you cannot add message to the SSB queue. – Dominic Zukiewicz Aug 24 '12 at 17:18
  • 1
    The way to solve the network issue is to have all communicating servers have their own database server. Because Service Broker is supported by SQL Server Express this need not cost much. – Martin Brown Jan 13 '14 at 09:43