sql always on_Always On可用性组中具有发布者数据库SQL复制

最新推荐文章于 2024-12-02 00:18:43 发布

culuo4781

最新推荐文章于 2024-12-02 00:18:43 发布

阅读量516

点赞数 2

原文链接：https://www.sqlshack.com/sql-replication-with-publisher-database-in-always-on-availability-groups/

版权

sql always on

In this article, we will review how to setup SQL replication with publisher database in Always On availability groups so that replication continuous to sync even after failover to the secondary server in the availability group.

在本文中，我们将回顾如何在Always On可用性组中使用发布者数据库设置SQL复制，以便即使故障转移到可用性组中的辅助服务器后复制也可以继续同步。

The following are the steps involved in setting up SQL Server replication with publisher database in Always on Availability Groups.

以下是在Always on可用性组中使用发布者数据库设置SQL Server复制所涉及的步骤。

Configuring the remote distribution
配置远程分发
Configure publishers to use the remote distribution
配置发布者以使用远程分发
Add the publisher database to the Always on Availability Group
将发布者数据库添加到Always on Availability组
Configuring the SQL Server replication
配置SQL Server复制
Creating the linked servers to subscribers on the secondary servers
在辅助服务器上创建链接到订阅服务器的服务器
Redirect the original publisher to the Always on Availability Group Listener
将原始发布者重定向到Always on Availability组侦听器

Let’s go over these steps one by one.

让我们一步一步地进行这些步骤。

For the demo purposes, I am using the following servers to configure SQL replication.

出于演示目的，我使用以下服务器来配置SQL复制。

SQL01V– Primary server
SQL01V –主服务器
SQL02V– Secondary server
SQL02V –辅助服务器
SQL03V – Remote distribution server
SQL03V –远程分发服务器
SQL03V – Subscriber
SQL03V –订阅服务器

配置远程分发 (Configuring the remote distribution)

We need to configure the distribution database on a remote server and the remote distribution server must be available for both the nodes in the Always on availability group. For demo purposes, I used mssql3 for both distribution servers and as a subscriber server.

我们需要在远程服务器上配置分发数据库，并且“始终在线”可用性组中的两个节点都必须有远程分发服务器。出于演示目的，我将mssql3用作分发服务器和订阅服务器。

Log in to the distribution server mssql3 using SQL Server Management Studio, navigate to the Replication folder, Right-click and click on Configure Distribution. Click Next on Configure Distribution Wizard window.

使用SQL Server Management Studio登录到分发服务器mssql3，导航到Replication文件夹，右键单击并单击Configure Distribution 。在“配置分发向导”窗口上单击“ 下一步 ”。

Select the first option i.e. ‘SQL03V’ will act as its own Distributor; SQL Server will create a distribution database and log and click Next.

选择第一个选项，即“ SQL03V”将充当其自己的分发服务器； SQL Server将创建一个分发数据库并登录 ，然后单击“ 下一步”。

SQL replication remote distribution configuration

In case if your SQL Server Agent service is not set to start automatically, you will see the below window that asks you to select the SQL Server Agent startup behavior. Select “Yes, configure the SQL Server Agent service to start automatically” and click Next.

如果您SQL Server代理服务未设置为自动启动，则会看到以下窗口，要求您选择SQL Server代理的启动行为。选择“ 是，将SQL Server代理服务配置为自动启动 ”，然后单击“ 下一步” 。

This is because the replication setup creates replication agent job’s which are run by SQL server agent that runs as per schedule or continuously to synchronize the data from the publisher to the subscriber or from the subscriber to the publisher. In case, if you select a manual option and your SQL server agent is not running, the data synchronization between publisher and subscriber stops. It is advised to select the “Yes, configure the SQL Server Agent service to start automatically” so that SQL replication runs unattended.

这是因为复制设置会创建复制代理作业，该复制代理作业由SQL Server代理运行，该SQL Server代理按计划运行或连续运行，以同步数据从发布者到订阅者或从订阅者到发布者。如果选择手动选项，并且SQL Server代理未运行，则发布者和订阅者之间的数据同步将停止。建议选择“ 是，将SQL Server代理服务配置为自动启动 ”，以便SQL复制在无人看管的情况下运行。

Enter the snapshot folder location and click Next. The location you entered will be used to store the initial snapshot generated by the snapshot agent. Use the shared path which is accessible to the subscriber in case if your agent runs on the subscriber (pull subscription).

输入快照文件夹位置，然后单击下一步 。您输入的位置将用于存储由快照代理生成的初始快照。如果您的代理在订阅服务器上运行（拉订阅），请使用订阅服务器可以访问的共享路径。

SQL Server replication snapshot folder location

