We are migrating a SQL Server 2008R2 with mirroring and transactional replication to SQL Server 2016 with AlwaysOn. So far, so good but we still have one question.
Is it better to use always on feature "read only routing" or to use an external load balancer to balance connection between replicas ?
I read a lot but I did not find any feedback. What I found is the following :
- We already use an external load balancer with our current platform and it's working well
- Since Sql server 2016, we can do round robin in sql server ror.
- we must keep in mind that in case of persistent connection (or connection pooling), connection will be reused so it will point to the very same server. In other words, connections are balanced, not workload. That's the same for both solutions.
MultiSubnetFailover=Trueare useful with both solutions.
To what I understand here are the advantage and drawback of each solution :
- Always On ROR : Can "group" instances, failover easy to configure, knows easily wich instance is primary and instance states. Very recent (untested?) feature.
- External Load Balancing : easier to manage (does not need a dba to do network work), tried and true, can do other load balancing method than Round Robin (weighted round robin, least connection ...).
Did I miss something? Does somebody have any insight or feedback on always on ROR?