0

I have 5 sql servers. 3 of them are working sync and 2 are async. Load Balance Read-Only Routing is a great feature on SQL Server 2016. But if I add async servers into the load balancing configuration it becomes unreliable because of data loss.

I want to add a listener for only queries that data loss is unimportant for a short time. Like reporting queries. How can I configure the sql server load balance routing configuration for different listeners?

1

But if I add async servers into the load balancing configuration it becomes unreliable because of data loss.

There are two different things going on here that I believe are being confused. The first is dealing with data that has not yet been sent or received by the replica, the second is the data being available to be read by transactions because it has been redone and following the rules of snapshot isolation.

Synchronous commit does not guarantee that the data is immediately available, both sync and async must go through redo - and while sync commit does acknowledge when the data has been hardened on the remote replica, it does not mean the data is immediately available to be read - it still needs to go through redo and follow snapshot isolation requirements.

Really, data loss comes into play during a forced failover event - which should be very infrequent.

I want to add a listener for only queries that data loss is unimportant for a short time. Like reporting queries. How can I configure the SQL Server load balance routing configuration for different listeners?

Read only routing is configurable per replica per availability group, it doesn't matter if there is one listener or three listeners.

To do this, you're going to want to use a hardware load balancer (or DNS round robin, etc.) to create these specific requirements

|improve this answer|||||
  • Yes, data loss may be on sync mode but we are sure that data becomes available after finishing the commit on the secondaries, right? If this is right, reading on the secondaries in sync mode is not a problem. On the second part, to seperate sync and async connections, using a load balancer would be the best solution. Thanks. – Baran Apr 17 '17 at 11:55
  • @Baran "but we are sure that data becomes available after finishing the commit on the secondaries, right?" No, it is not. It is hardened but not necessarily "available" - this is what I explained above. – Sean Gallardy Apr 17 '17 at 12:17
0

There is a bit of a hack workaround for this, it's not ideal, but it might help in your situation.

Create a new database, and within the database create a set of synonyms that point to your main database. Add this new database to an entirely new Availability Group, and configurute the read-routing target to be whatever replicas you want.

Now change your read-intent connection string for those queries that are more latency sensitive to use that new AG listener to connect. That will hit the database, and the synonym will hit the correct database.

The challenges for this will come with ensuring that this AG is in the same place, and the relevant sync nodes are used for the read-routing (this is something you could script out, and check configurations for frequently). Also, security might become a bit more difficult to handle (but that is all going to depend on how things are configured for your servers).

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.