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

Once a new message is added they both pull off the same message off the queue and run the code

The behavior you describe is how SQLDependency is designed to work. If there are multiple listeners, all listeners are notified. For example, you can see this described in the SignalR SQL Backplane documentation

Notice how all VMs receive notification from SQL Server, including the VM that initiated the update.

If you want to distribute SQL Notifications across a pool of worker VMs, you need a way to share state. Note that the SQL Notification is only an indication that something changed and doesn't indicate what changed. One approach is to add a table to the database to act as a queue of jobs or actions. Subscribers can query this queue on each notification and claim the action by updating or deleting from this table. (Appropriate locks would have to be configured on the table)

Alternatively, you can do this using other tools for shared state, such as a message queue (eg. RabbitMQ), or distributed cache (eg. Redis)