创建并使用活动异地复制 - Azure SQL 数据库Creating and using active geo-replication - Azure SQL Database

适用于:是Azure SQL 数据库 APPLIES TO: yesAzure SQL Database

活动异地复制是一项 Azure SQL 数据库功能,可用于在相同或不同的数据中心(区域)的服务器上创建各个数据库的可读辅助数据库。Active geo-replication is an Azure SQL Database feature that allows you to create readable secondary databases of individual databases on a server in the same or different data center (region).


Azure SQL 托管实例不支持活动异地复制。Active geo-replication is not supported by Azure SQL Managed Instance. 对于 SQL 托管实例的实例的地理故障转移,请使用自动故障转移组For geographic failover of instances of SQL Managed Instance, use Auto-failover groups.

活动异地复制旨在充当业务连续性解决方案,允许应用程序在发生区域性灾难或大规模中断时执行各个数据库的快速灾难恢复。Active geo-replication is designed as a business continuity solution that allows the application to perform quick disaster recovery of individual databases in case of a regional disaster or large scale outage. 如果启用了异地复制,则应用程序可以向其他 Azure 区域中的辅助数据库启动故障转移。If geo-replication is enabled, the application can initiate failover to a secondary database in a different Azure region. 在相同或不同的区域中最多支持四个辅助数据库,并且辅助数据库也可以用于只读访问查询。Up to four secondaries are supported in the same or different regions, and the secondaries can also be used for read-only access queries. 故障转移必须由应用程序或用户手动启动。The failover must be initiated manually by the application or the user. 故障转移后,新的主数据库具有不同的连接终结点。After failover, the new primary has a different connection end point.


活动异地复制通过流式处理数据库事务日志来复制更改。Active geo-replication replicates changes by streaming database transaction log. 它与事务复制无关,后者通过执行 DML (INSERT, UPDATE, DELETE) 命令复制更改。It is unrelated to transactional replication, which replicates changes by executing DML (INSERT, UPDATE, DELETE) commands.

下图演示了使用活动异地复制的异地冗余云应用程序的典型配置。The following diagram illustrates a typical configuration of a geo-redundant cloud application using Active geo-replication.



SQL 数据库还支持自动故障转移组。SQL Database also supports auto-failover groups. 有关详细信息,请参阅自动故障转移组For more information, see using auto-failover groups.

如果主数据库因某种原因而出现故障,或者只是需要脱机,则可以启动故障转移到任何辅助数据库的操作。If for any reason your primary database fails, or simply needs to be taken offline, you can initiate failover to any of your secondary databases. 当将故障转移激活到辅助数据库之一时,所有其他辅助数据库会自动链接到新的主数据库。When failover is activated to one of the secondary databases, all other secondaries are automatically linked to the new primary.

可以使用活动异地复制管理服务器或弹性池中单个数据库或一组数据库的复制和故障转移。You can manage replication and failover of an individual database or a set of databases on a server or in an elastic pool using active geo-replication. 可通过以下方式执行此操作:You can do that using:

活动异地复制利用数据库引擎的 Always On 可用性组技术,使用快照隔离以异步方式将主数据库上已提交的事务复制到辅助数据库。Active geo-replication leverages the Always On availability group technology of the database engine to asynchronously replicate committed transactions on the primary database to a secondary database using snapshot isolation. 自动故障转移组提供基于活动异地复制的组语义,但使用相同的异步复制机制。Auto-failover groups provide the group semantics on top of active geo-replication but the same asynchronous replication mechanism is used. 尽管在任意给定时间,辅助数据库可能略微滞后于主数据库,但系统可以保证辅助数据永不包含部分事务。While at any given point, the secondary database might be slightly behind the primary database, the secondary data is guaranteed to never have partial transactions. 跨区域冗余使应用程序能够在自然灾害、灾难性人为失误或恶意行为导致整个或部分数据中心永久性数据丢失后得以快速恢复。Cross-region redundancy enables applications to quickly recover from a permanent loss of an entire datacenter or parts of a datacenter caused by natural disasters, catastrophic human errors, or malicious acts. 业务连续性概述中提供了具体的 RPO 数据。The specific RPO data can be found at Overview of Business Continuity.


