3

We are considering RabbitMQ for queuing several thousand tasks per minute and then processing them from 300+ virtual machines. We have a smart scheduling web API that will intelligently (fairly) distribute tasks from 100+ queues to the VMs based on business needs. We know that RabbitMQ is definitely capable of handling this kind of load. Currently, we are querying the database each time across multiple tables which is contributing to 30-40% of the SQL Server load. Instead, we were thinking about setting up a trigger and then pushing the data to RabbitMQ (via an intermediate exchange API) as the data comes in. I realize that SQL Service Broker is also a potential option vs. RabbitMQ for queues, so I have a few questions about SQL Service Broker (SQL Server 2016 and up) that I hope you can answer:

  • Can we store the queue data in a durable manner and then fetch tasks from specific queues based on business needs? (I assume yes, but just confirming.)
  • How reliable is it for 100+ queues? I recall that the older versions of the Service Broker were quite unstable.
  • Can we create new queues dynamically / programmatically?
  • Can we create min-priority queues (i.e. as tasks are pushed onto the queue, it is sorted based on priority)?
  • What is the impact in terms of performance and load? At least with RabbitMQ, we can offload the queue management responsibility to a RabbitMQ cluster, leaving our SQL Server free to process other queries.
  • Is it true that we need to setup some kind of "conversation" between queues in order for it to work?
  • Are there any other differences / pros/cons that we need to be aware of considering one versus the other?

closed as too broad by Tom V, Erik Darling, McNets, mustaccio, Colin 't Hart Feb 2 '18 at 16:11

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    If you already put this much effort into creating an ecosphere for RabbitMQ, I wouldn't change using it at this point. Just my personal opinion based on what you wrote. Regardless of what the answers to your questions may be. – Sean Gallardy Jan 31 '18 at 22:58
  • Thanks Sean. I very much value your opinion. We've not implemented RabbitMQ yet, and we're very much open to using Service Broker, as that would eliminate the need to maintain a RabbitMQ cluster, but at the same time, trying to determine between Service Broker vs. RabbitMQ performance, features, and infrastructure-wise. – arao6 Feb 1 '18 at 0:55
6

Can we store the queue data in a durable manner and then fetch tasks from specific queues based on business needs? (I assume yes, but just confirming.)

Yes.

How reliable is it for 100+ queues? I recall that the older versions of the Service Broker were quite unstable.

A queue is a table. SEND is an insert. RECEIVE is a delete. There is some functionality to process queues from inside SQL Server, but you can always just use external clients.

See eg https://code.msdn.microsoft.com/Service-Broker-Message-e81c4316 for a sample of how to process a queue from an external application.

Can we create new queues dynamically / programmatically?

Yes. But like creating tables dynamically, it's got bad code smell.

Can we create min-priority queues (i.e. as tasks are pushed onto the queue, it is sorted based on priority)?

Yes.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-broker-priority-transact-sql

What is the impact in terms of performance and load?

A SEND is a single-row insert into a clustered-index table with a single non-clustered index. A RECEIVE is a delete of one row.

Is it true that we need to setup some kind of "conversation" between queues in order for it to work?

Every SEND and RECEIVE is in the context of a "conversation" which is a potentially long-lived session between two "services". conversations can live forever and be reused, and are the source of much confusion for getting started with service broker.

Are there any other differences / pros/cons that we need to be aware of considering one versus the other?

Service broker is quite complex, as all messages are sent on potentially-long-lived duplex conversations, and you must program against it in TSQL. Once you grok the design, and the strange troubleshooting a lot of the TSQL is boilerplate.

The best thing about Service Broker is that it's not a separate repository and your messages are have the same HA/DR and Backup as your other data. And it's trivial to enlist SEND or RECEIVE in a transaction involving other application data.

If you don't want in-database messaging, consider Azure Service Bus is much simpler to use, more functional, is operated for you in the cloud, and is able to handle that level of scale quite easily and cheaply.

Also for many scenarios you can just use a simple table as a queue. See eg http://rusanu.com/2010/03/26/using-tables-as-queues/ from Remus Rusanu, who used to be a SQL on the SQL product team that owned Service Broker. You don't get blocking reads, but if your latency requirements can tolerate a reader in a polling loop, it can be a good option.

  • Thanks! We decided to use both RabbitMQ and Service Broker for different parts of the application, as each had its use case. – arao6 Feb 13 '18 at 18:29
5

I thought I would add my 2 cents since I have been working quite a lot with both SSB as well as RabbitMQ (RMQ).

First of all: I agree with most of what David says in his post.The only thing I might disagree with a "little" bit is the:

"Once you grok the design, and the strange troubleshooting a lot of the TSQL 
 is boilerplate." 

At my workplace we are using SSB quite extensively, and we have a lot of processes using it. We started using SSB when SQL server 2005 was still in beta (so quite a few years now), but we still get "bitten" by SSB's "black box" behaviour now and then.

So, the last few years - for new projects - we have started using RMQ. All of our data processing happens in stored procedures, so it is easy to make a "hook-point" in the procedure, and in that "hook-point" send data via an SQLCLR assembly to RMQ. You can read more about that here. The SQLCLR solution works extremely well, and we are sending thousands of messages per second (our databases are OLTP 24/7 with very high throughput).

Anyway, in my mind the choice between SSB and RMQ comes down to a couple of things (I assume that the source of the data is in the database):

  • Where does the processing of the data happen after you have de-queued it; in the database or an external process? If it is an external process I would lean towards RMQ. I would look at SSB as a solution if processing happens in another database. However, due to its idiosyncrasies, I might look at creating a pub-sub SQLCLR assembly instead. This assembly would read the data off the table(s) async, and send it to the other database for processing (we have done this as well where I work).
  • Do you need the duplex capabilities SSB gives you? Don't forget SSB is not quite intended to be a message broker but is built for (as David says) duplex conversations, which potentially are long-lived. If you need the duplex capabilities, then SSB is (almost) a given.

Hope this helps! Please let us know what you decide.

  • Thanks! We decided to use both for different parts of the application, as each had its use case. – arao6 Feb 13 '18 at 18:29
  • Glad to be of help. – Niels Berglund Feb 14 '18 at 3:23

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