I am after advice regarding message queueing. We have requirements for "jobs" to be posted to a message queue.

The original suggestion was just to use a SQL Server instance and process messages from that. Everything I have read on the internet suggests that using a database for a Message Queue isn't a scalable solution. For this reason, the idea of using RabbitMQ or some other 3rd party MQ was suggested.

The other thing to take into account is that the requirement for "job processing" won't be any lower than 30 seconds, so the process that does the job will poll the database every 30 seconds. To me, this doesn't seem so bad and would probably work ok without adding a large load to the Database.

We already have a Database in place on our clients we could use for this so it won't add much extra support required to our clients, whereas if we added a 3rd party MQ there would be extra support for network configuration etc, which would be considerable given there is a lot of users.

The other option I was considering was allowing users to choose between either. If they are a small user then the Sql Server solution will be ok, but if they are a larger user then we allow them to configure a 3rd party MQ solution.

I'm not sold on any solution, I am wondering if anyone has anything I should consider or advice.

  • Are these 'jobs' a 'fire and forget' or will the process have to phone back home to let the server know the status of that job? – c_maker Jun 23 '17 at 1:43
  • How scalable does it need to be? Are you running a few hundred thousand messages through it a day or several billion? – Blrfl Jun 23 '17 at 2:52
  • Thanks for the comments: there is a requirement for the job to be marked as incomplete (the are considered complete unless marked as incomplete/failed). I would think no more than 20 000 messages a day. Most likely only a few thousand. – user1653400 Jun 23 '17 at 3:26
  • Use the most appropriate tool for the job. If you need a message queue, then use a message queue, not a database. I've seen people using database tables as message queues before and I regard it as an anti-pattern. You can use a screwdriver as a hammer, but a hammer works better if you need to drive in a nail. Most of the time when people do this, it's because they understand databases but not message queues. – Jesper Jun 23 '17 at 10:19
  • There are some good pointers here: rusanu.com/2010/03/26/using-tables-as-queues – Michael Green Jun 26 '17 at 1:09

Everything I have read on the internet suggests that using a database for a Message Queue isn't a scalable solution.

The reality frequently ignored by the "don't use X because it doesn't scale" (link contains language some may find objectionable) crowd is that scale isn't always important. I'd go so far as to say that if you look at every application on the face of the planet in aggregate, scale is rarely important.

Your comment cites a rate of 20,000 messages daily, which means you'd be looking at needing to support an average rate of 0.23 messages per second (one every 4.3 seconds). If your project turns out to be two orders of magnitude more successful than you expected, your requirements jump to processing 23 messages per second, which is a task I'd be very comfortable giving to my four-year-old mobile phone or a Raspberry Pi. This still isn't a high-scale application even if you tack another couple of orders of magnitude on top of that.

I've watched (fortunately, from the sidelines) projects end badly because they either spent too much time too early obsessing over scale that wasn't going to happen or no time on it whatsoever and got crushed by scale that eventually did. Like everything else, there's a happy medium. If you think large scaling for your application is a realistic possibility, it shouldn't be difficult to make a business case for doing the small amount of extra work to build in enough abstraction around non-scalable parts that are inexpensive to deploy now. Doing this means that later, if a need for scale should arise, you have a way (and possibly the revenue) to do wholesale replacement of those parts without having to re-think the entire system.

While your application's message volume isn't going to make a database or a message queueing system break a sweat even on modest hardware, you probably have other requirements for how your message transactions are handled that make one or the other a better choice. Those requirements are what you should be evaluating.

  • I have a database having millions of transaction done daily. which i need to process through sms. I am currently using sql table as queuing but i get stuck when collect large amount of data. I can not use MQ because i need to route my sms based on real time configuration. If i use MQ then it does not use current configuration for client. as we are changing provider on backend when some provider fail to process. So you guy what suggest me in my scenario? – mayur Rathod Jul 12 at 14:06
  • @mayurRathod That topic seems like fodder for a separate question. Word it carefully, because a request for specific tools or resources will get closed as off-topic. – Blrfl Jul 12 at 16:14

Message Queues really come into their own when you have many of them and route messages between them, fanout to more than one consumer etc.

If you just have a single 'job queue' of stuff you want to process 'off line' then an SQL table will do just fine.

Don't forget to ensure you have some way of marking jobs in-progress, cleaning out old ones and alerting when the system stops. But for a single queue manually managing these things will be less work than maintaining a separate queuing solution.

For practicality, main reasons for me to use a message queue are:

  • I didn't have to reinvent the wheel. Designing even the simplest message queue using database requires at least a couple of hours of thinking, a few hours of coding, and so on. Compared to implementing a message queue, the time required to configure and/or automate configuration is trivial
  • Message queues have nice and clear interface for producer and consumer. This is probably the most important thing in writing application. Without the interface, message queues are basically just a collection of data
  • Message queues give more features that might be needed in the future

Regarding allowing users to choose, that's really an implementation detail that users shouldn't care about. Users should get the same interface and there should be no difference to users if database or message queue is used. Once a single design is set, a probable choice for users is how many nodes need to be deployed to accommodate their needs.

As other have mentioned scale is probably not important here. The problem with using two different storage mechanisms is transactional integritety.

If going with a dedicated message queue you need to choose one of the following in case of failure.

  1. Allow that data can be on mb but not in db
  2. Allow that data can be in db but not in mb
  3. Setup two phase commit or distributed transactions between db an mq which can be complicated

All these problems go away if you only save data in one place using a normal transaction. For this reason using db as a task queue is a perfectly fine solution.

I have created an Mssql message queue solution which can handle 20k operations per second, according to a performance test, and we need 10/sec most of the time. I think that the fact that you can actually have priority built in is a feature that the dedicated message queue lack. And this was a major requirement in my case.

protected by gnat Oct 11 at 22:39

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.