ALTER AVAILABILITY GROUP (Transact-SQL)ALTER AVAILABILITY GROUP (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

更改 SQL ServerSQL Server 中现有的 Always On 可用性组。Alters an existing Always On availability group in SQL ServerSQL Server. 只有当前主副本支持大多数 ALTER AVAILABILITY GROUP 参数。Most ALTER AVAILABILITY GROUP arguments are supported only the current primary replica. 但是,只有辅助副本支持 JOIN、FAILOVER 和 FORCE_FAILOVER_ALLOW_DATA_LOSS 参数。However the JOIN, FAILOVER, and FORCE_FAILOVER_ALLOW_DATA_LOSS arguments are supported only on secondary replicas.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
ALTER AVAILABILITY GROUP group_name   
  {  
     SET ( <set_option_spec> )   
   | ADD DATABASE database_name   
   | REMOVE DATABASE database_name  
   | ADD REPLICA ON <add_replica_spec>   
   | MODIFY REPLICA ON <modify_replica_spec>  
   | REMOVE REPLICA ON <server_instance>  
   | JOIN  
   | JOIN AVAILABILITY GROUP ON <add_availability_group_spec> [ ,...2 ]  
   | MODIFY AVAILABILITY GROUP ON <modify_availability_group_spec> [ ,...2 ]  
   | GRANT CREATE ANY DATABASE  
   | DENY CREATE ANY DATABASE  
   | FAILOVER  
   | FORCE_FAILOVER_ALLOW_DATA_LOSS   
   | ADD LISTENER 'dns_name' ( <add_listener_option> )  
   | MODIFY LISTENER 'dns_name' ( <modify_listener_option> )  
   | RESTART LISTENER 'dns_name'  
   | REMOVE LISTENER 'dns_name'  
   | OFFLINE  
  }  
[ ; ]  
  
<set_option_spec> ::=   
    AUTOMATED_BACKUP_PREFERENCE = { PRIMARY | SECONDARY_ONLY| SECONDARY | NONE }  
  | FAILURE_CONDITION_LEVEL  = { 1 | 2 | 3 | 4 | 5 }   
  | HEALTH_CHECK_TIMEOUT = milliseconds  
  | DB_FAILOVER  = { ON | OFF }   
  | DTC_SUPPORT  = { PER_DB | NONE }  
  | REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = { integer }
  
<server_instance> ::=   
 { 'system_name[\instance_name]' | 'FCI_network_name[\instance_name]' }  
  
<add_replica_spec>::=  
  <server_instance> WITH  
    (  
       ENDPOINT_URL = 'TCP://system-address:port',  
       AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT | CONFIGURATION_ONLY },  
       FAILOVER_MODE = { AUTOMATIC | MANUAL }   
       [ , <add_replica_option> [ ,...n ] ]  
    )   
  
  <add_replica_option>::=  
       SEEDING_MODE = { AUTOMATIC | MANUAL }  
     | BACKUP_PRIORITY = n  
     | SECONDARY_ROLE ( {   
            [ ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } ]   
        [,] [ READ_ONLY_ROUTING_URL = 'TCP://system-address:port' ]  
     } )  
     | PRIMARY_ROLE ( {   
            [ ALLOW_CONNECTIONS = { READ_WRITE | ALL } ]   
        [,] [ READ_ONLY_ROUTING_LIST = { ( '<server_instance>' [ ,...n ] ) | NONE } ]  
        [,] [ READ_WRITE_ROUTING_URL = { ( '<server_instance>' ) ] 
     } )  
     | SESSION_TIMEOUT = integer
  
<modify_replica_spec>::=  
  <server_instance> WITH  
    (    
       ENDPOINT_URL = 'TCP://system-address:port'   
     | AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }   
     | FAILOVER_MODE = { AUTOMATIC | MANUAL }   
     | SEEDING_MODE = { AUTOMATIC | MANUAL }   
     | BACKUP_PRIORITY = n  
     | SECONDARY_ROLE ( {   
          ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }   
        | READ_ONLY_ROUTING_URL = 'TCP://system-address:port'   
          } )  
     | PRIMARY_ROLE ( {   
          ALLOW_CONNECTIONS = { READ_WRITE | ALL }   
        | READ_ONLY_ROUTING_LIST = { ( '<server_instance>' [ ,...n ] ) | NONE }   
          } )  
     | SESSION_TIMEOUT = seconds  
    )   
  
<add_availability_group_spec>::=  
 <ag_name> WITH  
    (  
       LISTENER_URL = 'TCP://system-address:port',  
       AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT },  
       FAILOVER_MODE = MANUAL,  
       SEEDING_MODE = { AUTOMATIC | MANUAL }  
    )  
  
<modify_availability_group_spec>::=  
 <ag_name> WITH  
    (  
       LISTENER = 'TCP://system-address:port'  
       | AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }  
       | SEEDING_MODE = { AUTOMATIC | MANUAL }  
    )  
  
<add_listener_option> ::=  
   {  
      WITH DHCP [ ON ( <network_subnet_option> ) ]  
    | WITH IP ( { ( <ip_address_option> ) } [ , ...n ] ) [ , PORT = listener_port ]  
   }  
  
  <network_subnet_option> ::=  
     'ipv4_address', 'ipv4_mask'    
  
  <ip_address_option> ::=  
     {   
        'four_part_ipv4_address', 'four_part_ipv4_mask'  
      | 'ipv6_address'  
     }  
  
<modify_listener_option>::=  
    {  
       ADD IP ( <ip_address_option> )   
     | PORT = listener_port  
    }  
  

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

group_namegroup_name
指定新可用性组的名称。Specifies the name of the new availability group. group_name 必须是一个有效的 SQL ServerSQL Server 标识符,并且它在 WSFC 群集的所有可用性组中必须是唯一的。group_name must be a valid SQL ServerSQL Server identifier, and it must be unique across all availability groups in the WSFC cluster.

AUTOMATED_BACKUP_PREFERENCE = { PRIMARY | SECONDARY_ONLY| SECONDARY | NONE }AUTOMATED_BACKUP_PREFERENCE = { PRIMARY | SECONDARY_ONLY| SECONDARY | NONE }
指定在选择执行备份的位置时有关备份作业应该如何评估主副本的首选项。Specifies a preference about how a backup job should evaluate the primary replica when choosing where to perform backups. 您可以编写给定备份作业的脚本,以便纳入自动备份首选项。You can script a given backup job to take the automated backup preference into account. SQL ServerSQL Server 不会强制执行首选项,因此它对即席备份没有影响,了解这一点很重要。It is important to understand that the preference is not enforced by SQL ServerSQL Server, so it has no impact on ad hoc backups.

仅在主要副本上受支持。Supported only on the primary replica.

这些值如下所示:The values are as follows:

PRIMARYPRIMARY
指定备份应该始终在主副本上发生。Specifies that the backups should always occur on the primary replica. 如果您需要在对辅助副本运行备份时不支持的备份功能,例如创建差异备份,此选项将很有用。This option is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica.

重要

如果你计划使用日志传送为可用性组准备任何辅助数据库,请将自动备份首选项设置为“主要”,直到准备好所有辅助数据库并将其联接到可用性组。If you plan to use log shipping to prepare any secondary databases for an availability group, set the automated backup preference to Primary until all the secondary databases have been prepared and joined to the availability group.

SECONDARY_ONLYSECONDARY_ONLY
指定备份应该永远不会在主副本上执行。Specifies that backups should never be performed on the primary replica. 如果主副本是唯一的联机副本,则备份应不会发生。If the primary replica is the only replica online, the backup should not occur.

SECONDARYSECONDARY
指定备份应在辅助副本上发生,但在主副本是唯一联机的副本时除外。Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. 在该情况下,备份应在主副本上发生。In that case, the backup should occur on the primary replica. 此选项为默认行为。This is the default behavior.

NONE
指定您希望在选择要执行备份的副本时备份作业将忽略可用性副本的角色。Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. 请注意,备份作业可能评估其他因素,例如每个可用性副本的备份优先级及其操作状态和已连接状态。Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.

重要

不会强制实施 AUTOMATED_BACKUP_PREFERENCE 设置。There is no enforcement of the AUTOMATED_BACKUP_PREFERENCE setting. 对此首选项的解释依赖于您为给定可用性组中的数据库撰写作业脚本的逻辑(如果有)。The interpretation of this preference depends on the logic, if any, that you script into back jobs for the databases in a given availability group. 自动备份首选项设置对即席备份没有影响。The automated backup preference setting has no impact on ad hoc backups. 有关详细信息,请参阅配置可用性副本备份 (SQL Server)For more information, see Configure Backup on Availability Replicas (SQL Server).

