启用或禁用 AlwaysOn 可用性组功能Enable or Disable Always On availability group feature

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

启用 AlwaysOn 可用性组Always On availability groups 是服务器实例使用可用性组的先决条件。Enabling AlwaysOn 可用性组Always On availability groups is a prerequisite for a server instance to use availability groups. 在创建和配置任何可用性组之前,必须在将承载一个或多个可用性组的可用性副本的每个 AlwaysOn 可用性组Always On availability groups 实例上启用 SQL ServerSQL Server 功能。Before you can create and configure any availability group, the AlwaysOn 可用性组Always On availability groups feature must have been enabled on the each instance of SQL ServerSQL Server that will host an availability replica for one or more availability groups.

重要

如果您删除后重新创建了 WSFC 群集,则必须在其原始 WSFC 群集上承载可用性副本的每个 AlwaysOn 可用性组Always On availability groups 实例上都禁用然后重新启用 SQL ServerSQL Server 功能。If you delete and re-create a WSFC cluster, you must disable and re-enable the AlwaysOn 可用性组Always On availability groups feature on each instance of SQL ServerSQL Server that hosted an availability replica on the original WSFC cluster.

启用 AlwaysOn 可用性组的先决条件Prerequisites for Enabling Always On Availability Groups

  • 该服务器实例必须驻留在 Windows Server 故障转移群集 (WSFC) 节点上。The server instance must reside on a Windows Server Failover Clustering (WSFC) node.

  • 该服务器实例必须正在运行支持 AlwaysOn 可用性组Always On availability groups的 SQL Server 版本。The server instance must be running an edition of SQL Server that supports AlwaysOn 可用性组Always On availability groups. 有关详细信息,请参阅 SQL Server 2016 各个版本支持的功能For more information, see Features Supported by the Editions of SQL Server 2016.

  • 一次仅在一个服务器实例上启用 AlwaysOn 可用性组。Enable Always On Availability Groups on only one server instance at a time. 在启用 AlwaysOn 可用性组之后,一直等待直到 SQL ServerSQL Server 服务已重启,然后才继续在另一个服务器实例上进行操作。After enabling Always On Availability Groups, wait until the SQL ServerSQL Server service has restarted before you proceed to another server instance.

有关用于创建和配置可用性组的其他先决条件的详细信息,请参阅针对 AlwaysOn 可用性组的先决条件、限制和建议 (SQL Server)For information about additional prerequisites for creating and configuring availability groups, see Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server).

权限Permissions

SQL ServerSQL Server实例上启用 AlwaysOn 可用性组时,服务器实例具有对 WSFC 群集的完全控制权限。While Always On Availability Groups is enabled on an instance of SQL ServerSQL Server, the server instance has full control on the WSFC cluster.

要求本地计算机上 Administrator 组中的成员身份以及对 WSFC 群集的完全控制。Requires membership in the Administrator group on the local computer and full control on the WSFC cluster. 使用 PowerShell 启用 AlwaysOn 时,使用“以管理员身份运行”选项打开命令提示符窗口。When enabling Always On by using PowerShell, open the Command Prompt window using the Run as administrator option.

要求 Active Directory 创建对象和管理对象权限。Requires Active Directory Create Objects and Manage Objects permissions.

确定是否已启用 AlwaysOn 可用性组Determine Whether Always On Availability Groups is Enabled

使用 SQL Server Management StudioUsing SQL Server Management Studio

确定是否已启用 AlwaysOn 可用性组To determine whether Always On Availability Groups is enabled

  1. 在“对象资源管理器”中,右键单击服务器实例,再单击“属性”。In Object Explorer, right-click the server instance, and click Properties.

  2. “服务器属性” 对话框中,单击 “常规” 页。In the Server Properties dialog box, click the General page. “启用 HADR” 属性显示以下值之一:The Is HADR Enabled property displays one of the following values:

    • True(如果启用了 AlwaysOn 可用性组)True, if Always On Availability Groups is enabled

    • False(如果禁用了 AlwaysOn 可用性组)。False, if Always On Availability Groups is disabled.

使用 Transact-SQLUsing Transact-SQL