if you want a custom name, Enter the name of the distribution database else set it to default. Enter the location of the data and log file of the distribution database and click Next.

如果需要自定义名称，请输入分发数据库的名称，否则将其设置为默认名称。输入分发数据库的数据和日志文件的位置，然后单击“ 下一步” 。

Click Next. Select configure distribution, Click Next, and Finish.

单击下一步。 选择配置分发，单击下一步，然后单击完成。

Once the distribution database setup is complete, right-click on Replication folder and click on Distributor Propertie

分发数据库设置完成后，右键单击“ 复制”文件夹，然后单击“ 分发服务器属性”。

Click on Publishers, give the password of your choice. This password will be used when configuring the distribution on both nodes (SQL01V and SQL02V) in the Always on Availability group.

单击发布者 ，输入您选择的密码。在始终可用组中的两个节点（SQL01V和SQL02V）上配置分发时，将使用此密码。

Click on Add -> Add SQL Server Publisher. Enter SQL01V as a server name, enter the login credentials and add SQL01V as a publisher. Similarly, add SQL02V as a publisher. In this case, I have SQL01V and SQL02V as possible failover nodes. If you have more than two nodes participating in Always on Availability Group, Add all the nodes as publishers at the distribution server.

单击添加 -> 添加SQL Server Publisher 。输入SQL01V作为服务器名称，输入登录凭据，并将SQL01V添加为发布者。同样，将SQL02V添加为发布者。在这种情况下，我将SQL01V和SQL02V作为故障转移节点。如果您有两个以上的节点参与Always on Availability组，请在分发服务器上将所有节点添加为发布者。

配置发布者以使用远程分发 (Configure publishers to use the remote distribution)

Make sure SQL replication is installed on all the replica nodes in the Always on Availability Group. Executing the following T-SQL script on each replica node. It should return 1.

确保SQL复制安装在Always on Availability组的所有副本节点上。在每个副本节点上执行以下T-SQL脚本。它应该返回1。

USE master;  
GO  
DECLARE @installed int;  
EXEC @installed = sys.sp_MS_replication_installed;  
SELECT @installed;

Now we need to configure the publishers SQL01V and SQL02V to use remote distribution server SQL03V which was set up above.

现在，我们需要配置发布者SQL01V和SQL02V以使用上面设置的远程分发服务器SQL03V。

Log in to the primary node SQL01V using SQL Server Management Studio. Navigate to the Replication folder. Right-click on the Replication folder and click on Configure Distribution. Click Next. select “Use the following server as distributor”. Click on Add and add the remote distribution server SQL03V which was configured above. Click Next.

使用SQL Server Management Studio登录到主节点SQL01V。导航到“ 复制”文件夹。右键单击“ 复制”文件夹，然后单击“ 配置分发” 。单击下一步。 选择“ 使用以下服务器作为分发服务器 ”。单击添加，然后添加上面配置的远程分发服务器SQL03V。单击下一步。

SQL replication configure publisher to use remote distributor

Enter the administrative password you set above. Click Next -> Enable Configure Distribution -> Click Next and Finish.

输入您在上方设置的管理密码。单击下一步->启用配置分发->单击下一步并完成。

Similarly, configure the secondary node SQL02V to use the same remote distribution server. If you have more nodes in the Availability group, configure all the possible failover nodes to use the same remote distribution server.

同样，将辅助节点SQL02V配置为使用同一远程分发服务器。如果“可用性”组中有更多节点，请将所有可能的故障转移节点配置为使用同一远程分发服务器。

将发布者数据库添加到可用性组 (Add the publisher database to the availability group)

After configuring the publishers to use the remote distribution server, we need to add the database to the availability group on which we are going to configure the SQL Server replication. i.e. Publisher database.

在配置发布者以使用远程分发服务器之后，我们需要将数据库添加到要在其上配置SQL Server复制的可用性组。即发布者数据库。

This part can also be done after configuring the SQL replication. Please refer to the below links to configure Always on Availability group and add the database to the availability group.

配置SQL复制后，也可以完成此部分。请参考以下链接来配置“始终在线”可用性组并将数据库添加到可用性组。

配置SQL Server复制 (Configuring the SQL Server replication)

Log in to primary node SQL01V using SQL Server Management Studio, Navigate to the Replication folder and then Local Publications. Right-click and click on New Publication.

使用SQL Server Management Studio登录到主节点SQL01V，依次导航到“ 复制”文件夹和“ 本地出版物” 。右键单击，然后单击新建出版物 。

Click Next and select the database that you want to replicate. Click Next and select the type of replication.

单击“下一步”，然后选择要复制的数据库。单击“ 下一步”，然后选择复制类型。