备注

若要查看现有可用性组的自动备份首选项,请选择 sys.availability_groups 目录视图的 automated_backup_preference 或 automated_backup_preference_desc 列 。To view the automated backup preference of an existing availability group, select the automated_backup_preference or automated_backup_preference_desc column of the sys.availability_groups catalog view. 此外,sys.fn_hadr_backup_is_preferred_replica (Transact-SQL) 可用于确定首选备份副本。Additionally, sys.fn_hadr_backup_is_preferred_replica (Transact-SQL) can be used to determine the preferred backup replica. 此函数始终对至少一个副本返回 1(即使 AUTOMATED_BACKUP_PREFERENCE = NONE)。This function will always return 1 for at least one of the replicas, even when AUTOMATED_BACKUP_PREFERENCE = NONE.

FAILURE_CONDITION_LEVEL = { 1 | 2 | 3 | 4 | 5 } FAILURE_CONDITION_LEVEL = { 1 | 2 | 3 | 4 | 5 }
指定将为此可用性组触发自动故障转移的失败条件。Specifies what failure conditions will trigger an automatic failover for this availability group. FAILURE_CONDITION_LEVEL 在组级别设置,但仅针对为同步-提交可用性模式 (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT) 配置的可用性副本。FAILURE_CONDITION_LEVEL is set at the group level but is relevant only on availability replicas that are configured for synchronous-commit availability mode (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT). 此外,只有在主要副本和次要副本均配置为自动故障转移模式 (FAILOVER_MODE = AUTOMATIC) 并且次要副本当前与主要副本同步的情况下,失败条件才可以触发自动故障转移。Furthermore, failure conditions can trigger an automatic failover only if both the primary and secondary replicas are configured for automatic failover mode (FAILOVER_MODE = AUTOMATIC) and the secondary replica is currently synchronized with the primary replica.

仅在主要副本上受支持。Supported only on the primary replica.

失败条件级别的范围 (1-5) 是从最少限制的级别 1 到最多限制的级别 5。The failure-condition levels (1-5) range from the least restrictive, level 1, to the most restrictive, level 5. 给定的条件级别包含所有限制较少的级别。A given condition level encompasses all of the less restrictive levels. 因此,最严格的条件级别 5 包含四个限制较少的级别 (1-4),级别 4 包含级别 1-3,依此类推。Thus, the strictest condition level, 5, includes the four less restrictive condition levels (1-4), level 4 includes levels 1-3, and so forth. 下表介绍了与各级别相对应的失败条件。The following table describes the failure-condition that corresponds to each level.

级别Level 失败条件Failure Condition
11 指定在发生以下任何情况时应启动自动故障转移:Specifies that an automatic failover should be initiated when any of the following occurs:

SQL ServerSQL Server 服务停止。The SQL ServerSQL Server service is down.

因为没有从服务器实例接收到 ACK,连接到 WSFC 群集的可用性组的租期到期。The lease of the availability group for connecting to the WSFC cluster expires because no ACK is received from the server instance. 有关详细信息,请参阅工作原理:SQL Server Always On 租约超时For more information, see How It Works: SQL Server Always On Lease Timeout.
22 指定在发生以下任何情况时应启动自动故障转移:Specifies that an automatic failover should be initiated when any of the following occurs:

SQL ServerSQL Server 的实例未连接到群集,并且超出了可用性组的用户指定的 HEALTH_CHECK_TIMEOUT 阈值。The instance of SQL ServerSQL Server does not connect to cluster, and the user-specified HEALTH_CHECK_TIMEOUT threshold of the availability group is exceeded.

可用性副本处于失败状态。The availability replica is in failed state.
33 指定在发生了严重的 SQL ServerSQL Server 内部错误(例如孤立的自旋锁、严重的写访问冲突或过多的转储)时应启动自动故障转移。Specifies that an automatic failover should be initiated on critical SQL ServerSQL Server internal errors, such as orphaned spinlocks, serious write-access violations, or too much dumping.

此选项为默认行为。This is the default behavior.
44 指定在发生了中等程度的 SQL ServerSQL Server 内部错误(例如在 SQL ServerSQL Server 内部资源池中出现持久的内存不足情况)时应启动自动故障转移。Specifies that an automatic failover should be initiated on moderate SQL ServerSQL Server internal errors, such as a persistent out-of-memory condition in the SQL ServerSQL Server internal resource pool.
55 指定在出现任何符合的失败条件时应启动自动故障转移,这些失败条件包括:Specifies that an automatic failover should be initiated on any qualified failure conditions, including:

SQL 引擎的工作线程耗尽。Exhaustion of SQL Engine worker-threads.

检测到无法解决的死锁。Detection of an unsolvable deadlock.

备注

缺少 SQL ServerSQL Server 的实例对客户端请求的响应与可用性组无关。Lack of response by an instance of SQL ServerSQL Server to client requests is not relevant to availability groups.

FAILURE_CONDITION_LEVEL 和 HEALTH_CHECK_TIMEOUT 值为给定组定义“灵活的故障转移策略”。The FAILURE_CONDITION_LEVEL and HEALTH_CHECK_TIMEOUT values, define a flexible failover policy for a given group. 此灵活的故障转移策略向您提供对必须导致自动故障转移的条件的精确控制。This flexible failover policy provides you with granular control over what conditions must cause an automatic failover. 有关详细信息,请参阅针对可用性组的自动故障转移的灵活的故障转移策略 (SQL Server)For more information, see Flexible Failover Policy for Automatic Failover of an Availability Group (SQL Server).

HEALTH_CHECK_TIMEOUT = millisecondsHEALTH_CHECK_TIMEOUT = milliseconds
指定在 WSFC 群集假定服务器实例速度较慢或无响应前,等待 sp_server_diagnostics 系统存储过程返回服务器运行状况信息的等待时间(毫秒)。Specifies the wait time (in milliseconds) for the sp_server_diagnostics system stored procedure to return server-health information before WSFC cluster assumes that the server instance is slow or not responding. HEALTH_CHECK_TIMEOUT 在组级别设置,但仅针对为具有自动故障转移的同步-提交可用性模式 (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT) 配置的可用性副本。HEALTH_CHECK_TIMEOUT is set at the group level but is relevant only on availability replicas that are configured for synchronous-commit availability mode with automatic failover (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT). 此外,只有在主要副本和次要副本均配置为自动故障转移模式 (FAILOVER_MODE = AUTOMATIC) 并且次要副本当前与主要副本同步的情况下,运行状况检查超时才可以触发自动故障转移。Furthermore, a health-check timeout can trigger an automatic failover only if both the primary and secondary replicas are configured for automatic failover mode (FAILOVER_MODE = AUTOMATIC) and the secondary replica is currently synchronized with the primary replica.

默认的 HEALTH_CHECK_TIMEOUT 值为 30000 毫秒(30 秒)。The default HEALTH_CHECK_TIMEOUT value is 30000 milliseconds (30 seconds). 最小值为 15000 毫秒(15 秒),最大值为 4294967295 毫秒。The minimum value is 15000 milliseconds (15 seconds), and the maximum value is 4294967295 milliseconds.

仅在主要副本上受支持。Supported only on the primary replica.

重要

sp_server_diagnostics 在数据库级别不执行运行状况检查。sp_server_diagnostics does not perform health checks at the database level.

DB_FAILOVER = { ON | OFF }DB_FAILOVER = { ON | OFF }
指定主要副本上的数据库脱机时要执行的响应。Specifies the response to take when a database on the primary replica is offline. 设置为 ON 时,可用性组中数据库 ONLINE 以外的任何状态都会触发自动故障转移。When set to ON, any status other than ONLINE for a database in the availability group triggers an automatic failover. 当此选项设置为 OFF 时,仅使用实例的运行状况来触发自动故障转移。When this option is set to OFF, only the health of the instance is used to trigger automatic failover.

有关此设置的详细信息,请参阅数据库级别运行状况检测选项For more information regarding this setting, see Database Level Health Detection Option

