Sql-server – SQL Always On automatic failover incase of multiple secondary

1) From Overview of AlwaysOn Availability Groups (SQL Server)

The primary replica sends transaction log records of each primary database to every secondary database. Every secondary replica caches the transaction log records (hardens the log) and then applies them to its corresponding secondary database.

So, primary first, then secondary.

2) Are you asking how node 1 is brought back on line? That's what your operations people and DBAs are paid their huge salaries to do.

Alerts would be a good idea. Maybe even a very good idea.

If node 1 dies AlwaysOn will re-direct connects to node 2, transparently to the application. In-flight work may receive an error. New connections will be routed to node 2. This is the HA and DR in action. When node 1 comes back you can continue running on node 2 as primary and have node 1 as secondary, or organise a managed failback at a time of your choice. If node 2 dies while node 1 is still down, then you have a problem.

3) Active-passive has the secondary as "warm": it is ready to become the primary when required but cannot do any application work until then. Active-active has a "hot" secondary: it can process read-only transaction from the application, and be used for backups and such like. IIRC one license is needed for active-passive and two for active-active.

There is a max of 8 secondary replicas for AlwaysOn in SQL 2016, I do not believe there is a change for that in 2017

Always On Availability Groups on 2016 supports up to eight secondary replicas including one primary replica and two synchronous-commit secondary replicas (no Enhancement on this compared to SQL 2014). Also use SQL Server 2016 Always On Availability Groups to provide high availability for SQL Server databases hosted in Azure, etc. Source

In any case adding a local secondary would not add value from a SQL failover scenario. Only the instance with votes for quorum can be primary, of these there can only be three potential primaries

Another enhancement is an increase in the number of Automatic Failover partners which means you can have two more failover partners apart from the primary. In a three node scenario, if one node fails still we can have high availability with other two nodes. Source

Automatic failover requires synchronous-commit, Spreading synchronous-commit over geographic distance can have an impact on performance.

If you have a 3 node cluster (file share and other quorum considerations excepted ...), any node that can not talk to the at least one of the other two will be down. If all three loose connection, there will be no auto failover (all three will be down). If any two can talk, one of those two will be primary.

Any of the replicas that can communicate with the current primary can have asynchronous copies that are current. So if corporate goes down, one of your two stores will be primary, and you could have several stores with asynchronous read only copies.

Given your scenario you probably want the primary at corporate, and your two synchronous-commit automatic failover partners at the stores that provide the fastest and most dependable connections to corporate.