Database-level health detection in SQL Server Always On Availability Groups

Introduction

SQL Server Always On Availability Groups create a group of databases that fails over together in case the primary replica is unavailable. These availability group databases failover manually or automatically depending upon your AG configuration. By default, SQL Server fails over all databases from the primary replica to the secondary replica if the primary replica is down or SQL Services stopped in the primary instance.

You can create multiple SQL Server Always On Availability Group in SQL Server configuration. Usually, we group the application dependent databases in an availability group. These databases must be failed over together for smooth working on the application.

Suppose you have two databases in an AG group. Your primary instance is online, but one of the AG databases becomes unhealthy on the primary replica. Your application reports issue because it could not access a database in the AG group. SQL Server does not perform a failover of the AG group to the secondary replica. It requires manual intervention to fix the issues at the primary replica or perform a manual failover to the secondary replica. Sometimes we want that SQL Server should check the database health as well in an AG group and perform automatic failover if any of the databases is not healthy.

Do we have the option to enable the database-level health detection in SQL Server Always ON? Let’s explore it in this article.

Enable enhanced database failover using GUI in an existing availability group

To enable the enhanced database failover, connect to the primary replica using SSMS. In the Always On Availability Group, right-click on the availability group and open its properties.

In the AG property, you get an option as Database-level health detection.

Put a tick for the Database-level health detection and generate the T-SQL script.

Create a new SQL Server Always On Availability Group with enhanced database failover

To enable the database-level health detection while creating a new availability group, enable it in the Specify option page, as shown below.

Conclusion

In this article, we explored the useful enhanced database-level failover feature in an availability group. You should enable it for the production databases. You should also group the database appropriately in an AG group so that it gets failover in case any database reports fails the database-level health detection. You can create multiple AG groups and group databases as per their availability and application requirements.