镜像数据库上SQL Server复制

最新推荐文章于 2021-01-23 10:34:35 发布

culuo4781

最新推荐文章于 2021-01-23 10:34:35 发布

阅读量345

点赞数

文章标签：数据库 java mysql git sql

原文链接：https://www.sqlshack.com/sql-server-replication-on-a-mirrored-database/

版权

This article will review on how to configure SQL Server Replication along with mirroring on a database.

本文将回顾如何配置SQL Server复制以及数据库镜像。

复制概述 (Replication Overview)

SQL Server Replication is used to transfer data and objects from one database to another database. There are three main types of replication SQL server supports.

SQL Server复制用于将数据和对象从一个数据库传输到另一个数据库。 SQL Server支持三种主要的复制类型。

Snapshot Replication
快照复制
Merge Replication
合并复制
Transactional Replication
事务复制

快照复制 (Snapshot Replication)

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers. The snapshot agent generates the snapshot and place snapshot files in snapshot folder. The distribution agent applies the snapshot in subscriber.

快照复制完全按照特定时间出现的数据来分发数据，并且不监视数据更新。发生同步时，将生成整个快照并将其发送给订阅服务器。快照代理会生成快照并将快照文件放置在快照文件夹中。分发代理将快照应用到订阅服务器中。

事务复制 (Transactional Replication)

In SQL Server Replication (transactional), the initial snapshot is generated by snapshot agent and stores the snapshot files in snapshot folder and the distribution agent applies the snapshot on subscribers. The log reader agent monitors the transaction log for all incremental changes and copies the transactions to distribution database and these transactions applied on subscribers by distribution agent.

在SQL Server复制（事务性）中，初始快照由快照代理生成，并将快照文件存储在快照文件夹中，而分发代理将快照应用于订阅服务器。日志读取器代理监视事务日志中的所有增量更改，并将事务复制到分发数据库，然后由分发代理将这些事务应用于订阅服务器。

合并复制 (Merge Replication)

In merge replication the snapshot agent generates the snapshot and places snapshot files in snapshot folder. These files include the schema of the objects, bulk copy files, triggers, constraints and stored procedures and the initial snapshot is applied on subscribers by merge agent. The incremental changes on both publisher and subscriber were tracked by merge triggers created on each table added to merge replication and these changes are downloaded/uploaded by merge agent.

在合并复制中，快照代理会生成快照并将快照文件放置在快照文件夹中。这些文件包括对象的架构，大容量复制文件，触发器，约束和存储过程，并且初始快照由合并代理应用于订阅服务器。通过在添加到合并复制的每个表上创建的合并触发器跟踪发布者和订阅者上的增量更改，并且这些更改由合并代理下载/上载。

Below are servers I used while configuring replication on mirroring.

以下是我在配置镜像复制时使用的服务器。

Principal Server: SQL01V
主体服务器：SQL01V
Mirror Server: SQL02V
镜像服务器：SQL02V
Distributor: SQLDIST01V
发行人：SQLDIST01V
Subscriber: SQLSUB01V
订阅者：SQLSUB01V

步骤摘要 (Steps Summary)

Below are the steps for configuring replication along with mirroring on a database. In this setup we are configuring mirroring on publisher database.

以下是在数据库上配置复制和镜像的步骤。在此设置中，我们将在发布者数据库上配置镜像。

Setup a remote distributor
设置一个远程分配器
Enable principal server and mirror server to publish using same remote distributor
启用主体服务器和镜像服务器使用同一远程分发服务器进行发布
Configure publication and add subscriber
配置发布并添加订阅者
Configure mirroring
配置镜像
Change SQL Server Replication agent parameters
更改SQL Server复制代理参数

发行设置 (Distribution Setup)

The distribution database should be configured on a remote SQL server instance and should be available for both principal server and mirror server.

分发数据库应在远程SQL Server实例上配置，并且对于主体服务器和镜像服务器均应可用。

On the distributor “SQLDIST01V”, Login to SQL Server instance using SQL Server management studio.

在分发服务器“ SQLDIST01V”上，使用SQL Server Management Studio登录到SQL Server实例。

Navigate to the replication folder, right click and click on Configure Distribution. The configure distribution wizard opens. Click Next -> select the first option “SQLDIST01V” will act as its own distributor; SQL Server will create a distribution database and log (Here the server name changes as per your distribution server) and click Next -> Input the snapshot folder (give a shared path which will be available for both principal server and mirror server) -> Click Next -> Input the distribution database name (default is distribution) and the locations to save the data file and the log file ->