确定是否已启用 AlwaysOn 可用性组To determine whether Always On Availability Groups is enabled

  1. 使用以下 SERVERPROPERTY 语句:Use the following SERVERPROPERTY statement:

    SELECT SERVERPROPERTY ('IsHadrEnabled');  
    

    IsHadrEnabled 服务器属性的设置指示是否为 AlwaysOn 可用性组启用 SQL ServerSQL Server 的实例,如下所示:The setting of the IsHadrEnabled server property indicates whether an instance of SQL ServerSQL Server is enabled for Always On Availability Groups, as follows:

    • 如果 IsHadrEnabled = 1,将启用 AlwaysOn 可用性组。If IsHadrEnabled = 1, Always On Availability Groups is enabled.

    • 如果 IsHadrEnabled = 0,将禁用 AlwaysOn 可用性组。If IsHadrEnabled = 0, Always On Availability Groups is disabled.

    备注

    有关 IsHadrEnabled 服务器属性的详细信息,请参阅 SERVERPROPERTY (Transact-SQL)的 SQL Server 版本。For more information about the IsHadrEnabled server property, see SERVERPROPERTY (Transact-SQL).

使用 PowerShellUsing PowerShell

确定是否已启用 AlwaysOn 可用性组To determine whether Always On Availability Groups is enabled

  1. 将默认值 (cd) 设置为要确定是否启用 AlwaysOn 可用性组Always On availability groups 的服务器实例。Set default (cd) to the server instance on which you want to determine whether AlwaysOn 可用性组Always On availability groups is enabled.

  2. 请输入以下 PowerShell Get-Item 命令:Enter the following PowerShell Get-Item command:

    PS SQLSERVER:\SQL\NODE1\DEFAULT> get-item . | select IsHadrEnabled  
    

    备注

    若要查看 cmdlet 的语法,请在 PowerShell 环境中使用 Get-Help SQL ServerSQL Server cmdlet。To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL ServerSQL Server PowerShell environment. 有关详细信息,请参阅 Get Help SQL Server PowerShellFor more information, see Get Help SQL Server PowerShell.

设置和使用 SQL Server PowerShell 提供程序To set up and use the SQL Server PowerShell provider

启用 AlwaysOn 可用性组Enable Always On Availability Groups

启用 AlwaysOn,请使用:To enable Always On, using:

使用 SQL Server 配置管理器Using SQL Server Configuration Manager

启用 AlwaysOn 可用性组To enable Always On Availability Groups

  1. 连接到承载要启用 AlwaysOn 可用性组的 SQL ServerSQL Server 实例的 Windows Server 故障转移群集 (WSFC) 节点。Connect to the Windows Server Failover Cluster (WSFC) node that hosts the SQL ServerSQL Server instance where you want to enable Always On Availability Groups.

  2. 在“开始” 菜单上,依次指向“所有程序” 、 Microsoft SQL Server 2017Microsoft SQL Server 2017、“配置工具” ,然后单击“SQL Server 配置管理器” 。On the Start menu, point to All Programs, point to Microsoft SQL Server 2017Microsoft SQL Server 2017, point to Configuration Tools, and click SQL Server Configuration Manager.

  3. SQL Server 配置管理器中,单击“SQL Server 服务”,右键单击 “SQL Server (<instance name>)”,其中 <instance name> 是要启用 AlwaysOn 可用性组的本地服务器实例的名称,然后单击“属性”In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server (<instance name>), where <instance name> is the name of a local server instance for which you want to enable Always On Availability Groups, and click Properties.

  4. 选择“AlwaysOn 高可用性”选项卡。Select the Always On High Availability tab.

  5. 验证 Windows 故障转移群集名称字段包含本地故障转移群集的名称。Verify that Windows failover cluster name field contains the name of the local failover cluster. 如果此字段为空,则此服务器实例当前不支持 AlwaysOn 可用性组Always On availability groupsIf this field is blank, this server instance currently does not support AlwaysOn 可用性组Always On availability groups. 原因包括本地计算机不是群集节点、WSFC 群集已关闭或此版本的 SQL Server 2017SQL Server 2017 不支持 AlwaysOn 可用性组Always On availability groupsEither the local computer is not a cluster node, the WSFC cluster has been shut down, or this edition of SQL Server 2017SQL Server 2017 that does not support AlwaysOn 可用性组Always On availability groups.

  6. 选中“启用 AlwaysOn 可用性组”复选框,然后单击“确定”。Select the Enable Always On Availability Groups check box, and click OK.

    SQL ServerSQL Server 配置管理器保存您的更改。Configuration Manager saves your change. 然后,必须手动重新启动 SQL ServerSQL Server 服务。Then, you must manually restart the SQL ServerSQL Server service. 这使您可以选择最适合您的业务要求的重新启动时间。This enables you to choose a restart time that is best for your business requirements. SQL ServerSQL Server 服务重启后,AlwaysOn 将启用,而且 IsHadrEnabled 服务器属性将设置为 1。When the SQL ServerSQL Server service restarts, Always On will be enabled, and the IsHadrEnabled server property will be set to 1.

