Consuming Multiple Messages In Parallel from Multiple Windows Services

Hello All,

After hitting limitations in the SQL CLR world that bar us from invoking COM objects we are forced to use windows services to read the messages off the Service Broker Queues.
Unfortunately we loose the auto activation feature in the Queues, but we can still read messages and perform the SQL work under one transaction.

We are going to attempt to take N messages simultaneously from the Queue, though N instances of a windows service. If the messages send to the queue are one message per conversation, will we be able to achieve having N readers take messages off simultaneounsly?

Thank you very much,

Lubomir

P.S. if anyone has a better approach to obtaining the message in "out of sql code" or invoking external (not assemblies stores in SQL server) code libraries, that would be etremely nice to hear. I have thought about invoking a web service through CLR, but that is probably too much overhead - MSMQ seems much more appealing than a web service;

Lubomir,

Retrieving messages from a queue with the RECEIVE statement should be regarded similar with running an UPDATE statement on a table. Multiple clients (Windows Services in you case) can run concurent updates (receives in your case) as long as they don't try to update the same rows (messages in your case). The difference is that in the RECEIVE case there is a built in mechanism to choose what rows should be updated (i.e. what messages should be dequeued) in order to avoid update conflicts. Each RECEIVE will grab the next available (i.e. not locked) conversation group, lock it, and then retrieve (dequeue) messages from conversations in this group. In fact, one can use any of the tools that show query plans (Profiler, Management Studio, Query Analyzer) and ask for the query plan of the RECEIVE in order to understand what this statement does.

So yes, RECEIVE statements can be issued in parallel and they will execute simultaneously.

There is an External Activator sample at you might want to take a look at, http://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319.

HTH,
~ Remus

|||Thank you very much for the insight, Remus. The external activator seems rather promising.

Lubomir|||

Thanks

So if I understand well I should build a different conversation for each client, and replicate same messages on different conversation, so that every client gets the message.

But what if the number of clients isn't fixed?

Any suggestion?

|||

Basically this is a Publish/Subscribe scenario. Look at this example at http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx and see if you can start building something from it.

HTH,
~ Remus