SQL Server replication is a relatively old high-availability solution part of the Microsoft world. In fact, there have not been any significant changes to this solution in the recent versions SQL versions, but it is still a widespread mechanism for distributing objects from one database to another and synchronize them. Replication is very useful when you have remote and mobile users accessing your data. Let’s cut to the chase and give you more details about our specific case. 🙂
SQL Server复制是Microsoft世界中相对较旧的高可用性解决方案的一部分。 实际上，在最新版本SQL版本中，此解决方案没有任何重大更改，但它仍然是一种将对象从一个数据库分发到另一个数据库并对其进行同步的广泛机制。 当您有远程和移动用户访问数据时，复制非常有用。 让我们开始追逐，为您提供有关我们特定案例的更多详细信息。 🙂
我们的环境 ( Our environment )
Replication setup, that I am going to describe briefly, is a bit complex. We are using transactional type replications with two publisher instances and three subscriber instances. One of the busiest subscribers, holding five subscriptions, has been acting as a distributor as well. This has been the situation in the last years, but as the time goes and more and more data has been sending back and forth as part of the replication, the latency has been increasing and the IO subsystem was not able to serve us at the expected levels anymore. Our subscribers have been always in catching up situation with the publishers and the performance delay has already been below the critical limit. Problems with our high-availability solution has been resulting in:
我将简要描述的复制设置有点复杂。 我们正在使用具有两个发布者实例和三个订阅者实例的事务类型复制。 拥有五个订阅的最繁忙的订阅者之一也一直充当分发者的角色。 过去几年一直是这种情况，但是随着时间的流逝，越来越多的数据作为复制的一部分来回发送，延迟一直在增加，并且IO子系统无法按预期提供服务水平了。 我们的订阅者一直在与发布者保持同步，并且性能延迟已经低于关键限制。 我们的高可用性解决方案存在以下问题：
- Old reporting data on the subscribers 订户上的旧报告数据
- The permanent issue with the free space for the drive of the Distribution database as it has been holding the undistributed commands 分发数据库驱动器的可用空间的永久性问题，因为它一直持有未分发的命令
- A long conference calls with the stakeholders 与利益相关者的漫长会议
In addition to this, our transactional replication has been setup in a way, that DELETE commands are not being propagated to the subscribers which means that we have a lot more data there than on the publishers:
除此之外，我们的事务复制已以某种方式设置，即DELETE命令不会传播到订阅服务器，这意味着我们那里的数据比发布服务器上的要多得多：
Due to this scenario, we are not able just to reinitialize the subscriptions if there are major problems with them or they just fall badly behind.
由于这种情况，如果订阅存在重大问题，或者订阅落后，我们将无法重新初始化订阅。
拟议的解决方案 ( Proposed Solution )
The cure to our situation was probably the very obvious choice to build a new SQL Server, include it in the replication topology and dedicate it to be acting solely as a distributor. The new high-availability schema would be: two publisher instances, three subscriber instances and one distributor instance. Luckily, we managed to convince all the important people involved, that this is the right solution for us and it has been approved. Next step, the actual work
解决我们的情况的方法可能是构建新SQL Server的非常明显的选择，将其包含在复制拓扑中并专用于充当分发服务器。 新的高可用性架构将是：两个发布者实例，三个订阅者实例和一个分发者实例。 幸运的是，我们设法说服了所有重要人员，这对我们来说是正确的解决方案，并且已经获得批准。 下一步，实际工作
分发数据库的实际移动 ( Actual movement of the Distribution database )
Initially this seemed like a piece of cake, but as we were going deeper and deeper into particular details, part of the implementation plan, this was not the case. We have read and tried literally tons of articles that we managed to find about moving the distribution database, but they were not working for us due to some reasons – either the posts were not described thoroughly enough and important details are missing or they were intended for more simple scenarios.
最初，这似乎只是小菜一碟，但是随着我们对实施计划一部分的特定细节越来越深入，事实并非如此。 我们已经阅读并尝试了数不清的关于移动分发数据库的文章，但是由于某些原因，这些文章对我们没有用–帖子描述得不够详尽，重要细节丢失或打算用于更简单的方案。
After countless numbers of tests, here are the particular steps, we have taken and worked for us:
经过无数次测试后，我们采取了以下特定步骤并为我们工作：
On Publication server:
在发布服务器上：
Script create plus distribution properties for the publication (Generate Script➜Save to File)
发布的脚本创建和分发属性（生成脚本保存到文件）
Script drop publication without distribution properties (Generate Script➜Save to File)
没有分发属性的脚本放置发布（生成脚本➜保存到文件）
From the publications, check the subscriptions’ properties for all subscriptions (accounts being used by the replication), if it is a push subscription. If it is a pull subscription you have to check the properties for the subscriptions from the publisher (to see that it is a pull subscription) and from the subscriber to see the accounts:
从发布中，检查所有订阅（复制使用的帐户）的订阅属性，如果它是推送订阅。 如果是请求订阅，则必须检查发布者的订阅属性（以查看它是请求订阅），也可以检查订阅者的属性：
From the Distributor, script its properties:
在分发服务器上，编写其属性的脚本：
Create distributor without the Subscriber Properties:
创建没有订阅服务器属性的分发服务器：
Drop distributor without the Subscriber Properties:
没有订阅者属性的投递分发者：
From all subscribers:
来自所有订户：
Script create the subscription without Distribution properties:
脚本创建没有分发属性的订阅：
Script drop the subscription without Distribution properties:
脚本删除没有分发属性的订阅：
- Drop all publications with the scripts generated from Step 1 (Run them from the respective publishers) 使用步骤1中生成的脚本删除所有发布（从相应的发布者处运行）
Drop all subscriptions with the scripts generated from Step 4 – you have to use only the parts to be run on the Subscribers. Another option is to Delete them via the GUI – right click on every subscriber and then click “Yes” (you will need to do it twice):
使用步骤4生成的脚本删除所有订阅-您仅需使用要在订阅服务器上运行的部件。 另一个选择是通过GUI删除它们-右键单击每个订阅者，然后单击“是”（您需要做两次）：
From the Distributor, go to Replication➜Right Click➜Disable Publishing and Distribution:
在分发服务器上，转到复制➜右键单击➜禁用发布和分发：
Configure the new Distributor, with enabling the Publisher (local/remote):
配置新的分发服务器，并启用发布服务器（本地/远程）：
- Replication➜Configure Distribution 复制➜配置分发
- Choose the server will act as its own distributor 选择服务器将充当其自己的分发服务器
- Snapshot Folder 快照文件夹
On the Publisher page, remove the Default (local) Publisher and add the remote one (if needed):
在“发布者”页面上，删除“默认（本地）发布者”并添加远程发布者（如果需要）：
- Enter a password that will be used for the connection (it is a new pass) 输入将用于连接的密码（这是新密码）
- Make sure Configure Distribution is selected 确保选择配置分发
Use the scripts from Step 1 to create the publications on the respective servers. You need to change the beginning of the scripts with the name of the new Distributor and the password entered in the previous step:
使用步骤1中的脚本在各自的服务器上创建发布。 您需要使用新分发服务器的名称和在上一步中输入的密码来更改脚本的开头：
/****** Begin: Script to be run on Publisher ******/ /****** Installing the server as a Distributor ******/ USE master EXEC sp_adddistributor @distributor = N'DIMMIROS4', @password = N'new_pass' GO EXEC sp_addsubscriber @subscriber = N'DIMMIROS4', @type = 0, @description = N'' GO /****** End: Script to be run on Publisher ******/Use the scripts from Step 4 to create the Subscriptions – part of the script should be run on the Subscriber and the other part on the Publisher (the other option is to create the Subscriptions from the Replication wizard on every Subscriber):
使用步骤4中的脚本创建订阅-脚本的一部分应在订阅服务器上运行，另一部分应在发布服务器上运行（另一个选项是通过每个订阅服务器上的复制向导创建订阅）：
- Choose the Publisher 选择发布者
- Choose the Publication 选择出版物
- Choose Push or Pull 选择推或拉
- Choose subscription database 选择订阅数据库
- Choose the security 选择证券
- Choose the schedule 选择时间表
Choose carefully if the subscribers are to be reinitialized and when:
仔细选择是否要重新初始化订户，以及何时：
Make sure Create subscription is clicked
确保单击创建订阅
- Launch the Replication Monitor and check the health of the Replication 启动复制监视器并检查复制的运行状况
Note that depending on the specifics in your environment, some of the steps might be a little bit different in some situations (for example based on type of subscriptions or whether you have a remote or local publisher).
请注意，根据您环境中的具体情况，某些步骤在某些情况下可能会略有不同（例如，基于订阅的类型或您是远程发布者还是本地发布者）。
Moving the distribution database is not that simple task as it looks like, especially in complex replication scenarios. Hope this article will help you do this task flawlessly next time you have to
移动分发数据库并不是看起来那么简单的任务，尤其是在复杂的复制方案中。 希望本文能帮助您下次再次完美地完成此任务
Thanks for reading!
谢谢阅读！
1054
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
