All geek questions in one place

Can I use SqlDependency with multiple listeners / load balance

I am currently using SqlDependency with SQL Server 2012 Service Broker, and I want to be able to have two servers configured for the listening service broker and pull the queues, but the message should only be pulled out of the queue as soon as the general. Each machine should try to bring down what it can, but if there is too much in it, it must share the balance, drawing in what it can. Right now I am running two instances of the program, and both of them are listening. After adding a new message, they both remove the same message from the queue and run the code.

Is SqlDependency not the solution of what I want to do? What is the best solution for something like that?

+4
source share
2 answers

You do not need SQL notifications or SQLDependency. Each instance can perform:

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

This command will WAIT, leaving the connection open until a message appears or a timeout occurs. You will not receive a message in the timeout, so just connect and try again.

Each message can be received by only one process. Internally, the row in the Server Broker queue is locked, and other readers will WRITE the locked rows.

Since SQL can be a little more complicated, I wrote what I find useful a wrapper class that you can use .

+1
source

, , - , SQLDependency . , . , , SignalR SQL

SQL Pub / Sub

, SQL Server, , .

SQL , . , SQL Notification , - , . , . . ( )

, , (, RabbitMQ) (, Redis)

+2

Source: https://habr.com/ru/post/1612295/

More articles:


All Articles