The order of automatic failover

One of the new features that has been added in SQL Server 2016 for High Availability is the possibility to configure 3 replicas for automatic failover. In SQL Server 2014, it was only possible to configure 2 automatic failover replicas. But how is SQL Server going to decide to which replica it will failover first? Imagine that you have an AlwaysOn Availability Group configuration with 3 nodes. Node 1 is primary, Node 2 and 3 are secondaries. All three replicas are configured for automatic failover. Will it failover first to Node 2 and then Node 3, or the other way around? Well, let’s figure out how it works!

First of all, I’ve added Node 2 and 3 to my availability group as secondary replicas.

If you verify the Preferred Owners of your Availability Group Role in the Failover Cluster Manager, you will notice that the order will be the same as you have added the replicas to the availability group. This means if Node1 becomes unavailable, Node 2 should become the new primary.

During my tests, I’ve stopped the SQL Server Service to verify the automatic failover. And as expected, Node 2 became my new primary replica.

If we look back at the preferred owners in the Failover Cluster Manager, you will notice that the order of the Preferred Owners has changed.

Secondly I make Node 2 also unavailable. I expect that Node 3 will become my new primary as Node 1 is still down.

And again, the order of the Preferred Owners has changed.

For my second test, I added the secondary replicas in a different order, first Node 3 and secondly Node 2.

In the Object Explorer, you will see that the replicas are added again in alphabetical order.

However, if you check again the Preferred Owners of the Availability Group Cluster role, you will see that now the order of the replicas is the same like I’ve added them to the availability group.

I disabled the SQL Server service on Node 1 again and as expected Node 3 became my new primary replica.

To conclude, the nodes are given to the cluster in the order that they were added to the AlwaysOn Availability Group.  SQL Server iterates the replica list in the availability group and does not reorder. Microsoft does not support manual updates to the cluster config for the availability group.  There have been problems with users getting into the config and breaking failover or disabling the full availability group.  We readjust preferred owners to what we think it should be every time something changes so even if the user changed the order we might reset it back to what we think it should be.

To change the failover order you will have to drop the replica and then add it back in, so it is after the other replica.