SQLServer2016对等复制
业务场景:双活数据库实例可满足热备需求,高可用场景。但是需要注意尽量避免同时操作同一笔业务。另外一旦出现一方宕机或者网络中断,需要严格划分操作的功能与数据范围,避免交叉操作数据导致的数据冲突或异常。
准备工作:
1、网络环境:
Windows Server 2016 + SQLServer 2016
服务器类型 | 服务器名称 |
---|---|
分发服务器 | 初始机器名A |
发布服务器 | 初始机器名B |
订阅服务器 | 初始机器名C |
2、WINDOWS环境检查并配置
2.1、分别在三台服务器"SQL SERVER代理"服务设置为自启动;
2.2、分别关闭三台服务器的防火墙(或把端口1433或指定端口设置为例外);
2.3、分别在三台服务器本地安全策略把"密码最长使用期限"设置为0(不过期)
2.4、分别在三台服务器创建同名WINDOWS账号repl_snapshot
这里简化操作只创建一个账号
3、分别在发布服务器
和订阅服务器
(创建数据库和表),用于搭建同步环境,测试用数据库(TEST)
USE [master]
GO
/****** Object: Database [TEST] Script Date: 2017/4/21 10:51:58 ******/
CREATEDATABASE [TEST]
GO
USE [TEST]
GO
CREATETABLE GUIDTab(
ID UNIQUEIDENTIFIERROWGUIDCOLCONSTRAINT PK_GUIDTab PRIMARYKEYNONCLUSTERED,
Name NVARCHAR(50)
);
CREATETABLE IDENTab(
ID INTIDENTITY(1,1)CONSTRAINT PK_IDENTab PRIMARYKEY,
Name NVARCHAR(50)
);
注意:通过数据库镜像复制有三个前提条件
1.数据库版本由于兼容性问题必须保持一致;
2.复制功能可指定表或全部表进行复制,但是需要复制的表必须包含主键约束;
3.复制表如果含有自增主键会出现冲突问题,此类表需要修改调整自增种子号与自增基数,建议提前设计好;
4.已发布的复制表字段只允许增加新的字段或者删除重新添加,不允许修改原有字段;
4、在分发服务器、发布服务器、订阅服务器
把新创建的WINDOWS账号repl_snapshot
映射为SQL SERVER账号并设置权限,设置SQL SERVER服务器角色
1、新建登录名
5、在发布服务器、订阅服务器
配置SQLServer安装目录读写权限【WINDOWS账号(repl_snapshot)】
默认安装路径:
C:\Program Files\Microsoft SQL Server
C:\Program Files(x86)\Microsoft SQL Server
6、在分发服务器
创建设置共享文件夹(ReplData)并配置读写权限[WINDOWS账号(repl_snapshot)]
共享路径:\[初始服务器名A]\ReplData
7、在发布服务器、订阅服务器
配置有自增主键的表标识列(不用于复制设置为是)
可通过T-SQL设置
--生成T-SQL语句
SELECT'ALTER TABLE '+QUOTENAME(b.name)+' ALTER COLUMN '
+QUOTENAME(a.name)+' ADD NOT FOR REPLICATION;'
FROM sys.columnsAS a
INNER JOIN sys.tablesAS b ON a.object_id= b.object_id
WHERE a.is_identity = 1;
--在订阅端执行(生成T-SQL语句)
--ALTER TABLE [IDENTab] ALTER COLUMN [ID] ADD NOT FOR REPLICATION;
8、配置分发服务器
1、在分发服务器,SSMS—复制—右健—配置分发
后面步骤 一直点”下一步”就行了,直到完成。
2、在分发服务器
添加发布服务器映射,并配置管理链接密码(SSMS—复制—右健—分发服务属性)
9、配置发布服务器
1、添加发布数据库(SMS—复制—本地发布—右健—新建发布)
2、登录分发服务器
3、输入管理链接密码
4、选择需要复制的表或其他资源
5、配置对等拓扑(复制—本地发布—选择发布名称(TEST-Peer)—右健—配置对等拓扑)
下一步(注意看随机生成ID:100)
添加新的对等节点(在空白处—右健—添加新的对等节点)
选择新节点数据库和设置节点ID
设置日志代理账号
设置好账号和密码点确定,下一步至到完成。配置对等拓扑完成
10、测试分别在两台机添加修改数据,查看效果。