複寫、變更追蹤和異動資料擷取 - AlwaysOn 可用性群組Replication, change tracking, & change data capture - Always On availability groups

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL ServerAlwaysOn 可用性群組Always On availability groups支援複寫、異動資料擷取 (CDC) 和變更追蹤 (CT)。Replication, change data capture (CDC), and change tracking (CT) are supported on AlwaysOn 可用性群組Always On availability groups. AlwaysOn 可用性群組Always On availability groups 有助於提供高可用性以及其他資料庫復原功能。helps provide high availability and additional database recovery capabilities.

可用性群組的複寫概觀Overview of replication with availability groups

發行者重新導向Publisher Redirection

當發行的資料庫能夠感知 AlwaysOn 可用性群組Always On availability groups時,提供發行資料庫之代理程式存取權的散發者就會使用 redirected_publishers 項目來設定。When a published database is aware of AlwaysOn 可用性群組Always On availability groups, the distributor that provides agent access to the publishing database is configured with redirected_publishers entries. 這些項目會重新導向原本設定的發行者/資料庫配對,並利用可用性群組接聽程式名稱來連接到發行者和發行資料庫。These entries redirect the originally configured publisher/database pair, making use of an availability group listener name to connect to the publisher and publishing database. 透過可用性群組接聽程式名稱所建立的連接將會在容錯移轉時失敗。Established connections through the availability group listener name will fail on failover. 在容錯移轉之後,當複寫代理程式重新啟動時,連接將自動重新導向至新的主要複本。When the replication agent restarts after failover, the connection will automatically be redirected to the new primary.

在可用性群組中,次要資料庫不能是發行者。In an availability group a secondary database cannot be a publisher. 只有在異動複寫與 AlwaysOn 可用性群組Always On availability groups結合時,才支援重新發行。Republishing is only supported when transactional replication is combined with AlwaysOn 可用性群組Always On availability groups.

如果發行的資料庫是可用性群組的成員,而且發行者已重新導向,它就必須重新導向至與可用性群組相關聯的可用性群組接聽程式名稱。If a published database is a member of an availability group and the publisher is redirected, it must be redirected to an availability group listener name associated with the availability group. 它可能不會重新導向至明確節點。It may not be redirected to an explicit node.

注意

在容錯移轉到次要複本之後,複寫監視器就無法調整 SQL ServerSQL Server 發行執行個體的名稱,而且會繼續在原始主要 SQL ServerSQL Server執行個體名稱之下顯示複寫資訊。After failover to a secondary replica, Replication Monitor is unable to adjust the name of the publishing instance of SQL ServerSQL Server and will continue to display replication information under the name of the original primary instance of SQL ServerSQL Server. 在容錯移轉之後,便無法使用複寫監視器輸入追蹤 Token,但是可以在複寫監視器中看到在新的發行者端使用 Transact-SQLTransact-SQL輸入的追蹤 Token。After failover, a tracer token cannot be entered by using the Replication Monitor, however a tracer token entered on the new publisher by using Transact-SQLTransact-SQL, is visible in Replication Monitor.

支援可用性群組的複寫代理程式一般變更General changes to replication agents to support availability groups

三個複寫代理程式已修改成支援 AlwaysOn 可用性群組Always On availability groupsThree replication agents were modified to support AlwaysOn 可用性群組Always On availability groups. 記錄讀取器、快照集和合併代理程式已修改成查詢重新導向發行者的散發資料庫,並且使用傳回的可用性群組接聽程式名稱來連接到資料庫發行者 (如果已宣告重新導向發行者的話)。The Log Reader, Snapshot, and Merge agents were modified to query the distribution database for the redirected publisher and to use the returned availability group listener name, if a redirected publisher was declared, to connect to the database publisher.