DTC_SUPPORT = { PER_DB | NONE }DTC_SUPPORT = { PER_DB | NONE }
指定是否已对此可用性组启用分布式事务。Specifies whether distributed transactions are enabled for this Availability Group. 分布式事务仅适用于 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及以上版本中的可用性组数据库,而跨数据库事务仅适用于 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 及以上版本。Distributed transactions are only supported for availability group databases beginning in SQL Server 2016 (13.x)SQL Server 2016 (13.x), and cross-database transactions are only supported beginning in SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2. PER_DB 将创建支持这些事务的可用性组,并且将自动把涉及可用性组中的数据库的跨数据库事务提升为分布式事务。PER_DB creates the availability group with support for these transactions and will automatically promote cross-database transactions involving database(s) in the Availability Group into distributed transactions. NONE 可阻止将跨数据库事务自动提升为分布式事务,并且不向 DTC 中的稳定 RMID 注册该数据库。NONE prevents the automatic promotion of cross-database transactions to distributed transactions and does not register the database with a stable RMID in DTC. 使用 NONE 设置时不会阻止分布式事务,但数据库故障转移和自动恢复在某些情况下可能失败。Distributed transactions are not prevented when the NONE setting is used, but database failover and automatic recovery may not succeed under some circumstances. 有关详细信息,请参阅用于 AlwaysOn 可用性组和数据库镜像的跨数据库事务和分布式事务 (SQL Server)For more information, see Cross-Database Transactions and Distributed Transactions for Always On Availability Groups and Database Mirroring (SQL Server).

备注

SQL Server 2016 (13.x)SQL Server 2016 (13.x) Service Pack 2 中引入了对更改可用性组的 DTC_SUPPORT 设置的支持。Support for changing the DTC_SUPPORT setting of an Availability Group was introduced in SQL Server 2016 (13.x)SQL Server 2016 (13.x) Service Pack 2. 此选项无法用于早期版本。This option cannot be used with earlier versions. 若要在 SQL ServerSQL Server 的早期版本中更改此设置,必须对可用性组再次执行 DROP 和 CREATE 操作。To change this setting in earlier versions of SQL ServerSQL Server, you must DROP and CREATE the availability group again.

REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMITREQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT
在 SQL Server 2017 中引入。Introduced in SQL Server 2017. 用于设置需要在主要副本提交事务之前提交的最小数量的同步次要副本。Used to set a minimum number of synchronous secondary replicas required to commit before the primary commits a transaction. 保证 SQL Server 事务等待至事务日志在最小数量的次要副本上更新为止。Guarantees that SQL Server transactions will wait until the transaction logs are updated on the minimum number of secondary replicas. 默认值为 0,可提供与 SQL Server 2016 相同的行为。The default is 0 which gives the same behavior as SQL Server 2016. 最小值为 0。The minimum value is 0. 最大值为副本数量减 1。The maximum value is the number of replicas minus 1. 此选项与同步提交模式中的副本相关。This option relates to replicas in synchronous commit mode. 当副本处于同步提交模式时,对主要副本的写入操作会等待至将次要同步副本上的写入提交到副本数据库事务日志为止。When replicas are in synchronous commit mode, writes on the primary replica wait until writes on the secondary synchronous replicas are committed to the replica database transaction log. 如果托管次要同步副本的 SQL Server 停止响应,则托管主要副本的 SQL Server 将此次要副本标记为 NOT SYNCHRONIZED(未同步)并继续执行操作。If a SQL Server that hosts a secondary synchronous replica stops responding, the SQL Server that hosts the primary replica will mark that secondary replica as NOT SYNCHRONIZED and proceed. 当无响应的数据库恢复联机状态时,它将处于“未同步”状态,副本将标记为不正常,直到主要副本可再次对其执行同步。When the unresponsive database comes back online it will be in a "not synced" state and the replica will be marked as unhealthy until the primary can make it synchronous again. 此设置可确保主要副本会等待至最少数量的副本已提交每个事务。This setting guarantees that the primary replica will not proceed until the minimum number of replicas have committed each transaction. 如果最少数量的副本不可用,则主要副本上的提交会失败。If the minimum number of replicas is not available then commits on the primary will fail. 对于群集类型 EXTERNAL,可用性组添加到群集资源时,设置会更改。For cluster type EXTERNAL the setting is changed when the availability group is added to a cluster resource. 请参阅可用性组配置的高可用性和数据保护See High availability and data protection for availability group configurations.

ADD DATABASE database_nameADD DATABASE database_name
指定要添加到可用性组的一个或多个用户数据库的列表。Specifies a list of one or more user databases that you want to add to the availability group. 这些数据库必须位于承载当前主副本的 SQL ServerSQL Server 实例上。These databases must reside on the instance of SQL ServerSQL Server that hosts the current primary replica. 您可以为一个可用性组指定多个数据库,但每个数据库只能属于一个可用性组。You can specify multiple databases for an availability group, but each database can belong to only one availability group. 有关可用性组可支持的数据库类型的详细信息,请参阅针对 Always On 可用性组的先决条件、限制和建议 (SQL Server)For information about the type of databases that an availability group can support, see Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server). 若要找出已属于某个可用性组的本地数据库,请参阅 sys.databases 目录视图中的 replica_id 列。To find out which local databases already belong to an availability group, see the replica_id column in the sys.databases catalog view.

仅在主要副本上受支持。Supported only on the primary replica.

备注

在创建可用性组后,将需要连接到承载辅助副本的每个服务器实例,然后准备每个辅助数据库并将它们加入可用性组。After you have created the availability group, you will need connect to each server instance that hosts a secondary replica and then prepare each secondary database and join it to the availability group. 有关详细信息,请参阅本主题后面的 启动 AlwaysOn 辅助数据库的数据移动 (SQL Server)For more information, see Start Data Movement on an Always On Secondary Database (SQL Server).

REMOVE DATABASE database_nameREMOVE DATABASE database_name
从可用性组中删除指定的主数据库和相应的辅助数据库。Removes the specified primary database and the corresponding secondary databases from the availability group. 仅在主要副本上受支持。Supported only on the primary replica.

有关在从可用性组中删除可用性数据库之后推荐执行的后续任务的信息,请参阅从可用性组删除主要数据库 (SQL Server)For information about the recommended follow up after removing an availability database from an availability group, see Remove a Primary Database from an Availability Group (SQL Server).

ADD REPLICA ONADD REPLICA ON
指定一到八个 SQL Server 实例以便在可用性组中承载辅助副本。Specifies from one to eight SQL server instances to host secondary replicas in an availability group. 通过在每个副本的服务器实例地址后追加 WITH (…) 子句来指定每个副本。Each replica is specified by its server instance address followed by a WITH (...) clause.

仅在主要副本上受支持。Supported only on the primary replica.

您需要将每个新的辅助副本联接到可用性组。You need to join every new secondary replica to the availability group. 有关详细信息,请参阅本节后面对 JOIN 选项的说明。For more information, see the description of the JOIN option, later in this section.

<server_instance>
指定承载副本的 SQL ServerSQL Server 实例的地址。Specifies the address of the instance of SQL ServerSQL Server that is the host for a replica. 地址格式依赖于该实例是默认实例还是命名实例以及它是独立实例还是故障转移群集实例 (FCI)。The address format depends on whether the instance is the default instance or a named instance and whether it is a standalone instance or a failover cluster instance (FCI). 语法如下所示:The syntax is as follows:

{ 'system_name[\instance_name]' | 'FCI_network_name[\instance_name]' }{ 'system_name[\instance_name]' | 'FCI_network_name[\instance_name]' }

此地址由以下部分组成:The components of this address are as follows:

system_namesystem_name
SQL ServerSQL Server 的目标实例所在的计算机系统的 NetBIOS 名称。Is the NetBIOS name of the computer system on which the target instance of SQL ServerSQL Server resides. 此计算机必须是一个 WSFC 节点。This computer must be a WSFC node.

FCI_network_nameFCI_network_name
用于访问 SQL ServerSQL Server 故障转移群集的网络名称。Is the network name that is used to access a SQL ServerSQL Server failover cluster. 如果服务器实例作为 SQL ServerSQL Server 故障转移伙伴参与,则使用此名称。Use this if the server instance participates as a SQL ServerSQL Server failover partner. 对 FCI 服务器实例执行 SELECT @@SERVERNAME 会返回其完整的 'FCI_network_name[\instance_name]' 字符串(即完整的副本名称) 。Executing SELECT @@SERVERNAME on an FCI server instance returns its entire 'FCI_network_name[\instance_name]' string (which is the full replica name).

