設定 Always On 可用性群組的複寫Configure replication with Always On availability groups

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

設定 SQL ServerSQL Server 複寫和 AlwaysOn 可用性群組包含七個步驟。Configuring SQL ServerSQL Server replication and Always On availability groups involves seven steps. 下列各節將詳細說明每個步驟。Each step is described in more detail in the following sections.

1.設定資料庫發行集和訂閱1. Configure the Database Publications and Subscriptions

設定散發者Configure the distributor

散發資料庫不能搭配 SQL Server 2012 和 SQL Server 2014 置於可用性群組中。The distribution database cannot be placed in an availability group with SQL Server 2012 and SQL Server 2014. SQL 2016 和更新版本支援將散發資料庫放置到可用性群組內。Placing the distribution database into an availability group is supported with SQL 2016 and greater. 如需詳細資訊,請參閱在可用性群組中設定散發資料庫For more information, see Configure distribution database in an availability group.

  1. 在散發者端設定散發。Configure distribution at the distributor. 如果預存程序正用於組態,請執行 sp_adddistributorIf stored procedures are being used for configuration, run sp_adddistributor. 您可以使用 @password 參數來識別遠端發行者連接到散發者時使用的密碼。Use the @password parameter to identify the password that will be used when a remote publisher connects to the distributor. 設定遠端散發者時,每個遠端發行者也需要此密碼。The password will also be needed at each remote publisher when the remote distributor is set up.

    USE master;  
    GO  
    EXEC sys.sp_adddistributor  
        @distributor = 'MyDistributor',  
        @password = '**Strong password for distributor**';  
    
  2. 在散發者端建立散發資料庫。Create the distribution database at the distributor. 如果預存程序正用於組態,請執行 sp_adddistributiondbIf stored procedures are being used for configuration, run sp_adddistributiondb.

    USE master;  
    GO  
    EXEC sys.sp_adddistributiondb  
        @database = 'distribution',  
        @security_mode = 1;  
    
  3. 設定遠端發行者。Configure the remote publisher. 如果預存程序正用於設定散發者,請執行 sp_adddistpublisherIf stored procedures are being used to configure the distributor, run sp_adddistpublisher. @security_mode 參數是用來決定從複寫代理程式執行的發行者驗證預存程序如何連線到目前主要複本。The @security_mode parameter is used to determine how the publisher validation stored procedure that is run from the replication agents, connects to the current primary. 如果設定為 1,就會使用 Windows 驗證來連接到目前主要複本。If set to 1 Windows authentication is used to connect to the current primary. 如果設定為 0,就會使用 SQL ServerSQL Server 驗證搭配指定的 @login@password 值。If set to 0, SQL ServerSQL Server authentication is used with the specified @login and @password values. 在每個次要複本上指定的登入和密碼必須有效,才能讓驗證預存程序成功連接到該複本。The login and password specified must be valid at each secondary replica for the validation stored procedure to successfully connect to that replica.

    注意

    如果任何修改的複寫代理程式在散發者以外的電腦上執行,則使用 Windows 驗證來連接到主要複本時,就必須針對複本主機電腦之間的通訊設定 Kerberos 驗證。If any modified replication agents run on a computer other than the distributor, use of Windows authentication for the connection to the primary will require Kerberos authentication to be configured for the communication between the replica host computers. 使用 SQL ServerSQL Server 登入來連接到目前主要複本時,不需要 Kerberos 驗證。Use of a SQL ServerSQL Server login for the connection to the current primary will not require Kerberos authentication.

    USE master;  
    GO  
    EXEC sys.sp_adddistpublisher  
        @publisher = 'AGPrimaryReplicaHost',  
        @distribution_db = 'distribution',  
        @working_directory = '\\MyReplShare\WorkingDir',  
        @login = 'MyPubLogin',  
        @password = '**Strong password for publisher**';  
    

如需詳細資訊,請參閱 sp_adddistpublisher (Transact-SQL)For more information, see sp_adddistpublisher (Transact-SQL).

在原始發行者端設定發行者Configure the publisher at the original publisher

  1. 設定遠端散發。Configure remote distribution. 如果預存程序正用於設定發行者,請執行 sp_adddistributorIf stored procedures are being used to configure the publisher, run sp_adddistributor. 請以在散發者端執行 sp_adddistrbutor 時用來設定散發的相同值,來指定 @password 的值。Specify the same value for @password as that used when sp_adddistrbutor was run at the distributor to set up distribution.

    exec sys.sp_adddistributor  
        @distributor = 'MyDistributor',  
        @password = 'MyDistPass'  
    
  2. 啟用資料庫進行複寫。Enable the database for replication. 如果預存程序正用於設定發行者,請執行 sp_replicationdboptionIf stored procedures are being used to configure the publisher, run sp_replicationdboption. 如果要針對資料庫設定異動和合併式複寫,就必須啟用每個複寫。If both transactional and merge replication are to be configured for the database, each must be enabled.

    USE master;  
    GO  
    EXEC sys.sp_replicationdboption  
        @dbname = 'MyDBName',  
        @optname = 'publish',  
        @value = 'true';  
    
    EXEC sys.sp_replicationdboption  
        @dbname = 'MyDBName',  
        @optname = 'merge publish',  
        @value = 'true';  
    
  3. 建立複寫發行集、發行項和訂閱。Create the replication publication, articles, and subscriptions. 如需有關如何設定複寫的詳細資訊,請參閱<發行資料和資料庫物件>。For more information about how to configure replication, see Publishing Data and Database objects.

