执行 Always On 可用性组的计划手动故障转移 (SQL Server)Perform a planned manual failover of an Always On availability group (SQL Server)

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

本主题说明如何在 SQL Server 2017SQL Server 2017 中使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL 或 PowerShell 对 AlwaysOn 可用性组执行手动故障转移而不丢失数据(计划的手动故障转移 )。This topic describes how to perform a manual failover without data loss (a planned manual failover) on an AlwaysOn availability group by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell in SQL Server 2017SQL Server 2017. 可用性组在可用性副本级别进行故障转移。An availability group fails over at the level of an availability replica. 计划的手动故障转移类似于所有 AlwaysOn 可用性组的故障转移,将次要副本转换为主要角色。A planned manual failover, like any AlwaysOn availability group failover, transitions a secondary replica to primary role. 故障转移同时会将先前的主要副本转换为次要角色。Concurrently, the failover transitions the former primary replica to the secondary role.

仅当主要副本和目标次要副本在同步提交模式下运行且当前同步时,才支持计划的手动故障转移。A planned manual failover is supported only when the primary replica and the target secondary replica are running in synchronous-commit mode and are currently synchronized. 计划的手动故障转移暂留辅助数据库中的所有数据,这些数据库加入目标次要副本上的可用性组。A planned manual failover preserves all the data in the secondary databases that are joined to the availability group on the target secondary replica. 将以前的主要副本转换为次要角色后,其数据库将成为辅助数据库。After the former primary replica transitions to the secondary role, its databases become secondary databases. 然后它们开始与新的主数据库同步。Then they begin to synchronize with the new primary databases. 在将其全部转换为 SYNCHRONIZED 状态之后,新的辅助副本将变成适于充当将来计划的手动故障转移的目标。After they all transition into the SYNCHRONIZED state, the new secondary replica becomes eligible to serve as the target of a future planned manual failover.

备注

如果次要副本和主要副本都配置为自动故障转移模式,那么同步次要副本后,该副本还可以用作自动故障转移的目标。If the secondary and primary replicas are both configured for automatic failover mode, after the secondary replica is synchronized, it also can serve as the target for an automatic failover. 有关详细信息,请参阅可用性模式(AlwaysOn 可用性组)For more information, see Availability modes (AlwaysOn availability groups).

开始之前Before you begin

重要

在没有群集管理器的情况下,需要通过一些特定过程对读取缩放可用性组进行故障转移。There are specific procedures to fail over a read-scale availability group with no cluster manager. 当可用性组具有 CLUSTER_TYPE = NONE 时,请按照故障转移读取缩放可用性组上的主要副本中的过程进行操作。When an availability group has CLUSTER_TYPE = NONE, follow the procedures under Fail over the primary replica on a read-scale availability group.

限制和局限Limitations and restrictions

先决条件和限制Prerequisites and restrictions

  • 目标次要副本和主要副本必须同时在同步提交可用性模式下运行。Both the target secondary replica and the primary replica must be running in synchronous-commit availability mode.

  • 目标次要副本当前必须与主要副本同步。Currently, the target secondary replica must be synchronized with the primary replica. 此次要副本上的所有辅助数据库都必须已联接到可用性组。All the secondary databases on this secondary replica must be joined to the availability group. 它们还必须与自己对应的主数据库同步(即本地辅助数据库必须为 SYNCHRONIZED)。They also must be synchronized with their corresponding primary databases (that is, the local secondary databases must be SYNCHRONIZED).

    提示

    要确定次要副本的故障转移就绪状态,请查询 sys.dm_hadr_database_cluster_states 动态管理视图中的 is_failover_ready 列。To determine the failover readiness of a secondary replica, query the is_failover_ready column in the sys.dm_hadr_database_cluster_states dynamic management view. 或查看 AlwaysOn 组仪表板的“故障转移就绪” 列。Or you can look at the Failover Readiness column of the AlwaysOn group dashboard.

  • 只有目标辅助副本支持该任务。This task is supported only on the target secondary replica. 您必须连接到承载目标辅助副本的服务器实例。You must be connected to the server instance that hosts the target secondary replica.

SecuritySecurity

权限Permissions