instance_nameinstance_name
由 system_name 或 FCI_network_name 承载且已启用 Always On 的 SQL ServerSQL Server 实例的名称 。Is the name of an instance of a SQL ServerSQL Server that is hosted by system_name or FCI_network_name and that has Always On enabled. 对于默认服务器实例, instance_name 是可选的。For a default server instance, instance_name is optional. 此实例名不区分大小写。The instance name is case insensitive. 在独立服务器实例上,此值名称与执行 SELECT @@SERVERNAME 所返回的值相同。On a stand-alone server instance, this value name is the same as the value returned by executing SELECT @@SERVERNAME.

\
仅在指定 instance_name 时使用的分隔符,以便将其与 system_name 或 FCI_network_name 分隔 。Is a separator used only when specifying instance_name, in order to separate it from system_name or FCI_network_name.

有关 WSFC 节点和服务器实例的先决条件的信息,请参阅针对 Always On 可用性组的先决条件、限制和建议 (SQL Server)For information about the prerequisites for WSFC nodes and server instances, see Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server).

ENDPOINT_URL ='TCP://system-address:port'ENDPOINT_URL ='TCP://system-address:port'
指定 SQL ServerSQL Server 实例(该实例将承载要添加或修改的可用性副本)的数据库镜像终结点的 URL 路径。Specifies the URL path for the database mirroring endpoint on the instance of SQL ServerSQL Server that will host the availability replica that you are adding or modifying.

ENDPOINT_URL 在 ADD REPLICA ON 子句中是必需的,在 MODIFY REPLICA ON 子句中是可选的。ENDPOINT_URL is required in the ADD REPLICA ON clause and optional in the MODIFY REPLICA ON clause. 有关详细信息,请参阅 在添加或修改可用性副本时指定终结点 URL (SQL Server)配置服务器实例时遇到的典型问题。For more information, see Specify the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server).

' TCP :// system-address : port '' TCP ://system-address:port'
指定一个 URL,它用于指定端点 URL 或只读路由 URL。Specifies a URL for specifying an endpoint URL or read-only routing URL. URL 参数如下所示:The URL parameters are as follows:

system-addresssystem-address
一个字符串,例如系统名称、完全限定的域名或 IP 地址,它们明确标识了目标计算机系统。Is a string, such as a system name, a fully qualified domain name, or an IP address, that unambiguously identifies the destination computer system.

portport
是与服务器实例的镜像端点关联的端口号(对于 ENDPOINT_URL 选项)或服务器实例的数据库引擎Database Engine使用的端口号(对于 READ_ONLY_ROUTING_URL 选项)。Is a port number that is associated with the mirroring endpoint of the server instance (for the ENDPOINT_URL option) or the port number used by the 数据库引擎Database Engine of the server instance (for the READ_ONLY_ROUTING_URL option).

AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT | CONFIGURATION_ONLY }AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT | CONFIGURATION_ONLY }
指定在主副本可以在给定主数据库上提交事务前,是否必须等待辅助副本确认日志记录硬编码(写入)到磁盘。Specifies whether the primary replica has to wait for the secondary replica to acknowledge the hardening (writing) of the log records to disk before the primary replica can commit the transaction on a given primary database. 针对同一主副本上不同数据库的事务可以单独提交。The transactions on different databases on the same primary replica can commit independently.

SYNCHRONOUS_COMMITSYNCHRONOUS_COMMIT
指定主副本已在此辅助副本上进行硬编码(同步提交模式)前,将等待提交事务。Specifies that the primary replica will wait to commit transactions until they have been hardened on this secondary replica (synchronous-commit mode). 您可以为最多三个副本(包括主副本)指定 SYNCHRONOUS_COMMIT。You can specify SYNCHRONOUS_COMMIT for up to three replicas, including the primary replica.

ASYNCHRONOUS_COMMITASYNCHRONOUS_COMMIT
指定主副本无需等待此辅助副本对日志进行硬编码(同步提交可用性模式)即可提交事务。Specifies that the primary replica commits transactions without waiting for this secondary replica to harden the log (synchronous-commit availability mode). 您可以为最多五个可用性副本(包括主副本)指定 ASYNCHRONOUS_COMMIT。You can specify ASYNCHRONOUS_COMMIT for up to five availability replicas, including the primary replica.

CONFIGURATION_ONLY 指定主要副本将可用性组配置元数据同步提交到此副本的主数据库。CONFIGURATION_ONLY Specifies that the primary replica synchronously commit availability group configuration metadata to the master database on this replica. 副本将不包含用户数据。The replica will not contain user data. 此选项:This option:

  • 可以在任何版本的 SQL Server 上承载,包括 Express Edition。Can be hosted on any edition of SQL Server, including Express Edition.

  • 要求 CONFIGURATION_ONLY 副本的数据镜像终结点为 WITNESS 类型。Requires the data mirroring endpoint of the CONFIGURATION_ONLY replica to be type WITNESS.

  • 无法更改。Can not be altered.

  • CLUSTER_TYPE = WSFC 时无效。Is not valid when CLUSTER_TYPE = WSFC.

    有关详细信息,请参阅仅配置副本For more information, see Configuration only replica.

AVAILABILITY_MODE 在 ADD REPLICA ON 子句中是必需的,在 MODIFY REPLICA ON 子句中是可选的。AVAILABILITY_MODE is required in the ADD REPLICA ON clause and optional in the MODIFY REPLICA ON clause. 有关详细信息,请参阅 可用性模式(AlwaysOn 可用性组)For more information, see Availability Modes (Always On Availability Groups).

FAILOVER_MODE = { AUTOMATIC | MANUAL }FAILOVER_MODE = { AUTOMATIC | MANUAL }
指定您要定义的可用性副本的故障转移模式。Specifies the failover mode of the availability replica that you are defining.

AUTOMATICAUTOMATIC
启用自动故障转移。Enables automatic failover. 仅在指定 VAILABILITY_MODE = SYNCHRONOUS_COMMIT 的情况下才支持 AUTOMATIC。AUTOMATIC is supported only if you also specify AVAILABILITY_MODE = SYNCHRONOUS_COMMIT. 可以为最多三个可用性副本(包括主要副本)指定 AUTOMATIC。You can specify AUTOMATIC for three availability replicas, including the primary replica.

备注

在 SQL Server 2016 之前,限制为两个自动故障转移副本,包括主要副本Prior to SQL Server 2016, you were limited to two automatic failover replicas, including the primary replica

备注

SQL Server 故障转移群集实例 (FCI) 不支持通过可用性组来自动进行故障转移,因此,只能为手动故障转移配置任何由 FCI 承载的可用性副本。SQL Server Failover Cluster Instances (FCIs) do not support automatic failover by availability groups, so any availability replica that is hosted by an FCI can only be configured for manual failover.

MANUALMANUAL
允许数据库管理员手动故障转移或强制手动故障转移(“强制故障转移”)。Enables manual failover or forced manual failover (forced failover) by the database administrator.

FAILOVER_MODE 在 ADD REPLICA ON 子句中是必需的,在 MODIFY REPLICA ON 子句中是可选的。FAILOVER_MODE is required in the ADD REPLICA ON clause and optional in the MODIFY REPLICA ON clause. 存在在不同条件下支持的两种手动故障转移,没有数据丢失的手动故障转移和强制故障转移(可能存在数据丢失)。Two types of manual failover exist, manual failover without data loss and forced failover (with possible data loss), which are supported under different conditions. 有关详细信息,请参阅 故障转移和故障转移模式(AlwaysOn 可用性组)概念。For more information, see Failover and Failover Modes (Always On Availability Groups).

SEEDING_MODE = { AUTOMATIC | MANUAL }SEEDING_MODE = { AUTOMATIC | MANUAL }
指定初始设定次要副本种子的方式。Specifies how the secondary replica will be initially seeded.

AUTOMATICAUTOMATIC
启用直接种子设定。Enables direct seeding. 此方法将通过网络设定次要副本种子。This method will seed the secondary replica over the network. 此方法不要求在副本上备份和还原主数据库的副本。This method does not require you to backup and restore a copy of the primary database on the replica.

备注

为实现直接种子设定,必须通过使用 GRANT CREATE ANY DATABASE(授权创建任何数据库)选项调用 ALTER AVAILABILITY GROUP(改变可用性组)来允许在每个次要副本上创建数据库 。For direct seeding, you must allow database creation on each secondary replica by calling ALTER AVAILABILITY GROUP with the GRANT CREATE ANY DATABASE option.

