Can I use SqlDependency with multiple listeners / load balance

Question

I am currently using a SqlDependency with a SQL Server 2012 Service Broker and I want to be able to have two servers configured both listening to the service broker and pull off the queue but message should only be pulled off the queue once total. Each machine should try and pull down what it can but if too many are coming in it should share a balance in pulling in what it can. Right now I start two instances of the program and both are listening. Once a new message is added they both pull off the same message off the queue and run the code.

Is SqlDependency not the solution to what I want to do? What is the better solution to something like this?

Answer 1

You don't need SQL Notifications or SQLDependency. Each instance can execute:

WAITFOR(
    RECEIVE TOP(1) * FROM {NameOfQueue}
), TIMEOUT @timeoutvalue;

This command will WAIT, leaving the connection open, until either a message is available or the timeout has occurred. On the timeout you receive no message so just connect and try again.

Each message can only be RECEIVED by a single process. Internally the row in the Server Broker queue is locked, and other readers will READPAST locked rows.

Because the SQL can be a little bit tricky, I've written what I think is a helpful wrapper class that you are free to use.