I have a service broker application that currently has 5 or 6 queues on two servers.
The general workflow is:
Server A
- User populates some tables
- User fires stored proc which puts a message into a header queue indicating there is work to be done
- Broker reads from this header queue and creates a potentially large number of messages to send to Server B
Server B
- Messages all go to 1 queue
- In this queue, message is examined and unbundled, payload is run and a response is collected from another set of procs/tables that we interact with
- Response message is created and sent back to Server A
Server A
- Response messages go to a special queue and are used to update tables the user populated at the top
- End dialog messages are read from the initiator queue and used to update another table that tracks the progress of a header record (i.e. 50 out of 100 messages processed)
- When we detect that the header record has been entirely processed, a new message is sent to another queue that sends a notification email
I've had a recommendation to consolidate some of those queues on Server A and I'm not sure what the best practice is for this. Two of the queues will have a relatively high volume of message processing, and the email queue will be be pretty slow.
We did (I think) a good job of managing message types so theoretically we could put it all into one queue and the application would still function.
Is there a best practice on when to maintain separate queues and when to consolidate in a scenario like this?
Best Answer
This is an open question with no clear choice. YMMV so you have to test. Here is my opinion:
Having one queue to handle everything is a good choice if you want to be able to control the number of activated tasks, as there is no global max_queue_readers. Other than that, I don't see many advantages. One could argue that one single activated proc is easier to maintain, but I think you should use some sort of template/code generation when creating SSB activated process, since typically much is cookie-cutter code and little is specific to service business logic.
However I can see several potential problems with a single queue, and they're all the kind of problems that manifest only when things are already going bad (ie. under stress/load):
In general my recommendation is to have one-queue-for-each-service.