如果两个区域之间存在网络故障,我们会每 10 秒钟重试一次以重新建立连接。If there is a network failure between two regions, we retry every 10 seconds to re-establish connections.


若要确保对主数据库所做的关键更改已在故障转移前复制到辅助数据库,可以强制同步,以确保复制关键更改(例如密码更新)。To guarantee that a critical change on the primary database is replicated to a secondary before you failover, you can force synchronization to ensure the replication of critical changes (for example, password updates). 强制同步会阻止调用线程,直到所有提交的事务得到复制,因此会影响性能。Forced synchronization impacts performance because it blocks the calling thread until all committed transactions are replicated. 有关详细信息,请参阅 sp_wait_for_database_copy_syncFor details, see sp_wait_for_database_copy_sync. 若要监视主数据库和地域辅助数据库之间的复制延迟,请参阅 sys.dm_geo_replication_link_statusTo monitor the replication lag between the primary database and geo-secondary, see sys.dm_geo_replication_link_status.

下图显示了配置的活动异地复制示例,其中主数据库位于中国东部区域,辅助数据库位于中国北部区域。The following figure shows an example of active geo-replication configured with a primary in the China East region and secondary in the China North region.


因为辅助数据库是可读的,并且可用于卸载只读工作负荷,如报表作业。Because the secondary databases are readable, they can be used to offload read-only workloads such as reporting jobs. 如果使用活动异地复制,则可在主数据库所在的区域中创建辅助数据库,但不会增加应用程序针对灾难性故障的复原能力。If you are using active geo-replication, it is possible to create the secondary database in the same region with the primary, but it does not increase the application's resilience to catastrophic failures. 如果使用自动故障转移组,则会始终在不同的区域中创建辅助数据库。If you are using auto-failover groups, your secondary database is always created in a different region.

除了灾难恢复外,活动异地复制还可用于以下情况:In addition to disaster recovery active geo-replication can be used in the following scenarios:

  • 数据库迁移:可以使用活动异地复制将数据库在联机情况下从一台服务器迁移到另一台服务器,只需要极少的停机时间。Database migration: You can use active geo-replication to migrate a database from one server to another online with minimum downtime.
  • 应用程序升级:可以在应用程序升级期间创建额外的辅助数据库作为故障回复副本。Application upgrades: You can create an extra secondary as a fail back copy during application upgrades.

若要真正实现业务连续性,只需添加数据中心之间的数据库冗余即可,这只是该解决方案的一部分功能。To achieve real business continuity, adding database redundancy between datacenters is only part of the solution. 在发生灾难性故障后,端对端地恢复应用程序(服务)需要恢复构成该服务的所有组件以及所有依赖服务。Recovering an application (service) end-to-end after a catastrophic failure requires recovery of all components that constitute the service and any dependent services. 这些组件的示例包括客户端软件(例如,使用自定义 JavaScript 的浏览器)、Web 前端、存储和 DNS。Examples of these components include the client software (for example, a browser with a custom JavaScript), web front ends, storage, and DNS. 所有组件必须能够弹性应对相同的故障,并在应用程序的恢复时间目标 (RTO) 值内变为可用,这一点非常关键。It is critical that all components are resilient to the same failures and become available within the recovery time objective (RTO) of your application. 因此,需要识别所有依赖服务,并了解它们提供的保证和功能。Therefore, you need to identify all dependent services and understand the guarantees and capabilities they provide. 然后,必须执行适当的步骤来确保对用户的服务所依赖的服务执行故障转移期间,用户的服务能够正常运行。Then, you must take adequate steps to ensure that your service functions during the failover of the services on which it depends. 有关设计灾难恢复解决方案的详细信息,请参阅设计使用活动异地复制的灾难恢复云解决方案For more information about designing solutions for disaster recovery, see Designing Cloud Solutions for Disaster Recovery Using active geo-replication.