需要具有针对可用性组的 ALTER AVAILABILITY GROUP 权限。The ALTER AVAILABILITY GROUP permission is required on the availability group. 还要求具备 CONTROL AVAILABILITY GROUP 权限、ALTER ANY AVAILABILITY GROUP 权限或 CONTROL SERVER 权限。The CONTROL AVAILABILITY GROUP permission, the ALTER ANY AVAILABILITY GROUP permission, or the CONTROL SERVER permission also is required.

使用 SQL Server Management StudioUse SQL Server Management Studio

对可用性组执行手动故障转移:To manually fail over an availability group:

  1. 在对象资源管理器中,连接到托管需要进行故障转移的可用性组的一个次要副本的服务器实例。In Object Explorer, connect to a server instance that hosts a secondary replica of the availability group that needs to be failed over. 展开服务器树。Expand the server tree.

  2. 依次展开 “AlwaysOn 高可用性” 节点和 “可用性组” 节点。Expand the AlwaysOn High Availability node and the Availability Groups node.

  3. 右键单击要进行故障转移的可用性组,然后选择“故障转移” 。Right-click the availability group to be failed over, and select Failover.

  4. “故障转移可用性组向导”随即启动。The Failover Availability Group wizard starts. 有关详细信息,请参阅使用故障转移可用性组向导 (SQL Server Management Studio)For more information, see Use the Failover Availability Group wizard (SQL Server Management Studio).

使用 Transact-SQLUse Transact-SQL

对可用性组执行手动故障转移:To manually fail over an availability group:

  1. 连接到承载目标辅助副本的服务器实例。Connect to the server instance that hosts the target secondary replica.

  2. 按如下所示使用 ALTER AVAILABILITY GROUP 语句:Use the ALTER AVAILABILITY GROUP statement, as follows:

    ALTER AVAILABILITY GROUP group_name FAILOVERALTER AVAILABILITY GROUP group_name FAILOVER

    在该语句中,group_name 是可用性组的名称。In the statement, group_name is the name of the availability group.

    以下示例将 MyAg 可用性组手动故障转移到连接的次要副本:The following example manually fails over the MyAg availability group to the connected secondary replica:

    ALTER AVAILABILITY GROUP MyAg FAILOVER;  
    

使用 PowerShellUse PowerShell

对可用性组执行手动故障转移:To manually fail over an availability group:

  1. 将目录 (cd ) 更改为托管目标次要副本的服务器实例。Change the directory (cd) to the server instance that hosts the target secondary replica.

  2. 使用 Switch-SqlAvailabilityGroup cmdlet。Use the Switch-SqlAvailabilityGroup cmdlet.

    备注

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

    下面的示例将 MyAg 可用性组手动故障转移到具有指定路径的次要副本:The following example manually fails over the MyAg availability group to the secondary replica with the specified path:

    Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\SecondaryServer\InstanceName\AvailabilityGroups\MyAg  
    

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

跟进:在对可用性组进行手动故障转移后Follow up: After you manually fail over an availability group

如果故障转移到可用性组的 自动故障转移集automatic failover set 之外,请调整 Windows Server 故障转移群集节点的仲裁投票以反映新的可用性组配置。If you failed over outside the 自动故障转移集automatic failover set of the availability group, adjust the quorum votes of the Windows Server failover clustering nodes to reflect your new availability group configuration. 有关详细信息,请参阅 Windows Server 故障转移群集 (WSFC) 与 SQL ServerFor more information, see Windows Server failover clustering (WSFC) with SQL Server.

故障转移读取缩放可用性组上的主要副本Fail over the primary replica on a read-scale availability group

每个可用性组仅有一个主要副本。Each availability group has only one primary replica. 主要副本允许读取和写入操作。The primary replica allows reads and writes. 若要更改哪个副本为主要副本,可进行故障转移。To change which replica is primary, you can fail over. 在高可用性的可用性组中,群集管理器自动执行故障转移过程。In an availability group for high availability, the cluster manager automates the failover process. 在群集类型为 NONE 的可用性组中,需手动执行故障转移过程。In an availability group with cluster type NONE, the failover process is manual.