MANUALMANUAL
指定手动种子设定(默认)。Specifies manual seeding (default). 此方法要求在主要副本上创建数据库的备份,并在次要副本上手动还原该备份。This method requires you to create a backup of the database on the primary replica and manually restore that backup on the secondary replica.

BACKUP_PRIORITY =nBACKUP_PRIORITY =n
指定相对于同一可用性组中的其他副本,在此副本上执行备份的优先级。Specifies your priority for performing backups on this replica relative to the other replicas in the same availability group. 该值是范围 0..100 中的整数。The value is an integer in the range of 0..100. 这些值将具有以下含义:These values have the following meanings:

  • 1..100 表示可被选择来执行备份的可用性副本。1..100 indicates that the availability replica could be chosen for performing backups. 1 表示最低优先级,100 表示最高优先级。1 indicates the lowest priority, and 100 indicates the highest priority. 如果 BACKUP_PRIORITY = 1,则只有在没有更高的优先级可用性副本当前可用的情况下,才会选择可用性副本来执行备份。If BACKUP_PRIORITY = 1, the availability replica would be chosen for performing backups only if no higher priority availability replicas are currently available.

  • 0 表示此可用性副本将永远不会被选择执行备份。0 indicates that this availability replica will never be chosen for performing backups. 例如,这对于您永远不希望备份故障转移到的远程可用性副本十分有用。This is useful, for example, for a remote availability replica to which you never want backups to fail over.

有关详细信息,请参阅活动次要副本:次要副本备份(Always On 可用性组)For more information, see Active Secondaries: Backup on Secondary Replicas (Always On Availability Groups).

SECONDARY_ROLE ( ... )SECONDARY_ROLE ( ... )
指定在此可用性副本当前拥有辅助角色(即它是辅助副本)时将要生效的角色特有设置。Specifies role-specific settings that will take effect if this availability replica currently owns the secondary role (that is, whenever it is a secondary replica). 在括号内指定一个或两个辅助角色选项。Within the parentheses, specify either or both secondary-role options. 如果指定两个选项,则使用以逗号分隔的列表。If you specify both, use a comma-separated list.

辅助角色选项如下所示:The secondary role options are as follows:

ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }
指定给定的可用性副本(正在执行辅助角色,也就是充当辅助副本)的数据库是否可以接受来自客户端的连接,可以是以下之一:Specifies whether the databases of a given availability replica that is performing the secondary role (that is, is acting as a secondary replica) can accept connections from clients, one of:

NO
不允许与此副本的辅助数据库的用户连接。No user connections are allowed to secondary databases of this replica. 它们不可用于读访问。They are not available for read access. 此选项为默认行为。This is the default behavior.

READ_ONLYREAD_ONLY
只允许连接应用程序意向属性设置为 ReadOnly 的次要副本中的数据库。Only connections are allowed to the databases in the secondary replica where the Application Intent property is set to ReadOnly. 有关此属性的详细信息,请参阅 Using Connection String Keywords with SQL Server Native ClientFor more information about this property, see Using Connection String Keywords with SQL Server Native Client.

ALLALL
允许针对辅助副本中的数据库的所有连接进行只读访问。All connections are allowed to the databases in the secondary replica for read-only access.

有关详细信息,请参阅活动次要副本:可读次要副本(Always On 可用性组)For more information, see Active Secondaries: Readable Secondary Replicas (Always On Availability Groups).

READ_ONLY_ROUTING_URL =' TCP :// system-address : port 'READ_ONLY_ROUTING_URL =' TCP ://system-address:port'
指定要用于此可用性副本的路由读意向连接请求的 URL。Specifies the URL to be used for routing read-intent connection requests to this availability replica. 这是 SQL Server 数据库引擎侦听的 URL。This is the URL on which the SQL Server Database Engine listens. 通常,SQL Server 数据库引擎的默认实例侦听 TCP 端口 1433。Typically, the default instance of the SQL Server Database Engine listens on TCP port 1433.

对于命名实例,可以通过查询 sys.dm_tcp_listener_states 动态管理视图的 port 和 type_desc 列来获取端口号 。For a named instance, you can obtain the port number by querying the port and type_desc columns of the sys.dm_tcp_listener_states dynamic management view. 服务器实例使用 Transact-SQL 侦听器 (type_desc='TSQL')。The server instance uses the Transact-SQL listener (type_desc='TSQL').

有关计算可用性副本的只读路由 URL 的详细信息,请参阅计算 Always On 的 read_only_routing_urlFor more information about calculating the read-only routing URL for an availability replica, see Calculating read_only_routing_url for Always On.

备注

对于 SQL ServerSQL Server 的命名实例,应将 Transact-SQL 侦听器配置为使用特定端口。For a named instance of SQL ServerSQL Server, the Transact-SQL listener should be configured to use a specific port. 有关详细信息,请参阅将服务器配置为侦听特定 TCP 端口(SQL Sever 配置管理器)For more information, see Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager).

PRIMARY_ROLE ( ... )PRIMARY_ROLE ( ... )
指定在此可用性副本当前拥有主角色(即它是主副本)时将要生效的角色特有设置。Specifies role-specific settings that will take effect if this availability replica currently owns the primary role (that is, whenever it is the primary replica). 在括号内指定一个或两个主角色选项。Within the parentheses, specify either or both primary-role options. 如果指定两个选项,则使用以逗号分隔的列表。If you specify both, use a comma-separated list.

主角色选项如下所示:The primary role options are as follows:

ALLOW_CONNECTIONS = { READ_WRITE | ALL }ALLOW_CONNECTIONS = { READ_WRITE | ALL }
指定给定的可用性副本(正在执行主要角色,也就是充当主副本)的数据库可以接受的来自客户端的连接类型,可以是以下之一:Specifies the type of connection that the databases of a given availability replica that is performing the primary role (that is, is acting as a primary replica) can accept from clients, one of:

READ_WRITEREAD_WRITE
不允许 Application Intent 连接属性设置为 ReadOnly 的连接。Connections where the Application Intent connection property is set to ReadOnly are disallowed. 在 Application Intent 属性设置为 ReadWrite 或者未设置 Application Intent 连接属性时,将允许连接。When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed. 有关 Application Intent 连接属性的详细信息,请参阅 Using Connection String Keywords with SQL Server Native ClientFor more information about Application Intent connection property, see Using Connection String Keywords with SQL Server Native Client.

ALLALL
主副本中的数据库允许所有连接。All connections are allowed to the databases in the primary replica. 此选项为默认行为。This is the default behavior.

READ_ONLY_ROUTING_LIST = { ('<server_instance>' [ ,...n ] ) | NONE }READ_ONLY_ROUTING_LIST = { ('<server_instance>' [ ,...n ] ) | NONE }
指定一个以逗号分隔的服务器实例列表,这些实例承载在以辅助角色运行时满足以下要求的此可用性组的可用性副本:Specifies a comma-separated list of server instances that host availability replicas for this availability group that meet the following requirements when running under the secondary role:

  • 被配置为允许所有连接或只读连接(参阅上文 SECONDARY_ROLE 选项的 ALLOW_CONNECTIONS 参数)。Be configured to allow all connections or read-only connections (see the ALLOW_CONNECTIONS argument of the SECONDARY_ROLE option, above).

  • 定义了只读路由 URL(参阅上文 SECONDARY_ROLE 选项的 READ_ONLY_ROUTING_URL 参数)。Have their read-only routing URL defined (see the READ_ONLY_ROUTING_URL argument of the SECONDARY_ROLE option, above).

READ_ONLY_ROUTING_LIST 的值如下:The READ_ONLY_ROUTING_LIST values are as follows:

<server_instance>
指定承载可用性副本的 SQL ServerSQL Server 实例的地址,该副本在以辅助角色运行时是可读辅助副本。Specifies the address of the instance of SQL ServerSQL Server that is the host for an availability replica that is a readable secondary replica when running under the secondary role.

