SQL Server复制

最新推荐文章于 2024-06-03 17:16:35 发布

culuo4781

最新推荐文章于 2024-06-03 17:16:35 发布

阅读量240

点赞数

文章标签：数据库 java python mysql 大数据

原文链接：https://www.sqlshack.com/sql-server-replications/

版权

本文深入介绍了SQL Server的复制功能，包括快照复制、事务复制和合并复制三种类型，详细阐述了复制过程中的角色（发布者、分发者和订阅者），并提供了一个分步教程，展示了如何使用合并复制在SQL Server间同步数据。此外，还强调了复制在数据备份和跨地区服务器同步中的重要性。

摘要由CSDN通过智能技术生成

介绍 (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)

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.

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