创建并使用活动异地复制Creating and using active geo-replication

作用中異地複寫是 Azure SQL Database 的功能,可讓您在相同或不同資料中心 (區域) 中的 SQL Database 伺服器上建立個別資料庫的可讀取次要資料庫。Active geo-replication is Azure SQL Database feature that allows you to create readable secondary databases of individual databases on a SQL Database server in the same or different data center (region).


受控執行個體不支援作用中異地複寫。Active geo-replication is not supported by managed instance. 針對受控執行個體的異地容錯移轉,請使用自動容錯移轉群組For geographic failover of managed instances, 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. 下圖說明使用作用中異地複寫之異地備援雲端應用程式的一般設定。The following diagram illustrates a typical configuration of a geo-redundant cloud application using Active geo-replication.



SQL Database 也支援自動容錯移轉群組。SQL Database also supports auto-failover groups. 如需詳細資訊,請參閱使用自動容錯移轉群組For more information, see using auto-failover groups. 此外,在受控執行個體中建立的資料庫不支援作用中異地複寫。Also, active geo-replication is not supported for databases created within a Managed Instance. 請考慮使用容錯移轉叢集搭配受控執行個體。Consider using failover groups with Managed Instances.

若您的主要資料庫因為任何原因而失敗,或只需要離線,您可以起始容錯移轉至任何次要資料庫。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:

容錯移轉之後,請確認已在新的主要資料庫上設定伺服器和資料庫的驗證需求。After failover, ensure the authentication requirements for your server and database are configured on the new primary. 如需詳細資訊,請參閱 災害復原後的 SQL Database 安全性For details, see SQL Database security after disaster recovery.