根據預設,當代理程式查詢散發者以判斷原始發行者是否已重新導向時,會驗證重新導向目前目標的適用性,然後才將重新導向的主機傳回至代理程式。By default, when the agents query the distributor to determine whether the original publisher has been redirected, the suitability of the current target or redirection will be verified prior to returning the redirected host to the agent. 這是建議的行為。This is recommended behavior. 不過,如果代理程式啟動太頻繁,與驗證預存程序相關的負擔成本可能太高。However, if agent startup occurs very frequently the overhead associated with the validation stored procedure may be deemed too costly. 新命令列參數 BBypassPublisherValidation 已加入至記錄讀取器、快照集和合併代理程式。A new command-line switch, BypassPublisherValidation, has been added to the Logreader, Snapshot, and Merge agents. 使用此參數時,重新導向的主機會立即傳回至代理程式,而略過驗證預存程序的執行。When the switch is used, the redirected publisher is returned immediately to the agent and execution of the validation stored procedure is bypassed.

從驗證預存程序傳回的失敗會記錄在代理程式記錄檔中。Failures returned from the validation stored procedure are logged in the agent history logs. 嚴重性大於或等於 16 的這些錯誤會導致代理程式終止。Those errors with severity greater than or equal to 16 will cause the agents to terminate. 某些重試功能已內建到代理程式,以處理在容錯移轉至新主要複本時預期的已發行資料庫中斷連接。Some retry capabilities have been built in to the agents to handle the expected disconnect from a published database when it fails over to a new primary.

記錄讀取器代理程式修改Log Reader Agent Modifications

記錄讀取器代理程式有下列變更。The Logreader Agent has the following changes.

  • 複寫的資料庫一致性Replicated Database Consistency

    當發行的資料庫是可用性群組的成員時,記錄讀取器預設不會處理所有可用性群組次要複本上尚未強行寫入的記錄檔記錄。When a published database is a member of an availability group, by default the log reader will not process log records that have not already been hardened at all availability group secondary replicas. 這樣可確保容錯移轉時,所有複寫至訂閱者的資料列也會存在新的主要複本上。This ensures that on failover, all rows replicated to a subscriber also are present at the new primary.

    當發行者只有兩個可用性複本 (一個主要和一個次要) 且發生容錯移轉時,原始的主要複本會保持離線,因為在所有次要資料庫恢復連線或發生錯誤的次要複本從可用性群組中移除之前,Logreader 不會向前移動。When the publisher has only two availability replicas (one primary and one secondary) and a failover happens, the original primary replica remains down because the logreader does not move forward until all secondary databases are brought back online or until the failing secondary replicas are removed from the availability group. 現在針對次要資料庫執行的 Logreader 將不會繼續前進,因為 AlwaysOn 無法強行對任何次要資料庫進行任何變更。The logreader, now running against the secondary database, will not proceed forward since Always On cannot harden any changes to any secondary database. 若要讓 Logreader 繼續前進並且仍然擁有災害復原的能力,請使用 ALTER AVAILABITY GROUP <group_name> REMOVE REPLICA 從可用性群組中移除原始主要複本。To allow the logreader to proceed further and still have disaster recovery capacity, remove the original primary replica from the availability group using ALTER AVAILABITY GROUP <group_name> REMOVE REPLICA. 然後將新的次要複本加入至可用性群組。Then add a new secondary replica to the availability group.

  • 追蹤旗標 1448Trace flag 1448

    追蹤旗標 1448 可讓複寫記錄讀取器向前移動,即使非同步次要複本尚未認可收到變更也一樣。Trace flag 1448 enables the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change. 即使這個追蹤旗標已啟用,記錄讀取器也一律會等候同步次要複本。Even with this trace flag enabled, the log reader always waits for the synchronous secondary replicas. 記錄讀取器不會超過同步次要複本的最小認可。The log reader will not go beyond the min ack of the synchronous secondary replicas. 這個追蹤旗標會套用至 SQL ServerSQL Server執行個體,而不只套用至可用性群組、可用性資料庫或記錄讀取器執行個體。This trace flag applies to the instance of SQL ServerSQL Server, not just to an availability group, an availability database, or a log reader instance. 這個追蹤旗標會立即生效,不必重新啟動。This trace flag takes effect immediately without a restart. 您可以事先或在非同步次要複本失敗時啟動它。It can be activated ahead of time or when an asynchronous secondary replica fails.

