Rajendra Gupta
SQL Server Always On Availability Groups dashboard synchronizes

Explore failover types in SQL Server Always On Availability Groups

September 29, 2020 by

In this 27th article of the SQL Server Always On Availability Group series, we will discuss various failover modes for AG databases.

Introduction

In the earlier articles, we saw that the SQL Server Always On Availability Group provides high availability and disaster recovery solution in SQL Server. As we have explored various configurations for AG, it is essential to know the failover options present for your availability groups. It helps to take the decision in case of any infrastructure issues and utilize AG to bring your database online.

Failover options in the SQL Server Always On Availability Group

Failover is a process to switch the roles of the availability replicas in an AG configuration. Once a failover occurs, the failover target (current secondary) takes the primary role and brings the database online to accept connections. Similarly, in a failback, the old primary (current secondary) again takes charge of the availability group as a primary replica.

We can have one primary and multiple secondary replicas depending upon different SQL Server versions. These multiple replicas can be in the Synchronous, or Asynchronous data commit mode.

  • Synchronous: Primary receives the acknowledgement from the secondary replica, and then it commits transaction on the primary
  • Asynchronous: Primary commits the transactions without waiting for any confirmation from the secondary replica

The failover modes in SQL Server Always On Availability Groups depend upon these Synchronous or Asynchronous commit. In a high-level, SQL Server supports the following failovers in AG.

  • Automatic failover
  • Planned Manual failover
  • Forced failover (manual)

Let’s understand these failover types as per the data synchronization methods.

Failover modes in a Synchronous Commit Mode for SQL Server Always On Availability Groups

We can do automatic failover and planned manual failover for SQL Server Always On Availability Groups in the synchronous commit replica.

Automatic failover in SQL Server Always On Availability Groups

This type of failover automatically occurs in the case of the primary replica goes down.

  • For an automatic failover, both primary and secondary replica (minimum one secondary) must be configured with the Synchronous commit and Automatic failover
  • Both replicas should be synchronized

To verify your AG’s support automatic failover, right-click on the availability group and open its properties. It shows the highlighted configuration.

  • Availability Mode: Synchronous commit
  • Failover Mode: Automatic

Availability  group properties

Let’s see automatic failover in action. To initiate an automatic failover, I rebooted my primary replica intentionally. Before shutting down the primary replica, verify that your AG databases in synchronized. It also does not show any data loss on the dashboard.

  • Note: Do not perform these steps in a production environment for testing purposes

Availability  dashboard

  • The current primary replica is SQLNode2\INST1
  • Secondary replica: SQLNode1\INST1

Reboot the SQLNode2 and launch the dashboard from the new primary replica SQLNode1\INST1. We can see a new primary replica SQLNode1\INST1. The AG dashboard is in a critical state because the old primary is not up yet, and status is Not Synchronizing.

Error in AG dashboard

Once the server comes up (SQLNode2\INST1), and it captures the transaction logs, the status changes to Synchronized.

The status changes to Synchronized

To investigate the background work done by SQL Server, view the error logs on the new primary replica. I highlight the useful messages for your reference.

  • It terminates the availability group connection with the primary database on SQLNode2\INST1

    terminates the availability group connection

  • The secondary replica database changes roles from the Secondary to Resolving

    secondary replica database changes

  • It logs an entry in the error logs for the hardened LSN and Commits LSN

    entry in the error logs

  • The secondary replica (SQLNode1\INST1) prepares to transition the role to the primary

    secondary replica role

  • The AG group status changes from the Resolving_Normal to Primary_Pending

    AG group status changes

  • It starts listening to the SQL listener

    the SQL listener

  • The local availability replica status changes from the Primary_Pending to Primary_Normal

    local availability replica

  • Now, the availability group database role changes from Resolving to Primary

    database role

  • Our databases were in Synchronized mode. It performs the database recovery and notifies if any transactions rolled back is required

    the database recovery

  • The old primary replica comes online. It detects that another replica is now the owner of the availability group. It establishes a connection with the new primary replica SQLNode2\INST1. It catches all pending transaction records from the current primary replica and comes in the Synchronized state. We can failback resources now once the status changes to Synchronized

    current primary replica and role