活动异地复制术语和功能Active geo-replication terminology and capabilities

  • 自动异步复制Automatic Asynchronous Replication

    只能通过添加到现有数据库来创建辅助数据库。You can only create a secondary database by adding to an existing database. 辅助数据库可在任意服务器中创建。The secondary can be created in any server. 创建完成之后,使用从主数据库复制的数据填充辅助数据库。Once created, the secondary database is populated with the data copied from the primary database. 这个过程称为种子设定。This process is known as seeding. 创建辅助数据库并设定其种子后,会自动以异步方式将主数据库的更新复制到辅助数据库。After secondary database has been created and seeded, updates to the primary database are asynchronously replicated to the secondary database automatically. 异步复制是指先在主数据库上提交事务,然后将事务复制到辅助数据库。Asynchronous replication means that transactions are committed on the primary database before they are replicated to the secondary database.

  • 可读取的辅助数据库Readable secondary databases

    应用程序可使用与访问主数据库时所用的相同或不同的安全主体来访问辅助数据库以执行只读操作。An application can access a secondary database for read-only operations using the same or different security principals used for accessing the primary database. 辅助数据库在快照隔离模式下运行,以确保对主数据库的更新的复制(日志重播)不会因辅助数据库上执行的查询操作而延迟。The secondary databases operate in snapshot isolation mode to ensure replication of the updates of the primary (log replay) is not delayed by queries executed on the secondary.


如果主数据库上有架构更新,则日志重播会在辅助数据库上延迟。The log replay is delayed on the secondary database if there are schema updates on the Primary. 因为架构更新需要在辅助数据库上有架构锁。The latter requires a schema lock on the secondary database.


可以使用异地复制在与主数据库相同的区域中创建辅助数据库。You can use geo-replication to create a secondary database in the same region as the primary. 可以使用此辅助数据库对同一区域中的只读工作负荷进行负载均衡。You can use this secondary to load-balance a read-only workloads in the same region. 但是,同一区域中的辅助数据库不能提供额外的故障恢复能力,因此不适合用作灾难恢复的故障转移目标。However, a secondary database in the same region does not provide additional fault resilience and therefore is not a suitable failover target for disaster recovery.

  • 计划的故障转移Planned failover

    计划内故障转移会在完全同步完成以后切换主数据库和辅助数据库的角色。Planned failover switches the roles of primary and secondary databases after the full synchronization is completed. 这是一项联机操作,不会导致数据丢失。It is an online operation that does not result in data loss. 操作时间取决于主数据库上需要同步的事务日志的大小。The time of the operation depends on the size of the transaction log on the primary that needs to be synchronized. 计划内故障转移设计用于以下方案:(a) 在数据丢失是可以接受的情况下,在生产环境中进行 DR 演练;(b) 将数据库重新定位到另一区域;(c) 解决服务中断问题后将数据库恢复到主要区域(故障回复)。Planned failover is designed for following scenarios: (a) to perform DR drills in production when the data loss is not acceptable; (b) to relocate the database to a different region; and (c) to return the database to the primary region after the outage has been mitigated (failback).

  • 未计划的故障转移Unplanned failover

    计划外故障转移或强制故障转移立即将辅助角色切换为主要角色,而不与主要节点进行任何同步。Unplanned or forced failover immediately switches the secondary to the primary role without any synchronization with the primary. 任何已提交到主数据库但未复制到辅助数据库的事务都会丢失。Any transactions committed to the primary but not replicated to the secondary will be lost. 根据设计,当主数据库在服务中断期间不可访问,而数据库可用性必须快速恢复时,可将此操作用作恢复方法。This operation is designed as a recovery method during outages when the primary is not accessible, but the database availability must be quickly restored. 原始主数据库重新联机后,会自动重新进行连接,并成为新的辅助数据库。When the original primary is back online it will automatically re-connect and become a new secondary. 所有在故障转移之前未同步的事务会保留在备份文件中,但不会与新的主数据库同步,以免出现冲突。All unsynchronized transactions before the failover will be preserved in the backup file but will not be synchronized with the new primary to avoid conflicts. 需要通过手动方式将这些事务与主数据库的最新版本合并。These transactions will have to be manually merged with the most recent version of the primary database.

  • 多个可读的辅助数据库Multiple readable secondaries

    可以为每个主要数据库创建最多 4 个辅助数据库。Up to 4 secondary databases can be created for each primary. 如果只有一个辅助数据库,一旦它发生故障,应用程序就会遭受更高的风险,直到创建了新的辅助数据库。If there is only one secondary database, and it fails, the application is exposed to higher risk until a new secondary database is created. 如果存在多个辅助数据库,即使其中一个辅助数据库发生故障,应用程序仍会受到保护。If multiple secondary databases exist, the application remains protected even if one of the secondary databases fails. 也可使用其他辅助数据库来横向扩展只读工作负荷。The additional secondaries can also be used to scale out the read-only workloads

  • 在弹性池中异地复制数据库Geo-replication of databases in an elastic pool

    每个辅助数据库可单独参与弹性池或根本不在弹性池中。Each secondary database can separately participate in an elastic pool or not be in any elastic pool at all. 每个辅助数据库的池选择是单独的,并且不会依赖任何其他辅助数据库的配置(无论是主数据库还是辅助数据库)。The pool choice for each secondary database is separate and does not depend upon the configuration of any other secondary database (whether primary or secondary). 每个弹性池都包含在一个区域内,因此,同一拓扑中的多个辅助数据库永远无法共享弹性池。Each elastic pool is contained within a single region, therefore multiple secondary databases in the same topology can never share an elastic pool.

  • 用户控制的故障转移和故障回复User-controlled failover and failback

    应用程序或用户可随时会辅助数据库显式切换到主角色。A secondary database can explicitly be switched to the primary role at any time by the application or the user. 在实际服务中断期间,应使用“计划外”选项,这会立即将辅助数据库升级为主数据库。During a real outage the "unplanned" option should be used, which immediately promotes a secondary to be the primary. 当出现故障的主数据库恢复并再次可用时,系统会自动将恢复的主数据库标记为辅助数据库并使其与新的主数据库保持最新状态。When the failed primary recovers and is available again, the system automatically marks the recovered primary as a secondary and bring it up-to-date with the new primary. 由于复制的异步特性,在未计划的故障转移期间,如果主数据库在将最新的更改复制到辅助数据库之前出现故障,则可能会丢失少量数据。Due to the asynchronous nature of replication, a small amount of data can be lost during unplanned failovers if a primary fails before it replicates the most recent changes to the secondary. 当具有多个辅助数据库的主数据库进行故障转移时,系统自动重新配置复制关系,并将剩余辅助数据库链接到新升级的主数据库,无需任何用户的干预。When a primary with multiple secondaries fails over, the system automatically reconfigures the replication relationships and links the remaining secondaries to the newly promoted primary without requiring any user intervention. 导致了故障转移的服务中断得到缓解后,可能需要将应用程序返回到主要区域。After the outage that caused the failover is mitigated, it may be desirable to return the application to the primary region. 为此,应使用“计划内”选项调用故障转移命令。To do that, the failover command should be invoked with the "planned" option.

