MSMQ Versus a DB Table

Why should i use MSMQ when i can create a simple table in my db that does essentially the same thing, and is easier to query?  I won't have to run all the MSMQ services, and have a box sitting there managing the queue. 

I've already got a db, and I'm very comfortable with it.

What are the benefits of MSMQ over this simpler method?  I'm not generating a ton of messages, maybe a few hundred or thousand an hour at most. 

Queued Up
Wednesday, August 11, 2004

Hey, you don't need to convince us. If the shoe fits, wear it . . .

Anon
Wednesday, August 11, 2004

MSMQ is likely overkill if all you want is a queue-type thing on a single machine. As you have deduced, a table might be the simplest approach. The benefit of MSMQ is when you want to *communicate with another machine* and you want it all to carry on working (eventually) if it goes offline temporarily.

Duncan Smart
Wednesday, August 11, 2004

Another benefit of MSMQ is having fun while troubleshooting it using clues like 'ASSERT failed on line 57' in the event log.
Sorry, had to say it.

www.alexlechuck.com
Wednesday, August 11, 2004

One possible reason for using MSMQ instead of a database:

MSMQ can work around a possible lack of connectivity between computers, using a store and forward mechanism on the local machine. The app does not need to know that MSMQ is going to deliver the message later.

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

.NET Developer
Thursday, August 12, 2004

MSMQ is very useful when you have many writers who all want to insert into a single table.  Even MSSQL would have performance problems with 500 applications trying to write to the same table.  So, what you do instead is have the apps write to a Queue, and have a service pulling the messages off the Queue and writing them to the table.  The database is happy (only one writer to that table), and you get a measure of resiliency to your system (if the writer service goes down, you can start another instance of the service on another box via MOM or NetIQ script, or just write a cluster-aware service).

The biggest drawback I have with Microsoft's implementation is the lack of a publish/subscribe model.  Sure you can fake it by doing peeks, but it just doesn't work that well (been there, done that).  But for what it does, it works very well.  Maybe in the next release.

A DB table is more useful in a workflow situation where you need to record the various stages that something passes through before being considered "done".  A persistent message queue can help with this, but if you need to monitor the situation ("Which loans are waiting on credit checks to be done?"), doing a DB query can be pretty easy:

SELECT col1, col2
WHERE WorkStageID =
  (SELECT WorkStageID
    WHERE StageName=N'AwaitingCreditScore'
    FROM t_WorkStage)
FROM t_Work;

Doing the same from a message queue is much messier, involving peeks, and looping through all messages in the queue to find the ones you're interested in.

example
Saturday, August 14, 2004

  Recent Topics

  Fog Creek Home