SqlServer数据同步
SqlServer数据同步
永不匿名
一、项目需求
1. 数据库同步
- 实现两个Sqlserver之间的数据库同步
- 对实时性有较高要求，数据同步延时尽量在1S之内
- 可以全库、单表、多表的数据同步
2. 读写分离
- 数据同步之后程序上就可以通过分别使用不同的DbContext实现程序上的读写分离
3. 双机热备
- 即使程序没有实现读写分离，数据库同步之后也能够作为双机热备的方案，此时对数据库同步时间的实施性要求就不高了
4. 实现效果
- 内网A、B两个Sqlserver数据库服务器
- A服务器某库表结构、数据、存储过程、自定义函数发生变化时自动同步到B服务器的对应数据库中
二、实现过程
1. 环境准备
1） 服务器环境准备
- 这里使用Hyper-V搭建了WindowsServer2016的服务器两台
- 固定2台服务器的IP地址，使其在同一个网段下，这里A服务器的IP地址为：172.31.212.242，B服务器的IP地址为：172.31.212.243
- 两台服务器分别安装Sqlserver2014
- 由于没有使用域控，Sqlserver的复制功能必须使用主机名作为的登录名，所以局域网内需要确保两台两台服务器的1433端口互通
- 由于需要共享共享文件夹，端口135，136，137，138，139，445最好也都能互通
2）创建快照文件夹并共享
- 这里在主数据库服务器C盘下创建快照文件夹并共享（DataShare，名字随便，路径随便，不要拘泥于此），请确保此文件夹已共享并设置了适当的权限，共享此文件夹，在从数据库服务器上使用【\\【计算机名】\DataShare】看看是否能够正常打开该文件夹
- 后续发布的事务将存储在改文件夹下，用于数据的共享
2. 数据库配置
1） 数据库安装
- 数据库的安装不再过多赘述，安装的时候勾选实例的时候记得一定要勾选上【复制】功能，如果没有其他的别的额外要求，建议数据库功能安装时选择【全选】
- 由于本机环境没有域控服务器，所以这里使用了混合身份验证模式，默认使用【sa】，如果有其他需求，自行更改
- 数据库安装完成后使用 db_a、db_b分别连接数据库如下图
2）数据库配置
- 启动数据库的代理
- 配置数据库的服务器名，使用以下SQL语句查询出来的服务器名称和咱们配置在HOST程序里边的不一样，接下来咱们需要为发布服务器配置别名（如果直接使用查询出来的服务器名称，则不需要再HOST程序配置）。
USE master
-- 获取服务器当前的额名字
SELECT @@SERVERNAME
SELECT SERVERPROPERTY('SERVERNAME')
3）分发配置
1、找到数据库服务器下的【复制】--【本地发布】，选择【新建发布】。如下图
2.选择待发布的数据库。如下图：
3、选择发布类型。这里选择的默认类型【事务发布】。几种发布类型的区别，SQL SERVER都在下面给出了说明。如下图：
4、勾选需要发布的内容，针对疫情防控系统只需要勾选A16开头的数据库即可。如下图：
筛选器不需要管，直接下一步
、
5、设置代理安全性。如下图：
7、填写发布名称，完成发布
]
4） 订阅配置
1、找到数据库服务器下的【复制】--【本地订阅】，选择【新建订阅】。如下图：
2、选择订阅的发布。如下图：
3、选择分发代理的位置；如下图：
4、选择订阅服务器上的存放同步过来的数据的一个或者多个目标数据库。如下图：
若要添加多个订阅数据库，则点击【添加订阅服务器】。如下图：
5、设置分发代理的安全性。如下图：
6、设置同步计划。如下图：
7、完成订阅。如下图：
这样就完成了发布与订阅的整个流程。
3. 效果验证
其他：
1. 一些常用SQL
-- 当数据库编程“单用户”时将其改为多用户
use master;
ALTER DATABASE SyncTestDb SET MULTI_USER;
-- 删除数据库
Drop DATABASE SyncTestDb ;
-- 将数据设置单用户
USE [master]
ALTER DATABASE SyncTestDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE SyncTestDb
-- 当提示“当前数据库正用于复制，无法删除时”使用
sp_removedbreplication 'SyncTestDb'
Drop DATABASE SyncTestDb ;
-- 查看发布的相关信息
select * from msdb..MSdistpublishers
select * from distribution..MSpublisher_databases
select * from distribution..MSpublications
select * from distribution..MSarticles
select * from distribution..MSsubscriptions2. 数据库复制功能的兼容性，通常发布者和分发服务器是同一个服务器（微软官网：https://docs.microsoft.com/zh-cn/sql/relational-databases/replication/replication-backward-compatibility?view=sql-server-ver15）
（事务和快照复制兼容性矩阵）
| 发布者 | 分发服务器 | 订阅服务器 |
| SQL Server 2019 | SQL Server 2019 | SQL Server 2019 SQL Server 2017SQL Server 2016 |
| SQL Server 2017 | SQL Server 2019 SQL Server 2017 |
SQL Server 2019 SQL Server 2017SQL Server 2016SQL Server 2014 |
| SQL Server 2016 | SQL Server 2019 SQL Server 2017SQL Server 2016 |
SQL Server 2019 SQL Server 2017SQL Server 2016SQL Server 2014SQL Server 2012 |
| SQL Server 2014 | SQL Server 2019 SQL Server 2017SQL Server 2016SQL Server 2014 |
SQL Server 2017 SQL Server 2016SQL Server 2014SQL Server 2012SQL Server 2008 R2SQL Server 2008 |
| SQL Server 2012 | SQL Server 2019 SQL Server 2017SQL Server 2016SQL Server 2014SQL Server 2012 |
SQL Server 2016 SQL Server 2014SQL Server 2012SQL Server 2008 R2SQL Server 2008 |
| SQL Server 2008 R2 SQL Server 2008 |
SQL Server 2019 SQL Server 2017SQL Server 2016SQL Server 2014SQL Server 2012SQL Server 2008 R2SQL Server 2008 |
SQL Server 2014 SQL Server 2012SQL Server 2008 R2SQL Server 2008 |
（合并复制兼容性矩阵）
| 发布者 | 分发服务器 | 订阅服务器 |
| SQL Server 2019 | SQL Server 2019 | SQL Server 2019 SQL Server 2017SQL Server 2016SQL Server 2014SQL Server 2012SQL Server 2008 R2SQL Server 2008 |
| SQL Server 2017 | SQL Server 2019 SQL Server 2017 |
SQL Server 2017 SQL Server 2016SQL Server 2014SQL Server 2012SQL Server 2008 R2SQL Server 2008 |
| SQL Server 2016 | SQL Server 2019 SQL Server 2017SQL Server 2016 |
SQL Server 2016 SQL Server 2014SQL Server 2012SQL Server 2008 R2SQL Server 2008 |
| SQL Server 2014 | SQL Server 2019 SQL Server 2017SQL Server 2016SQL Server 2014 |
SQL Server 2014 SQL Server 2012SQL Server 2008 R2SQL Server 2008 |
| SQL Server 2012 | SQL Server 2019 SQL Server 2017SQL Server 2016SQL Server 2014SQL Server 2012 |
SQL Server 2012 SQL Server 2008 R2SQL Server 2008 |
| SQL Server 2008 R2 SQL Server 2008 |
SQL Server 2019 SQL Server 2017SQL Server 2016SQL Server 2014SQL Server 2012SQL Server 2008 R2SQL Server 2008 |
在发布库上给 C:\Program Files\Microsoft SQL Server\120\COM 中 设置everyone所有 权限
发布于 2022-08-24 07:44
漫思