2.設定 AlwaysOn 可用性群組2. Configure the Always On Availability Group

在預期的主要複本上,建立具有已發行 (或即將發行) 資料庫做為成員資料庫的可用性群組。At the intended primary, create the availability group with the published (or to be published) database as a member database. 如果使用可用性群組精靈,您就可以允許精靈一開始同步處理次要複本資料庫,也可以使用備份和還原來手動執行初始化。If using the Availability Group Wizard, you can either allow the wizard to initially synchronize the secondary replica databases or you can perform the initialization manually by using backup and restore.

針對可用性群組建立複寫代理程式將用來連接到目前主要複本的 DNS 接聽程式。Create a DNS listener for the availability group that will be used by the replication agents to connect to the current primary. 指定的接聽程式名稱將當做原始發行者/已發行資料庫配對的重新導向目標使用。The listener name that is specified will be used as the target of redirection for the original publisher/published database pair. 例如,如果您要使用 DDL 來設定可用性群組,可以使用下列程式碼範例,針對名為 MyAG 的現有可用性群組指定可用性群組接聽程式:For example, if you are using DDL to configure the availability group, the following code example can be used to specify an availability group listener for an existing availability group named MyAG:

ALTER AVAILABILITY GROUP 'MyAG'   
    ADD LISTENER 'MyAGListenerName' (WITH IP (('10.120.19.155', '255.255.254.0')));  

如需詳細資訊,請參閱建立及設定可用性群組 (SQL Server)For more information, see Creation and Configuration of Availability Groups (SQL Server).

3.確定所有次要複本主機都設定為複寫3. Ensure that all of the Secondary Replica Hosts are Configured for Replication

在每個次要複本主機上,確認 SQL ServerSQL Server 已經設定為支援複寫。At each secondary replica host, verify that SQL ServerSQL Server has been configured to support replication. 您可以在每個次要複本主機上執行下列查詢,以便判斷是否已安裝複寫:The following query can be run at each secondary replica host to determine whether replication is installed:

USE master;  
GO  
DECLARE @installed int;  
EXEC @installed = sys.sp_MS_replication_installed;  
SELECT @installed;  

如果 @installed 為 0,您就必須將複寫新增到 SQL ServerSQL Server 安裝。If @installed is 0, replication must be added to the SQL ServerSQL Server installation.

4.將次要複本主機設定為複寫發行者4. Configure the Secondary Replica Hosts as Replication Publishers

次要複本無法做為複寫發行者或重新發行者,但是您必須設定複寫,才能讓次要複本在容錯移轉之後接管。A secondary replica cannot act as a replication publisher or republisher but replication must be configured so that the secondary can take over after a failover. 在散發者端,設定每個次要複本主機的散發。At the distributor, configure distribution for each secondary replica host. 請指定當原始發行者加入至散發者時指定的相同散發資料庫和工作目錄。Specify the same distribution database and working directory as was specified when the original publisher was added to the distributor. 如果您要使用預存程序來設定散發,請使用 sp_adddistpublisher ,讓遠端發行者與散發者產生關聯。If you are using stored procedures to configure distribution, use sp_adddistpublisher to associate the remote publishers with the distributor. 如果 @login@password 已用於原始發行者,請在您新增次要複本主機作為發行者時,針對每個項目指定相同的值。If @login and @password were used for the original publisher, specify the same values for each when you add the secondary replica hosts as publishers.

EXEC sys.sp_adddistpublisher  
    @publisher = 'AGSecondaryReplicaHost',  
    @distribution_db = 'distribution',  
    @working_directory = '\\MyReplShare\WorkingDir',  
    @login = 'MyPubLogin',  
    @password = '**Strong password for publisher**';  

在每個次要複本主機上,設定散發。At each secondary replica host, configure distribution. 您可以將原始發行者的散發者識別為遠端散發者。Identify the distributor of the original publisher as the remote distributor. 請使用原本在散發者端執行 sp_adddistributor 時所使用的相同密碼。Use the same password as that used when sp_adddistributor was run originally at the distributor. 如果預存程序正在用於設定散發,則會使用 sp_adddistributor@password 參數來指定密碼。If stored procedures are being used to configure distribution, the @password parameter of sp_adddistributor is used to specify the password.

EXEC sp_adddistributor   
    @distributor = 'MyDistributor',  
    @password = '**Strong password for distributor**';  

