大网数据广告活动
数据库与应用安全

Manually configure AG for automatic failover for errors on data file

· 52CE 我爱测

This article describes how to configure an Always On availability group to perform a failover when the database data file is unavailable.

Summary

In a SQL Server Always On availability group, database-level health detection can perform a failover only if the error (disk failure or similar) occurs in the database transaction log.

If the error happens on the data file, SQL Server only sends the failure notice to a Windows Server Failover Cluster (WSFC) and relies on it to make the right decision based on policy configuration.

To configure an Always On availability group to perform a failover when errors 823 and others occur, use one of the following procedures:

Configure Failover Cluster Manager

This procedure sets up the WSFC policy to cause a failover to one of the possible owners instead of attempting to restart the role on the current primary node.

  1. Open Failover Cluster Manager.

  2. Expand your cluster and select Roles.

  3. Right-click the AG role and select Properties > Policies.

  4. Set the value of Maximum restarts in the specified period to 0.

  5. Check the box If all the restart attempts fail, begin restarting again after the specified period (hh:mm) and select OK.

  6. Make sure both your nodes are Possible Owners and preferred owners.

Custom configuration option

This procedure provides an example of how to utilize the available alert mechanism in SQL Server and SQL Server Agent to detect the error and perform a failover.

  1. In SQL Server Management Studio, expand SQL Server Agent, right-click Alerts, and select New Alert....

  2. Specify a value for Name, select SQL Server event alert for Type, specify the value of Error number to 823 or any other desired error as per the list, and then select OK.

  3. Select Response, check Execute job, select the job you want, and then select OK.

  4. On the Job Step Properties dialog, specify a value for Step name, select Operating system (CmdExec) for Type, and then select SQL Server Agent Service Account for Run as.

  5. Enter the following failover sqlcmd command:

    sqlcmd -S <SecondaryReplicaName> -U SQLADMIN -P <YourPassword> -Q "ALTER Availability Group <AGName> Failover"

    [!NOTE] <SecondaryReplicaName>, <YourPassword>, and <AGName> are placeholders. You need to change it to match your environments. This script is an example for reference. A full script should perform other checks before performing a failover.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 02/07/2023
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。