导航到复制文件夹，右键单击并单击配置分发 。将打开配置分发向导。单击下一步 ->选择第一个选项“ SQLDIST01V”将充当其自己的分发服务器； SQL Server将创建一个分发数据库并进行日志记录（此处服务器名称随分发服务器而变化），然后单击下一步 ->输入快照文件夹（给出一个共享路径，该路径将同时用于主体服务器和镜像服务器）->单击下一步 ->输入分发数据库名称（默认为分发）以及保存数据文件和日志文件的位置->

Click Next and add publishers. Here we need to add both principal server and mirror server so that both can publish using the same distribution. At the end enable configure distribution, click Next and then Finish.

单击下一步，然后添加发布者。在这里，我们需要添加主体服务器和镜像服务器，以便它们都可以使用相同的发行版进行发布。最后，启用配置分发，单击下一步 ，然后单击完成。

The remote distribution setup is completed, and we added both principal server and mirror server as publishers at distributor.

远程分发设置已完成，我们在分发服务器上同时添加了主体服务器和镜像服务器作为发布者。

使主体和镜像能够使用相同的远程分发服务器进行发布 (Enable Principal and Mirror to Publish Using Same Remote Distributor)

Login to principal server “SQL01V” and navigate to replication folder. Right click and select Configure Distribution.

登录到主体服务器“ SQL01V”并导航到复制文件夹。右键单击并选择配置分发 。

The configure distribution wizard opens. Click Next -> select second option Use the following server a Distributor -> Click on Add -> connect to the remote distributor

将打开配置分发向导。单击下一步 ->选择第二个选项使用以下服务器作为分发服务器 ->单击添加 ->连接到远程分发服务器

Click Next -> enter the administrative link password of distributor.

单击下一步 ->输入分发服务器的管理链接密码。

Add distribution administration password

Click Next -> check configure distribution and click Next and then Finish

单击下一步 ->检查配置分发，然后单击下一步 ，然后单击完成

登录到镜像服务器“ SQL02V”，并按照相同的步骤添加远程分发服务器“ SQLDIST01V”。

配置发布和添加订户 (Configuring Publication and Add Subscriber)

Login to principal server “SQL01V”, configure publication add articles to publication and generate the snapshot. Add the subscriber “SQLSUB01V” and apply the snapshot.

登录到主体服务器“ SQL01V”，配置发布将文章添加到发布并生成快照。添加订阅服务器“ SQLSUB01V”并应用快照。

Note:注意： For step by step to configure SQL Server Replication (merge) and adding subscribers please refer to any of the below articles.有关逐步配置SQL Server复制（合并）和添加订阅服务器的信息，请参阅以下任何文章。

SQL Server Replication (Merge) – What gets replicated and what doesn’t SQL Server复制（合并）–复制什么，什么不复制

Foreign key issues while applying a snapshot in SQL Server merge replication 在SQL Server合并复制中应用快照时出现外键问题
For step by step to configure transactional replication please refer to the below article.
SQL Server replication: Configuring Snapshot and Transactional Replication
有关逐步配置事务复制的信息，请参阅以下文章。
SQL Server复制：配置快照和事务复制

配置镜像 (Configuring Mirroring)

Login to principal server and configure mirroring on publisher database between principal server “SQL01V” and mirror server “SQL02V”.

登录到主体服务器，并在主体服务器“ SQL01V”和镜像服务器“ SQL02V”之间的发布者数据库上配置镜像。

Note: Please refer to the below article for configuring mirroring.
How to create a Database Mirroring
注意：请参考以下文章以配置镜像。
如何创建数据库镜像

更改SQL Server复制代理参数 (Change the SQL Server Replication Agent Parameters)

Login to your principal database instance. Navigate to the Replication folder and the publication we created. Right click on the publication and click on Launch Replication Monitor.

登录到您的主体数据库实例。导航到“ 复制”文件夹和我们创建的发布。右键单击发布，然后单击启动复制监视器。

In case of merge replication. Launch replication monitor, navigate to the publication and click on All Subscribers tab. if you have multiple subscribers select any one of the merge agents of the subscriber -> right click and click on Agent Profile. If the existing agent profile is default Click on New and clone new profile from default profile. Give the profile name and description of your choice and uncheck show only parameters used in this profile. Enter your mirror server name in the parameter called “-PublisherFailoverPartner” and click Ok.

