介绍 (Introduction)

This article is for people who need to replicate the SQL Server Databases. SQL Server includes several types of replications to synchronize the databases across different SQL Servers.

本文适用于需要复制SQL Server数据库的人员。 SQL Server包括几种复制类型，以跨不同SQL Server同步数据库。

Sometimes we need to replicate the data to have a Backup. Sometimes we need to synchronize Servers that are located in different cities or countries.

有时我们需要复制数据以具有备份。 有时我们需要同步位于不同城市或国家/地区的服务器。

In this article, we will introduce you to the SQL Server Replication Theory and then we will show a step-by-step tutorial to replicate a Table using the Merge replication.

在本文中，我们将向您介绍SQL Server复制理论，然后将显示分步教程，以使用合并复制来复制表。

复制类型 (Types of Replications)

In SQL Server, the main types of replication are SQL:

在SQL Server中，主要的复制类型是SQL：

• Snapshot

  
  

  快照

• Merge

  
  

  合并

• Transactional

  
  

  交易性

You also have the Heterogeneous Replication with Oracle, which is out of the scope of this article.

您还可以使用Oracle进行异构复制 ，这不在本文的讨论范围之内。

复制中的角色 (Roles in replication)

There are three roles in a replication:

复制中有三个角色：

• The Publisher is the SQL Server, which publishes the information to replicate (usually tables).

  
  

  发布服务器是SQL Server，它发布要复制的信息（通常是表）。

• The Distributor is the SQL Server that distributes de information in each Subscriber. The distributor can be installed at the Publisher, in the Subscriber or in a separate SQL Server.

  
  

  分发服务器是在每个订阅服务器中分发信息SQL Server。 分发服务器可以安装在发布服务器，订阅服务器或单独SQL Server中。

• The Subscriber is the SQL Server that receives the information Published.

  
  

  订阅服务器是接收发布信息SQL Server。

快照复制 (Snapshot Replication)

The snapshots replicates the complete table or tables selected in the Snapshot replication process. When the snapshot replication is applied, the entire snapshot is replicated.

快照复制在快照复制过程中选择的完整表或多个表。 应用快照复制时，将复制整个快照。

事务复制 (Transactional Replication)

In the transactional method, one of the SQL Servers is the publisher, which publishes the tables or objects to replicate, and the other is the subscriber (or the subscribers if multiple SQL Servers are being replicated).

在事务方法中，其中一个SQL Server是发布者，它发布要复制的表或对象，另一个是订阅者（如果要复制多个SQL Server，则是订阅者）。

合并复制 (Merge Replication)

For the first time, a snapshot is used to replicate the information and after that, all the data and schema is replicated every time is changed using triggers.

第一次使用快照复制信息，此后，每次使用触发器进行更改时，都会复制所有数据和架构。

先决条件 (Prerequisites)

1. Two SQL Server or at least two SQL Server instances.

   
   

   两个SQL Server或至少两个SQL Server实例。

2. Make sure that the SQL Server Agent Account is running under a common account for both Servers.

   
   

   确保SQL Server代理帐户在两个服务器的公共帐户下运行。

3. A table to be replicated.

   
   

   要复制的表。

入门 (Getting started)

1. In the SQL Server Management Studio (SSMS), open the node of the SQL Server with the table to be published. In this example, we are going to publish a table named emails installed in the DB1 database.

   
   

   

   
   Figure 1

   在SQL Server Management Studio（SSMS）中，使用要发布的表打开SQL Server的节点。 在这个例子中，我们将发布一个安装在DB1数据库中的名为emails的表。

   
   图1

2. In the Replication Node right click on the Local Publication and select New Publication.

   
   

   

   
   Figure 2

   在复制节点中，右键单击本地发布，然后选择新建发布。

   
   图2

3. In the new publication Window, press Next.

   
   

   

   
   Figure 3

   在新的发布窗口中，按下一步。

   
   图3

4. Select the Server that will be used as a distributor. In this example, the Publisher and the Distributor will be the same Server,

   
   

   

   
   Figure 4

   选择将用作分发服务器的服务器。 在此示例中，发布者和分发者将是同一台服务器，

   
   图4

5. You can optionally configure if you want the Agent to start automatically. This is recommended if you constantly require synchronization between SQL Servers.

   
   

   

   
   Figure 5

   您可以选择配置是否希望代理自动启动。 如果您经常需要在SQL Server之间进行同步，则建议这样做。

   
   图5

6. All the replication types require a Snapshot. The Snapshot Folder allows you to specify the Path where the folder will be published.

   
   

   

   
   Figure 6

   所有复制类型都需要一个快照。 Snapshot Folder（快照文件夹）允许您指定文件夹的发布路径。

   
   图6

7. Select the Database where you have the information to be published.

   
   

   

   
   Figure 7

   选择您要发布信息的数据库。

   
   图7

8. Select the Replication type

   
   

   

   
   Figure 8

   选择复制类型

   
   图8

9. Specify the SQL Server version of the Subscriber.

   
   

   

   
   Figure 8a

   指定订阅服务器SQL Server版本。

   
   图8a

10. Select the tables or tables to Replicate.

    
    

    

    
    Figure 9

    选择要复制的一个或多个表。

    
    图9

11. You can optionally add filters to Rows or Columns. This is common when you have some internal information that you do not want to replicate. Press Add if you want to add filters.

    
    

    

    
    Figure 10

    您可以选择将过滤器添加到行或列。 当您有一些不想复制的内部信息时，这很常见。 如果要添加过滤器，请按添加。

    
    图10