支援可用性群組的預存程序Stored procedures supporting availability groups

  • sp_redirect_publishersp_redirect_publisher

    預存程序 sp_redirect_publisher 可用來指定現有發行者/資料庫配對的重新導向發行者。The stored procedure sp_redirect_publisher is used to specify a redirected publisher for an existing publisher/database pair. 如果發行者資料庫屬於可用性群組,重新導向發行者就是可用性群組接聽程式名稱。If the publisher database belongs to an availability group, the redirected publisher is the availability group listener name.

  • sp_get_redirected_publishersp_get_redirected_publisher

    預存程序 sp_get_redirected_publisher 可由複寫代理程式用來查詢散發者,以便判斷發行者/資料庫配對是否具有定義的重新導向發行者。The stored procedure sp_get_redirected_publisher is used by replication agents to query a distributor to determine whether a publisher/database pair has a defined redirected publisher. 此預存程序有兩種目的。This stored procedure serves two purposes. 首先,它可讓代理程式判斷原始發行者是否已經重新導向。First, it allows the agent to determine whether the original publisher has been redirected. 其次,它也可以在散發者端起始驗證預存程序 (sp_validate_redirected_publisher),以便驗證重新導向的目標節點是否適合作為具名資料庫的發行者。Second, it may also initiate a validation stored procedure run at the distributor (sp_validate_redirected_publisher) that verifies the suitability of the target node of the redirection to serve as a publisher for the named database.

    若要執行此預存程序,呼叫端必須是 系統管理員 伺服器角色的成員、散發資料庫的 db_owner 資料庫角色,或是與發行者資料庫相關聯之定義發行集的 發行集存取清單 的成員。To execute this stored procedure the caller must either be a member of the sysadmin server role, the db_owner database role for the distribution database, or a member of a Publication Access List for a defined publication associated with the publisher database.

  • sp_validate_redirected_publishersp_validate_redirected_publisher

    此預存程序會嘗試驗證目前的發行者是否能夠裝載發行的資料庫。This stored procedure attempts to validate that the current publisher is capable of hosting the published database. 您可以隨時呼叫此預存程序,以便確認發行之資料庫的目前主機是否能夠支援複寫。It can be called at any time to verify that the current host for the published database is capable of supporting replication.

  • sp_validate_replicate_hosts_as_publisherssp_validate_replicate_hosts_as_publishers

    雖然讓代理程式確保目前主要複本能夠作為發行者資料庫的複寫發行者運作已經很有用,不過還是需要一項更全面的驗證功能,才能確立 AlwaysOn 可用性資料庫上整個複寫拓撲的有效性。While it is useful for the agents to ensure that the current primary can function as the replication publisher for a publisher database, a more general validation capability is needed to establish the validity of an entire replication topology on an Always On availability database. 預存程序 sp_validate_replica_hosts_as_publishers 就是為了滿足這項需求所設計。The stored procedure sp_validate_replica_hosts_as_publishers is designed to fill this need.

    此預存程序一律以手動方式執行。This stored procedure is always run manually. 呼叫端必須是散發者端的系統管理員 ( sysadmin )、散發資料庫的 dbowner 或是發行者資料庫中發行集之 發行集存取清單 的成員。The caller must either be sysadmin at the distributor, dbowner of the distribution database, or a member of the Publication Access List of a publication of the publisher database. 此外,對於所有可用性複本主機而言,呼叫端的登入必須是有效的登入,而且擁有與發行者資料庫相關聯之可用性資料庫的選取權限。In addition, the login of the caller must be a valid login for all of the availability replica hosts, and have select privileges on the availability database associated with the publisher database.

異動資料擷取Change Data Capture

