sql server 复制_SQL Server复制配置：点对点和合并复制

sql server 复制

This article will cover SQL Server replication configuration including Peer to peer replication and merge replication, initial configuration, adding nodes and data verification.

本文将介绍SQL Server复制配置，包括对等复制和合并复制，初始配置，添加节点和数据验证。

配置对等SQL Server复制 (Configuring Peer to Peer SQL Server Replication)

SQL Server Peer to Peer replication is a replication type where the publisher server replicates data to multiple subscriber servers at the same time.

SQL Server对等复制是一种复制类型，其中发布服务器将数据同时复制到多个订阅服务器。

Peer to peer SQL Server replication is useful for multiple data center locations across the globe. One centralized data center manages the data on the other datacenter data.

对等SQL Server复制对于全球多个数据中心位置非常有用。 一个集中式数据中心管理其他数据中心数据上的数据。

先决条件 (Pre-requisites)

Before configuring SQL Server replication, we need to sync the database which needs to replicate, with all other nodes. We will make the database in sync with other peer nodes by taking a backup of the database on the publisher server and restore it on the peer nodes.

在配置SQL Server复制之前，我们需要将需要复制的数据库与所有其他节点同步。 通过在发布服务器上备份数据库并将其还原到对等节点上，我们将使数据库与其他对等节点同步。

After restoring the database on the peer nodes, there should not any changes in the published database before configuring SQL Server replication; otherwise, it may occur conflict. If there are any data changes in the database, then we need to take a new backup and specify a new backup file path.

在对等节点上还原数据库之后，在配置SQL Server复制之前，发布的数据库中不应有任何更改； 否则，可能会发生冲突。 如果数据库中有任何数据更改，那么我们需要进行新的备份并指定新的备份文件路径。

We will use the following server configuration to configure Peer to Peer replication-

我们将使用以下服务器配置将点对点复制配置为-

• SQL1 – The Publisher SQL1 –发布者
• SQL2 – The Subscriber act as a 1SQL2 -认购充当1 ^st peer node ^日对等节点
• SQL3 – The Subscriber act as a 2SQL3 –订阅服务器充当^nd peer node ^第二个对等节点
• Database – AdventureWorksLT2012 数据库 – AdventureWorksLT2012

配置发行版 (Configure the Distribution)

In the Peer to Peer replication, we need to configure the Distribution database on all the peer nodes.

在点对点复制中，我们需要在所有对等节点上配置分发数据库。

Note: Configure the Distribution database on all the peer nodes in the same way as per the Steps PART-1 Configure the Distribution from the SQL Server replication: Configuring Snapshot and Transactional Replication article.

注意： 按照与步骤 PART-1配置 SQL Server复制中 的分发 相同的方式在所有对等节点上配置分发数据库 ：配置快照和事务复制 。

对等节点上的数据库备份还原 (Database backup restore on the peer nodes)

Once the distribution database configured on each node, before setting the publication on the SQL1, we need to restore the backup of the published database AdventureWorksLT2012 on each peer nodes. Once a backup is restored, we will proceed to configure the Publication.

在每个节点上配置了分发数据库之后，在SQL1上设置发布之前，我们需要在每个对等节点上还原已发布数据库AdventureWorksLT2012的备份。 恢复备份后，我们将继续配置发布。

配置发布者 (Configure the Publisher)

1. New Publication and新出版物，然后 Select 选择 New Publication 新出版物
2. Peer-to -Peer publication as a publication type and click 对等发布”作为发布类型，然后单击“ Next 下一步”。
   
   
   
   
   

3. Next, kindly proceed to the next steps as per the standard configuration steps of the publication as per PART-2 Configure the Publisher from the SQL Server replication: Configuring Snapshot and Transactional Replication article
4. 接下来，请按照PART-2的发布的标准配置步骤继续进行下一步，从SQL Server复制中配置 发布者 ：配置快照和事务复制文章
5. [AdventureWorksLT2012]: PTP_PUBLICATION is ready to use as shown in the following fig [AdventureWorksLT2012]：PTP_PUBLICATION准备就绪，如下图所示
   
   
   
   
   

配置点对点拓扑 (Configure Peer to Peer Topology)

1. Right-click publication and select 右键单击发布，然后选择“ Configure Peer to Peer topology 配置对等到对等”拓扑
   
   
   
   
   

2. Next 下一步”。
   
   
   
   
   

3. • Peer originator id is 100, so any other peer which we will add should not have the same identifier
     对等发起方ID为100，因此我们将添加的任何其他对等方都不应具有相同的标识符
   • Conflict detection is enabled so that if the update operation is running on peer node and at the same time, someone initiates to delete same data from the publisher, then conflict will occur, and it will generate an alert 启用了冲突检测，因此，如果更新操作在对等节点上运行，并且同时有人发起从发布者删除相同数据的操作，则会发生冲突，并会生成警报
   
   
   
   

4. To add a node, Right click in the gray surface area and select Add a New Peer Node
   
   
   
5. 要添加节点， 请在灰色表面区域上单击鼠标右键，然后选择“ 添加新对等节点”
   
   

6. 
   
   
   
   
   

7. 
   
   
   
   
   

8. Next 下一步
   
   
   
   
   

9. 
   
   
   
   
   
   
   

10. 
    
    
    
    
    

11. 
    
    
    
    
    

12. Next 下一步”。
    
    
    
    
    
    

13. distribution agent security, provide the connection details of the service account the same as the previous step 分发代理程序安全性中 ，提供与上一步相同的服务帐户的连接详细信息
    
    
    
    
    