使用 SQL Server PowerShellUsing SQL Server PowerShell

启用 AlwaysOnTo enable Always On

  1. 将目录 (cd) 更改为你要为 AlwaysOn 可用性组启用的服务器实例。Change directory (cd) to a server instance that you want to enable for Always On Availability Groups.

  2. 使用 Enable-SqlAlwaysOn cmdlet 启用 AlwaysOn 可用性组。Use the Enable-SqlAlwaysOn cmdlet to enable Always On Availability Groups.

    若要查看 cmdlet 的语法,请在 PowerShell 环境中使用 Get-Help SQL ServerSQL Server cmdlet。To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL ServerSQL Server PowerShell environment. 有关详细信息,请参阅 Get Help SQL Server PowerShellFor more information, see Get Help SQL Server PowerShell.

    备注

    有关如何控制 Enable-SqlAlwaysOn cmdlet 是否重新启动 SQL ServerSQL Server 服务的信息,请参阅本主题后面的 Cmdlet 何时重新启动 SQL Server 服务?For information about how to control whether the Enable-SqlAlwaysOn cmdlet restarts the SQL ServerSQL Server service, see When Does a Cmdlet Restart the SQL Server Service?, later in this topic.

设置和使用 SQL Server PowerShell 提供程序To set up and use the SQL Server PowerShell provider

示例:Enable-SqlAlwaysOnExample: Enable-SqlAlwaysOn

以下 PowerShell 命令在 SQL Server 实例上启用 AlwaysOn 可用性组Always On availability groups计算机\实例)。The following PowerShell command enables AlwaysOn 可用性组Always On availability groups on an instance of SQL Server (Computer\Instance).

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\Computer\Instance  

禁用 AlwaysOn 可用性组Disable Always On Availability Groups

重要

一次只能在一个服务器实例上禁用 AlwaysOn。Disable Always On on only one server instance at a time. 在禁用 AlwaysOn 可用性组之后,一直等待直到 SQL ServerSQL Server 服务已重启,然后才继续在另一个服务器实例上操作。After disabling Always On Availability Groups, wait until the SQL ServerSQL Server service has restarted before you proceed to another server instance.

建议Recommendations

在服务器实例上禁用 AlwaysOn 之前,我们建议你执行以下操作:Before you disable Always On on a server instance, we recommend that you do the following:

  1. 如果该服务器实例当前正在承载您要保留的可用性组的主副本,我们建议您尽可能手动将该可用性组故障转移到一个同步的辅助副本。If the server instance is currently hosting the primary replica of an availability group that you want to keep, we recommend that you manually fail over the availability group to a synchronized secondary replica, if possible. 有关详细信息,请参阅执行可用性组的计划手动故障转移 (SQL Server)For more information, see Perform a Planned Manual Failover of an Availability Group (SQL Server).

  2. 删除所有本地辅助副本。Remove all local secondary replicas. 有关详细信息,请参阅从可用性组中删除次要副本 (SQL Server)For more information, see Remove a Secondary Replica from an Availability Group (SQL Server).

使用 SQL Server 配置管理器Using SQL Server Configuration Manager