准备要进行故障转移的辅助数据库Preparing secondary database for failover

若要确保应用程序可以在故障转移后立即访问新的主数据库,请确保正确配置辅助服务器和数据库的身份验证要求。To ensure that your application can immediately access the new primary after failover, ensure the authentication requirements for your secondary server and database are properly configured. 有关详细信息,请参阅 SQL Database security after disaster recovery(灾难恢复后的 Azure SQL 数据库安全性)。For details, see SQL Database security after disaster recovery. 若要保证故障转移后的符合性,请确保辅助数据库上的备份保留策略与主数据库的匹配。To guarantee compliance after failover, make sure that the backup retention policy on the secondary database matches that of the primary. 这些设置不是数据库的一部分,因此不会进行复制。These settings are not part of the database and are not replicated. 默认情况下,会为辅助数据库配置七天的默认 PITR 保留期。By default, the secondary will be configured with a default PITR retention period of seven days. 有关详细信息,请参阅 SQL 数据库自动备份For details, see SQL Database automated backups.


如果数据库是故障转移组的成员,则无法使用异地复制故障转移命令启动其故障转移。If your database is a member of a failover group, you cannot initiate its failover using the geo-replication failover command. 对该组使用故障转移命令。Use the failover command for the group. 如果需要故障转移单个数据库,则必须先将其从故障转移组中删除。If you need to failover an individual database, you must remove it from the failover group first. 有关详细信息,请参阅故障转移组See failover groups for details.

配置辅助数据库Configuring secondary database

