Service Broker - Event Notification Question

Question

HI All,

I have a scenario where I want to write messages to a Service Broker Queue and have an application (WCF / Console, etc) listening on that queue.  When it sees a message arrive (Much like MSMQ) I want it to process that message.  What is the best process for handling this scenario.  I would preferablly like it to work very similar to WCF / MSMQ, but from what I have read there isn't a WCF / SB channel?

I have also noticed references to External Activation, but that seems like I would be tightly coupling the events to a specific applicaiton. 

Ultimate Goal:

• On change in DB submit a message to a Service Broker Queue
• Have an application (WCF / Console, etc) listenting on that queue for new messages
• When a message comes in the application will dequeue ("receive") that message and process it. 

I guess I could just setup my app to poll for new messages on the queue, but that doesn't seem to be very efficient...I would prefer to have a new message in the queue "wake up" the app and have it process the message. 

--Pretty straight forward with MSMQ, but I can't seem to find a similar strategy for Service Broker (Might must just be me overlooking the obvious)

Any suggestions would be greatly appreciated. 

Answer 1

the external event notification you mention will do the trick...but i've never used it.

i have a simular app i've written that works well...you'll have to write some client code...

fire up a listener thread in your client that read the queue that messages are being posted into...no ssb 'activation' on that queue...

it's easy to submit some t-sql to read from the queue directly...look at the 'receive top (1)'  t-sql command...

i use a timeout so it waits for a minute to read...if nothing read, timer expires boot'n listener...return'n an empty resultset to my client.

if result set isn't empty then i've got something to do...

if it is empty then pause a second or so and the try again.

you can easily spawn as many listener threads as you want if you architect it for multi-threading...but i've found one listener is adequate.

but it all depends on what you are try'n to get done with the message...if message was posted by some user button click and it's wait'n for a response from you i'd look for another way to do this...

Don't forget to send your 'end conversation' from your client!!!

By its nature, using an external client to read messages...you won't be able to work inside of a transaction...that is, unless you have it, you won't be able to do any XA stuff...again, if you don't need to worry about a lost message your okay...your solution should be considered 'optimistic' processing...that is...your 'optimistic' it's going to work 99% of the time. Good logging will allow you to recover a message if death hits you unexpectedly...:)

If you can't afford the likes of a 'lost message'...then you might want to do all this w/T-Sql and some Sql Agent code...that way you can maintain your transaction integrity on your sql instance...if it's accross multiple instances your toast w/out MSDTC (XA distributed coordinator).  Reading messages from a queue and carring a transaction across instances isn't easy...but can be done.

hope this gives you somethings to consider...

---

mike t.

Answer 2

Ben,

Also, see the Announcing Service Broker External Activator announcement the home page for this form, SQL Service Broker.

---

Dan Jameson
Manager SQL Server DBA
CureSearch for Children's Cancer
http://www.CureSearch.org

Answer 3

I actually went with the External Activator approach thanks Dan.