(精华)2020年10月16日 数据库调优 分库分表底层详解(读写分离)

发布、订阅都在同一台服务器

本地发布数据库SQLTEST、发布表user_info
本地订阅数据库copy_for_sqltest

一、配置分发

配置分发是发布和订阅前的基础，没有分发库就不能完成。

• 快照文件夹：可以使用默认的，也可以自己自定义（F:\My_Code\MSSQL_ReplData）
• 分发数据库名称用默认的：distribution

二、快照发布

顾名思义，就像拍照片一样，发布服务器对你要同步的表数据做一张快照，快照的数据集保存在本地的快照文件夹。
然后按照你设定的时间间隔向订阅服务器传输快照集，订阅服务器就按照收到的最新快照集完全覆盖当前对应的表数据。

1、创建本地发布
可以看到，配置完分发后，在系统系数库下生成了一个分发数据库distribution
点击新建发布后，弹出的窗体分别为

【新建发布向导】——下一步
【发布数据库】——SQLTEST

2、发布类型

3、选择发布的项目
本地发布选择user_info表

• 如果要对user_info表数据做筛选的话，就添加筛选条件，我这里直接整表发布，下一步

4、快照代理
设置运行快照任务的时间
我设置成每天0：50：0就运行快照，将user_info表的快照集保存在快照文件夹
5、设置快照代理的安全设置
发布命名【本地快照发布】，完成发布的创建
6、快照发布创建成功
7、发布的作业自动生成
创建完本地的快照发布后，在代理作业中，你会发现，自动多出了一些作业。
本地快照文件夹中会发现生成了发布表user_info的快照集
8、创建本地订阅
【新建订阅向导】——下一步
【发布】——发布服务器就是本地服务器的名称咯

9、分发代理位置——推送订阅/请求订阅
因为我这里是在同一台服务器上执行复制，所以推送和订阅都会是本地服务器，也就没什么分别了。

10、订阅服务器
选择另外一个数据库订阅SQLTEST库的user_info表
11、设置分发代理安全性
因为是同一台服务器，所以这里不管选发布还是订阅端运行代理，设置的账号密码都直接是本机账号密码即可。
12、 订阅的同步计划
连续运行就是订阅一直推送/请求
定义计划吧，可以定时执行：时间可以设置在上面的快照生成时间之后
如果要实时地执行同步每一个数据变化的同步的话，默认代理启动时启动就好，不需要实时的话就自定义计划的实行时间。

但是，快照发布这种方式，最好不要设置实时，因为它的同步方式是一次性复制所有订阅的表数据到订阅服务器。
如果发布服务器每做一个小修改都对订阅服务器整个表重新覆盖一次，这会造成频繁的页面数据短时间无法显示问题，因为在覆盖期间，订阅端的表数据会先整表删除，再整表覆盖。

所以，快照发布适合那些更新不频繁，但每次更新都比较大的数据，选择一个不多人使用的时段进行整表更新。

13、初始化订阅
如果上面创建本地发布时没有生成快照的话，这里选立即是不会运行成功的，因为没有对应的快照给订阅端覆盖数据。
选哪个都没关系，都可以后面再启动发布生成快照，然后再启动订阅获取快照

订阅创建成功

14、订阅的作业自动生成
可看到，继发布的作业生成后，再创建订阅后，订阅的作业也自动生成了。
15、发布、订阅创建后的运行测试
② 推送最新快照倒订阅端
③ 看订阅端数据库copy_for_sqltest
已经把发布端的user_info表中整表的数据覆盖了过来
④数据修改
这个，可以在发布的数据库SQLTEST中，将发布表user_info中的数据进行修改，再重复①发布端生成最新快照、② 推送最新快照倒订阅端这两步，就可以把发布数据库SQLTEST中的user_info表生成快照，推送到订阅库opy_for_sqltest，对订阅库的user_info表进行完全覆盖。
（做定时任务也是相同的步骤：①发布端生成最新快照、② 推送最新快照倒订阅端）

16、快照发布原理图
做完上面的步骤，现在再来看这张图，是不是瞬间完全理解了！

①、发布服务器，将要发布的表数据整个做一个快照，放在快照文件夹内

②、发布服务器将快照传给订阅服务器/订阅服务器向发布服务器请求快照

③、快照文件夹中的快照传到订阅服务器上

三、事务发布

与快照发布的整表覆盖到订阅库不同的是，事务发布是将发布端数据库发生的事务日志（也就是数据库的所有增删改操作）发送到订阅端，订阅端再根据事务日志执行一遍发布端执行过的操作。

不过，传输事务流的基础还是快照复制，订阅端还是得有一个初始架构和数据（这个通过快照复制完成），然后订阅端才能在初始架构和数据上，当发布服务器把每一次改动以日志的方式传送到订阅服务器后，进行数据同步。