主数据库和辅助数据库都需要有相同的服务层级。Both primary and secondary databases are required to have the same service tier. 另外,强烈建议创建与主数据库具有相同计算大小(DTU 或 vCore)的辅助数据库。It is also strongly recommended that the secondary database is created with the same compute size (DTUs or vCores) as the primary. 如果主数据库遇到很大的写入工作负载,则计算较小的辅助数据库可能在进度上跟不上主数据库。If the primary database is experiencing a heavy write workload, a secondary with lower compute size may not be able to keep up with it. 这会导致辅助数据库上出现重做滞后,并且可能会导致辅助数据库不可用。That will cause redo lag on the secondary, and potential unavailability of the secondary. 为了缓解这些风险,必要时,活动异地复制会限制主数据库的事务日志速率,让辅助数据库能够跟上进度。To mitigate these risks, active geo-replication will throttle the primary's transaction log rate if necessary to allow its secondaries to catch up.

辅助数据库的配置不平衡的另一结果是,在故障转移后,应用程序的性能可能会由于新的主数据库的计算能力不足而受影响。Another consequence of an imbalanced secondary configuration is that after failover, application performance may suffer due to insufficient compute capacity of the new primary. 在这种情况下,需要将数据库服务目标纵向扩展到所需的级别。这可能会占用大量时间和计算资源,并且在纵向扩展过程结束时需要执行高可用性故障转移。In that case, it will be necessary to scale up database service objective to the necessary level, which may take significant time and compute resources, and will require a high availability failover at the end of the scale up process.

如果决定创建计算大小较小的辅助数据库,可参阅 Azure 门户中的日志 IO 百分比图表,它可以很好地评估维持复制负载所需的辅助数据库的最小计算大小。If you decide to create the secondary with lower compute size, the log IO percentage chart in Azure portal provides a good way to estimate the minimal compute size of the secondary that is required to sustain the replication load. 例如,如果主数据库是 P6 (1000 DTU),其日志写入百分比为 50%,则辅助数据库至少必须是 P4 (500 DTU)。For example, if your primary database is P6 (1000 DTU) and its log write percent is 50%, the secondary needs to be at least P4 (500 DTU). 若要检索历史日志 IO 数据,请使用 sys.resource_stats 视图。To retrieve historical log IO data, use the sys.resource_stats view. 若要以更高粒度检索最新的日志写入数据,以便更好地反映日志速率的短期峰值,请使用 sys.dm_db_resource_stats 视图。To retrieve recent log write data with higher granularity that better reflects short-term spikes in log rate, use sys.dm_db_resource_stats view.

由于辅助数据库上的计算大小较小而在主数据库上进行的事务日志速率限制是使用 HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO 等待类型报告的,可通过 sys.dm_exec_requestssys.dm_os_wait_stats 数据库视图查看。Transaction log rate throttling on the primary due to lower compute size on a secondary is reported using the HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO wait type, visible in the sys.dm_exec_requests and sys.dm_os_wait_stats database views.


主数据库上的事务日志速率也可能会因与辅助数据库上计算大小较小无关的原因而受限制。Transaction log rate on the primary may be throttled for reasons unrelated to lower compute size on a secondary. 即使辅助数据库上的计算大小等于或大于主数据库上的计算大小,也可能会发生这种限制。This kind of throttling may occur even if the secondary has the same or higher compute size than the primary.

有关 SQL 数据库计算大小的详细信息,请参阅什么是 SQL 数据库服务层级For more information on the SQL Database compute sizes, see What are SQL Database Service Tiers.

保持凭据和防火墙规则同步Keeping credentials and firewall rules in sync

建议对异地复制数据库使用数据库级 IP 防火墙规则,以便这些规则可与数据库一起复制,确保所有辅助数据库具有与主数据库相同的 IP 防火墙规则。We recommend using database-level IP firewall rules for geo-replicated databases so these rules can be replicated with the database to ensure all secondary databases have the same IP firewall rules as the primary. 此方法不再需要客户手动配置和维护承载主数据库和辅助数据库的服务器上的防火墙规则。This approach eliminates the need for customers to manually configure and maintain firewall rules on servers hosting both the primary and secondary databases. 同样,将包含的数据库用户用于数据访问可确保主数据库和辅助数据库始终具有相同的用户凭据,以便在故障转移期间,不会因登录名和密码不匹配而产生中断。Similarly, using contained database users for data access ensures both primary and secondary databases always have the same user credentials so during a failover, there is no disruptions due to mismatches with logins and passwords. 通过添加 Azure Active Directory,客户可以管理主数据库和辅助数据库的用户访问权限,且不再需要同时管理数据库中的凭据。With the addition of Azure Active Directory, customers can manage user access to both primary and secondary databases and eliminating the need for managing credentials in databases altogether.