啟用異動資料擷取 (CDC) 的資料庫能夠運用 AlwaysOn 可用性群組Always On availability groups,以便確保資料庫在發生失敗時維持可用狀態,而且資料庫資料表的變更會繼續受到監視並且儲放在 CDC 變更資料表中。Databases enabled for change data capture (CDC) are able to leverage AlwaysOn 可用性群組Always On availability groups in order to ensure not only that the database remains available in the event of failure, but that changes to the database tables continue to be monitored and deposited in the CDC change tables. 設定 CDC 和 AlwaysOn 可用性群組Always On availability groups 的順序並不重要。The order in which CDC and AlwaysOn 可用性群組Always On availability groups are configured is not important. 啟用 CDC 的資料庫可以加入 AlwaysOn 可用性群組Always On availability groups中,而且可以啟用本身為 AlwaysOn 可用性群組成員之資料庫的 CDC。CDC enabled databases can be added to AlwaysOn 可用性群組Always On availability groups, and databases that are members of an Always On availability group can be enabled for CDC. 不過,在這兩種情況下,CDC 組態一律在目前或預期的主要複本上執行。In both cases, however, CDC configuration is always performed on the current or intended primary replica. CDC 會使用記錄讀取器代理程式,而且其限制與本主題稍早的< 記錄讀取器代理程式修改 >一節中所述的限制相同。CDC uses the log reader agent and has the same limitations as described in the Log Reader Agent Modifications section earlier in this topic.

  • 在有異動資料擷取但沒有複寫的情況下,收集變更Harvesting Changes for Change Data Capture Without Replication

    如果資料庫啟用了 CDC,但是沒有啟用複寫,用來從記錄中收集變更並將變更儲放在 CDC 變更資料表中的擷取處理序就會在 CDC 主機上當做它自己的 SQL 代理程式作業執行。If CDC is enabled for a database, but replication is not, the capture process used to harvest changes from the log and deposit them in CDC change tables runs at the CDC host as its own SQL Agent job.

    若要在容錯移轉之後繼續收集變更,您必須在新的主要複本上執行預存程序 sp_cdc_add_job ,以便建立本機擷取作業。In order to resume the harvesting of changes after failover, the stored procedure sp_cdc_add_job must be run at the new primary to create the local capture job.

    下列範例會建立擷取作業。The following example creates the capture job.

    EXEC sys.sp_cdc_add_job @job_type = 'capture';  
    
  • 在有異動資料擷取且有複寫的情況下,收集變更Harvesting Changes for Change Data Capture With Replication

    如果同時啟用了資料庫的 CDC 和複寫,記錄讀取器就會處理 CDC 變更資料表的母體擴展。If both CDC and replication are enabled for a database, the log reader handles the population of the CDC change tables. 在此情況中,複寫用來運用 AlwaysOn 可用性群組Always On availability groups 的技術將可確保系統在容錯移轉之後繼續從記錄中收集變更並儲放在 CDC 變更資料表中。In this case, the techniques used by replication to leverage AlwaysOn 可用性群組Always On availability groups will ensure that changes continue to be harvested from the log and deposited in CDC change tables after failover. 在此組態中,不需要為 CDC 執行其他動作,以確保變更資料表會擴展。Nothing additional needs to be done for CDC in this configuration to ensure that the change tables are populated.

  • 異動資料擷取清除Change Data Capture Cleanup

    為了確保新的主要資料庫會進行適當的清除,一定要建立本機清除作業。To ensure that appropriate cleanup occurs at the new primary database, a local cleanup job should always be created. 下列範例會建立清除作業。The following example creates the cleanup job.

    EXEC sys.sp_cdc_add_job @job_type = 'cleanup';  
    

    注意

    您應該在容錯移轉前於所有可能的容錯移轉目標上建立這些作業,並且將它們標示為停用,直到主機上的可用性複本變成新的主要複本為止。You should create the jobs at all of the possible failover targets before failover, and mark them as disabled until the availability replica at a host becomes the new primary replica. 當本機資料庫變成次要資料庫時,在舊主要資料庫上執行的 CDC 作業也應該停用。The CDC jobs running at the old primary database should be also disabled when the local database becomes a secondary database. 若要停用和啟用作業,請使用 sp_update_job (Transact-SQL)@enabled 選項。To disable and enable jobs, use the @enabled option of sp_update_job (Transact-SQL). 如需有關建立 CDC 作業的詳細資訊,請參閱 sys.sp_cdc_add_job (Transact-SQL)支援複寫、異動資料擷取 (CDC) 和變更追蹤 (CT)。For more information about creating CDC jobs, see sys.sp_cdc_add_job (Transact-SQL).

  • 將 CDC 角色加入 AlwaysOn 主要資料庫複本中Adding CDC Roles to an Always On Primary Database Replica

    當資料表啟用 CDC 時,有可能將資料庫角色與擷取執行個體建立關聯。When a table is enabled for CDC, it is possible to associate a database role with the capture instance. 如果指定了角色,希望使用 CDC 資料表值函式來存取資料表變更的使用者必須不只有追蹤資料表資料行的選取存取權,也必須是具名角色的成員。If a role is specified, the user wishing to use the CDC table-valued functions to access changes for the table must not only have select access to the tracked table columns, but must also be a member of the named role. 如果指定的角色尚未存在,則會建立角色。If the specified role does not already exist, the role will be created. 當資料庫角色自動加入 AlwaysOn 主要資料庫中時,這些角色也會傳播至可用性群組的次要資料庫。When database roles are automatically added to an Always On primary database, the roles are also propagated to the secondary databases of the availability group.

  • 存取 CDC 變更資料的用戶端應用程式和 AlwaysOnClient Applications Accessing CDC Change Data and Always On

    使用資料表值函式 (TVF) 或連結的伺服器存取變更資料表資料的用戶端應用程式,也需要在容錯移轉後找出適當 CDC 主機的功能。Client applications that use the table-valued functions (TVFs) or linked servers to access change table data also need the ability to locate an appropriate CDC host after failover. 可用性群組接聽程式名稱是 AlwaysOn 可用性群組Always On availability groups 所提供的機制,這項機制會以透明的方式允許連接以不同主機為新目標。The availability group listener name is the mechanism provided by AlwaysOn 可用性群組Always On availability groups to transparently allow a connection to be retargeted to a different host. 一旦可用性群組接聽程式名稱與可用性群組產生關聯之後,它就可用於 TCP 連接字串中。Once an availability group listener name is associated with an availability group, it is available to be used in TCP connection strings. 透過可用性群組接聽程式名稱支援兩個不同的連接案例。Two different connection scenarios are supported through the availability group listener name.

    • 一個確保連接要求一律導向至目前的主要複本。One ensures that connection requests are always directed to the current primary replica.

    • 一個確保連接要求會導向至唯讀的次要複本。One ensures that connection requests are directed to a read-only secondary replica.

    如果用來找出唯讀的次要複本,還必須為可用性群組定義唯讀的路由清單。If used to locate a read-only secondary replica, a read-only routing list must also be defined for the availability group. 如需可讀取次要複本之路由存取的詳細資訊,請參閱本節稍後的 若要將可用性複本設定為唯讀路由For more information about routing access to readable secondaries, see To Configure Availability Replicas for Read-Only Routing.

    注意

    建立可用性群組接聽程式名稱以及用戶端應用程式用它來存取可用性群組資料庫複本時,都會發生一些相關聯的傳播延遲。There is some propagation delay associated with the creation of an availability group listener name and its use by client applications to access an availability group database replica.

    請使用下列查詢來判斷是否已針對裝載 CDC 資料庫的可用性群組定義了可用性群組接聽程式名稱。Use the following query to determine whether an availability group listener name has been defined for the availability group hosting a CDC database. 如果已建立可用性群組接聽程式名稱,查詢會傳回它。The query will return the availability group listener name if one has been created.

    SELECT dns_name   
    FROM sys.availability_group_listeners AS l  
    INNER JOIN sys.availability_databases_cluster AS d  
        ON l.group_id = d.group_id  
    WHERE d.database_name = N'MyCDCDB';  
    
  • 將查詢負載重新導向至可讀取次要複本Redirecting the Query Load to a Readable Secondary Replica

    雖然在許多情況下用戶端應用程式一定會要連接到目前的主要複本,但這不是利用 AlwaysOn 可用性群組Always On availability groups 的唯一方法。While in many cases a client application will always want to connect to the current primary replica, that is not the only way to leverage AlwaysOn 可用性群組Always On availability groups. 如果可用性群組設定為支援可讀取的次要複本,則也可以從次要節點收集變更資料。If an availability group is configured to support readable secondary replicas, change data can also be gathered from secondary nodes.

    已設定可用性群組時,與 SECONDARY_ROLE 關聯的 ALLOW_CONNECTIONS 屬性會用來指定支援的次要存取類型。When an availability group is configured, the ALLOW_CONNECTIONS attribute associated with the SECONDARY_ROLE is used to specify the type of secondary access supported. 如果設定為 ALL,則會允許次要的所有連線,但只有需要唯讀存取的連線會成功。If configured as ALL, all connections to the secondary will be allowed, but only those requiring read-only access will succeed. 如果設定為 READ_ONLY,則需要在建立次要資料庫的連接時指定唯讀意圖,連接才會成功。If configured as READ_ONLY, it is necessary to specify read only intent when making the connection to the secondary database in order for the connection to succeed. 如需詳細資訊,請參閱設定可用性複本上的唯讀存取 (SQL Server)For more information, see Configure Read-Only Access on an Availability Replica (SQL Server).

    您可以使用下列查詢,以判斷是否需要唯讀意圖以連接到可讀取次要複本。The following query can be used to determine whether read-only intent is needed to connect to a readable secondary replica.

    SELECT g.name AS AG, replica_server_name, secondary_role_allow_connections_desc  
    FROM sys.availability_replicas AS r  
    JOIN sys.availability_groups AS g  
        ON r.group_id = g.group_id  
    WHERE g.name = N'MY_AG_NAME;  
    

    可用性群組接聽程式名稱或明確節點名稱都可用於找出次要複本。Either the availability group listener name or the explicit node name can be used to locate the secondary replica. 如果使用可用性群組接聽程式名稱,則存取會導向至任何合適的次要複本。If the availability group listener name is used, access will be directed to any suitable secondary replica.

    sp_addlinkedserver 用於建立連結的伺服器以存取次要複本時, @datasrc 參數會用於可用性群組接聽程式名稱或明確伺服器名稱,而 @provstr 參數會用於指定唯讀意圖。When sp_addlinkedserver is used to create a linked server to access the secondary, the @datasrc parameter is used for the availability group listener name or the explicit server name, and the @provstr parameter is used to specify read-only intent.

    EXEC sp_addlinkedserver   
    @server = N'linked_svr',   
    @srvproduct=N'SqlServer',  
    @provider=N'SQLNCLI11',   
    @datasrc=N'AG_Listener_Name',   
    @provstr=N'ApplicationIntent=ReadOnly',   
    @catalog=N'MY_DB_NAME';  
    
  • CDC 變更資料的用戶端存取和網域登入Client Access to CDC Change Data and Domain Logins

    一般而言,若要讓用戶端存取位於 AlwaysOn 可用性群組之成員資料庫的變更資料,您應該使用網域登入。In general, you should use domain logins for client access to change data residing in databases that are members of Always On availability groups. 為確保在容錯移轉後持續存取變更資料,網域使用者需要所有支援可用性群組複本之主機的存取權限。To ensure continued access to change data after failover, the domain user will need access privileges on all of the hosts supporting availability group replicas. 如果將資料庫使用者加入至主要複本的資料庫,而此使用者已與網域登入相關聯,則此資料庫使用者會傳播至次要資料庫並繼續與指定的網域登入相關聯。If a database user is added to a database in a primary replica, and the user is associated with a domain login, the database user is propagated to secondary databases and continues to be associated with the specified domain login. 如果新資料庫使用者與 SQL Server 驗證登入相關聯,則次要資料庫的使用者會傳播但沒有登入。If the new database user is associated with a SQL Server authentication login, the user at the secondary databases will be propagated without a login. 雖然相關 SQL ServerSQL Server 驗證登入可用來存取原本定義資料庫使用者所在之主要資料庫的變更資料,但該節點是唯一可存取的節點。While the associated SQL ServerSQL Server authentication login could be used to access change data at the primary where the database user was originally defined, that node is the only one where access would be possible. SQL ServerSQL Server 驗證登入無法存取任何次要資料庫的資料,也無法存取資料庫使用者定義所在之原始資料庫以外的任何新主要資料庫的資料。The SQL ServerSQL Server authentication login would not be able to access data from any secondary database, nor from any new primary databases other than the original database where the database user was defined.

  • 停用異動資料擷取Disabling Change Data Capture
    如果需要停用資料庫的異動資料擷取,而此資料庫屬於 AlwaysOn 可用性群組,則您需要執行額外的步驟,以確保不影響該記錄截斷。If Change Data Capture needs to be disabled on a database which is part of an Always On Availability Group, then you will need to perform additional steps to ensure that log truncation is not affected. 您需要實作下列步驟之一,避免停用異動資料擷取之後,異動資料擷取會阻礙記錄截斷:You will need to implement one of the following steps to prevent Change Data Capture from blocking log truncation after disabling Change Data Capture:

    • 重新啟動每個次要複本執行個體上的 SQL Server 服務Restart the SQL Server service on every secondary replica instance
    • 或者從可用性群組的所有次要複本執行個體中移除資料庫,並使用自動或手動植入,將它新增至可用性群組複本執行個體。OR remove the database from all the secondary replica instances of the Availability Group and add it to the Availability Group replica instances using automatic or manual seeding

