Sql-server – Migrate from SQL Server 2016 Failover Cluster to Availability Groups

availability-groupsclusteringfailoversql-server-2016

We have a 16 node SQL Server 2016 failover cluster with around 20 SQL Server instances and total number of databases around 600.

Since we are upgrading our hardware to Hyper-Converged infrastructure which has limitations with failover clusters with shared disks then we have to migrate our cluster to availability groups.

My question is: what should we consider if we are going to migrate to SQL Server availability groups? can we have the same number of instances and multiple availability groups on the same servers as we have in the current failover cluster?

Appreciate if someone guide us with some technet articles which talks about similar cases.

Best Answer

Prereqs, Restrictions, Recommendations - Always On Availability Groups states:

The actual number of databases and availability groups you can put on a computer (VM or physical) depends on the hardware and workload, but there is no enforced limit. Microsoft has extensively tested with 10 AGs and 100 DBs per physical machine.

An availability group is really just a container to group mirrored databases and associate a listener with them, and to provide the interface with Microsoft Cluster to determine what server should be primary. The only significant associated work is the replication of the databases.

So the short answer is "yes," you can use the same number of instances and databases, but you have to consider whether the additional load of replication will overload the system. So you'll want to pay close attention to the databases that have higher I/O and try to distribute them amongst the nodes so that one node isn't a primary and/or secondary for multiple heavy hitters. Or, if that's unavoidable, make sure you've got lots of bandwidth, CPU, memory, and disk space.

AlwaysOn Availability Groups, Listener, Named Instances, Port Numbers, etc. is also something that you'll want to peruse.