SQLServer发布和订阅数据同步和读写分离

一、订阅发布的特性

1. 实现原理

读写分离简单的说是把对数据库读和写的操作分开对应不同的数据库服务器，这样能有效地减轻数据库压力，也能减轻io压力。
主数据库提供写操作，从数据库提供读操作，其实在很多系统中，主要是读的操作。当主数据库进行写操作时，数据要同步到从的数据库，这样才能有效保证数据库完整性。

2. 实现方法

在MS Sql server中可以使用发布定义的方式实现数据库复制，实现读写分离，复制是将一组数据从一个数据源拷贝到多个数据源的技术，是将一份数据发布到多个存储站点上的有效方式。使用复制技术，用户可以将一份数据发布到多台服务器上。
复制技术可以确保分布在不同地点的数据自动同步更新，从而保证数据的一致性。SQL SERVER复制技术类型有三种，分别是：快照复制、事务复制、合并复制。
SQL SERVER 主要采用出版物、订阅的方式来处理复制。源数据所在的服务器是出版服务器，负责发表数据。出版服务器把要发表的数据的所有改变情况的拷贝复制到分发服务器，分发服务器包含有一个分发数据库，可接收数据的所有改变，并保存这些改变，再把这些改变分发给订阅服务器。

3. 优缺点

(1) 数据的实时性差:数据不是实时同步到只读服务器上的，当数据写入主服务器后，要在下次同步后才能查询到。
(2) 数据量大时同步效率差：单表数据量过大时插入和更新因索引,磁盘IO等问题，性能会变的很差。
(3) 同时连接多个(至少两个)数据库：至少要连接到两个数据数据库，实际的读写操作是在程序代码中完成的，容易引起混乱
(4) 读具有高性能高可靠性和可伸缩:只读服务器，因为没有写操作，会大大减轻磁盘IO等性能问题，大大提高效率;只读服务器可以采用负载均衡，主数据库发布到多个只读服务器上实现读操作的可伸缩性。

二、本地发布的部署

A服务器做为发布：10.0.0.1 DESKTOP-ONBRVN9
B服务器做为订阅：10.0.0.102 serverDB2

1. 新建发布

选择要A服务器，选择 “复制”，“本地发布”，右键新建发布

2. 给文件夹设置权限

注意：.. ReplData这个文件夹是发布所在的文件夹，要保持访问权限，当在c盘时，有时可能会出现无法访问，这里一般会给ReplData这个文件夹加上everyone权限

--注意：
事务发布无法同步没有'主键'的表
快照发布可以同步没有'主键'的表

3. 定制发布的内容

可以筛选库表列，可以定制发布的频率和方式。

比较关键的一步，不同的用户设置可能会导致问题。

-- 推荐使用SQL Server登录名方式
-- 创建一个专用于订阅发布的账户例如：
   sqlpub '赋予sysadmin权限'

4. 配置host文件

配置A服务器host文件
C:\Windows\System32\drivers\etc\hosts中添加一个B服务器的ip和服务器名字
同理在B服务器配置host文件
C:\Windows\System32\drivers\etc\hosts中添加一个A服务器的ip和服务器名字

ip和名字之间隔空格，这个ip可是是内网，也可以是公网

三、本地订阅的部署

1. 新建订阅

在B服务器
选择要B服务器，选择 “复制”，“本地订阅”，右键新建订阅

链接发布服务器

选择查找SqlServer服务器，这里服务器名称写A服务器的名称，不能用ip，用刚才在hosts文件里面那个A服务器的主机名

推荐在分发服务器上推送订阅

如果使用代理需要在发布端发布访问列表中添加该用户

3. 选择订阅的方式内容

这里可以选择订阅的方式和频率

4. 完成创建

最后完成创建。（根据选择的订阅模式，如果失败可能在发布端可以看到，本地看不到。）

四、其他事项

1. 查看监视器

在A服务器上面查看订阅的状况
右键当前的发布，“启动复制监视器”
可以看到当前的状况。

2. 常见故障分析

如果订阅没有成功，或者数据没能同步，可以在这里看看日志，查找原因。

常见问题:
1，  两台主机的 1433端口互相不通
2，  SqlServer Agent 代理没有开启
3，  没有配置 hosts 文件中的ip和主机名映射
4，  ReplData文件夹权限不足
5，  发布订阅前没有进行完整备份

3. 删除故障的订阅发布

1.删除
sp_removedbreplication '数据库名'

权限只有 sysadmin 固定服务器角色成员才能执行 sp_removedbreplication。

订阅发布