Sql Server 2017主从配置之：发布订阅

使用发布订阅模式搭建Sql Server 2017主从同步，类似事件通知机制，基本可以做到准实时同步，可以同时做到一对多的数据同步。
不过发布订阅模式，只能同时数据，不能同步表结构。在创建发布的时候，需要选择需要同步的表对象，如果有新增表需要做同步，那就需要重新选择表对象，然后重新发布快照。
该模式在同步时候，不影响从库使用。

环境准备

两台服务器，配置都是8g2核，50g硬盘，操作系统版本Windows Server 2019 Datacenter

• 主库：server1，192.168.56.110
• 从库：server2，192.168.56.111

本人使用虚拟机做模拟配置，在虚拟机操作系统上做如下配置

• 虚拟机配置两张网卡，以便和主机通讯

• 虚拟机启动远程桌面 ，右键左下角win图标-系统-远程桌面，点击启用

• 创建操作系统账户sqadmin，分配管理员权限

• 为方便测试，关闭系统防火墙，控制面板\系统和安全\Windows Defender 防火墙\自定义设置

• 安装Sql Server 2017企业版，个人的Express版本不支持事务日志复制

• 安装SSMS 19.2版本

两台服务共同配置准备

1、创建系统账号sqadmin，设置管理员权限

2、设置服务启动账户sqadmin

打开Sql Server Configuration Manager，进入SQL Server 服务，将SQL Server服务和SQL Server 代理服务的登录身份都改成sqadmin，并设置自动启动

3、配置服务器代理账户

打开SQL Server Management Studio，链接数据库
右键点击链接，选择属性，打开服务器属性面板，点击安全性选项页，勾选启动代理账户，输入sqadmin账户密码

4、创建数据库账户sqadmin，并设置管理员权限

一、主库 server1 配置

创建快照备份文件夹 c:\dbbackup

创建测试数据库test，并创建表t_user、t_company作为测试

注意：每个表都必须有主键，否则无法同步

create table t_user(            
id bigint primary key,        --主键
name nvarchar(50) not null,          --不为空
);
create table t_company(            
id bigint primary key,        --主键
name nvarchar(50) not null,          --不为空
)
1
2
3
4
5
6
7
8

新建发布

输入刚创建的备份文件夹 c:\dbbackup

选择要同步的数据库

选择事务发布

选择需要同步的表

这里没有筛选，直接下一步

快照代理，选择“立即创建快照…”，然后下一步

点击“安全设置”，配置快照代理账户

选择立即创建快照，然后点击下一步

随便输入一个发布名称，比如test_bakcup，点击完成


打开快照备份文件夹，可以看见表快照已经成功生成！

至此，主库发布创建完成，接下来到从库创建订阅

二、从库 server2 配置

创建备份数据库 test_bak

新建订阅，下来选择查找发布服务器，

输入主库ip，链接到主库

选择主库的订阅

选择选项一，在分发服务器上运行所有代理，然后点击下一步

选择从库的数据库 test_bak

配置分发代理，输入账号sqadmin

配置完成后点击下一步，直到完成

至此，订阅配置完成，可以看见两个表已经同步过来了

三、测试

查看快照代理状态

注意：如果重新选择了表对象，需要在这里点击“启动”，重新生成新的快照，否则新选择的表对象无法同步到从库

查看从库同步状态

1、在主库创建记录

insert into t_user values (1,'01');
insert into t_user values (2,'02');

insert into t_company values (1,'C01');
insert into t_company values (2,'C02');
1
2
3
4
5

2、在从库查询

select * from t_user;
select * from t_company;
1
2