主動式異地複寫會利用 SQL Server 的 Always On 技術,使用快照隔離,以非同步方式將主要資料庫上認可的交易複寫到次要資料庫。Active geo-replication leverages the Always On technology of SQL Server 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 North Central US region and secondary in the South Central US 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. 您可以在任何 Azure SQL Database 伺服器建立次要資料庫。The secondary can be created in any Azure SQL Database 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. 它也不保證會 avaialability 區域隔離。It will also not guarantee avaialability zone isolation. 使用商務關鍵性或使用 Premium 服務層區域備援設定以達到 avaialability 區域隔離。Use Business critical or Premium service tier with zone redundant configuration to achieve avaialability zone isolation.

  • 計劃性容錯移轉Planned failover

    計劃性容錯移轉會在主要資料庫和次要資料庫之間執行完整同步處理,然後將次要資料庫切換為主要角色。Planned failover performs full synchronization between primary and secondary databases before the secondary switches to the primary role. 這可確保不會遺失資料。This guarantees no data loss. 計劃性容錯移轉用於下列案例:(a) 在生產環境中執行 DR 鑽研而不接受遺失資料的時候;(b) 將資料庫重新放置到不同區域;(c) 在中斷已經緩和 (容錯回復) 之後,將資料庫歸回至主要區域。Planned failover is used the 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. 這項作業會導致資料遺失。This operation will result in data loss. 非計劃性容錯移轉是主要資料庫無法存取時,用來作為中斷期間的復原方法。Unplanned failover is used as a recovery method during outages when the primary is not accessible. 當原始的主要資料庫重新上線時,它會自動重新連線而不進行同步處理,並且變為新的次要資料庫。When the original primary is back online, it will automatically re-connect without synchronization and become a new secondary.

  • 多個次要資料庫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


    如果您使用主動式異地複寫建置分散在世界各地的應用程式,而且必須在四個以上的區域中提供唯讀的資料存取,您可以建立次要資料庫的次要資料庫 (稱為鏈結的程序)。If you are using active geo-replication to build a globally distributed application and need to provide read-only access to data in more than four regions, you can create secondary of a secondary (a process known as chaining). 如此一來您就可以達到幾乎無限擴充的資料庫複寫。This way you can achieve virtually unlimited scale of database replication. 此外,鏈結可減少來自主要資料庫的複寫額外負荷。In addition, chaining reduces the overhead of replication from the primary database. 缺點是會增加分葉最尾端之次要資料庫上的複寫延遲。The trade-off is the increased replication lag on the leaf-most secondary databases.

  • 彈性集區中的資料庫異地複寫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.

  • 次要資料庫的可設定計算大小Configurable compute size of the secondary database

    主要和次要資料庫必須有相同的服務層級。Both primary and secondary databases are required to have the same service tier. 此外也強烈建議您使用與主要資料庫相同的計算大小 (DTU 或虛擬核心) 來建立次要資料庫。It is also strongly recommended that secondary database is created with the same compute size (DTUs or vCores) as the primary. 計算大小較低的次要資料庫可能會有複寫延遲增加和無法使用的風險,進而有在容錯移轉之後遺失重要資料的風險。A secondary with lower compute size is at risk of an increased replication lag, potential unavailability of the secondary, and consequently at risk of substantial data loss after a failover. 因此,將無法保證已發佈的 RPO = 5 秒。As a result, the published RPO = 5 sec cannot be guaranteed. 另一個風險是,在容錯移轉之後,應用程式的效能會因為新主要資料庫的計算容量不足而受到影響,除非該資料庫升級至較高的計算大小。The other risk is that after failover the application’s performance will be impacted due to a lack of compute capacity of the new primary until it is upgraded to a higher compute size. 升級所需時間視資料庫大小而定。The time of the upgrade depends on the database size. 此外,目前此類升級要求主要與次要資料庫都必須在線上,因此在中斷情況趨緩之前無法完成。In addition, currently such upgrade requires that both primary and secondary databases are online and, therefore, cannot be completed until the outage is mitigated. 如果您決定建立具有較低計算大小的次要資料庫,您可以利用 Azure 入口網站上的記錄 IO 百分比圖表,來預估次要資料庫承受複寫負載所需的最低計算大小。If you decide to create the secondary with lower compute size, the log IO percentage chart on 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) 和其記錄 IO 百分比為 50%,則次要資料庫必須至少是 P4 (500 DTU)。For example, if your Primary database is P6 (1000 DTU) and its log IO percent is 50% the secondary needs to be at least P4 (500 DTU). 您也可以使用 sys.resource_statssys.dm_db_resource_stats 資料庫檢視來擷取記錄 IO 資料。You can also retrieve the log IO data using sys.resource_stats or sys.dm_db_resource_stats database views. 如需 SQL Database 計算大小的詳細資訊,請參閱 SQL Database 服務層是什麼For more information on the SQL Database compute sizes, see What are SQL Database Service Tiers.

  • 使用者控制的容錯移轉和容錯回復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.

  • 保持認證和防火牆規則同步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 a 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 intiated, 1 second of the most recent transtions 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 Resource Manager API,包括 Azure SQL Database 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. 因此它們也不適用於受控執行個體,因為受控執行個體僅支援容錯移轉群組。As such, they also do not apply to Managed Instances, 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 Database 和指定次要資料庫間的資料複寫。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 針對 Azure SQL Database 伺服器上的每個資料庫,傳回所有現有複寫連結的相關資訊。Returns information about all existing replication links for each database on the Azure SQL Database server.
sys.dm_geo_replication_link_statussys.dm_geo_replication_link_status 針對指定的 SQL Database,取得上次複寫時間、上次複寫延遲,以及複寫連結的其他相關資訊。Gets the last replication time, last replication lag, and other information about the replication link for a given SQL 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 Database 和指定次要資料庫間的資料複寫。Terminates data replication between a SQL Database and the specified secondary database.
Get-AzSqlDatabaseReplicationLinkGet-AzSqlDatabaseReplicationLink 取得 Azure SQL Database 和資源群組或 SQL Server 之間的異地複寫連結。Gets the geo-replication links between an Azure SQL Database and a resource group or SQL Server.

REST API:管理單一和集區資料庫的容錯移轉REST API: Manage failover of single and pooled databases

APIAPI 描述Description
Create or Update Database (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. 此選項不支援受控執行個體。This option is not supported for 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. 此選項不支援受控執行個體。This option is not supported for Managed Instance.
取得複寫連結Get Replication Link 取得異地複寫關聯性中指定 SQL Database 的特定複寫連結。Gets a specific replication link for a given SQL database in a geo-replication partnership. 它會擷取 sys.geo_replication_links 目錄檢視中顯示的資訊。It retrieves the information visible in the sys.geo_replication_links catalog view. 此選項不支援受控執行個體。This option is not supported for Managed Instance.
複寫連結 - 依資料庫列示Replication Links - List By Database 取得異地複寫關聯性中指定 SQL Database 的所有複寫連結。Gets all replication links for a given SQL 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