建立和使用主動式異地複寫-Azure SQL DatabaseCreating and using active geo-replication - Azure SQL Database

適用於: 是Azure SQL DatabaseAPPLIES TO: yesAzure SQL Database

主動式異地複寫是一項 Azure SQL Database 的功能,可讓您在相同或不同資料中心(區域)中的伺服器上,建立個別資料庫的可讀取次要資料庫。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 Database 也支援自動容錯移轉群組。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 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. 可以在任何伺服器上建立次要資料庫。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. 它也不保證可用性區域的隔離。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.


如果您的資料庫是容錯移轉群組的成員,您就無法使用異地複寫容錯移轉命令來起始其容錯移轉。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 或虛擬核心)來建立次要資料庫。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 資料,請使用resource_stats view。To retrieve historical log IO data, use the sys.resource_stats view. 若要以較高的資料細微性來抓取最新的記錄寫入資料,以更精確地反映記錄速率的短期尖峰,請使用sys.databases dm_db_resource_stats view。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.databases dm_exec_requestssys.databases 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. 如需詳細資料,包括不同類型記錄速率節流的等候類型,請參閱交易記錄速率治理For details, including wait types for different kinds of log rate throttling, see Transaction log rate governance.

如需 SQL Database 計算大小的詳細資訊,請參閱 SQL Database 服務層是什麼For more information on the SQL Database compute sizes, see What are SQL Database Service Tiers.

跨訂用帳戶異地複寫Cross-subscription geo-replication

若要在屬於不同訂用帳戶的兩個資料庫之間設定主動式異地複寫(不論是在相同的租使用者之下),您必須遵循本節所述的特殊程式。To setup active geo-replication between two databases belonging to different subscriptions (whether under the same tenant or not), you must follow the special procedure described in this section. 此程式是以 SQL 命令為基礎,而且需要:The procedure is based on SQL commands and requires:

  • 在兩部伺服器上建立特殊許可權登入Creating a privileged login on both servers
  • 將 IP 位址新增到在這兩部伺服器上執行變更的用戶端允許清單(例如,SQL Server Management Studio 的主機 IP 位址)。Adding the IP address to the allow list of the client performing the change on both servers (such as the IP address of the host running SQL Server Management Studio).

執行變更的用戶端需要主伺服器的網路存取權。The client performing the changes needs network access to the primary server. 雖然您必須將相同的用戶端 IP 位址新增至次要伺服器上的允許清單,但並不是絕對需要與次要伺服器的網路連線。Although the same IP address of the client must be added to the allow list on the secondary server, network connectivity to the secondary server is not strictly required.

在主伺服器的主機上On the master of the primary server

  1. 將 IP 位址新增至執行變更之用戶端的允許清單(如需詳細資訊,請參閱設定防火牆)。Add the IP address to the allow list of the client performing the changes (for more information see, Configure firewall).

  2. 建立專門用來設定主動式異地複寫的登入(並視需要調整認證):Create a login dedicated to setup active geo-replication (and adjust the credentials as needed):

    create login geodrsetup with password = 'ComplexPassword01'
  3. 建立對應的使用者,並將它指派給 dbmanager 角色:Create a corresponding user and assign it to the dbmanager role:

    create user geodrsetup for login geodrsetup
    alter role dbmanager add member geodrsetup
  4. 請記下使用此查詢的新登入 SID:Take note of the SID of the new login using this query:

    select sid from sys.sql_logins where name = 'geodrsetup'

在主伺服器上的源資料庫上On the source database on the primary server

  1. 為相同的登入建立使用者:Create a user for the same login:

    create user geodrsetup for login geodrsetup
  2. 將使用者新增至 db_owner 角色:Add the user to the db_owner role:

    alter role db_owner add member geodrsetup

在次要伺服器的主要複本上On the master of the secondary server

  1. 將 IP 位址新增至執行變更之用戶端的允許清單。Add the IP address to the allow list of the client performing the changes. 它必須與主伺服器完全相同的 IP 位址。It must the same exact IP address of the primary server.

  2. 使用相同的使用者名稱密碼和 SID,在主伺服器上建立相同的登入:Create the same login as on the primary server, using the same username password, and SID:

    create login geodrsetup with password = 'ComplexPassword01', sid=0x010600000000006400000000000000001C98F52B95D9C84BBBA8578FACE37C3E
  3. 建立對應的使用者,並將它指派給 dbmanager 角色:Create a corresponding user and assign it to the dbmanager role:

    create user geodrsetup for login geodrsetup;
    alter role dbmanager add member geodrsetup

在主伺服器的主機上On the master of the primary server

  1. 使用新的登入,登入主伺服器的主伺服器。Login to the master of the primary server using the new login.

  2. 在次要伺服器上建立源資料庫的次要複本(視需要調整資料庫名稱和 servername):Create a secondary replica of the source database on the secondary server (adjust database name and servername as needed):

    alter database dbrep add secondary on server <servername>

初始設定之後,就可以移除所建立的使用者、登入和防火牆規則。After the initial setup, the users, logins, and firewall rules created can be removed.

保持認證和防火牆規則同步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 方面的延遲,請使用主資料庫上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. 如果 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 角色型存取控制(AZURE RBAC)For more information on how to implement access roles, see Azure role-based access control (Azure RBAC).

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.

CommandCommand 說明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 傳回伺服器上每個資料庫的所有現有複寫連結的相關資訊。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.


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 伺服器之間的異地複寫連結。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
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. 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