SQL Server Service Broker vs. RabbitMQ 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?
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.