SQL server 2017镜像库主从同步架构部署

SQL server 2017镜像库主从同步架构部署目录1.主库配置2.镜像库配置3.检查状态4.手工故障转移测试-主备切换5.添加见证服务器实现自动主备切换6.自动故障切换测试-主备切换角色 IP 状态 主机名主库 192.168.56.120 可读写 sqldb2镜像库从库 192.168.56.119 不可读 sqldb1仲裁服务器 无 winers说明1.由于无配置仲裁服务器所以不能自动故障转移只能手工主从切换。2.配置仲裁服务器的情况下可以实现故障自动转移3.如果主库宕机强制切换镜像库为主库的情况主从同步需要重新部署4.主从库需要关闭防火墙5.主从库配置共享文件夹D:\ShareFolders1.主库配置说明1.第一步到第八步主库和镜像库同步执行--第一步主备库执行创建数据库主密钥 USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD Pa$$w0rd; /* --删除主密钥 USE master; DROP MASTER KEY */ --第二步创建证书并用主密钥加密 创建证书时默认在创建日期开始一年后过期所以针对证书的创建要注意其过期时间。下面是在“主体服务器”上创建HOST_A_cert证书的创建 USE master GO CREATE CERTIFICATE Host_sqldb2_Cert WITH Subject Host_sqldb2 Certificate, Expiry_Date 2215-01-01; --过期日期 /* --删除证书 USE master; DROP CERTIFICATE Host_sqldb2_Cert */ --第三步创建端点 可以使用下面的代码在主体服务器中创建端点并且指定使用5022,端口端口在镜像配置过程中不强制使用特定端口被占用或者特定端口如1433除外。 --使用 Host_sqldb2_Cert 证书创建端点 IF NOT EXISTS ( SELECT 1 FROM sys.database_mirroring_endpoints ) BEGIN CREATE ENDPOINT [DatabaseMirroring] STATE STARTED AS TCP ( LISTENER_PORT 5022, --修改端点名称可保持不变 LISTENER_IP ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION CERTIFICATE Host_sqldb2_Cert, ENCRYPTION REQUIRED Algorithm AES, ROLE --修改证书名称 ALL ); END --第四步备份证书 备份证书的目的是发送到别的服务器并导入证书以便别的服务器能通过证书访问这台服务器主体服务器。 BACKUP CERTIFICATE Host_sqldb2_Cert TO FILE D:\ShareFolders\Host_sqldb2_Cert.cer; --第五步创建登录账号 针对每个服务器单独创建一个服务器登录账号这里只需要创建一个登录给镜像服务器即可 CREATE LOGIN Host_sqldb1_Login WITH PASSWORD Pa$$w0rd; --给副本镜像库创建的登陆 /* drop login Host_sqldb2_Login */ --第六步创建用户并映射到Step 5中创建的登录账号中 CREATE USER Host_sqldb1_User For Login Host_sqldb1_Login; --第七步使用证书授权用户 创建一个新的证书并使用从伙伴服务器中复制过来的证书导入然后映射step 6中的账号到这个新证书上。 CREATE CERTIFICATE Host_sqldb1_Cert --修改证书名称 副本 AUTHORIZATION Host_sqldb1_User --修改用户名 副本 FROM FILE D:\ShareFolders\Host_sqldb1_Cert.cer; --修改路径 --第八步把Step 5中的登录账号授权访问端口 GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_sqldb1_Login]; --修改登录名–到此为止配置镜像的步骤已经完毕后续会给出尽可能自动化的配置脚本。2.镜像库配置--第一步主备库执行创建数据库主密钥 USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD Pa$$w0rd; /* --删除主密钥 USE master; DROP MASTER KEY */ --第二步创建证书并用主密钥加密 创建证书时默认在创建日期开始一年后过期所以针对证书的创建要注意其过期时间。下面是在“副体服务器”上创建HOST_A_cert证书的创建 USE master GO CREATE CERTIFICATE Host_sqldb1_Cert WITH Subject Host_sqldb1 Certificate, Expiry_Date 2215-01-01; --过期日期 /* --删除证书 USE master; DROP CERTIFICATE Host_sqldb1_Cert */ --第三步创建端点 可以使用下面的代码在副体服务器中创建端点并且指定使用5022,端口端口在镜像配置过程中不强制使用特定端口被占用或者特定端口如1433除外。 --使用 Host_sqldb1_Cert 证书创建端点 IF NOT EXISTS ( SELECT 1 FROM sys.database_mirroring_endpoints ) BEGIN CREATE ENDPOINT [DatabaseMirroring] STATE STARTED AS TCP ( LISTENER_PORT 5022, --修改端点名称可保持不变 LISTENER_IP ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION CERTIFICATE Host_sqldb1_Cert, ENCRYPTION REQUIRED Algorithm AES, ROLE --修改证书名称 ALL ); END --第四步备份证书 备份证书的目的是发送到别的服务器并导入证书以便别的服务器能通过证书访问这台服务器副体服务器。 BACKUP CERTIFICATE Host_sqldb1_Cert TO FILE D:\ShareFolders\Host_sqldb1_Cert.cer; --第五步创建登录账号 针对每个服务器单独创建一个服务器登录账号这里只需要创建一个登录给镜像服务器即可 CREATE LOGIN Host_sqldb2_Login WITH PASSWORD Pa$$w0rd; --给副本镜像库创建的登陆 /* drop login Host_sqldb1_Login */ --第六步创建用户并映射到Step 5中创建的登录账号中 CREATE USER Host_sqldb2_User For Login Host_sqldb2_Login; --第七步使用证书授权用户 创建一个新的证书并使用从伙伴服务器中复制过来的证书导入然后映射step 6中的账号到这个新证书上。 CREATE CERTIFICATE Host_sqldb2_Cert --修改证书名称 副本 AUTHORIZATION Host_sqldb2_User --修改用户名 副本 FROM FILE D:\ShareFolders\Host_sqldb2_Cert.cer; --修改路径 --第八步把Step 5中的登录账号授权访问端口 GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_sqldb2_Login]; --修改登录名 /* USE [master] GO DROP ENDPOINT [DatabaseMirroring] GO */ --到此为止配置镜像的步骤已经完毕后续会给出尽可能自动化的配置脚本。–备份还原数据库/* 这一步把主体服务器上的演示数据库备份并还原到上进行初始化操作 1.完整备份的数据库到共享文件夹 D:\ShareFolders 2.复制备份文件到镜像服务器如果权限足够直接使用共享路径来还原即可 3.以Nonrecovery选项还原数据库到镜像服务器sqldb1 4.日志备份数据库并同样方式还原到 sqldb1 */BACKUP DATABASE mirrordb TO DISK ND:\ShareFolders\mirrordb20260515.bak --修改备份文件名 WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS 10; GO BACKUP DATABASE Demodbmirr TO DISK ND:\ShareFolders\Demodbmirr20260515.bak --修改备份文件名 WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS 10; GO --把备份文件拷贝到镜像库在镜像服务器上还原数据库并使用Nonrecovery方式–备份及还原日志BACKUP LOG mirrordb --修改库名 TO DISK D:\ShareFolders\mirrordb_log20260515.bak --修改文件名 WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS 10; BACKUP LOG Demodbmirr --修改库名 TO DISK D:\ShareFolders\Demodbmirr_log20260515.bak --修改文件名 WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS 10; --把备份文件拷贝到镜像库在镜像服务器上还原数据库并使用Nonrecovery方式–镜像库还原数据库RESTORE DATABASE mirrordb --修改库名 FROM DISK D:\ShareFolders\mirrordb20260515.bak WITH NORECOVERY; -- 还原完整备份但不完成还原过程 RESTORE DATABASE Demodbmirr --修改库名 FROM DISK D:\ShareFolders\Demodbmirr20260515.bak WITH NORECOVERY; -- 还原完整备份但不完成还原过程–还原日志文件RESTORE DATABASE mirrordb FROM DISK ND:\ShareFolders\mirrordb_log20260515.bak WITH STATS 10, NORECOVERY RESTORE DATABASE Demodbmirr FROM DISK ND:\ShareFolders\Demodbmirr_log20260515.bak WITH STATS 10, NORECOVERY至此事务日志恢复完成。–还原完成后 启动镜像Step 1右键主体服务器的主体数据库选择【镜像】Step 2选择【配置镜像】这一步我们主要是获取主体服务器的网络地址看下图的红框部分Step 3在镜像服务器上执行下面脚本注意顺序先要在镜像库从库上执行 也就是镜像库先执行转镜像库配置–启用镜像库Step 4在主体服务器执行下面脚本把镜像库添加成主体的伙伴ALTER DATABASE mirrordb SET PARTNER TCP://sqldb1:5022; GO ALTER DATABASE Demodbmirr SET PARTNER TCP://sqldb1:5022; GO–执行后刷新数据库状可以看到RepA上的镜像配置–主库显示主体已同步–镜像库显示镜像已同步正在还原转 3.检查状态–启用镜像库ALTER DATABASE mirrordb SET PARTNER TCP://sqldb2:5022; GO ALTER DATABASE Demodbmirr SET PARTNER TCP://sqldb2:5022; GO3.检查状态主库状态从库状态查看证书USE master;SELECT * FROM sys.certificates;查看镜像端点状态SELECT name, role_desc, state_desc, connection_auth_desc, encryption_algorithm_descFROM sys.database_mirroring_endpoints;查看所有端点信息select * from sys.endpoints查看镜像同步状态SELECTDB_NAME(database_id) AS DatabaseName,dm.mirroring_role AS MirroringRole,(CASE dm.mirroring_roleWHEN 1 THEN ‘主体’WHEN 2 THEN ‘镜像’END) AS MirroringRoleDesc,dm.mirroring_partner_name AS MirroringPartnerName,(CASE WHEN dm.mirroring_witness_name IS NULLTHEN ‘–’ELSE dm.mirroring_witness_nameEND)AS MirroringWitnessName,dm.mirroring_state AS MirroringState,(CASE dm.mirroring_stateWHEN 0 THEN ‘已挂起’WHEN 1 THEN ‘与其他伙伴断开’WHEN 2 THEN ‘正在同步’WHEN 3 THEN ‘挂起故障转移’WHEN 4 THEN ‘已同步’WHEN 5 THEN ‘伙伴未同步’WHEN 6 THEN ‘伙伴已同步’WHEN NULL THEN ‘无镜像’END) AS MirroringStateDesc,dm.mirroring_safety_level AS MirroringSafetyLevel,(CASE dm.mirroring_safety_levelWHEN 0 THEN ‘未知’WHEN 1 THEN ‘异步’WHEN 2 THEN ‘同步’WHEN NULL THEN ‘无镜像’END) AS MirroringSafetyLevelDesc,dm.mirroring_witness_state AS MirroringWitnessState,(CASE dm.mirroring_witness_stateWHEN 0 THEN ‘见证未知’WHEN 1 THEN ‘见证连接’WHEN 2 THEN ‘见证断开’WHEN NULL THEN ‘无见证’END) AS MirroringWitnessStateDescFROM sys.database_mirroring dmWHERE dm.mirroring_guid IS NOT NULL注册服务器组分别把sqldb1,sqldb2使用sa账号注册select * from sys.database_mirroringwhere database_idDB_ID(‘mirrordb’);4.手工故障转移测试-主备切换切换前sqldb1是主库sqldb2是备库手工切换主从先切换mirrordb数据库,Demodbmirr数据库按照同样的步骤操作即可。刷新数据库状态sqldb1转为从库sqldb2转为主库5.添加见证服务器实现自动主备切换安装数据库忽略。--创建主密钥 USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD Pa$$w0rd; --演示所需否则不要设置这么简单的密码 GO /* --删除主密钥 USE master; DROP MASTER KEY */ --创建证书 USE master; CREATE CERTIFICATE HOST_witness_cert --修该证书名字 WITH SUBJECT HOST_witness certificate--在Winess实例上创建证书命名为 HOST_witness_cert这个选项是描述证书 ,EXPIRY_DATE 2215-01-01 ;--证书过期时间可以适当设置长一点具体按实际需要设置 GO /* --删除证书 USE master; DROP CERTIFICATE HOST_witness_cert */ --创建端点 CREATE ENDPOINT Endpoint_Mirroring STATE STARTED AS TCP ( LISTENER_PORT5022 --使用5022端口这个端口可以改成未被使用的端口但是镜像过程中的所有合作者都应该使用相同的端口 , LISTENER_IP ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION CERTIFICATE HOST_witness_cert --使用证书来授权端点 , ENCRYPTION REQUIRED ALGORITHM AES , ROLE ALL --表示这个端点可以作为任何角色包括主服务器、镜像服务器、见证服务器。具体可看联机丛书。 ); GO /* --删除镜像端点 IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name NEndpoint_Mirroring) DROP ENDPOINT [Endpoint_Mirroring] GO */ BACKUP CERTIFICATE HOST_witness_cert TO FILE D:\ShareFolders\HOST_witness_cert.cer; GO --在见证服务器上为主体、镜像服务器创建以证书为验证的账号、用户名及端点。 --在Witness实例上创建一个登录名给Principal实例 USE master; CREATE LOGIN HOST_sqldb1_login WITH PASSWORD Pa$$w0rd; GO --创建一个用于给这个登录名 CREATE USER HOST_sqldb1_user FOR LOGIN HOST_sqldb1_login; GO --让该帐号使用证书授权 CREATE CERTIFICATE HOST_sqldb1_cert AUTHORIZATION HOST_sqldb1_user FROM FILE D:\ShareFolders\HOST_sqldb1_cert.cer GO --授予这个新账号连接端点的权限 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_sqldb1_login; GO /* --删除账号 DROP LOGIN HOST_A_user */ --在Witness实例上创建一个登录名给Mirror实例 USE master; CREATE LOGIN HOST_sqldb2_login WITH PASSWORD Pa$$w0rd; GO --创建一个用于给这个登录名 CREATE USER HOST_sqldb2_user FOR LOGIN HOST_sqldb2_login; GO --让该帐号使用证书授权 CREATE CERTIFICATE HOST_sqldb2_cert AUTHORIZATION HOST_sqldb2_user FROM FILE D:\ShareFolders\HOST_sqldb2_cert.cer GO --授予这个新账号连接端点的权限 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_sqldb2_login; GO /* --删除账号 DROP LOGIN HOST_B_user */ --分别在sqldb1和sqldb2中执行下面语句为见证服务器创建连接端点的权限 USE master; CREATE LOGIN HOST_witness_login WITH PASSWORD Pa$$w0rd; GO --创建一个用于给这个登录名 CREATE USER HOST_witness_user FOR LOGIN HOST_witness_login; GO --让该帐号使用证书授权 CREATE CERTIFICATE HOST_witness_cert AUTHORIZATION HOST_witness_user FROM FILE D:\ShareFolders\HOST_witness_cert.cer GO --授予这个新账号连接端点的权限 GRANT CONNECT ON ENDPOINT::DatabaseMirroring TO HOST_witness_login; GO --然后在主体服务器上执行下面语句加入见证服务器 ALTER DATABASE mirrordb SET WITNESS TCP://witness:5022 ALTER DATABASE Demodbmirr SET WITNESS TCP://witness:5022 --分别在sqldb1和sqldb2中执行下面语句为见证服务器创建连接端点的权限 USE master; CREATE LOGIN HOST_witness_login WITH PASSWORD Pa$$w0rd; GO --创建一个用于给这个登录名 CREATE USER HOST_witness_user FOR LOGIN HOST_witness_login; GO --让该帐号使用证书授权 CREATE CERTIFICATE HOST_witness_cert AUTHORIZATION HOST_witness_user FROM FILE D:\ShareFolders\HOST_witness_cert.cer GO --授予这个新账号连接端点的权限 GRANT CONNECT ON ENDPOINT::DatabaseMirroring TO HOST_witness_login; GO至此配置完成。查看镜像库状态可以看到见证服务器已经配置连接模式是带自动故障转移的高安全模式。6.自动故障切换测试-主备切换切换前sqldb1,sqldb2的状态停止sqldb1的服务再次查看sqldb2的状态已经自动切换为主体但是由于sqldb1服务是关闭的所以显示断开连接。启动sqldb1的服务并刷新sqldb1,sqldb2的状态。Sqldb1已经自动转为从库。Sqldb2已经连接。主从同步恢复正常。