Executive Summary – Always On Availability Group

December 20, 2020

The ‘Always On Availability Group’ feature is a high-availability and disaster-recovery solution. Since the launch with SQL Server 2012, data professionals have adopted this solution widely. Before implementing ‘Always On Availability Group’, data professionals need to make decisions about availability and failover mode. These decisions will involve discussion with business owners of the applications that will be supported by the databases. While engaging in these discussions, I often get the same questions from business owners who do not have a technical background about the solution.

In this article, I have listed those common questions with answers. I hope this will help in your next Recovery Point Objective (RPO) and Recovery Time Objective (RTO) discussions with business owners while implementing ‘Always On Availability Group’.

What is Always On Availability Group?

An availability group supports a replicated environment for a discrete set of user databases, known as availability databases. You can create an availability group for high availability (HA) and/or for read-scale. An HA availability group is a group of databases that fails over together. A read-scale availability group is a group of databases that are copied to other instances of SQL Server for read-only workload. An availability group supports one set of primary databases and one to eight sets of corresponding secondary databases.

What are the Supported Availability Modes?

Asynchronous-Commit Mode

The primary replica does not wait for any of the secondary replicas to harden the log. Instead, immediately after writing the log record to the local log file, the primary replica sends the transaction confirmation to the client.
Suitable for situations where performance is more critical than synchronized data protection.
The only form of failover supported by asynchronous-commit mode is forced failover (with possible data loss).

Synchronous-Commit Mode

Under synchronous-commit mode, transactions wait to send the transaction confirmation to the client until the secondary replica has hardened the log to disk. The secondary replica hardens every new transaction before the log record is written to the local log file.
Suitable for situations where data protection is more important than performance.
When all the secondary databases of a given secondary replica are synchronized, synchronous-commit mode supports manual failover and, optionally, automatic failover.

What Are the Failover Modes?

Automatic Failover (Without Data Loss)

A failover that occurs automatically on the loss of the primary replica. Automatic failover is supported only when the current primary and one secondary replica are both configured with failover mode set to AUTOMATIC, and the secondary replica is currently synchronized. If the failover mode of either the primary or secondary replica is MANUAL, automatic failover cannot occur.

Planned Manual Failover (Without Data Loss)

Manual failover is initiated by a database administrator, typically, for administrative purposes. A planned manual failover is supported only if both the primary replica and secondary replica are configured for synchronous-commit mode, and the secondary replica is currently synchronized (in the SYNCHRONIZED state).

When the secondary target replica is synchronized, manual failover (without data loss) is possible even if the primary replica has crashed because the secondary databases are ready for failover.

Forced Manual Failover (With Possible Data Loss) aka Forced Failover

Forced failover risks possible data loss and is recommended strictly for disaster recovery. Forced failover is also known as forced manual failover because it can only be initiated manually. This is the only form of failover supported by in asynchronous-commit availability mode.

A failover that can be initiated by a database administrator when no secondary replica is SYNCHRONIZED with the primary replica or the primary replica is not running, and no secondary replica is failover ready.

What Combination Can I Choose?

	Asynchronous-Commit Mode	Synchronous-Commit Mode
Automatic Failover	NO	YES
Planned Manual Failover	NO	YES
Forced Failover	YES	YES*

*If you issue a forced failover command on a synchronized secondary replica, the secondary replica behaves the same as for a manual failover.

What Happens When Primary Goes Down?

Synchronous-Commit Mode	Automatic Failover	Planned: Secondary replica automatically transitions to the primary role. No data loss. If the primary has log records that the secondary does not have, it will rollback when the primary is restarted.Unplanned: Secondary replica automatically transitions to the primary role. No data loss. If the secondary has log records that the primary does not have, the primary will catch up when restarted.
Synchronous-Commit Mode	Planned Manual Failover	Planned: No data loss. Manually failover to the secondary. If any log is waiting in the recovery queue of any secondary database, the secondary replica finishes rolling forward that secondary database. The amount of time required depends on the speed of the system, the recent workload, and the amount of log in the recovery queue.Unplanned: Depends on the synchronization state. If synchronized manually, failover with no data loss. If not synchronized, failover with potential data loss. Potential data loss depends on whether or not any transaction logs had been sent to the secondary replica before the failure. Under synchronous-commit mode, this is possible only until the secondary databases become synchronized.
Asynchronous-Commit Mode	Forced Failover	Planned: First change to synchronized commit. Let everything sync and failover with no data loss.Unplanned: Manually failover with potential data loss. Potential data loss depends on whether or not any transaction logs had been sent to the secondary replica before the failure. Under the asynchronous-commit mode, the accumulated unsent log is always a possibility.

What Happens When the Secondary Goes Down?

Synchronous-Commit Mode	Automatic Failover	Primary continue works as-is. The transaction log will not truncate unless secondary is available. Close monitoring required to avoid filling up the log file, which makes the database read_only.
Synchronous-Commit Mode	Planned Manual Failover	Primary continue works as-is. The transaction log will not truncate unless secondary is available. Close monitoring required to avoid filling up the log file, which makes the database read_only.
Asynchronous-Commit Mode	Forced Failover	Primary continue works as-is. The transaction log will not truncate unless secondary is available. Close monitoring required to avoid filling up the log file, which makes the database read_only.

What Happens When the Old Primary is Available and Online?

Synchronous-Commit Mode	Automatic Failover	When the former primary replica comes back online, it takes on the secondary role, and the former primary database becomes the secondary database. The new secondary replica quickly resynchronizes the new secondary databases with the corresponding primary databases.
Synchronous-Commit Mode	Planned Manual Failover	When the former primary replica comes back online, it takes on the secondary role, and the former primary database becomes the secondary database. The new secondary replica quickly resynchronizes the new secondary databases with the corresponding primary databases.
Asynchronous-Commit Mode	Forced Failover	It transitions to the secondary role, causing the former primary databases to become secondary databases and transition into the SUSPENDED state. It gives you a chance to recover data before resuming synchronization.