禁用 AlwaysOnTo disable Always On

  1. 连接到承载要禁用 AlwaysOn 可用性组的 SQL ServerSQL Server 实例的 Windows Server 故障转移群集 (WSFC) 节点。Connect to the Windows Server Failover Cluster (WSFC) node that hosts the SQL ServerSQL Server instance where you want to disable Always On Availability Groups.

  2. “开始” 菜单中,依次指向 “所有程序”Microsoft SQL Server 2017Microsoft SQL Server 2017“配置工具”,然后单击 “SQL Server 配置管理器”On the Start menu, point to All Programs, point to Microsoft SQL Server 2017Microsoft SQL Server 2017, point to Configuration Tools, and click SQL Server Configuration Manager.

  3. 在“SQL Server 配置管理器”中,单击“SQL Server 服务”,右键单击 “SQL Server (<instance name>)”,其中 <instance name> 是要禁用 AlwaysOn 可用性组的本地服务器实例的名称,然后单击“属性”。In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server (<instance name>), where <instance name> is the name of a local server instance for which you want to disable Always On Availability Groups, and click Properties.

  4. 在“Always On 高可用性”选项卡上,取消选中“启用 Always On 可用性组”复选框,然后单击“确定”。On the Always On High Availability tab, deselect the Enable Always On Availability Groups check box, and click OK.

    SQL ServerSQL Server 配置管理器保存您的更改并重新启动 SQL ServerSQL Server 服务。Configuration Manager saves your change and restarts the SQL ServerSQL Server service. SQL ServerSQL Server 服务重启时,将禁用 AlwaysOn 且 IsHadrEnabled 服务器属性将设置为 0,以指示已禁用 AlwaysOn 可用性组。When the SQL ServerSQL Server service restarts, Always On will be disabled, and the IsHadrEnabled server property will be set to 0, to indicate that Always On Availability Groups is disabled.

  5. 建议阅读本主题后面的跟进:在禁用 AlwaysOn 之后部分中的信息。We recommend that you read the information in Follow Up: After Disabling Always On, later in this topic.

使用 SQL Server PowerShellUsing SQL Server PowerShell

禁用 AlwaysOnTo disable Always On

  1. 将目录 (cd) 更改为你要为 AlwaysOn 可用性组禁用的当前启用的服务器实例。Change directory (cd) to a currently-enabled server instance that you want to disenable for Always On Availability Groups.

  2. 使用 Disable-SqlAlwaysOn cmdlet 来启用 AlwaysOn 可用性组。Use the Disable-SqlAlwaysOn cmdlet to enable Always On Availability Groups.

    例如,以下命令在 SQL Server 的实例上禁用 AlwaysOn 可用性组 (Computer\Instance)。For example, the following command disables Always On Availability Groups on an instance of SQL Server (Computer\Instance). 此命令需要重新启动实例,并将提示您确认此重新启动。This command requires restarting the instance, and you will be prompted to confirm this restart.

    Disable-SqlAlwaysOn -Path SQLSERVER:\SQL\Computer\Instance  
    

    重要

    有关如何控制 Disable-SqlAlwaysOn cmdlet 是否重新启动 SQL ServerSQL Server 服务的信息,请参阅本主题后面的 Cmdlet 何时重新启动 SQL Server 服务?For information about how to control whether the Disable-SqlAlwaysOn cmdlet restarts the SQL ServerSQL Server service, see When Does a Cmdlet Restart the SQL Server Service?, later in this topic.

    若要查看 cmdlet 的语法,请在 PowerShell 环境中使用 Get-Help SQL ServerSQL Server cmdlet。To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL ServerSQL Server PowerShell environment. 有关详细信息,请参阅 Get Help SQL Server PowerShellFor more information, see Get Help SQL Server PowerShell.

设置和使用 SQL Server PowerShell 提供程序To set up and use the SQL Server PowerShell provider

跟进:在禁用 AlwaysOn 之后Follow Up: After Disabling Always On

禁用 AlwaysOn 可用性组后,必须重启 SQL ServerSQL Server 实例。After you disable Always On Availability Groups, the instance of SQL ServerSQL Server must be restarted. SQL 配置管理器将自动重新启动该服务器实例。SQL Configuration Manager restarts the server instance automatically. 但是,如果使用了 Disable-SqlAlwaysOn cmdlet,则需要手动重新启动该服务器实例 。However, if you used the Disable-SqlAlwaysOn cmdlet, you will need to restart the server instance manually. 有关详细信息,请参阅 sqlservr ApplicationFor more information, see sqlservr Application.

