MSMQ as buffer for SQL Server Inserts

Question

I'm learning about MSMQ and am successfully using it to queue email and text messages from a consumer-facing ASP.NET MVC website, to be handled by a separate client application.

In the event of a missing SQL Server database, perhaps while swapping drives or a broken database deploy, would it make sense to queue non time-critical inserts in a local MSMQ queue to improve up-time?

Theoretically, I can then pause/resume queue processing (persistence) while making database changes. Has anyone tried this or is there a better way?

Ashlar

Using MSMQ as a buffer for SQL server is a common implementation. – Finicking

Answer 1

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.

Pina

How did you store the queries? Just plain "INSERT INTO ..." which is pulled by a listener? – Ashlar

We created custom message classes that stored the information. These message classes were sent over the MSMQ transport using guaranteed delivery techniques. On the DB server, there was a MSMQ listener that picked them off the queue and ran the inserts. – Pina

Please can you explain "a MSMQ listener that picked them off the queue" ? – Aleciaaleck

Answer 2

If you're looking at higher availability by queueing locally then you should consider Service Broker deployed on SQL Express instances collocated with your IIS/ASP instance. The advantage of using SSB over MSMQ is that you have consistency between your message store and your data store (one consistent backup/restore, one consistent failover unit), it does scale much better than MSMQ under load, it does not require tw-phase-commit DTC to coordinate the MSMQ dequeue with the DB insert (can use one local DB transaction to dequeue/insert), it offers queryability of the pending messages (SELECT .. FROM queue), is integrated with the DB HA/DR solution (cluster failover/mirroring), you get DB contained activation and it all works from the familiar T-SQL programming environment. MSMQ's main advantage is support of a client side C#/.Net API.

Skep

Enqueueing to a local SQL Express could not be made transactionally consistent with a remote SQL Server, right? That would require DTC which does not fit with many high availability solutions. – Keiko

@usr: SSB is transactionally consistent at each end (SEND, RECEIVE), but messaging apps achieve consistency by different means. I recommend cs.brown.edu/courses/cs227/archives/2012/papers/weaker/… – Skep

Answer 3

This is very subjective because it depends on what your application does and how. Generally, something like MSMQ is not used for this purpose, rather you want to set up some kind of high-availability clustering on your database of choice. The occurrence of a database going completely down is rare in most cases, and generally a bigger problem for most LOB applications than just having somewhere to store data entered while the DB is down for whatever reason.

There's also overhead to think about. An INSERT operation to a database is relatively quick (in the larger scheme of things); writing a serialized something into a queue and having something pick it up and do that insert operation is going to add large amounts of lag to your application, not to mention the fact that you'll have to account for the fact that now everything is asynchronous.

That said, MSMQ can be used to ensure delivery of stuff from one end of an application to another, so I suppose there are instances where this scenario might be desirable. Most of the time though you're just better off trusting your DB and using MSMQ to enable asynchronous processing and performing interprocess and intermachine communication.

Selfwill