升级或降级主数据库Upgrading or downgrading primary database

无需断开连接任何辅助数据库,即可将主数据库升级或降级到不同的计算大小(在相同的服务层级中,但不在“常规用途”与“业务关键”类型之间)。You can upgrade or downgrade a primary database to a different compute size (within the same service tier, not between General Purpose and Business Critical) without disconnecting any secondary databases. 升级时,建议先升级辅助数据库,再升级主数据库。When upgrading, we recommend that you upgrade the secondary database first, and then upgrade the primary. 降级时,应反转顺序:先降级主数据库,再降级辅助数据库。When downgrading, reverse the order: downgrade the primary first, and then downgrade the secondary. 将数据库升级或降级到不同服务层级时,将强制执行此建议操作。When you upgrade or downgrade the database to a different service tier, this recommendation is enforced.


如果辅助数据库是作为故障转移组配置的一个部分创建的,则我们不建议对辅助数据库进行降级。If you created secondary database as part of the failover group configuration it is not recommended to downgrade the secondary database. 这是为了确保激活故障转移后,数据层有足够的容量来处理常规工作负荷。This is to ensure your data tier has sufficient capacity to process your regular workload after failover is activated.


不能将故障转移组中的主数据库扩展到更高的层,除非已先将辅助数据库扩展到该层。The primary database in a failover group can't scale to a higher tier unless the secondary database is first scaled to the higher tier. 如果尝试在扩展辅助数据库之前扩展主数据库,可能会收到以下错误:If you try to scale the primary database before the secondary database is scaled, you might receive the following error:

Error message: The source database 'Primaryserver.DBName' cannot have higher edition than the target database 'Secondaryserver.DBName'. Upgrade the edition on the target before upgrading the source.

防止丢失关键数据Preventing the loss of critical data

由于广域网的延迟时间较长,连续复制使用了异步复制机制。Due to the high latency of wide area networks, continuous copy uses an asynchronous replication mechanism. 在发生故障时,异步复制会不可避免地丢失某些数据。Asynchronous replication makes some data loss unavoidable if a failure occurs. 但是,某些应用程序可能要求不能有数据丢失。However, some applications may require no data loss. 为了保护这些关键更新,应用程序开发人员可以在提交事务后立即调用 sp_wait_for_database_copy_sync 系统过程。To protect these critical updates, an application developer can call the sp_wait_for_database_copy_sync system procedure immediately after committing the transaction. 调用 sp_wait_for_database_copy_sync 会阻止调用线程,直到将上次提交的事务传输到辅助数据库。Calling sp_wait_for_database_copy_sync blocks the calling thread until the last committed transaction has been transmitted to the secondary database. 但是,它不会等待传输的事务提交到辅助数据库进行重播。However, it does not wait for the transmitted transactions to be replayed and committed on the secondary. sp_wait_for_database_copy_sync 的范围是一个具体的连续复制链路。sp_wait_for_database_copy_sync is scoped to a specific continuous copy link. 对主数据库具有连接权限的任何用户都可以调用此过程。Any user with the connection rights to the primary database can call this procedure.


sp_wait_for_database_copy_sync 将在故障转移后防止数据丢失,但它不会保证读取访问的完全同步。sp_wait_for_database_copy_sync prevents data loss after failover, but does not guarantee full synchronization for read access. sp_wait_for_database_copy_sync 过程调用导致的延迟可能会很明显,具体取决于调用时的事务日志大小。The delay caused by a sp_wait_for_database_copy_sync procedure call can be significant and depends on the size of the transaction log at the time of the call.

监视异地复制延迟Monitoring geo-replication lag

若要监视与 RPO 相关的延迟,请使用主数据库中 sys.dm_geo_replication_link_statusreplication_lag_sec 列。To monitor lag with respect to RPO, use replication_lag_sec column of sys.dm_geo_replication_link_status on the primary database. 它显示在主数据库上提交的事务与在辅助数据库上保留的事务之间的延迟(以秒为单位)。It shows lag in seconds between the transactions committed on the primary and persisted on the secondary. 例如E.g. 如果延迟值为 1 秒,则意味着如果主数据库现在受到某个中断的影响并启动了故障转移,则不会保存最近 1 秒执行的事务。if the value of the lag is 1 second, it means if the primary is impacted by an outage at this moment and failover is initiated, 1 second of the most recent transitions will not be saved.