在群集类型为 NONE 的可用性组中,有两种对主要副本进行故障转移的方法:There are two ways to fail over the primary replica in an availability group with cluster type NONE:

  • 强制手动故障转移(会丢失数据)Forced manual failover with data loss
  • 手动故障转移(无数据丢失)Manual failover without data loss

强制手动故障转移(会丢失数据)Forced manual failover with data loss

当主要副本不可用且无法恢复时,请使用此方法。Use this method when the primary replica isn't available and can't be recovered.

若要强制执行会丢失数据的故障转移,请连接托管目标次要副本的 SQL Server 实例,然后运行以下命令:To force failover with data loss, connect to the SQL Server instance that hosts the target secondary replica and then run the following command:

ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS;

当以前的主要副本恢复时,它还将扮演主角色。When the previous primary replica recovers, it will also assume the primary role. 若要确保以前的主要副本转换为辅助角色,请在之前的主要副本上运行以下命令。To ensure that the previous primary replica transitions into a secondary role run the following command on the previous primary replica.

ALTER AVAILABILITY GROUP [ag1]  SET (ROLE = SECONDARY);

手动故障转移(无数据丢失)Manual failover without data loss

主要副本可用时使用此方法,但需要暂时或永久更改配置,并更改托管主要副本的 SQL Server 实例。Use this method when the primary replica is available, but you need to temporarily or permanently change the configuration and change the SQL Server instance that hosts the primary replica. 若要避免潜在的数据丢失,发出手动故障转移前,确保目标次要副本为最新版本。To avoid potential data loss, before you issue the manual failover, ensure that the target secondary replica is up to date.

手动故障转移(无数据丢失):To manually fail over without data loss:

  1. 使目标次要副本 SYNCHRONOUS_COMMITMake the target secondary replica SYNCHRONOUS_COMMIT.

    ALTER AVAILABILITY GROUP [ag1] 
         MODIFY REPLICA ON N'<node2>' 
         WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
    
  2. 若要确定已将活动事务提交到主要副本和至少一个同步次要副本,请运行以下查询:To identify that active transactions are committed to the primary replica and at least one synchronous secondary replica, run the following query:

    SELECT ag.name, 
       drs.database_id, 
       drs.group_id, 
       drs.replica_id, 
       drs.synchronization_state_desc, 
       ag.sequence_number
    FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
    WHERE drs.group_id = ag.group_id; 
    

    synchronization_state_descSYNCHRONIZED 时,会同步次要副本。The secondary replica is synchronized when synchronization_state_desc is SYNCHRONIZED.

  3. REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT 更新为 1。Update REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1.

    以下脚本在名为 ag1 的可用性组上将 REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT 设置为 1。The following script sets REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1 on an availability group named ag1. 运行以下脚本前,将 ag1 替换为可用性组的名称:Before you run the following script, replace ag1 with the name of your availability group:

    ALTER AVAILABILITY GROUP [ag1] 
         SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
    

    此设置可确保将每个活动事务提交到主要副本和至少一个同步次要副本。This setting ensures that every active transaction is committed to the primary replica and at least one synchronous secondary replica.

  4. 将主要副本降级为次要副本。Demote the primary replica to a secondary replica. 将主要副本降级后,该副本为只读。After the primary replica is demoted, it's read-only. 若要将角色更新为 SECONDARY,在托管主要副本的 SQL Server 实例上运行以下命令:To update the role to SECONDARY, run the following command on the SQL Server instance that hosts the primary replica:

    ALTER AVAILABILITY GROUP [ag1] 
         SET (ROLE = SECONDARY); 
    
  5. 将目标次要副本升级为主要副本。Promote the target secondary replica to primary.

    ALTER AVAILABILITY GROUP ag1 FORCE_FAILOVER_ALLOW_DATA_LOSS; 
    

    备注

    若要删除可用性组,请使用删除可用性组To delete an availability group, use DROP AVAILABILITY GROUP. 对于使用群集类型为 NONE 或 EXTERNAL 创建的可用性组,请对可用性组的所有副本执行该命令。For an availability group that's created with cluster type NONE or EXTERNAL, execute the command on all replicas that are part of the availability group.

另请参阅See also