變更追蹤Change Tracking

啟用變更追蹤 (CT) 的資料庫可以屬於 AlwaysOn 可用性群組的一部分。A database enabled for change tracking (CT) can be part of an Always On availability group. 不需要進行其他組態設定。No additional configuration is needed. 使用 CDC 資料表值函式 (TVF) 存取變更資料表資料的變更追蹤用戶端應用程式,需要在容錯移轉後找出主要複本的功能。Change tracking client applications that use the CDC table-valued functions (TVFs) to access change data will need the ability to locate the primary replica after failover. 如果用戶端應用程式透過可用性群組接聽程式名稱進行連接,連接要求一律會適當導向至目前的主要複本。If the client application connects through the availability group listener name, connection requests will always be appropriately directed to the current primary replica.

注意

變更追蹤資料必須一律從主要複本取得。Change tracking data must always be obtained from the primary replica. 嘗試存取次要複本的變更資料會導致下列錯誤:An attempt to access change data from a secondary replica will result in the following error:

訊息 22117,層級 16,狀態 1,行 1Msg 22117, Level 16, State 1, Line1

次要複本的成員資料庫 (即次要資料庫) 不支援變更追蹤。For databases that are members of a secondary replica (that is, for secondary databases), change tracking is not supported. 請在主要複本的資料庫上執行變更追蹤查詢。Run change tracking queries on the databases in the primary replica.

