What is Enhanced Database Failover and How to Enable it for Availability Group?
As we know Always on Availability Group is the set of user databases that are combined to failover if primary replica becomes inaccessible. Till SQL Server 2014, Alwayson Availability Group was not able to start failover operation if any individual database within AOAG become inaccessible. Microsoft introduced an option named Enhanced Database Failover in SQL Server 2016 to trigger the failover in case any database participating in Availability Group loses the ability to write transactions. We also call it Database Level Health Detection in Availability Group.
Enhanced Database Failover option is not mandatory while configuring Always on Availability Group in SQL Server 2016. It’s optional and If you have enabled Enhanced Database Failover option for any Availability Group along with automatic failover configuration then this option will trigger failover to a synchronized secondary replica in case any database participating in that availability group is no longer able to write transactions.
You cannot set this option for specific databases within availability group. Enhanced database failover or database level health detection will be enabled at availability group level. Once you configure it, all databases participating in that availability group can use this feature to trigger failover if anything goes wrong with them.
Configure Enhanced Database Failover to Availability Group
We can set this option as per our choice or requirement either by using GUI Wizard or by using Transact-SQL. As of now, you cannot configure Enhanced Database Failover option using PowerShell cmdlets. Remember, by default, enhanced database failover option is disabled. If you want to use this feature, you need to enable it. Here, I will explain how to enable Enhanced Database Failover to an Availability Group while its creation and to the existing one.
If you want to check whether an availability group has enhanced database failover enabled or not then we can use dynamic management view sys.availability_groups. You will see a column named db_failover in this DMV. If the value of any availability group is showing zero then it means this feature is disabled and if value shows 1 then it means it’s enabled. You can execute below command to get the settings for all availability group.
select name, db_failover from sys.availability_groups Enable Enhanced Database Failover using GUI in SSMS
We can enable this option while configuring availability group or later to the existing availability groups that was already created without enabling this option. As we know there are two ways to create an availability group using GUI in SSMS. One is by using New Availability Group dialog box and another way is by using Availability Group wizard window. Enabling this option is quite same in both ways. To enable enhanced database failover, select its checkbox next to Database Level Health Detection as shown in below image.
You can get this window by following given steps. In SQL Server Management Studio, connect to your SQL Server database engine. Right click on the AlwaysOn High Availability node, and run the New Availability Group Wizard. Check the Database Level Health Detection checkbox on the Specify Name page. Then complete the rest of the pages in the wizard.
Another way to enable this option for existing availability groups are given below. Launch Properties window of existing Availability Group in SQL Server Management Studio. Here, you can see this option as shown in below image. Check the option Database Level Health Detection, then click OK to apply the changes.
Enable Enhanced Database Failover using T-SQL
We can enable enhanced database failover using T-SQL during availability group creation or after creation by running ALTER AVAILABILITY GROUP statement. To configure it during the creation of an availability group, we must set DB_FAILOVER to ON as showing in below command. I have highlighted this in green colour.
CREATE AVAILABILITY GROUP [AG_Techyaz] WITH (DB_FAILOVER = ON) FOR DATABASE [Techyaz] REPLICA ON 'SQLInstance1' WITH (ENDPOINT_URL = 'ENDPOINT ADDRESS:PORT-Number', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT), 'SQLInstance2' WITH (ENDPOINT_URL = 'ENDPOINT ADDRESS:PORT-Number', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT)
If you have already created availability group without enabling this option then we have also an option to add this behavior to existing availability groups by using ALTER AVAILABILITY GROUP command as given below.
ALTER AVAILABILITY GROUP [AG_Techyaz] SET (DB_FAILOVER = ON)Disable Enhanced Database Failover using T-SQL
We can disable this option simply by running below command. We must set DB_FAILOVER value to OFF to disable this feature.
ALTER AVAILABILITY GROUP [Techyaz] SET (DB_FAILOVER = OFF)You will see error message 41653 logged in SQL Server error log if an availability group has failed over due to the database level health detection checks.
Cautions
It is very important to understand how enhanced database failover or database level health detection works. This option does not monitor disk uptime or database file availability to trigger failover.
Suppose your database is idle with no active transactions, and with no physical writes occurring and some of the database files become inaccessible in between then this option will not trigger automatic failover. Later, when a database checkpoint occurs, or a physical read or write occurs for fulfilling a query, then SQL Server may then notice the file issue, and react by changing the database status, and subsequently the availability group with database level health detection set on would failover due to the database health change.
As another example, when the SQL Server database engine needs to read a data page to fulfill a query, if the data page is cached in the buffer pool memory, then no disk read with physical access may be required to fulfill the query request. Therefore, a missing or unavailable data file may not immediately trigger an automatic failover even when database health option is enabled.
I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.
Read More:
- How to Patch SQL Server Failover Cluster Instance Running with Always on Availability Group?
- SQL Server Alwayson Interview Questions & Answers
- Understanding Backup Preferences in Alwayson Availability Group
- How to Change Failover Mode of Availability Replica in AOAG?
- Fixing Application connectivity Issue after AOAG automatic failover
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
