The official PASS Blog is where you’ll find the latest blog posts from PASS community members and the PASS Board. Contributors share their thoughts and discuss a wide variety of topics spanning PASS and the data community.

Always On Availability Group – Executive Summary

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.

 

Reference

Taiob Ali
About the author

Taiob Ali, MVP Data Platform, is an accomplished technical leader with a proven record of success. For 14 years, he has worked with the Microsoft Data Platform and MongoDB both on-premise and cloud. His experience includes all three major business sectors: finance, e-commerce, and healthcare. Taiob has hands-on experience in managing large database projects, massive data migration, intricate process design, testing and deployment, performance tuning, and long-term capacity planning.

Please login or register to post comments.

Theme picker

Back to Top
cage-aids
cage-aids
cage-aids
cage-aids