SQL Server复制-白红宇

SQL Server复制

阅读量：2516 次

发布时间：2019-05-11

本文共 7622 字，大约阅读时间需要 25 分钟。

介绍 (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 , which is out of the scope of this article.

您还可以进行，这不在本文的讨论范围之内。

复制中的角色 (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)

Two SQL Server or at least two SQL Server instances.
两个SQL Server或至少两个SQL Server实例。

Make sure that the SQL Server Agent Account is running under a common account for both Servers.
确保SQL Server代理帐户在两个服务器的公共帐户下运行。

A table to be replicated.
要复制的表。

入门 (Getting started)

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.

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

Figure 1
图1

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

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

Figure 2
图2

In the new publication Window, press Next.

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

Figure 3
图3

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

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

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

Figure 5
图5

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

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

Figure 6
图6

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

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

Figure 7
图7

Select the Replication type

选择复制类型

Figure 8
图8

Specify the SQL Server version of the Subscriber.

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

Figure 8a
图8a

Select the tables or tables to Replicate.

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

Figure 9
图9

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

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.

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

Figure 11
图11

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

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

Figure 12
图12

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

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

Figure 13
图13

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

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

Figure 14
图14

Once selected the security settings press Next.

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

Figure 15
图15

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

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

Select the version of the SQL Server Subscriber.

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

Create the publication and press Next.

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

Figure 16
图16

Specify a name for the Publication and press Finish.

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

Figure 17
图17

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

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

Figure 18
图18

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

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

Figure 19
图19

In the New Subscription Wizard, press Next.

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

Figure 20
图20

Select the Publisher Server where you published the article.

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

Figure 21
图21

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

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

Figure 22
图22

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

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

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

Figure 24

图24

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

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

Figure 25
图25

Click in the ellipsis button to specify the security accounts.

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

Figure 26
图26

You can specify the Schedule for the Synchronization.

您可以指定同步时间表。

Figure 27
图27

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

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

Figure 28
图28

You also need to specify the type of subscription

您还需要指定订阅类型

Figure 29
图29

Finally, create the subscription.

最后，创建订阅。

Figure 30
图30

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

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

Figure 31
图31

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

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

Figure 32
图32

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

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

Figure 33
图33

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.

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