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?

Answer 1

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.