On a database which has multiple user application instances en-queuing messages to one initiator queue sending messages to a target queue which activates a server application, I am revising the framework to be many user services, one user initiator queue, one target queue instead of having many user services, many initiator queues, many target queues.
see Rusanu's side note:
On a side note I'm surprised to hear that you have many queues. In general the tendency is to have a single queue and several queue readers (activated procedures). Since SSB programing is event driven (wait for for message, process message, wait for message, process message, wait for message...) having more than one queue to wait for messages on it becomes harder, as the application now has to wait on multiple sources (eg. one thread per queue at least). Even with SSB activation, which alleviates the need for explicit wait wince it launches the code to process the message on-demand, multiple queues are harder to manage (max_queue_readers per queue adds up to perhaps too many internal activated procedures launched). Consider using a single service and queue on the RECEIVE side. Even when multiple services are needed (for whatever reason) they can be consolidated on a single queue.
Actually this one queue approach seems to be the easiest way to accommodate the conversation grouping and "channel communication" we want. However, when the initiator gets a reply back from the target queue, I would like to externally activate a particular (1) user application instance that a reply has come for their message. Is there an easy way to notify a particular user application instance of a response for only their conversation when multiple users share the same queue? I came up with a couple ideas below, is there something better?
My 2 solutions so far for notifying a particular user of a response were:
A. internal activation starts a procedure which receives all incoming messages into the initiator queue and updates a status table for ongoing conversations, and updates a message reply column. This row would have a trigger which the user app is listening to in case of an update, then when an update to the message reply column happens, the user application would run a procedure which reads the contents of the reply column for that conversation, then deletes the reply column for future use.
B. Second solution I was thinking was when a user application instance uses the send message stored procedure in the beginning, the same procedure has a built in Wait(Receive component, where it will wait for a response from the target queue for that conversation handle, and only receive that one message back, then return it to the application. The trick would be if there is a timeout due to connectivity or other issue, would have to return an error message to the user, even though the target service might still reply correctly after some time.