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?