Click Next. Select 2008 or later and click Next.

单击下一步。 选择2008或更高版本，然后单击“ 下一步”。

Select the tables you want to replicate and click Next.

选择要复制的表，然后单击下一步 。

Enable Create Snapshot Immediately and Click Next.

启用立即创建快照，然后单击下一步 。

create snapshot in SQL Server replication

Configure Agent Security and click Next. Enable Create the Publication. Click Next, Enter the name of the publication in SQL Replication and click Finish. Once you click on Finish, the publication is created, and the snapshot agent is started which generates the snapshot.

配置代理安全性 ，然后单击下一步。 启用创建发布。 单击“ 下一步”，在“ SQL复制”中输入发布的名称，然后单击“ 完成” 。单击完成后，将创建发布，并启动快照代理，该快照代理将生成快照。

Once the snapshot is generated add the subscriber and apply the initial snapshot.

生成快照后，添加订户并应用初始快照。

To add the subscriber, log in to the publisher SQL01V and navigate to Replication -> Local Publications.

要添加订阅者，请登录到发布者SQL01V并导航到复制 -> 本地发布 。

Right-click on the publication you created above and click on New Subscriptions. Click Next. Select the publication and click Next. Select the Agent location and click Next. Click on Add SQL Server subscriber and add SQL03V as a subscriber. Configure agent security and click Next. Schedule the SQL replication agent to run as per your choice. Initialize the subscription Immediately to apply the snapshot immediately after creation. Select the subscription type as Server if you want to republish the subscriber else select the subscription type as Client and create the subscriber.

右键点击您在上方创建的发布，然后点击新建订阅 。单击下一步。 选择出版物，然后单击“ 下一步” 。选择代理位置，然后单击下一步 。单击添加SQL Server订阅服务器，然后将SQL03V添加为订阅服务器。配置代理安全性，然后单击下一步。 安排SQL复制代理根据您的选择运行。立即初始化订阅，以在创建后立即应用快照。如果要重新发布订阅服务器，则将订阅类型选择为“ 服务器” ，否则将订阅类型选择为“ 客户端”并创建订阅服务器。

Once the subscriber is created the initial snapshot is applied and all the incremental data changes are replicated to the subscriber from the publisher and vice versa.

创建订户后，将应用初始快照，并将所有增量数据更改从发布者复制到订户，反之亦然。

在辅助服务器上创建到订阅服务器的链接服务器 (Creating linked servers to the subscribers on the secondary servers)

We need to create a linked server for SQL03V (subscriber) on SQL02V (secondary node). Repeat this for all the SQL replication subscribers on all the possible failover nodes in the Always on Availability group.

我们需要在SQL02V（辅助节点）上为SQL03V（订户）创建一个链接服务器。对Always on Availability组中所有可能的故障转移节点上的所有SQL复制订阅服务器重复此操作。

Please refer to the following sample script to add a linked server.

请参考以下示例脚本以添加链接服务器。

EXEC sp_addlinkedserver   
    @server = 'SQL03V';

将原始发布者重定向到Always on Availability组侦听器 (Redirect the original publisher to the Always on Availability Group Listener)

Log in to the remote distribution server SQL03V using SQL Server management studio. Execute the following script on the distribution database to redirect the original publisher to Always on the listener.

使用SQL Server Management Studio登录到远程分发服务器SQL03V。在分发数据库上执行以下脚本，以将原始发布者重定向到“在侦听器上始终存在”。

USE distribution;  
GO  
EXEC sp_redirect_publisher   
@original_publisher = 'SQL01V',  
@publisher_db = 'pub_AOAG',  
@redirected_publisher = 'MyAGListenerName';

At the distribution database, Execute the following script to validate the redirection.

在分发数据库上，执行以下脚本以验证重定向。

USE distribution;  
GO  
DECLARE @redirected_publisher sysname;  
EXEC sys.sp_validate_replica_hosts_as_publishers  
    @original_publisher = 'SQL01V',  
    @publisher_db = 'pub_AOAG',  
    @redirected_publisher = @redirected_publisher output;

Once the validation is completed, manually failover the primary node to the secondary node and test the replication agent synchronization and verify the data on the SQL replication publishers and the subscribers.

验证完成后，请手动将主节点故障转移到辅助节点，并测试复制代理同步，并验证SQL复制发布者和订阅者上的数据。

结论 (Conclusion)

In this article, we explored how to create SQL replication with a publisher in Always on Availability Group. In case you have any questions, please feel free to ask in the comment section below.

在本文中，我们探讨了如何与Always on Availability组中的发布者创建SQL复制。如果您有任何疑问，请随时在下面的评论部分中提问。