使用複寫的必要條件、限制和考量Prerequisites, Restrictions, and Considerations for Using Replication

本節描述使用 AlwaysOn 可用性群組Always On availability groups來部署複寫的考量,包括必要條件、限制和建議。This section describes considerations for deploying replication with AlwaysOn 可用性群組Always On availability groups, including prerequisites, restrictions, and recommendations.

PrerequisitesPrerequisites

  • 當使用異動複寫,而且發行集資料庫是在可用性群組時,發行者和散發者都必須至少執行 SQL Server 2012 (11.x)SQL Server 2012 (11.x)When using transactional replication and the publishing database is in an availability group both the publisher and the distributor must run at least SQL Server 2012 (11.x)SQL Server 2012 (11.x). 訂閱者可以使用較低層級的 SQL ServerSQL ServerThe subscriber can be using a lower level of SQL ServerSQL Server.

  • 當使用合併式複寫,而且發行集資料庫是在可用性群組時:When using merge replication and the publishing database is in an availability group:

    • 發送訂閱:發行者和散發者都必須至少執行 SQL Server 2012 (11.x)SQL Server 2012 (11.x)Push subscription: Both the publisher and the distributor must run at least SQL Server 2012 (11.x)SQL Server 2012 (11.x).

    • 提取訂閱:發行者、散發者和訂閱者資料庫都必須至少是 SQL Server 2012 (11.x)SQL Server 2012 (11.x)Pull subscription: The publisher, distributor, and subscriber databases must be on at least SQL Server 2012 (11.x)SQL Server 2012 (11.x). 這是因為訂閱者的合併代理程式必須知道可用性群組如何容錯移轉到次要複本。This is because the merge agent on the subscriber must understand how an availability group can fail over to its secondary.

  • 發行者執行個體必須滿足參與 AlwaysOn 可用性群組所需的所有必要條件。The Publisher instances satisfy all the prerequisites required to participate in an Always On availability group. 如需詳細資訊,請參閱 AlwaysOn 可用性群組的必要條件、限制和建議 (SQL Server)支援複寫、異動資料擷取 (CDC) 和變更追蹤 (CT)。For more information see Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server).