若要以在主数据库上所做的更改应用到辅助数据库(即可以从辅助数据库读取)所需的时间来衡量延迟,请将辅助数据库上的 last_commit 时间与主数据库上的同一值进行比较。To measure lag with respect to changes on the primary database that have been applied on the secondary, i.e. available to read from the secondary, compare last_commit time on the secondary database with the same value on the primary database.


有时候,主数据库上的 replication_lag_sec 的值为 NULL,这意味着主数据库目前不知道辅助数据库辅助数据库有多远。Sometimes replication_lag_sec on the primary database has a NULL value, which means that the primary does not currently know how far the secondary is. 这通常发生在进程重启之后,应该是一个暂时情况。This typically happens after process restarts and should be a transient condition. 如果 replication_lag_sec 在长时间内一直返回 NULL,考虑向应用程序报警。Consider alerting the application if the replication_lag_sec returns NULL for an extended period of time. 这表示辅助数据库因永久连接故障而无法与主数据库通信。It would indicate that the secondary database cannot communicate with the primary due to a permanent connectivity failure. 此外还有情况可能会导致辅助数据库上的 last_commit 时间与主数据库上的该时间的差异变得很大。There are also conditions that could cause the difference between last_commit time on the secondary and on the primary database to become large. 例如E.g. 如果在长期没有进行更改的情况下进行提交,则该差异会突然变成一个很大的值,然后快速回到 0。if a commit is made on the primary after a long period of no changes, the difference will jump up to a large value before quickly returning to 0. 如果这两个值之间的差异长时间保持很大,可将其视为一种错误情况。Consider it an error condition when the difference between these two values remains large for a long time.

以编程方式管理活动异地复制Programmatically managing active geo-replication

如上所述,也可以使用 Azure PowerShell 和 REST API 以编程方式管理活动异地复制。As discussed previously, active geo-replication can also be managed programmatically using Azure PowerShell and the REST API. 下表描述了可用的命令集。The following tables describe the set of commands available. 活动异地复制包括一组用于管理的 Azure 资源管理器 API,其中包括 Azure SQL 数据库 REST APIAzure PowerShell cmdletActive geo-replication includes a set of Azure Resource Manager APIs for management, including the Azure SQL Database REST API and Azure PowerShell cmdlets. 这些 API 需要使用资源组,并支持基于角色的安全性 (RBAC)。These APIs require the use of resource groups and support role-based security (RBAC). 有关如何实现访问角色的详细信息,请参阅 Azure 基于角色的访问控制For more information on how to implement access roles, see Azure Role-Based Access Control.

T-SQL:管理单一数据库和共用数据库的故障转移T-SQL: Manage failover of single and pooled databases


这些 Transact-SQL 命令仅适用于活动异地复制,不适用于故障转移组。These Transact-SQL commands only apply to active geo-replication and do not apply to failover groups. 因此,它们也不适用于 SQL 托管实例的实例,因为它们仅支持故障转移组。As such, they also do not apply to instances of SQL Managed Instance, as they only support failover groups.

命令Command 说明Description
ALTER DATABASEALTER DATABASE 使用 ADD SECONDARY ON SERVER 参数为现有数据库创建辅助数据库,并开始数据复制Use ADD SECONDARY ON SERVER argument to create a secondary database for an existing database and starts data replication
ALTER DATABASEALTER DATABASE 使用 FAILOVER 或 FORCE_FAILOVER_ALLOW_DATA_LOSS 将辅助数据库切换为主数据库,启动故障转移Use FAILOVER or FORCE_FAILOVER_ALLOW_DATA_LOSS to switch a secondary database to be primary to initiate failover
ALTER DATABASEALTER DATABASE 使用 REMOVE SECONDARY ON SERVER 终止 SQL 数据库和指定的辅助数据库之间的数据复制。Use REMOVE SECONDARY ON SERVER to terminate a data replication between a SQL Database and the specified secondary database.
sys.geo_replication_linkssys.geo_replication_links 返回有关服务器上每个数据库的所有现有复制链接的信息。Returns information about all existing replication links for each database on a server.
sys.dm_geo_replication_link_statussys.dm_geo_replication_link_status 获取有关给定数据库的复制链接的上次复制时间、上次复制滞后时间和其他信息。Gets the last replication time, last replication lag, and other information about the replication link for a given database.
sys.dm_operation_statussys.dm_operation_status 显示所有数据库操作的状态,包括复制链接的状态。Shows the status for all database operations including the status of the replication links.
sp_wait_for_database_copy_syncsp_wait_for_database_copy_sync 使应用程序等待,直到所有提交的事务已复制,并由活动辅助数据库确认。causes the application to wait until all committed transactions are replicated and acknowledged by the active secondary database.

