建立和使用主動式異地複寫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:

主動式異地複寫會利用 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. 它也不保證可用性區域的隔離。It will also not guarantee availability zone isolation. 使用業務關鍵或高階服務層級搭配區域冗余設定, 以達到可用性區域隔離。Use Business critical or Premium service tier with zone redundant configuration to achieve availability zone isolation.

  • 計劃性容錯移轉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


    如果您使用主動式異地複寫建置分散在世界各地的應用程式,而且必須在四個以上的區域中提供唯讀的資料存取,您可以建立次要資料庫的次要資料庫 (稱為鏈結的程序)。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.

  • 使用者控制的容錯移轉和容錯回復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 安全性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 保留期限 (7 天)。By default, the secondary will be configured with a default PITR retention period of seven days. 如需詳細資訊,請參閱 SQL Database 自動備份For details, see SQL Database automated backups.

正在設定次要資料庫Configuring 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. 如果主資料庫遇到大量寫入的工作負載, 則計算大小較低的次要複本可能無法跟上。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. 這會導致次要複本上的重做延遲, 而且可能無法用。It will cause the redo lag on the secondary and potential unavailability. 落後主要資料庫的次要資料庫也可能在需要強制容錯移轉時,有遺失大量資料的風險。A secondary database that is lagging behind the primary also risks a large data loss should a forced failover be required. 若要降低這些風險, 有效的主動式異地複寫將會節流主要的記錄速率, 讓次要複本得以趕上。To mitigate these risks, effective active geo-replication will throttle the primary's log rate to allow its secondaries to catch up. 不平衡次要設定的另一個結果是, 在容錯移轉之後, 應用程式的效能會因為新主資料庫的計算容量不足而受到影響。The other consequence of an imbalanced secondary configuration is that after failover the application’s performance will suffer due to insufficient compute capacity of the new primary. 將需要升級至較高的計算, 才能達到所需的層級, 直到降低中斷的情況下, 才會發生這種情況。It will be required to upgrade to a higher compute to the necessary level, which will not be possible until the outage is mitigated.


除非使用與主要複本相同的計算大小來設定次要資料庫, 否則無法保證已發佈的 RPO = 5 秒。The published RPO = 5 sec cannot be guaranteed unless the secondary database is configured with the same compute size as the primary.

如果您決定建立具有較低計算大小的次要資料庫,您可以利用 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. 系統會在_exec_requestssys.databases _os_wait_stats資料庫 views 中, 將節流回報為 HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO 等候狀態。The throttling is reported as a HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO wait state in the sys.dm_exec_requests and sys.dm_os_wait_stats database views.

如需 SQL Database 計算大小的詳細資訊,請參閱 SQL Database 服務層是什麼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 相關的 lag, 請在主資料庫上使用sys.databases _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. 如果 lag 的值為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.

若要針對已在次要上套用的主資料庫變更來測量 lag (也就是可從次要複本讀取), 請將次要資料庫上的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.


Azure SQL Database 仍然支援 PowerShell Azure Resource Manager 模組, 但所有未來的開發都是針對 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, 請參閱AzureRMFor 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