12. In the Filter statement you can configure the T-SQL Sentences to filter the columns or to restrict the rows usually with a where clause.

    
    

    

    
    Figure 11

    在Filter语句中，您可以配置T-SQL语句以通常使用where子句来过滤列或限制行。

    
    图11

13. You can create the snapshot immediately or Schedule at a specific time.

    
    

    

    
    Figure 12

    您可以立即创建快照，也可以在特定时间计划。

    
    图12

14. With the Security Settings button, you will specify the account under which the connection will run.

    
    

    

    
    Figure 13

    使用“安全设置”按钮，您将指定用于运行连接的帐户。

    
    图13

15. You can run under a specific account, impersonate the process Account, use a SQL Server Login.

    
    

    

    
    Figure 14

    您可以在特定帐户下运行，模拟进程帐户，使用SQL Server登录名。

    
    图14

16. Once selected the security settings press Next.

    
    

    

    
    Figure 15

    选择安全设置后，按下一步。

    
    图15

17. Select a publication type. In this example, Merge Replication.

    
    

    

    
    

    选择一种出版物类型。 在此示例中，合并复制。

    
    

18. Select the version of the SQL Server Subscriber.

    
    

    

    选择SQL Server订阅服务器的版本。

    
    

19. Create the publication and press Next.

    
    

    

    
    Figure 16

    创建出版物，然后按下一步。

    
    图16

20. Specify a name for the Publication and press Finish.

    
    

    

    
    Figure 17

    指定发布的名称，然后按完成。

    
    图17

21. If you open the SQL Server, you will be able to see a publication named db1: pub1.

    
    

    

    
    Figure 18

    如果打开SQL Server，将可以看到名为db1的发布：pub1。

    
    图18

22. In the other server used as a Subscribers, go to Replication>Local Subscription and select New Subscriptions.

    
    

    

    
    Figure 19

    在用作订阅服务器的另一台服务器中，转到“复制”>“本地订阅”，然后选择“新订阅”。

    
    图19

23. In the New Subscription Wizard, press Next.

    
    

    

    
    Figure 20

    在“新建订阅向导”中，按“下一步”。

    
    图20

24. Select the Publisher Server where you published the article.

    
    

    

    
    Figure 21

    选择您在其中发布文章的发布服务器。

    
    图21

25. You can select where you want the Merge Agent to run. It can run at the Distributor or at each subscriber.

    
    

    

    
    Figure 22

    您可以选择希望合并代理运行的位置。 它可以在分发服务器或每个订阅服务器上运行。

    
    图22

26. At the subscriber database, you can create a new database to be used as the database subscriber to receive the data replicated.

    
    

    

    
    Figure 23

    在订户数据库上，您可以创建一个新数据库，用作数据库订户以接收复制的数据。

    
    图23

27. Create a Database with the same name that the database used as the publisher.

    

    
    Figure 24

    
    

    创建与该数据库用作发布者的名称相同的数据库。

    
    图24

28. You can choose the subscribers and the subscription database. Press Next.

    
    

    

    
    Figure 25

    您可以选择订阅者和订阅数据库。 按下一步。

    
    图25

29. Click in the ellipsis button to specify the security accounts.

    
    

    

    
    Figure 26

    单击省略号按钮以指定安全帐户。

    
    图26

30. You can specify the Schedule for the Synchronization.

    
    

    

    
    Figure 27

    您可以指定同步时间表。

    
    图27

31. You have to Schedule when you want to initialize the replication with the snapshot publication.

    
    

    

    
    Figure 28

    要使用快照发布初始化复制时，必须计划。

    
    图28

32. You also need to specify the type of subscription

    
    

    

    
    Figure 29

    您还需要指定订阅类型

    
    图29

33. Finally, create the subscription.

    
    

    

    
    Figure 30

    最后，创建订阅。

    
    图30

34. If everything is OK, add a new row to the table replicated at the publisher.

    
    

    

    
    Figure 31

    如果一切正常，请将新行添加到在发布服务器上复制的表中。

    
    图31

35. At the publisher, go to Replication>Local Publication and select the article. Right click and select View Synchronization Status.

    
    

    

    
    Figure 32

    在发布者处，转到“复制”>“本地发布”，然后选择文章。 右键单击并选择查看同步状态。

    
    图32

36. If you want to merge inmediately, press the start button. You will check that 1 insert is detected

    
    

    

    
    Figure 33

    如果要立即合并，请按开始按钮。 您将检查是否检测到1个插入物

    
    图33

37. If everything is OK, you will be able to check the replication at the subscriber. Your replication is now ready!

    
    

    

    
    Figure 34

    如果一切正常，您将能够在订阅服务器上检查复制。 复制已准备就绪！

    
    图34

结论 (Conclusion)

As you can see, replicating a database is a straightforward process. You can replicate any database that you want anytime. In this article, we teach the basis of the replication, the replication types, roles and finally, we show how to create a Merge Replication.

如您所见，复制数据库是一个简单的过程。 您可以随时复制所需的任何数据库。 在本文中，我们将介绍复制的基础，复制类型，角色，最后，我们将展示如何创建合并复制。

以上就是SQL Server复制的详细内容，更多请关注php知识-学习天地 www.lxywzjs.com其它相关文章！