PowerShell:管理单一数据库和共用数据库的故障转移PowerShell: Manage failover of single and pooled databases


本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.


PowerShell Azure 资源管理器模块仍受 Azure SQL 数据库的支持,但所有未来的开发都是针对 Az.Sql 模块的。The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. 若要了解这些 cmdlet,请参阅 AzureRM.SqlFor these cmdlets, see AzureRM.Sql. Az 模块和 AzureRm 模块中的命令参数大体上是相同的。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

CmdletCmdlet 说明Description
Get-AzSqlDatabaseGet-AzSqlDatabase 获取一个或多个数据库。Gets one or more databases.
New-AzSqlDatabaseSecondaryNew-AzSqlDatabaseSecondary 为现有数据库创建辅助数据库,并开始数据复制。Creates a secondary database for an existing database and starts data replication.
Set-AzSqlDatabaseSecondarySet-AzSqlDatabaseSecondary 将辅助数据库切换为主数据库,启动故障转移。Switches a secondary database to be primary to initiate failover.
Remove-AzSqlDatabaseSecondaryRemove-AzSqlDatabaseSecondary 终止 SQL 数据库和指定的辅助数据库之间的数据复制。Terminates data replication between a SQL Database and the specified secondary database.
Get-AzSqlDatabaseReplicationLinkGet-AzSqlDatabaseReplicationLink 获取 Azure SQL 数据库和资源组或逻辑 SQL 服务器之间的异地复制链路。Gets the geo-replication links between an Azure SQL Database and a resource group or logical SQL server.

REST API:管理单一数据库和共用数据库的故障转移REST API: Manage failover of single and pooled databases

APIAPI 说明Description
创建或更新数据库 (createMode=Restore)Create or Update Database (createMode=Restore) 创建、更新或还原主数据库或辅助数据库。Creates, updates, or restores a primary or a secondary database.
获取创建或更新数据库状态Get Create or Update Database Status 返回创建操作过程中的状态。Returns the status during a create operation.
将辅助数据库设为主数据库(计划的故障转移)Set Secondary Database as Primary (Planned Failover) 通过来自当前主要数据库的故障转移设置辅助数据库。Sets which secondary database is primary by failing over from the current primary database. SQL 托管实例不支持此选项。This option is not supported for SQL Managed Instance.
将辅助数据库设为主数据库(未计划的故障转移)Set Secondary Database as Primary (Unplanned Failover) 通过来自当前主要数据库的故障转移设置辅助数据库。Sets which secondary database is primary by failing over from the current primary database. 此操作可能导致数据丢失。This operation might result in data loss. SQL 托管实例不支持此选项。This option is not supported for SQL Managed Instance.
获取复制链接Get Replication Link 获取异地复制合作关系中给定数据库的特定复制链接。Gets a specific replication link for a given database in a geo-replication partnership. 它检索 sys.geo_replication_links 目录视图中可见的信息。It retrieves the information visible in the sys.geo_replication_links catalog view. SQL 托管实例不支持此选项。This option is not supported for SQL Managed Instance.
复制链接 - 按数据库列出Replication Links - List By Database 获取异地复制合作关系中给定数据库的所有复制链接。Gets all replication links for a given database in a geo-replication partnership. 它检索 sys.geo_replication_links 目录视图中可见的信息。It retrieves the information visible in the sys.geo_replication_links catalog view.
删除复制链接Delete Replication Link 删除数据库复制链接。Deletes a database replication link. 在故障转移期间无法执行。Cannot be done during failover.

后续步骤Next steps