Msmq V Database Table

MSMQ v Database Table

There are several reasons, which were discussed on the Fog Creek forum here: http://discuss.fogcreek.com/joelonsoftware5/default.asp?cmd=show&ixPost=173704&ixReplies=5

The main benefit is that MSMQ can still be used when there is intermittant connectivity between computers (using a store and forward mechanism on the local machine). As far as the application is concerned it delivered the message to MSMQ, even though MSMQ will possibly deliver the message later.

You can only insert a record to a table when you can connect to the database.

A table approach is better when a workflow approach is required, and the process will move through various stages, and these stages need persisting in the DB.

to MSMQ or not to MSMQ? (or SQL Table as the Queue)

I would use MSMQ, it doesnt add that much complexity, and it is so easy to backup the messages, so processing can continue even after a system restart. You could use something like SSB.

MSMQ vs Temporary Table Dump

MSMQ isn't a bad choice and is definitely not difficult to learn, but keep in mind that there are some constraints that you should be aware of.

Cons:

• Each queue can only be 2GB.
• Each message 4MB (altough the 4MB limit can be fixed by using MSMQ with WCF).
• Only for Windows so you're limited to use it with .NET, C/C++ or COM library for COM-enabled environments.

Pros:

• Supports Windows Network Load Balancer.
• Supports Microsoft Cluster Service.
• Integrated with Active Directory.
• Ships with Windows.
• Supports transactions.
• MSMQ messages can be tracked by audit messages in the Windows Event log.
• Messages can be automatically authenticated (signed) or encrypted upon sending, and verified and decrypted upon reception.

Another approach you might want to consider is writing your data to a staging table. This might be a good idea since you want to have a message back log.

It's difficult giving advice when I don't know the rest of the system's architecture, but I hope this answer will help a little.

Useful links

Programming MSMQ in .NET - Part 1

Using MSMQ with WCF

Queuing using the Database or MSMQ?

The MSMQ approach decouples your web-facing application from the validation logic service and the database.

This brings many advantages, a few of which:

• It would be easier to handle situations where the validation logic can handle 5 sessions per second, and it receives 300 all at once. Otherwise you would have to handle copmlicated timeouts, re-attempts, etc.

• It would be easier to do maintanance on the validation service, without having to interrupt the rest of the application. When the validation service is brought down, messages would queue up in MSMQ, and would get processed again as soon as it is brought up.

• The same as above applies for database maintanance.

Queues against Tables in messaging systems

The phrase beats every time totally depends on what your requirements were to begin with. Certainly its not going to beat every time for everyone.

If you are building a single system which is already using a database, you don't have very high performance throughput requirements and you don't have to communicate with any other teams or systems then you're probably right.

For simple, low thoughput, mostly single threaded stuff, database are a totally fine alternative to message queues.

Where a message queue shines is when

• you want a high performance, highly concurrent and scalable load balancer so you can process tens of thousands of messages per second concurrently across many servers/processes (using a database table you'd be lucky to process a few hundred a second and processing with multiple threads is pretty hard as one process will tend to lock the message queue table)
• you need to communicate between different systems using different databases (so don't have to hand out write access to your systems database to other folks in different teams etc)

For simple systems with a single database, team and fairly modest performance requirements - sure use a database. Use the right tool for the job etc.

However where message queues shine is in large organisations where there are lots of systems that need to communicate with each other (and so you don't want a business database to be a central point of failure or place of version hell) or when you have high performance requirements.

In terms of performance a message queue will always beat a database table - as message queues are specifically designed for the job and don't rely on pessimistic table locks (which are required for a database implementation of a queue - to do the load balancing) and good message queues will perform eager loading of messages to queues to avoid the network overhead of a database.

Similarly - you'd never use a database to do load balancing of HTTP requests across your web servers - as it'd be too slow - if you have high performance requirements for your load balancer you'd not use a database either.

Good Strategy for Message Queuing?

If you have Azure in mind, perhaps you should start straight on Azure as the APIs and semnatics are significantly different between Azure queues and any of MSMQ or SSB.

A quick 3048 meters comparison of MSMQ vs. SSB (I'll leave a custom table-as-queue out of comparison as it really depends how you implement it...)

• Deployment: MSMQ is a Windows component, SSB is a SQL compoenent. SSB requires a SQL instance to store any message, so disconencted clients need access to an instance (can be Express). MSMQ requires deployment of MSMQ on the client (part of OS, but optional install).
• Programmability: MSMQ offers a fully fledged, supported, WCF channel. SSB offers only an experimental WCF channel at http://ssbwcf.codeplex.com
• Performance: SSB will be significantly faster than MSMQ in transacted mode. MSMQ will be faster if let operate in untransacted mode (best effort, unordered, delivery)
• Queriability: SSB queues can be SELECTE-ed uppon (view any message, full SQL JOIN/WHERE/ORDER/GROUP power), MSMQ queues can be peeked (only next message)
• Recoverability: SSB queues are integrated in the database so they are backed up and restored with the database, keeping a consitent state with the application state. MSMQ queues are backed up in the NT file backup subsytem, so to keep the backup in sync (coherent) the queue and database have to be suspended.
• Transactions (since every enqueue/dequeue is always accompanied by a database update): SSB is fully integrated in SQL so dequeueing and enqueueing are local transaction operations. MSMQ is a separate TM (Transaction Manager) so queue/dequeue has to be a Distributed Transaction operation to enroll both SQL and MSMQ in the transaction.
• Management and Monitoring: both equaly bad. No tools whatsoever.
• Correlated Messages processing: SSB can block processing of correlated message by concurent threads via built-in Conversation Group Locking.
• Event Driven: SSB has Activation to launch stored procedures, MSMQ uses Windows Activation service. Similar. SSB though has self load balancing capalities due to the way WAITFOR(RECEIVE) and MAX_QUEUE_READERS interact.
• Availability: SSB piggybacks on the SQL Server High Availability story, it can work either in a clustered or in database miroring environment. MSMQ rides the Windows clustering story only. Database Mirroring is much cheaper than clustering as a HA solution.

In addition I'd add that SSB and MSMQ differ significantly at the level ofthe primitive they offer: SSB primitive is a conversation, while MSMQ primitive is a message. Think TCP vs. UDP semantics.

Should I use MSMQ or SQL Service Broker for transactions?

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.

MSMQ as buffer for SQL Server Inserts

I was on a team that implemented this for purposes of guaranteed delivery. We used MSMQ to forward the insert requests to the database server, which had its own service running that dequeued the requests and ran the inserts, then acknowledged the message (to ensure delivery). It's been running for over a year now, and we've never been asked to come figure out why it isn't working...seems pretty solid to me.

MSMQ vs. SQL Server Service Broker

I have used both, in different situations, equally well. My preference is really pretty basic: use SQL Service Broker if message sending event is triggered from a database event; Use message queue if event is in code. That preference is based just that it easier to setup on the same platform that will trigger the event.

Queue Machanism in .NET and SQL Server

No need to mix MSMQ or WCF in, just use the SQL Server built-in queuing capabilities. See Asynchronous procedure execution for an example. This way you do not need a new process to dequeue MSMQ requests and process the DB long calls, you do not have to deal with separate message store and DB strore, you have one coherent back-restore for both messages and data, your messages fail over together with the data in case of High Availability situation. Not to mention that SQL Server queues scale to significantly higher margins than MSMQ (both in throughput and capacity). Using MSMQ would require you to provide a solution for all these problems: consistent backup/restore, fail over the MSMQ together with the database in case of HA incident, provide a process to read the MSMQ messages and do the DB work.

---

Related Topics