使用以逗号分隔的列表指定可能承载可读辅助副本的所有服务器实例。Use a comma-separated list to specify all of the server instances that might host a readable secondary replica. 只读路由将遵循在列表中指定服务器实例的顺序。Read-only routing will follow the order in which server instances are specified in the list. 如果在副本的只读路由列表中包含副本的宿主服务器实例,通常将此服务器实例放在列表末尾比较好,这样在一个辅助副本可用时读意向连接将访问它。If you include a replica's host server instance on the replica's read-only routing list, placing this server instance at the end of the list is typically a good practice, so that read-intent connections go to a secondary replica, if one is available.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,可在可读次要副本间实现读意向请求的负载均衡。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can load-balance read-intent requests across readable secondary replicas. 可通过将副本放入只读路由列表中的一组嵌套括号中来指定。You specify this by placing the replicas in a nested set of parentheses within the read-only routing list. 有关详细信息和示例,请参阅在只读副本间配置负载均衡For more information and examples, see Configure load-balancing across read-only replicas.

NONE
指定此可用性副本为主副本时将不支持只读路由。Specifies that when this availability replica is the primary replica, read-only routing will not be supported. 此选项为默认行为。This is the default behavior. 与 MODIFY REPLICA ON 一起使用时,此值将禁用现有列表(如果有)。When used with MODIFY REPLICA ON, this value disables an existing list, if any.

READ_WRITE_ROUTING_URL = { ('<server_instance>') }READ_WRITE_ROUTING_URL = { ('<server_instance>') }
适用对象:SQL Server(从 SQL Server 2019 (15.x) 开始)Applies to: SQL Server (Starting with SQL Server 2019 (15.x))

指定服务器实例,这些实例承载在以主角色运行时满足以下要求的此可用性组的可用性副本:Specifies server instances that host availability replicas for this availability group that meet the following requirements when running under the primary role:

  • 副本规范 PRIMARY_ROLE 包括 READ_WRITE_ROUTING_URL。The replica spec PRIMARY_ROLE includes READ_WRITE_ROUTING_URL.
  • 连接字符串为 ReadWrite,通过将 ApplicationIntent 定义为 ReadWrite 或不设置 ApplicationIntent 并使默认值 (ReadWrite) 生效来实现。The connection string is ReadWrite either by defining ApplicationIntent as ReadWrite or by not setting ApplicationIntent and letting the default (ReadWrite) take effect.

有关详细信息,请参阅次要副本到主要副本读/写连接重定向(AlwaysOn 可用性组)For more information, see Secondary to primary replica read/write connection redirection (Always On Availability Groups).

SESSION_TIMEOUT =secondsSESSION_TIMEOUT =seconds
以秒为单位指定会话超时期限。Specifies the session-timeout period in seconds. 如果不指定此选项,则在默认情况下,超时期限为 10 秒。If you do not specify this option, by default, the time period is 10 seconds. 最小值为 5 秒。The minimum value is 5 seconds.

重要

我们建议您将超时期限保持为 10 秒或更长。We recommend that you keep the time-out period at 10 seconds or greater.

有关会话超时期限的详细信息,请参阅 Always On 可用性组概述 (SQL Server)For more information about the session-timeout period, see Overview of Always On Availability Groups (SQL Server).

MODIFY REPLICA ONMODIFY REPLICA ON
修改可用性组的任何副本。Modifies any of the replicas of the availability group. 要修改的副本列表包含每个副本的服务器实例地址和 WITH (…) 子句。The list of replicas to be modified contains the server instance address and a WITH (...) clause for each replica.

仅在主要副本上受支持。Supported only on the primary replica.

REMOVE REPLICA ONREMOVE REPLICA ON
从可用性组中删除指定的辅助副本。Removes the specified secondary replica from the availability group. 不能从可用性组删除当前的主副本。The current primary replica cannot be removed from an availability group. 在删除时,副本停止接收数据。On being removed, the replica stops receiving data. 其辅助数据库从可用性组中删除,并且进入 RESTORING 状态。Its secondary databases are removed from the availability group and enter the RESTORING state.

仅在主要副本上受支持。Supported only on the primary replica.

备注

如果您在某一副本处于不可用或失败状态时删除该副本,则在其恢复联机状态时,将会发现不再属于该可用性组。If you remove a replica while it is unavailable or failed, when it comes back online it will discover that it no longer belongs the availability group.

JOINJOIN
导致本地服务器实例承载指定可用性组中的辅助副本。Causes the local server instance to host a secondary replica in the specified availability group.

仅在尚未加入可用性组的辅助副本上支持。Supported only on a secondary replica that has not yet been joined to the availability group.

有关详细信息,请参阅 将辅助副本联接到可用性组 (SQL Server)或 PowerShell 将辅助数据库联接到 Always On 可用性组。For more information, see Join a Secondary Replica to an Availability Group (SQL Server).

FAILOVERFAILOVER
启动可用性组的手动故障转移,并且没有对您连接到的辅助副本的数据丢失。Initiates a manual failover of the availability group without data loss to the secondary replica to which you are connected. 将承载主要副本的副本是故障转移目标。The replica that will host the primary replica is the failover target. 故障转移目标将接管主要角色,恢复各数据库的副本并且使它们作为新的主数据库处于联机状态。The failover target will take over the primary role and recover its copy of each database and bring them online as the new primary databases. 以前的主副本同时转换为辅助角色,并且其数据库将成为辅助数据库且立即挂起。The former primary replica concurrently transitions to the secondary role, and its databases become secondary databases and are immediately suspended. 在发生一系列故障后,这些角色可能来回切换。Potentially, these roles can be switched back and forth by a series of failures.

仅在当前与主副本同步的同步提交辅助副本上支持。Supported only on a synchronous-commit secondary replica that is currently synchronized with the primary replica. 请注意,对于要同步的辅助副本,主副本也必须在同步提交模式下运行。Note that for a secondary replica to be synchronized the primary replica must also be running in synchronous-commit mode.

备注

故障转移命令将在故障转移目标接受它之后立即返回。A failover command returns as soon as the failover target has accepted the command. 但是,在可用性组完成故障转移之后,数据库恢复操作将以异步方式执行。However, database recovery occurs asynchronously after the availability group has finished failing over.

有关执行计划的手动故障转移的限制、先决条件和建议的信息,请参阅执行可用性组的计划的手动故障转移 (SQL Server)For information about the limitations, prerequisites and recommendations for a performing a planned manual failover, see Perform a Planned Manual Failover of an Availability Group (SQL Server).

FORCE_FAILOVER_ALLOW_DATA_LOSSFORCE_FAILOVER_ALLOW_DATA_LOSS

注意

强制故障转移(这可能会涉及一些数据丢失)严格来说是一种灾难恢复方法。Forcing failover, which might involve some data loss, is strictly a disaster recovery method. 因此,我们强烈建议您仅在以下情况下才强制故障转移:主副本不再运行、您愿意承担丢失数据的风险并且您必须立即将服务还原到可用性组。Therefore, We strongly recommend that you force failover only if the primary replica is no longer running, you are willing to risk losing data, and you must restore service to the availability group immediately.

仅在其角色处于 SECONDARY 或 RESOLVING 状态的副本上支持。Supported only on a replica whose role is in the SECONDARY or RESOLVING state. --对其输入故障转移命令的副本称为“故障转移目标”。--The replica on which you enter a failover command is known as the failover target.

强制将可用性组故障转移到故障转移目标(可能会丢失数据)。Forces failover of the availability group, with possible data loss, to the failover target. 故障转移目标将接管主要角色,恢复各数据库的副本并且使它们作为新的主数据库处于联机状态。The failover target will take over the primary role and recover its copy of each database and bring them online as the new primary databases. 在剩余的任何辅助副本上,在手动恢复前每个辅助数据库都处于挂起状态。On any remaining secondary replicas, every secondary database is suspended until manually resumed. 在以前的主副本可用前,它将切换到辅助角色,并且其数据库将成为挂起的辅助数据库。When the former primary replica becomes available, it will switch to the secondary role, and its databases will become suspended secondary databases.

备注

故障转移命令将在故障转移目标接受它之后立即返回。A failover command returns as soon as the failover target has accepted the command. 但是,在可用性组完成故障转移之后,数据库恢复操作将以异步方式执行。However, database recovery occurs asynchronously after the availability group has finished failing over.

有关强制故障转移的限制、先决条件和建议的信息,以及强制故障转移对可用性组中以前的主数据库的影响,请参阅执行可用性组的强制手动故障转移 (SQL Server)For information about the limitations, prerequisites and recommendations for forcing failover and the effect of a forced failover on the former primary databases in the availability group, see Perform a Forced Manual Failover of an Availability Group (SQL Server).