在重新启动的服务器实例上:On the restarted server instance:

  • 可用性数据库在 SQL Server 启动时不启动,因此无法访问它们。Availability databases do not start up at SQL Server startup, making them inaccessible.

  • 唯一支持的 AlwaysOn Transact-SQLTransact-SQL 语句是 DROP AVAILABILITY GROUPThe only supported Always On Transact-SQLTransact-SQL statement is DROP AVAILABILITY GROUP. 不支持 CREATE AVAILABILITY GROUP、ALTER AVAILABILITY GROUP 和 ALTER DATABASE 的 SET HADR 选项。CREATE AVAILABILITY GROUP, ALTER AVAILABILITY GROUP, and the SET HADR options of ALTER DATABASE are not supported.

  • SQL ServerSQL Server 元数据和 WSFC 中的 AlwaysOn 可用性组Always On availability groups 配置数据不受禁用 AlwaysOn 可用性组的影响。metadata and AlwaysOn 可用性组Always On availability groups configuration data in WSFC are unaffected by disabling Always On Availability Groups.

如果你在为承载一个或多个可用性组的可用性副本的每个服务器实例上都永久禁用 AlwaysOn 可用性组,则我们建议你完成以下步骤:If you permanently disable Always On Availability Groups on every server instance that hosts an availability replica for one or more availability groups, we recommend that you complete the following steps:

  1. 如果你在禁用 AlwaysOn 前未删除本地可用性副本,则删除服务器实例正为其承载可用性副本的每个可用性组。If you did not remove the local availability replicas before disabling Always On, delete (drop) each availability group for which the server instance is hosting an availability replica. 有关删除可用性组的信息,请参阅删除可用性组 (SQL Server)For information about deleting an availability group, see Remove an Availability Group (SQL Server).

  2. 若要删除留在原地的元数据,请删除服务器实例上作为原始 WSFC 一部分的受影响的可用性组。To remove the metadata left behind, delete (drop) each affected availability group on a server instance that is part of the original WSFC.

  3. 所有连接仍可以访问任何主数据库,但是主数据库和辅助数据库之间的数据同步将停止。Any primary databases continue to be accessible to all connections but the data synchronization between the primary and secondary databases stops.

  4. 辅助数据库将进入 RESTORING 状态。The secondary databases enter the RESTORING state. 您可以删除这些数据库,或者可通过使用 RESTORE WITH RECOVERY 还原它们。You can delete them, or you can restore them by using RESTORE WITH RECOVERY. 但是,还原的数据库不再参与可用性组数据同步。However, restored databases are no longer participating in availability-group data synchronization.

Cmdlet 何时重新启动 SQL Server 服务?When Does a Cmdlet Restart the SQL Server Service?

在当前正在运行的服务器实例上,使用 Enable-SqlAlwaysOn 或 Disable-SqlAlwaysOn 更改当前 AlwaysOn 设置可能导致 SQL Server 服务重新启动。On a server instance that is currently running, using Enable-SqlAlwaysOn or Disable-SqlAlwaysOn to change the current Always On setting could cause the SQL Server service to restart. 重新启动行为取决于以下条件:The restart behavior on depends on the following conditions:

指定了 -NoServiceRestart 参数-NoServiceRestart parameter specified 指定了 -Force 参数-Force parameter specified 重新启动 SQL ServerSQL Server 服务?Is the SQL ServerSQL Server service restarted?
No No 默认情况。By default. 但是 cmdlet 提示您以下信息:But the cmdlet prompts you as follows:

若要完成此操作,必须重新启动服务器实例“<instance_name>”的 SQL Server 服务。是否继续?To complete this action, we must restart the SQL Server service for server instance '<instance_name>'. Do you want to continue?

[Y] 是 [N] 否 [S] 挂起 [?] 帮助(默认值为“Y”):[Y] Yes [N] No [S] Suspend [?] Help (default is "Y"):

如果指定 NS,则不重新启动该服务。If you specify N or S, the service is not restarted.
No Yes 重新启动服务。Service is restarted.
Yes No 不重新启动服务。Service is not restarted.
Yes Yes 不重新启动服务。Service is not restarted.

另请参阅See Also

AlwaysOn 可用性组概述 (SQL Server) Overview of Always On Availability Groups (SQL Server)
SERVERPROPERTY (Transact-SQL)SERVERPROPERTY (Transact-SQL)