在每個次要複本主機上,確定資料庫發行集的發送訂閱者顯示成連結的伺服器。At each secondary replica host, make sure that the push subscribers of the database publications appear as linked servers. 如果預存程序正用於設定遠端發行者,請使用 sp_addlinkedserver ,將訂閱者 (如果原本不存在的話) 當作連結的伺服器加入至發行者。If stored procedures are being used to configure the remote publishers, use sp_addlinkedserver to add the subscribers (if not already present) as linked servers to the publishers.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';  

5.將原始發行者重新導向至 AG 接聽程式名稱5. Redirect the Original Publisher to the AG Listener Name

在散發者端的散發資料庫中,執行 sp_redirect_publisher 預存程序,以便讓原始發行者和已發行資料庫與可用性群組的可用性群組接聽程式名稱產生關聯。At the distributor, in the distribution database, run the stored procedure sp_redirect_publisher to associate the original publisher and the published database with the availability group listener name of the availability group.

USE distribution;  
GO  
EXEC sys.sp_redirect_publisher   
@original_publisher = 'MyPublisher',  
    @publisher_db = 'MyPublishedDB',  
    @redirected_publisher = 'MyAGListenerName';  

6.執行複寫驗證預存程序以確認組態6. Run the Replication Validation Stored Procedure to Verify the Configuration

在散發者端的散發資料庫中,執行 sp_validate_replica_hosts_as_publishers 預存程序,以便確認所有複本主機現在都設定為當作已發行資料庫的發行者。At the distributor, in the distribution database, run the stored procedure sp_validate_replica_hosts_as_publishers to verify that all replica hosts are now configured to serve as publishers for the published database.

USE distribution;  
GO  
DECLARE @redirected_publisher sysname;  
EXEC sys.sp_validate_replica_hosts_as_publishers  
    @original_publisher = 'MyPublisher',  
    @publisher_db = 'MyPublishedDB',  
    @redirected_publisher = @redirected_publisher output;  

在每個可用性群組複本主機上, sp_validate_replica_hosts_as_publishers 預存程序應該從具有足夠授權的登入執行,以便查詢可用性群組的相關資訊。The stored procedure sp_validate_replica_hosts_as_publishers should be run from a login with sufficient authorization at each availability group replica host to query for information about the availability group. sp_validate_redirected_publisher不同之處在於,它會使用呼叫端的認證,而不會使用保留在 msdb.dbo.MSdistpublishers 中的登入來連接到可用性群組複本。Unlike sp_validate_redirected_publisher, it uses the credentials of the caller and does not use the login retained in msdb.dbo.MSdistpublishers to connect to the availability group replicas.

注意

在驗證不允許讀取存取或需要指定讀取意圖的次要複本主機時,sp_validate_replica_hosts_as_publishers 會失敗並發生下列錯誤。sp_validate_replica_hosts_as_publishers will fail with the following error when validating secondary replica hosts that do not allow read access, or require read intent to be specified.

訊息 21899、層級 11、狀態 1、程序 sp_hadr_verify_subscribers_at_publisher、行 109Msg 21899, Level 11, State 1, Procedure sp_hadr_verify_subscribers_at_publisher, Line 109

在重新導向的發行者 'MyReplicaHostName' 上用以判斷原始發行者 'MyOriginalPublisher' 的訂閱者是否有 sysserver 項目的查詢失敗,發生錯誤 '976',錯誤訊息為「錯誤 976,層級 14,狀態 1,訊息:目標資料庫 'MyPublishedDB' 正參與可用性群組,目前無法供查詢存取。The query at the redirected publisher 'MyReplicaHostName' to determine whether there were sysserver entries for the subscribers of the original publisher 'MyOriginalPublisher' failed with error '976', error message 'Error 976, Level 14, State 1, Message: The target database, 'MyPublishedDB', is participating in an availability group and is currently not accessible for queries. 資料移動已暫停,或者可用性複本無法進行讀取存取。Either data movement is suspended or the availability replica is not enabled for read access. 若要允許唯讀存取可用性群組中的這個資料庫和其他資料庫,請啟用群組中一個或多個次要可用性複本的讀取存取。To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. 如需詳細資訊,請參閱《 線上叢書》的< ALTER AVAILABILITY GROUP SQL ServerSQL Server 陳述式>。For more information, see the ALTER AVAILABILITY GROUP statement in SQL ServerSQL Server Books Online.'.

複本主機 'MyReplicaHostName' 發生了一個或多個發行者驗證錯誤。One or more publisher validation errors were encountered for replica host 'MyReplicaHostName'.

這是預期的行為。This is expected behavior. 您必須直接在主機上查詢 sysserver 項目,藉以確認訂閱者伺服器項目是否存在這些次要複本主機上。You must verify the presence of the subscriber server entries at these secondary replica hosts by querying for the sysserver entries directly at the host.

7.將原始發行者加入至複寫監視器7. Add the Original Publisher to Replication Monitor

在每個可用性群組複本上,將原始發行者加入至複寫監視器。At each availability group replica, add the original publisher to Replication Monitor.

相關工作Related Tasks

複寫Replication

若要建立並設定可用性群組To create and configure an availability group

另請參閱See Also

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)
SQL Server 複寫SQL Server Replication