如果是合并复制。启动复制监视器，导航到发布，然后单击“ 所有订户”选项卡。如果您有多个订阅者，请选择该订阅者的任一合并代理程序->右键单击并单击代理配置文件 。如果现有代理配置文件是默认配置文件，请单击“ 新建”，然后从默认配置文件克隆新配置文件。提供配置文件名称和您选择的描述，然后取消选中仅显示此配置文件中使用的参数 。在名为“ -PublisherFailoverPartner ”的参数中输入镜像服务器名称，然后单击“确定”。

SQL Server Replication Agent Parameters - PublisherFailoverPartner

Now use the profile you created for the agent. The agent uses parameters in the newly selected profile from next run. Stop and start your agents.

现在使用您为代理创建的配置文件。代理将从下次运行中使用新选择的配置文件中的参数。停止并启动您的代理。

In case of transactional replication, launch replication monitor, navigate to the publication and click on Agents tab. select the log reader agent, right click and click on Agent Profile. If the existing agent profile is default Click on New and clone new profile from default profile. Give the profile name and description of your choice and uncheck show only parameters used in this profile. Enter your mirror server name in the parameter called “-PublisherFailoverPartner” and click Ok.

如果是事务复制，请启动复制监视器，导航到发布，然后单击“ 代理”选项卡。选择日志阅读器代理，右键单击并单击代理配置文件 。如果现有代理配置文件是默认配置文件，请单击“ 新建”，然后从默认配置文件克隆新配置文件。提供配置文件名称和您选择的描述，然后取消选中仅显示此配置文件中使用的参数 。在名为“ -PublisherFailoverPartner ”的参数中输入镜像服务器名称，然后单击“确定”。

We can also create new agent profile from distributor. Login to the distribution server Navigate to Replication -> Local Publications -> Right click and click on Distributor properties -> Click on Profile Defaults -> select the type of agent and click New -> Clone from default profile, give the name and description of agent, change the agent parameters and click ok.

我们还可以从分销商创建新的代理商资料。登录到分发服务器导航到复制 -> 本地发布 ->右键单击，然后单击分发服务器属性 ->单击配置文件默认值 ->选择代理的类型，然后单击新建 ->从默认配置文件克隆，提供名称和描述。代理，更改代理参数，然后单击确定。

We need to take care while creating the agent profiles with publisher failover partner parameter when we have multiple publisher databases being mirrored.

当我们有多个发布者数据库被镜像时，在使用发布者故障转移伙伴参数创建代理配置文件时，我们需要注意。

For example, if we have two different publications and both the publisher databases are mirrored to different mirror servers. Create two different agent profiles with two different server names in “-PublisherFailoverPartner” and use the appropriate profile for each agent.

例如，如果我们有两个不同的发布，并且两个发布者数据库都镜像到不同的镜像服务器。在“ -PublisherFailoverPartner”中使用两个不同的服务器名称创建两个不同的代理配置文件，并对每个代理使用适当的配置文件。

Now login to the principal server perform a failover. Do some transactions on current principal server. Check if the SQL Server Replication jobs were running with out any failure and query your subscriber database to see if the new transactions got replicated or not.

现在登录到主体服务器执行故障转移。在当前主体服务器上执行一些事务。检查SQL Server复制作业是否在正常运行，并查询您的订户数据库以查看是否复制了新事务。

监控方式 (Monitoring)

After failover from principal server to mirror server, all the publications configured will be shown in local publication folder of active server. Replication monitor can be launched from the active server in mirroring.

从主体服务器故障转移到镜像服务器后，所有配置的发布将显示在活动服务器的本地发布文件夹中。可以从活动服务器中以镜像方式启动复制监视器。

In case if you are using the stored procedures to administer replication at mirror database, use the actual publisher name where you configured replication i.e. the principal server. The replication meta data stored in principal server and mirror server is same. The publisher name stored in systems tables at mirror server is original publisher name.

如果要使用存储过程在镜像数据库上管理复制，请使用配置复制的实际发布者名称，即主体服务器。主体服务器和镜像服务器中存储的复制元数据相同。镜像服务器的系统表中存储的发布者名称是原始发布者名称。

Example: If you are adding a pull subscription to the publication after failover using stored procedures, use the actual publisher name i.e. principal server while passing the publisher name as a parameter to replication stored procedures.

示例：如果要使用存储过程在故障转移后向发布添加请求订阅，请使用实际的发布者名称（即主体服务器），同时将发布者名称作为参数传递给复制存储过程。