How to Change Failover Mode of Availability Replica in Always On Availability Group?
Today, i will describe about the overview of failover modes in AOAG and how to Change failover mode of an availability replica in SQL Server Alwayson Availability Group. As we know SQL Server Always on availability group fails over to one of its secondary replica in case of any failover incident. There are three types of failover exists in SQL Server Always on Availability group. Any such failover might be one of them that are given below.
- Automatic failover
- Planned Manual failover
- Forced Manual failover (forced failover).
There is no data loss if you perform automatic or planned manual failover but there might be some data loss if you are performing forced manual failover. Whether SQL Server Always on Availability group will failover automatically or manual, it depends on the configuration of AOAG availability mode. SQL Server Always on Availability Group supports two availability modes. First one is Synchronous-commit and another one is Asynchronous-commit mode. Synchronous-commit mode supports Automatic and Manual both failover types whereas Asynchronous-commit mode supports only manual failover that is also forced failover type.
The failover type (Automatic or Manual) will be decided by the failover mode property of availability replica. Always on Availability group failover modes depends on the AOAG availability mode configuration as I described above.
If you have synchronous commit replicas then you would have two failover mode options. One is Automatic failover mode and another is Manual failover mode whereas you have only manual failover mode option available in asynchronous commit replicas.
Here, I will describe how to change failover mode of SQL Server Always On availability group using SSMS and T-SQL statements. You can change from automatic failover mode to manual failover mode or vice-versa based on their availability mode configuration. As I explained above, failover mode is a replica property that determines the failover mode for replicas that run under synchronous-commit availability mode. Read below prerequisites before going to change failover mode of availability replica in SQL Server Always On availability group.
- Make sure to perform this activity on primary replicas.
- As we know SQL Server Failover Cluster Instances (FCIs) do not support automatic failover by availability groups, so any availability replica that is hosted by an FCI can only be configured for manual failover.
- Make sure that you have admin privilege or minimum you should have ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.
Steps to Change Failover Mode of an Availability Replica
Here, I will describe to change it using two ways. One is using SSMS and another is using T-SQL statements. Let’s start with SSMS method.
Using SQL Server Management Studio
Follow below step to change failover mode of an availability replica.
- Launch SQL Server Management Studio and connect to the primary replica, and expand the server tree.
- Expand the Always On High Availabilitynode and then Availability Groups
- Select the availability group for which you have to change the failover mode then right-click on the identified replica, and click Properties.
- In the Availability Replica Properties dialog box, you can select correct Failover mode as per your requirement from the dropdown list to change the failover mode of this replica.
- Choose any value from drop down as per your requirement and close the windows post apply this new change.
We can change failover mode of an availability replica using T-SQL statement as well. Follow below steps to do this change using T-SQL.
- Connect to the Primary Replica.
- Open New Query window to execute your T-SQL statements.
- Run below statement to change the failover mode from Manual to Automatic of an availability group named Techyaz.
ALTER AVAILABILITY GROUP Techyaz MODIFY REPLICA ON 'GURTY01' WITH (FAILOVER_MODE = AUTOMATIC);
Techyaz is the name of the availability group and GURTY01 is either the computer name or the failover cluster network name. For named instances add full instance name post \ as well. Use the name that hosts the replica that you want to modify.
Here, I have shown you step by step method to change Failover Mode of Availability Replica in SQL Server Always On Availability Group. I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.
- How to Upgrade or Apply Patches on AOAG Instances?
- Understanding Backup Preferences in Alwayson Availability Group
- How to Restore a Database participating in AOAG?
- SQL Server Alwayson Interview Questions & Answers
- Fix SQL Server Alwayson Error 35250: Joining Database on Secondary Replica resulted in an Error
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018