ADD LISTENER 'dns_name'( <add_listener_option> )ADD LISTENER 'dns_name'( <add_listener_option> )
为此可用性组定义新的可用性组侦听器。Defines a new availability group listener for this availability group. 仅在主要副本上受支持。Supported only on the primary replica.

重要

创建第一个侦听器之前,强烈建议阅读创建或配置可用性组侦听程序 (SQL Server)Before you create your first listener, we strongly recommend that you read Create or Configure an Availability Group Listener (SQL Server).

为给定可用性组创建侦听器后,我们强烈建议您执行以下操作:After you create a listener for a given availability group, we strongly recommend that you do the following:

  • 请求您的网络管理员将该侦听器的 IP 地址保留为专用。Ask your network administrator to reserve the listener's IP address for its exclusive use.
  • 将该侦听器的 DNS 主机名提供给应用程序开发人员,以便在请求与此可用性组的客户端连接时用于连接字符串中。Give the listener's DNS host name to application developers to use in connection strings when requesting client connections to this availability group.

dns_namedns_name
指定可用性组侦听器的 DNS 主机名。Specifies the DNS host name of the availability group listener. 在域和 NetBIOS 中,侦听器的 DNS 名称必须唯一。The DNS name of the listener must be unique in the domain and in NetBIOS.

dns_name 为字符串值。dns_name is a string value. 该名称只能包含字母数字字符、破折号 (-) 和连字符 (),顺序不分先后。This name can contain only alphanumeric characters, dashes (-), and hyphens (), in any order. DNS 主机名不区分大小写。DNS host names are case insensitive. 最大长度为 63 个字符。The maximum length is 63 characters.

我们建议您指定一个有意义的字符串。We recommend that you specify a meaningful string. 例如,对于名为 AG1的可用性组,有意义的 DNS 主机名将是 ag1-listenerFor example, for an availability group named AG1, a meaningful DNS host name would be ag1-listener.

重要

NetBIOS 只识别 dns_name 中的前 15 个字符。NetBIOS recognizes only the first 15 chars in the dns_name. 如果您的两个 WSFC 群集均由同一 Active Directory 控制,而您试图使用超过 15 个字符的名称(具有相同的 15 字符前缀)在这两个群集中创建可用性组侦听器,此时您将收到错误,报告无法使虚拟网络名称资源联机。If you have two WSFC clusters that are controlled by the same Active Directory and you try to create availability group listeners in both of clusters using names with more than 15 characters and an identical 15 character prefix, you will get an error reporting that the Virtual Network Name resource could not be brought online. 有关 DNS 名称的前缀命名规则的信息,请参阅 分配域名For information about prefix naming rules for DNS names, see Assigning Domain Names.

JOIN AVAILABILITY GROUP ONJOIN AVAILABILITY GROUP ON
联接到分布式可用性组。Joins to a distributed availability group. 创建分布式可用性组时,在群集上创建的可用性组为主要可用性组。When you create a distributed availability group, the availability group on the cluster where it is created is the primary availability group. 联接分布式可用性组的可用性组为次要可用性组。The availability group that joins the distributed availability group is the secondary availability group.

<ag_name>
指定构成一半分布式可用性组的可用性组名称。Specifies the name of the availability group that makes up one half of the distributed availability group.

LISTENER =' TCP :// system-address : port 'LISTENER =' TCP ://system-address:port'
指定与可用性组关联的侦听器的 URL 路径。Specifies the URL path for the listener associated with the availability group.

必须有 LISTENER 子句。The LISTENER clause is required.

' TCP :// system-address : port '' TCP ://system-address:port'
指定与可用性组关联的侦听器的 URL。Specifies a URL for the listener associated with the availability group. URL 参数如下所示:The URL parameters are as follows:

system-addresssystem-address
一个字符串,例如系统名称、完全限定的域名或 IP 地址,它们明确标识了侦听器。Is a string, such as a system name, a fully qualified domain name, or an IP address, that unambiguously identifies the listener.

portport
是与可用性组的镜像终结点关联的端口号。Is a port number that is associated with the mirroring endpoint of the availability group. 请注意,这不是侦听器的端口。Note that this is not the port of the listener.

AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }
指定在主要副本可以在给定主数据库上提交事务前,是否必须等待次要可用性组确认日志记录硬编码(写入)到磁盘。Specifies whether the primary replica has to wait for the secondary availability group to acknowledge the hardening (writing) of the log records to disk before the primary replica can commit the transaction on a given primary database.

SYNCHRONOUS_COMMITSYNCHRONOUS_COMMIT
指定主要副本等到在次要可用性组上进行了硬编码后才提交事务。Specifies that the primary replica will wait to commit transactions until they have been hardened on the secondary availability group. 可以为最多两个可用性组(包括主要可用性组)指定 SYNCHRONOUS_COMMIT。You can specify SYNCHRONOUS_COMMIT for up to two availability groups, including the primary availability group.

ASYNCHRONOUS_COMMITASYNCHRONOUS_COMMIT
指定主要副本无需等待该次要可用性组对日志进行硬编码即可提交事务。Specifies that the primary replica commits transactions without waiting for this secondary availability group to harden the log. 可以为最多两个可用性组(包括主要可用性组)指定 ASYNCHRONOUS_COMMIT。You can specify ASYNCHRONOUS_COMMIT for up to two availability groups, including the primary availability group.

AVAILABILITY_MODE 子句是必需的。The AVAILABILITY_MODE clause is required.

FAILOVER_MODE = { MANUAL }FAILOVER_MODE = { MANUAL }
指定分布式可用性组的故障转移模式。Specifies the failover mode of the distributed availability group.

MANUALMANUAL
允许数据库管理员执行的手动故障转移或强制手动故障转移(通常称为“强制故障转移”)。Enables planned manual failover or forced manual failover (typically called forced failover) by the database administrator.

不支持自动故障转移到次要可用性组。Automatic failover to the secondary availability group is not supported.

SEEDING_MODE= { AUTOMATIC | MANUAL }SEEDING_MODE = { AUTOMATIC | MANUAL }
指定初始设定次要可用性组种子的方式。Specifies how the secondary availability group will be initially seeded.

AUTOMATICAUTOMATIC
启用自动种子设定。Enables automatic seeding. 此方法将通过网络设定次要可用性组种子。This method will seed the secondary availability group over the network. 此方法不要求在次要可用性组的副本上备份和还原主数据库的副本。This method does not require you to backup and restore a copy of the primary database on the replicas of the secondary availability group.

MANUALMANUAL
指定手动设定种子。Specifies manual seeding. 此方法要求在主要副本上创建数据库的备份,并在次要可用性组的副本上手动还原该备份。This method requires you to create a backup of the database on the primary replica and manually restore that backup on the replica(s) of the secondary availability group.

MODIFY AVAILABILITY GROUP ONMODIFY AVAILABILITY GROUP ON
修改分布式可用性组的任意可用性组设置。Modifies any of the availability group settings of a distributed availability group. 要修改的可用性组列表包含可用性组名称和每个可用性组的 WITH (…) 子句。The list of availability groups to be modified contains the availability group name and a WITH (...) clause for each availability group.

重要

必须同时对主要可用性组实例和次要可用性组实例重复此命令。This command must be repeated on both the primary availability group and secondary availability group instances.

GRANT CREATE ANY DATABASEGRANT CREATE ANY DATABASE
允许可用性组代表支持直接设定种子的主要副本创建数据库 (SEEDING_MODE = AUTOMATIC)。Permits the availability group to create databases on behalf of the primary replica, which supports direct seeding (SEEDING_MODE = AUTOMATIC). 在次要副本联接可用性组后,应对支持直接设定种子的每个次要副本运行此参数。This parameter should be run on every secondary replica that supports direct seeding after that secondary joins the availability group. 需要 CREATE ANY DATABASE 权限。Requires the CREATE ANY DATABASE permission.

DENY CREATE ANY DATABASEDENY CREATE ANY DATABASE
删除可用性组代表主要副本创建数据库的功能。Removes the ability of the availability group to create databases on behalf of the primary replica.

<add_listener_option>
ADD LISTENER 采用以下选项之一:ADD LISTENER takes one of the following options:

WITH DHCP [ ON { (' four_part_ipv4_address ',' four_part_ipv4_mask ') } ]WITH DHCP [ ON { ('four_part_ipv4_address','four_part_ipv4_mask') } ]
指定可用性组侦听器将使用动态主机配置协议 (DHCP)。Specifies that the availability group listener will use the Dynamic Host Configuration Protocol (DHCP). 或者,使用 ON 子句标识将在其上创建此侦听器的网络。Optionally, use the ON clause to identify the network on which this listener will be created. DHCP 限制为单个子网,该子网用于在可用性组中承载可用性副本的每个服务器实例。DHCP is limited to a single subnet that is used for every server instances that hosts an availability replica in the availability group.

