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?
Best Answer
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.
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