Sql-server – Automatic fail over in Always on availability group with 3 Node in synchronous mode and 1 Node in Asynchronous Mode

availability-groupsclusteringfailoverhadrsql-server-2012

I am laying out a plan where there will be 3 Node on site with automatic fail over with synchronous mode and 1 remote replica with asynchronous mode. My question is if the primary goes down which node among the remaining 2 replica will be failed over. I will have a file share for node majority with file share quorum.

Best Answer

My question is if the primary goes down which node among the remaining 2 replica will be failed over.

The node that is set to Synchronous with Automatic failover will be the chosen one to failover when the primary goes down. You can only have one node set as the automatic failover target, see the docs for 2014 (it was the same for 2012):

To configure an availability group for automatic failover, you need to set both the current primary replica and one secondary replica to synchronous-commit mode with automatic failover.

On SQL Server 2016 and newer, you can have a second automatic failover target (it's 3 including the primary):

To configure an availability group for automatic failover, you need to set both the current primary replica and at least one secondary replica to synchronous-commit mode with automatic failover. You can have up to three automatic failover replicas.

Remember that an Async replica might have data loss if you try to do a force failover.

Related Question