MSMQ v Database Table

Question

An existing process changes the status field of a booking record in a table, in response to user input.

I have another process to write, that will run asynchronously for records with a particular status. It will read the table record, perform some operations (including calls to third party web services), and update the record's status field to indicate that processing is completed (or In Error, with an error count).

This operation sounds very similar to a queue. What are the benefits and tradeoffs of using MSMQ over a SQL Table in this situation, and why should I choose one over the other?

It is our software that is adding and updating records in the table.

It is a new piece of work (a Windows Service) that will be performing the asynchronous processing. This needs to be "always up".

Dall

Answer 1

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.

Gapeworm

I don't get it. Can't you lose connection to a message queue the same way you could lose connection to a database? If the message queue is local can't the database be local too? – Oleaginous

Seun, for scalability the database is usually on its own server. The message queue is effectively local, so that in case of outage you can still post. The messages are forwarded when connectivity is restored without losing any, or breaking the application. – Dall

anyone know where to find a more complete database schema that is suggested in the last reply of the fogcreek discussion? – Arrhenius

Besides of Mitch's answer, some other scenarios: 1. each of your message have its own due date to trigger the action, this can be done through MQ as well, but in this case I prefer to store it into db as it is more controllable; 2. subscriber needs to filter message and then process a portion of it, this can be done by LINQ too, depends on how complex the filter is, the db approach is better because I can use linq to EF do complex query easily; 3. For deployment, i want fully automated deployment process so that DB is a better choice for me. I am not a big fan of manual configurations. – Mazonson

Answer 2

If the rate at which booking records is created is low I would have the second process periodically check the table for new bookings.

Unless you are already using MSMQ, introducing it just gives you an extra platform component to support.

If the database is heavily loaded, or you get a lot of lock contention with two process reading and writing to the same region of the bookings table, then consider introducing MSMQ.

Olsewski

Answer 3

I also like this answer from le dorfier in the previous discussion:

I've used tables first, then refactor to a full-fledged msg queue when (and if) there's reason - which is trivial if your design is reasonable.

Thanks, folks, for all the answers. Most helpful.

Dall

The previous discussion that you linked to was highly useful (https://mcmap.net/q/585600/-queues-against-tables-in-messaging-systems-closed) – Afrikaner

Answer 4

With MSMQ you can also offload the work to another server very easy by changing the location of the queue to another machine rather then the db server.

By the way, as of SQL Server 2005 there is built in queue in the DB. Its called SQL server Service Broker. See : http://msdn.microsoft.com/en-us/library/ms345108.aspx

Predict

Answer 5

Also see previous discussion.

Revert

Answer 6

If you have MSMQ expertise, it's a good option. If you know databases but not MSMQ, ask yourself if you want to become expert in another technology; whether your application is a critical one; and which you'd rather debug when there's a problem.

Revert

Answer 7

I have recently been investigating this myself so wanted to mention my findings. The location of the Database in comparison to your application is a big factor on deciding which option is faster.

I tested inserting the time it took to insert 100 database entries versus logging the exact same data into a local MSMQ message. I then took the average of the results of performing this test several times.

What I found was that when the database is on the local network, inserting a row was up to 4 times faster than logging to an MSMQ.

When the database was being accessed over a decent internet connection, inserting a row into the database was up to 6 times slower than logging to an MSMQ.

So:

Local database - DB is faster, otherwise MSMQ is.

Underclothing

Answer 8

Instead of making raw MSMQ calls, it might be easier if you implement your sevice as a queued COM+ component and make queued function calls from your client application. In the end, the asynchronous service still uses MSMQ in the background, but your code will be much clearer and easier to use.

Subjugate

Answer 9

I would probably go with MSMQ, or ActiveMQ myself. I would suggest (presuming that you are considering MSMQ you are using windows, with MS technology) looking into WCF, or if you are using MS-SQL 2005+ having a trigger that calls into .net code to run your processing.

Haustorium

Answer 10

Service Broker was introduced in SQL 2005 and it is designed to be very quick at handling messages as the process is relatively simple (I believe its roots were in triggers). If you are concerned about scalability, in SQL 2008 they have released an independant processing executable to separate the processing from SQL Server (in standard Service Broker, everything is controlled by the SQL Server instances).

I would definitely consider using Service Broker over MSMQ but this is dependant on your SQL Development/DBA resources and their knowledge.

Valine

Answer 11

Besides of Mitch's answer, some other scenarios: 1. each of your message have its own due date to trigger the action, this can be done through MQ as well, but in this case I prefer to store it into db as it is more controllable; 2. subscriber needs to filter message and then process a portion of it, this can be done by LINQ too, depends on how complex the filter is, the db approach is better because I can use linq to EF do complex query easily; 3. For deployment, i want fully automated deployment process so that DB is a better choice for me. I am not a big fan of manual configurations.

Mazonson