Sql Server Service Broker: How to structure Conversations for a simple queue scenario?
I'm a Sql Server Service Broker novice and I'm trying to grasp the best way to set Service Broker up for a (seemingly) simple use case: I want to create a simple work queue, where one application drops work items into the queue, and separate application picks up work items from that queue and processes them. There is no need for the first application to get status messages back from the second. I want the queue to live in a single Sql Server instance.
What most confuses me is how conversations/dialogs relate to this situation. I know you can only send/receive messages in the context of a conversation/dialog, but because there is no back-and-forth chatter between the two applications, I feel lost about when is the correct time to create a new conversation. The two extreme alternatives seem to be:
- Each time I enqueue a work item, I begin a new conversation. So each conversation ends up having exactly one message in it.
- At deployment time, I manually create a single infinite-lifespan conversation. When it's time to enqueue a work item, I always send it as part of that single conversation.
What would the consequences of going either of these routes be?
Also, in the first case, it seems like I need to do some END CONVERSATIONs, in order for Sql Server to be able to clean up resources internally. Is there any guidance for when would be the correct place to put these in? (Or might it potentially be better to rely on the conversations timing out eventually?)
Answer
You should start with each work item on its own conversation. The producer (initiator) begins a dialog and send the message describing the work item, then commits. The consumer (target) receives the message (or gets activated), inspects the payload to understand the work item details, executes the work, then ends the dialog and commit. The resulting EndDialog message gets sent back to the initiator service queue, and an activated procedure on the initiator queue responds to it by ending the dialog on the initiator side.
This is the simplest deployment and getting it up and running will ensure you have a sound foundation to build upon. Don't cut corners and end the dialog on the initiator side from when the producer enqueues the work item, this is fire-and-forget and has several draw backs.
If you have high performance requirements (over 200 requests per second) then you'll have to start managing the conversations more explicitly. I have a blog entry on reusing conversations for performance reasons. On the receive side I recommend reading Writing Service Broker Procedures.
I also have a blog entry that pretty much does what you need, albeit it does not schedule work items but instead launches a custom procedure: Asynchronous procedure execution.
If you decide to consume the work items from an activated context, thus leveraging the nice self balancing capabilities of activation, then you need to understand the EXECUTE AS context under which activation occurs.