1、新建发布
2、发布类型
3、选择发布项目
这里看到，和之前快照发布不同的是，多了一个user_info_work表，而前边的两个表居然不能被选择。
这是因为前面的两个表都没有主键，所以我复制了一个和user_info同样的，但是带主键的user_info_work表
4、快照代理
5、设置快照代理的安全设置
这里不同的是，快照代理下面还多出了一个日志读取器代理（如果想设置别的代理，将下面的【使用快照代理的安全设置】勾选去掉就好）

6、事务发布创建成功
【向导操作】——下一步

【完成向导】——命名为【本地事务发布】

7、发布的作业自动生成
与快照发布不同的是，在作业处，多了一个没有【本地事务发布】后缀名的任务。
右键该作业的属性进去查看，发现这个是日志读取的作业
8-14的步骤和快照发布一致
8、创建本地订阅

【发布】——发布服务器就是本地服务器的名称咯
9、分发代理位置——推送订阅/请求订
10、订阅服务器
11、设置分发代理安全
12、订阅的同步计划

如果要实时地执行同步每一个数据变化的同步的话，默认代理启动时启动就好，不需要实时的话就自定义计划的实行时间。

与快照发布不同的是，事务发布适合用于要求实时性较短的环境。

13、初始化订阅
14、订阅的作业自动生成

15、发布、订阅创建后的运行测试
会发现，相比快照发布多了一个【查看日志读取器代理状态】
①对订阅端生成初始架构和数据
这个一般在发布和订阅创建完成时就已经同步完成了，如果查看订阅端的数据库发现表没有同步的话，就重新生成一下初始数据的快照
快照生成后，再重新初始化一下，将其传到订阅端

或者也可以重新初始化所有订阅，重新用最新快照初始化订阅端的表，然后再启动订阅的同步状态
②修改发布端的表数据
③读取发布端的update的事务日志
④将事务日志传到订阅端
⑤查看
订阅库的copy_for_sqltest的表user_info_work，已经同步了发布库的事务
16、事务发布原理图
①、先通过快照复制，将发布服务器的基础架构和表数据生成快照，传到订阅服务器，在订阅服务形成初始架构

②、发布服务器的数据修改后（执行增删改等操作），生成事务日志

③、分发服务器的日志读取代理读取发生改变的数据的事务日志，把这些事务日志保存在发布服务器的发布数据库中。

④、分发服务器的分发代理程序 将分发数据库中的事务日志分发到各个订阅服务器上，然后把历史记录和错误记录在分发数据库中。

四、对等发布
五、合并发布

发布、订阅在不同的服务器

服务器A、服务器B之间，A数据库发布、B数据库订阅。
首先，A服务器和B服务器之间，数据库可互连，这个可以从这篇文章看一下怎么实现【服务器间的数据库远程连接】

建立发布和订阅的步骤和上面在同一服务器基本一致。
不同的是：创建订阅时，推送订阅和请求订阅会有所不同。

一、推送订阅
推送就是按照发布端设定的计划，向订阅端推送快照/事务日志。
（账号填写都是用发布服务器的）
二、请求订阅
请求就是按照订阅端设定的计划，向发布端请求获取快照/事务日志。
运行分发代理进程的账户是填写的订阅服务器的，连接的分发服务器是发布服务器的。

（如果分发服务器又建在另一台服务器的话，又是另一种比较复杂的情况了…）
一些遇到的报错问题
1、运行同步时报错，无法读取快照文件夹中的快照
处理：这是在快照文件夹中没读取到最新的快照集，我重新生成一个最新快照就解决了。

2、无法将【服务器名】配置为分发服务器
处理：这个就是被自己本地的杀毒软件拦截了数据库的进程，打开我的360，发现安全中心的拦截记录出现一些拦截记录。
设置360的防护弹窗模式，每次系统要执行什么操作时，都会弹窗提示，可以自己选择拒绝还是允许
（说作为程序员还用360的话，我无F可说）

3、对快照路径的访问被拒绝
这个就是在不同服务器做发布、订阅，请求订阅时会遇到的问题。

因为请求订阅是要在订阅服务器去访问发布服务器的快照文件夹，如果发布服务器的快照文件夹不共享给订阅服务器的话，订阅服务就无法同步到快照数据。

处理：这个就搜一下如何将文件夹共享给另一个服务就好，这里就不展示了。

4、无法连接到【服务器名】
在与SQL Server建立连接时出现与网络相关的或特定于实例的错误。未找到或无法访问服务器。请验证…
处理：这个出现的时候，也是让我弄了好久，最后在发布服务器的防火墙出站规则中发现，居然是1433端口被禁止了！