重要

不建议在生产环境中使用 DHCP。We do not recommend DHCP in production environment. 如果停止工作且 DHCP IP 租期已到,则需要额外的时间来注册与侦听器 DNS 名称相关联且影响客户端连接的新 DHCP 网络 IP 地址。If there is a down time and the DHCP IP lease expires, extra time is required to register the new DHCP network IP address that is associated with the listener DNS name and impact the client connectivity. 但是,DHCP 适合用于设置开发和测试环境以验证可用性组的基本功能并适合与应用程序集成。However, DHCP is good for setting up your development and testing environment to verify basic functions of availability groups and for integration with your applications.

例如:For example:

WITH DHCP ON ('10.120.19.0','255.255.254.0')

WITH IP ( { (‘ four_part_ipv4_address ’,‘ four_part_ipv4_mask ’) | (‘ ipv6_address ’) } [ , ...n ] ) [ , PORT = listener_port ]WITH IP ( { ('four_part_ipv4_address','four_part_ipv4_mask') | ('ipv6_address') } [ , ...n ] ) [ , PORT =listener_port ]
指定可用性组侦听器将使用一个或多个静态 IP 地址,而不使用 DHCP。Specifies that, instead of using DHCP, the availability group listener will use one or more static IP addresses. 若要跨多个子网创建一个可用性组,每个子网均需要一个侦听器配置中的静态 IP 地址。To create an availability group across multiple subnets, each subnet requires one static IP address in the listener configuration. 对于某一给定子网,静态 IP 地址可以是 IPv4 地址或 IPv6 地址。For a given subnet, the static IP address can be either an IPv4 address or an IPv6 address. 请与您的网络管理员联系以获取将承载新可用性组的可用性副本的每个子网的静态 IP 地址。Contact your network administrator to get a static IP address for each subnet that will host an availability replica for the new availability group.

例如:For example:

WITH IP ( ('10.120.19.155','255.255.254.0') )

ipv4_addressipv4_address
指定可用性组侦听器的由四部分组成的 IPv4 地址。Specifies an IPv4 four-part address for an availability group listener. 例如,10.120.19.155For example, 10.120.19.155.

ipv4_maskipv4_mask
指定可用性组侦听器的由四部分组成的 IPv4 掩码。Specifies an IPv4 four-part mask for an availability group listener. 例如,255.255.254.0For example, 255.255.254.0.

ipv6_addressipv6_address
指定可用性组侦听器的 IPv6 地址。Specifies an IPv6 address for an availability group listener. 例如,2001::4898:23:1002:20f:1fff:feff:b3a3For example, 2001::4898:23:1002:20f:1fff:feff:b3a3.

PORT = listener_portPORT = listener_port
指定端口号 listener_port,以供由 WITH IP 子句指定的可用组侦听器使用。Specifies the port number-listener_port-to be used by an availability group listener that is specified by a WITH IP clause. PORT 是可选的。PORT is optional.

支持默认端口号 1433。The default port number, 1433, is supported. 但出于安全考虑,我们建议使用其他端口号。However, if you have security concerns, we recommend using a different port number.

例如: WITH IP ( ('2001::4898:23:1002:20f:1fff:feff:b3a3') ) , PORT = 7777For example: WITH IP ( ('2001::4898:23:1002:20f:1fff:feff:b3a3') ) , PORT = 7777

MODIFY LISTENER 'dns_name'( <modify_listener_option> )MODIFY LISTENER 'dns_name'( <modify_listener_option> )
修改此可用性组的现有可用性组侦听器。Modifies an existing availability group listener for this availability group. 仅在主要副本上受支持。Supported only on the primary replica.

<modify_listener_option>
MODIFY LISTENER 采用以下选项之一:MODIFY LISTENER takes one of the following options:

ADD IP { (' four_part_ipv4_address ',' four_part_ipv4_mask ') | ('dns_name ipv6_address ') }ADD IP { ('four_part_ipv4_address','four_part_ipv4_mask') | ('dns_name ipv6_address') }
将指定的 IP 地址添加到由 dns_name 指定的可用性组侦听器。Adds the specified IP address to the availability group listener specified by dns_name.

PORT = listener_portPORT = listener_port
请参阅本节前面对此参数的说明。See the description of this argument earlier in this section.

RESTART LISTENER 'dns_name'RESTART LISTENER 'dns_name'
重新启动与指定的 DNS 名称关联的侦听器。Restarts the listener that is associated with the specified DNS name. 仅在主要副本上受支持。Supported only on the primary replica.

REMOVE LISTENER ' dns_name 'REMOVE LISTENER 'dns_name'
删除与指定的 DNS 名称关联的侦听器。Removes the listener that is associated with the specified DNS name. 仅在主要副本上受支持。Supported only on the primary replica.

OFFLINEOFFLINE
使联机的可用性组脱机。Takes an online availability group offline. 同步提交数据库没有数据丢失。There is no data loss for synchronous-commit databases.

在某一可用性组脱机后,其数据库将不可用于客户端,并且您无法使该可用性组重新联机。After an availability group goes offline, its databases become unavailable to clients, and you cannot bring the availability group back online. 因此,在将可用性组资源迁移到新 WSFC 群集时,仅在 Always On 可用性组Always On availability groups 的跨群集迁移过程中使用 OFFLINE 选项。Therefore, use the OFFLINE option only during a cross-cluster migration of Always On 可用性组Always On availability groups, when migrating availability group resources to a new WSFC cluster.

有关详细信息,请参阅使可用性组脱机 (SQL Server)For more information, see Take an Availability Group Offline (SQL Server).

先决条件和限制Prerequisites and Restrictions

有关可用性副本及其主机服务器实例与计算的先决条件和限制的信息,请参阅 Always On 可用性组的先决条件、限制和建议 (SQL Server)For information about prerequisites and restrictions on availability replicas and on their host server instances and computers, see Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server).

有关 AVAILABILITY GROUP Transact-SQL 语句的限制的信息,请参阅 Always On 可用性组的 Transact-SQL 语句的概述 (SQL Server)For information about restrictions on the AVAILABILITY GROUP Transact-SQL statements, see Overview of Transact-SQL Statements for Always On Availability Groups (SQL Server).

安全性Security

权限Permissions

对可用性组要求 ALTER AVAILABILITY GROUP 权限、CONTROL AVAILABILITY GROUP 权限、ALTER ANY AVAILABILITY GROUP 权限或 CONTROL SERVER 权限。Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission. 还需要 ALTER ANY DATABASE 权限。Also requires ALTER ANY DATABASE permission.

示例Examples

A.A. 将次要副本联接到可用性组Joining a secondary replica to an availability group

以下示例联接连接到 AccountsAG 可用性组的次要副本。The following example joins a secondary replica to which you are connected to the AccountsAG availability group.

ALTER AVAILABILITY GROUP AccountsAG JOIN;  
GO  

B.B. 强制可用性组的故障转移Forcing failover of an availability group

下面的示例强制 AccountsAG 可用性组故障转移到您所连接的辅助副本。The following example forces the AccountsAG availability group to fail over to the secondary replica to which you are connected.

ALTER AVAILABILITY GROUP AccountsAG FORCE_FAILOVER_ALLOW_DATA_LOSS;  
GO  

另请参阅See Also

CREATE AVAILABILITY GROUP (Transact-SQL) CREATE AVAILABILITY GROUP (Transact-SQL)
ALTER DATABASE SET HADR (Transact-SQL) ALTER DATABASE SET HADR (Transact-SQL)
DROP AVAILABILITY GROUP (Transact-SQL) DROP AVAILABILITY GROUP (Transact-SQL)
sys.availability_replicas (Transact-SQL) sys.availability_replicas (Transact-SQL)
sys.availability_groups (Transact-SQL) sys.availability_groups (Transact-SQL)
Always On 可用性组配置故障排除 (SQL Server) Troubleshoot Always On Availability Groups Configuration (SQL Server)
AlwaysOn 可用性组概述 (SQL Server) Overview of Always On Availability Groups (SQL Server)
可用性组侦听程序、客户端连接和应用程序故障转移 (SQL Server)Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)