限制Restrictions

AlwaysOn 可用性群組Always On availability groups所支援的複寫組合:Supported combinations of replication on AlwaysOn 可用性群組Always On availability groups:

發行者Publisher 散發者Distributor 訂閱者Subscriber
異動Transactional Yes

注意:不包含對雙向和相互異動複寫的支援。Note: Does not include support for bi-directional and reciprocal transactional replication.
Yes Yes
P2PP2P No No No
合併式Merge Yes No No
快照式Snapshot Yes No Yes

**散發者資料庫不支援與資料庫鏡像搭配使用。**The Distributor database is not supported for use with database mirroring.

考量Considerations

  • 散發資料庫不支援搭配 AlwaysOn 可用性群組Always On availability groups 或資料庫鏡像使用。The distribution database is not supported for use with AlwaysOn 可用性群組Always On availability groups or database mirroring. 複寫組態會結合至設定散發者所在的 SQL Server 執行個體。因此,無法鏡像或複寫散發資料庫。Replication configuration is coupled to the SQL Server instance where the Distributor is configured; therefore the distribution database cannot be mirrored or replicated. 若要針對散發者提供高可用性,請使用 SQL Server 容錯移轉叢集。To provide high availability for the Distributor, use a SQL Server failover cluster. 如需詳細資訊,請參閱 AlwaysOn 容錯移轉叢集執行個體 (SQL Server)支援複寫、異動資料擷取 (CDC) 和變更追蹤 (CT)。For more information, see Always On Failover Cluster Instances (SQL Server).

  • 雖然支援訂閱者容錯移轉至次要資料庫,不過這是合併式複寫訂閱者的手動程序。Subscriber failover to a secondary database, while supported, is a manual procedure for merge replication subscribers. 此程序基本上與用來容錯移轉鏡像訂閱者資料庫的方法完全相同。The procedure is essentially identical to the method used to fail over a mirrored subscriber database. 異動複寫訂閱者不需要特殊處理就能參與 AlwaysOn 可用性群組Always On availability groupsTransactional replication subscribers do not need special handling while participating in AlwaysOn 可用性群組Always On availability groups. 訂閱者必須執行 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 或更新版本,才能參與可用性群組。Subscribers must be running SQL Server 2012 (11.x)SQL Server 2012 (11.x) or later to participate in an availability group. 如需詳細資訊,請參閱 複寫訂閱者及 AlwaysOn 可用性群組 (SQL Server)For more information, see Replication Subscribers and Always On Availability Groups (SQL Server)

  • 存在於資料庫之外的中繼資料和物件不會傳播到次要複本,包括登入、作業、連結的伺服器。Metadata and objects that exist outside the database are not propagated to the secondary replicas, including logins, jobs, linked servers. 如果您需要在容錯移轉後使用新主要資料庫上的中繼資料和物件,則必須手動加以複製。If you require the metadata and objects at the new primary database after failover, you must copy them manually. 如需詳細資訊,請參閱 管理可用性群組之資料庫的登入及工作 (SQL Server)(Failover) 的程序中通常可以互換。For more information, see Management of Logins and Jobs for the Databases of an Availability Group (SQL Server).

相關工作Related Tasks

複寫Replication

Change data captureChange data capture

Change trackingChange tracking

另請參閱See Also

複寫訂閱者及 AlwaysOn 可用性群組 (SQL Server) Replication Subscribers and Always On Availability Groups (SQL Server)
AlwaysOn 可用性群組的必要條件、限制和建議 (SQL Server) Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server)
AlwaysOn 可用性群組概觀 (SQL Server) Overview of Always On Availability Groups (SQL Server)
Always On 可用性群組:互通性 (SQL Server) Always On Availability Groups: Interoperability (SQL Server)
AlwaysOn 容錯移轉叢集執行個體 (SQL Server) Always On Failover Cluster Instances (SQL Server)
關於異動資料擷取 (SQL Server) About Change Data Capture (SQL Server)
關於變更追蹤 (SQL Server) About Change Tracking (SQL Server)
SQL Server 複寫 SQL Server Replication
追蹤資料變更 (SQL Server) Track Data Changes (SQL Server)
sys.sp_cdc_add_job (Transact-SQL)sys.sp_cdc_add_job (Transact-SQL)