14. 
    
    
    
    
    • Backup restored, and a published database not changed 备份已还原，发布的数据库未更改

      If the backup of the published database restored on the other peer nodes SQL2 and SQL3, then data on the peer nodes should not have changed since the last backup

      如果在其他对等节点SQL2和SQL3上还原了已发布数据库的备份，则自上次备份以来对等节点上的数据不应更改

    • Backup restored, and the published database changed 恢复备份，并且已发布的数据库已更改

      We need to select this option when we restored the published database backup to the peer nodes, and after restoration, the published database changed, then again, we need to take backup of the database and specify the path of the new backup file

      将发布的数据库备份还原到对等节点时，需要选择此选项，还原后，发布的数据库已更改，然后再次进行数据库备份，并指定新备份文件的路径

    

15. Finish 完成
    
    
    
    
    

16. 
    
    
    
    
    

资料验证 (Data Verification )

In this example, we will make data change on the Publisher server SQL1 and will verify whether it makes the same change or not on the peer node server SQL2 and SQL3

在此示例中，我们将在发布服务器SQL1上进行数据更改，并将验证是否在对等节点服务器SQL2和SQL3上进行了相同的更改。

1. the CompanyName Column value of the table CUSTOMER CUSTOMER 的CompanyName Column值
   
   
   
   
   

2. CompanyName column value to new value CompanyName列值更新为新值A CAR STORE A CAR STORE
   
   
   
   
   

3. CompanyName column as shown in the following fig CompanyName列的新值已更新，如下图所示。
   
   
   
   
   

4. CompanyName column as shown in the following fig CompanyName列的新值已更新，如下图所示。
   
   
   
   
   

配置合并复制 (Configuring Merge Replication)

Merge Replication is the same as SQL Server Transactional replication; however, Merge replication replicates data from the Publisher to Subscriber and vice-a-versa. Though Merge replication is two-way change replication; however, we need to make any Schema changes only on the publisher, schema changes will not allow on the subscriber. Any schema changes on the publisher will make the change on the subscriber.

合并复制与SQL Server事务复制相同。 但是，合并复制将数据从发布服务器复制到订阅服务器，反之亦然。 尽管合并复制是双向更改复制； 但是，我们只需要在发布者上进行任何模式更改，而在订阅者上则不允许进行模式更改。 发布者上的任何架构更改都将对订阅者进行更改。

Merge replication uses the Snapshot Agent and the Merge Agent. The snapshot agent takes the snapshot of the published articles and put it into the snapshot folder. Merge agent can connect to both the publisher and the subscriber, it applies the Snapshot to the subscriber, and it tracks the changes from both publisher and the subscriber and transfers those changes to the distribution database further to use to the subscriber. The advantage of merge replication is that it can also work with no network connection.

合并复制使用快照代理和合并代理。 快照代理获取已发布文章的快照，并将其放入快照文件夹中。 合并代理可以同时连接到发布者和订阅者，将快照应用到订阅者，并跟踪发布者和订阅者的更改，并将这些更改传输到分发数据库，​​以供订阅者使用。 合并复制的优点是它也可以在没有网络连接的情况下工作。

配置发行版 (Configure the Distribution)

1. Configure the Distribution step by step as per PART-1 Configure the Distribution from the SQL Server replication: Configuring Snapshot and Transactional Replication article
2. 根据PART-1逐步配置分发从SQL Server复制配置 分发 ：配置快照和事务复制文章

配置发布 (Configure the Publication)

1. local publication and select 本地出版物，然后选择New Publication 新出版物。
2. Next 下一步
   
   
   
   
   

3. Merge publication as a publication type and click 合并出版物”作为出版物类型，然后单击“ Next 下一步”。
   
   
   
   
   

4. Next 下一步”。
   
   
   
   
   

5. Create a Snapshot immediately option and schedule the snapshot agent as per your requirement 立即创建快照选项，并根据需要安排快照代理
   
   
   
   
   

6. Kindly proceed to next publisher steps same as PART-2 Configure the publication from the SQL Server replication: Configuring Snapshot and Transactional Replication article
7. 请继续执行与PART-2相同的下一个发布者步骤从SQL Server复制中配置 发布 ：配置快照和事务复制文章
8. To configure the Subscriber, kindly continue to perform steps same as PART-3 Configure the Subscriber from the SQL Server replication: Configuring Snapshot and Transactional Replication article
9. 若要配置订阅服务器，请继续执行与PART-3相同的步骤，从SQL Server复制中配置 订阅 服务器：配置快照和事务复制
10. AdventureWorksLT2012_MERGE is online in the subscriber instance SQL2 AdventureWorksLT2012_MERGE在订阅者实例SQL2中处于联机状态

合并复制的数据验证 (Data verification for Merge Replication)

In this example, we will make data change on the Subscriber server SQL2 and will verify whether it makes the same change or not on the Publisher server SQL1.

在此示例中，我们将在订阅服务器SQL2上进行数据更改，并验证在发布服务器SQL1上是否进行了相同的更改。

• 
  
  
  
  
  

• 
  
  
  
  
  

• Verify the data on Publisher SQL1
  验证Publisher SQL1上的数据
• The modified value of the column CompanyName on the subscriber is updated on the publisher SQL1 as shown in the following window. So, data changes on the publisher, it will also change on the subscriber and vise-a-versa 的修改值在发布者SQL1上更新，如下窗口所示。 因此，发布者上的数据会发生变化，订户上的数据也会发生变化，反之亦然
  
  
  
  
  

目录 (Table of contents)

翻译自: https://www.sqlshack.com/sql-server-replication-configuration-peer-to-peer-and-merge-replication/

sql server 复制