SQL Server复制

2021-03-16 563

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


    Publish Table


    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.


    Replication Node


    Figure 2

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


    图2

  3. In the new publication Window, press Next.


    New Publication


    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,


    Select 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.


    Configure Start


    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.


    Snapshot Folder


    Figure 6

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


    图6

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


    Database Selection


    Figure 7

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


    图7

  8. Select the Replication type


    Replication Type


    Figure 8

    选择复制类型


    图8

  9. Specify the SQL Server version of the Subscriber.


    Server Version


    Figure 8a

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


    图8a

  10. Select the tables or tables to Replicate.


    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.


    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.


    FilterStatement


    Figure 11

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


    图11

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


    Specific snapshot


    Figure 12

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


    图12

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


    Security Settings


    Figure 13

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


    图13

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


    Impersonate Account


    Figure 14

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


    图14

  16. Once selected the security settings press Next.


    Agent Security


    Figure 15

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


    图15

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


    Merge Publication


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


  18. Select the version of the SQL Server Subscriber.


    Subscriber Types

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


  19. Create the publication and press Next.


    Create The Publication


    Figure 16

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


    图16

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


    Name Specification


    Figure 17

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


    图17

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


    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.


    Subscribtions


    Figure 19

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


    图19

  23. In the New Subscription Wizard, press Next.


    New Subscribtion Wizard


    Figure 20

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


    图20

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


    Publisher Server


    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.


    Merge Agent Locationr


    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.


    Replicated Data


    Figure 23

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


    图23

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

    Same Database


    Figure 24


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


    图24

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


    Choose Subscribers


    Figure 25

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


    图25

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


    Elipsis Button


    Figure 26

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


    图26

  30. You can specify the Schedule for the Synchronization.


    Spec Schedule


    Figure 27

    您可以指定同步时间表。


    图27

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


    Initialize Subscribtions


    Figure 28

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


    图28

  32. You also need to specify the type of subscription


    Spec Subscription Type


    Figure 29

    您还需要指定订阅类型


    图29

  33. Finally, create the subscription.


    Final Subscription Type


    Figure 30

    最后,创建订阅。


    图30

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


    New Row


    Figure 31

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


    图31

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


    Sync Status


    Figure 32

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


    图32

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


    merge Inmediately


    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!


    Replication 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其它相关文章!

分享至:

分享到QQ空间 分享到朋友社区 新浪微博分享

栏目地图
请您留言

乐逍遥建站

发 送

你好!欢迎光临乐逍遥建站!请问您需要购买这个网站吗?
手机:15317096739

稍后再说 现在咨询

在线咨询

Online Consulting