1

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.
  • ApplicationIntent=ReadOnly and MultiSubnetFailover=True are 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?

2

Is it better to use always on feature "read only routing" or to use an external load balancer to balance connection between replicas ?

That's going to 100% depend on your needs. For example, if you're just wanted to have readable workloads hit a secondary, then inside of SQL Server should be fine. Now, if you wanted to hit the secondary that is closest (geographically) to your internal users depending on where they are... then you'll need some fancy rules and external load balancing.

Regardless which you choose, unless creating your own health check or probe the external load balancer will not know which secondary servers are healthy or unhealthy.

does not need a dba to do network work

Neither of the ways requires a DBA to do "network" work, you setup the URL (SQL) and the list (SQL) and magic starts to happen.

Does somebody have any insight or feedback on always on ROR?

Use it where you need it, it's just another tool in the collection. You may find that connecting directly to the secondary is better for your use cases and scenarios that using ROR... or you may write your own health checks for the external load balancer (if it supports it) to only route to secondary servers that take into account locality of the user.

  • Thanks for your answer. When I say "network" work I was thinking about on-call duties. Our production team and our on-call guys know how to manage a load balancer and do changes when needed but they don't know how to connect to a db. In this case I prefer to put the load balancing logic in an external load balancer as we do today. We also have our own probes for sql servers. – Benjamin Baumann Feb 27 '17 at 15:07

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.