5

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?

Wai Ha Lee
  • 8,598
  • 83
  • 57
  • 92
Smeiff
  • 259
  • 1
  • 6
  • 17
  • Are you [configuring the queue yourself and telling SqlDependency about it](https://msdn.microsoft.com/en-us/library/ms224871%28v=vs.110%29.aspx), or just [calling SqlDependency with the connection string and letting it create the queue](https://msdn.microsoft.com/en-us/library/ms224872%28v=vs.110%29.aspx)? (The docs say "If no queue name is specified, SqlDependency creates a temporary queue and service in the server that is used for the entire process, even if the process involves more than one AppDomain. The queue and service are automatically removed upon application shutdown.") – stuartd Oct 20 '15 at 12:18
  • I configured the queue myself and told the SqlDependency to use that queue. – Smeiff Oct 20 '15 at 13:20

2 Answers2

2

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

SQL Pub/Sub

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)

Jared Dykstra
  • 3,596
  • 1
  • 13
  • 25
  • Thank you not much documentation available in this area... how would the table act as a queue of jobs? I would need another set of logic to divide out the work or something wouldn't I? Or are you saying avoid using the message broker and just constantly query the database for new data, put lock on the row marking it's been consumed etc. – Smeiff Nov 04 '15 at 16:26
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.

Joseph Daigle
  • 47,650
  • 10
  • 49
  • 73
  • Thank you! I combined this with a trigger and set it up to be an infinite loop pretty much and it's working. I ended up not having to use your wrapper class but I will look into it. – Smeiff Nov 12 '15 at 21:01
  • 1
    That link is broken: would the new link be https://github.com/jdaigle/LightRail/blob/master/src/LightRail.ServiceBus/SqlServer/ServiceBrokerWrapper.cs ? – Richard Nov 17 '16 at 10:15