Planned Manual failover in SQL Server Always On Availability Groups

The database administrator initiates a planned failover, and it causes the synchronized secondary replica to take over the role as the primary replica. For a manual failover, your AG databases should be synchronized to confirm that your secondary database copy is similar to the current primary database.

Suppose you have multiple availability groups in your SQL instance. Due to some specific requirement, you want to failover a specific availability group databases. If you stop SQL Services on the primary replica, it fails all synchronized availability group. In this case, the planned manual failover works. You can failover a specific availability group however point to note that it fails over all databases in that availability group. We cannot failover a specific database from the availability group.

You can use both automatic and manual failover in the synchronized data mode. In the case of multiple replicas, we might set a secondary replica for manual failover, as shown below.

Planned Manual failover

We can initiate a manual failover from both SSMS GUI wizard and T-SQL. Right-click on the availability group to failover from the primary instance and go to failover.

initiate a manual failover

Go through the failover wizard and perform an AG failover.

the failover wizard

Alternatively, you can run the Alter Availability Group command to initiate a failover from the primary to the secondary replica.

Failover modes in an Asynchronous Commit Mode

We cannot have automatic or planned manual failover in an asynchronous commit mode. In a forced failover, we can failover to a secondary replica, especially in a disaster recovery scenario.

In an asynchronous commit, the secondary replica might fall behind with the primary database. Therefore, if failover, it causes data loss depending upon the sync lag. You should do force AG failover only if you need to restore database availability and you can afford to lose some data.

For a forced failover, your availability database should be in the Asynchronous commit mode. It only supports a manual failover. If your AG database is in synchronous mode, but these databases are not synchronized, it only can have a forced failover.

Alter Availability Group

You can initiate a forced failover from the secondary replica. Before failover, you can measure the latency between primary and secondary replica using the Measuring Availability Group synchronization lag.

Connect to the secondary replica of SQL Server Always On Availability Group in SSMS and start a failover process. In the following screenshot, it shows a possible data loss warning if we perform a failover.

start a failover process

Click on the warning, and you get the message that databases are not in a Synchronized state. It could result in the data loss for any transactions that did not reach to the secondary instance before failover.

Click on the warning for SQL Server Always On Availability Groups

Confirm to perform a failover with the potential data loss.

potential data loss

Validate the failover configurations. In the failover actions, you get a message that this replica is not a failover ready.

Validate the failover configurations

It performs manual force failover. In the below AG dashboard, the primary replica is SQLNode2\INST1. AG dashboard is not healthy.

SQL Server Always On Availability Groups dashboard is not healthy

In the force failover, SQL Server starts a new recovery fork. Both the old primary and secondary database has different restore forks. Therefore, if we do a force failover, SQL Server suspends data movement to all secondary replicas. It is for you to verify the data loss and take a decision to suspend or resume the data movement.

In the availability database, resume data movement in the primary and secondary replicas, as shown below.

force failover

It synchronizes the AG with the new primary replica after the force failover.

SQL Server Always On Availability Groups dashboard synchronizes

Note: The secondary database can track a maximum of two recovery forks. In the case of multiple forced failovers, you might see data synchronization issues even if you resume it. In this case, remove the secondary database and rejoin after restoring the latest backups.

Conclusion

In this article, we explored a different kind of failover in SQL Server Always On Availability Groups. You should analyze your requirement and configure the AG to support automatic or manual failover.

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability
Configure SQL Server Always On Availability Groups using Windows PowerShell scripts
Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups
Synchronize logins between Availability replicas in SQL Server Always On Availability Group
Session timeouts in SQL Server Always On Availability Groups
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views