在非域环境下搭建MSSQL 2016高可用之镜像传输
👉 本文共约1513个字，系统预计阅读时间或需6分钟。
- 搭建MSSQL 2008R2高可用之发布订阅（数据库复制）：https://www.xmmup.com/dajianmssql-2008r2gaokeyongzhifabudingyueshujukufuzhi.html
- 搭建MSSQL 2008R2高可用之日志传输（Log Shipping）：https://www.xmmup.com/dajianmssql-2008r2gaokeyongzhirizhichuanshulog-shipping.html
架构
（1） 数据库：SQL Server 1016
（2） 操作系统：Windows Server 1012 R2 DataCenter 64位（数据中心版）
操作系统：都是Windows Server 1012 R2 DataCenter 64位
服务器包括3台，非域环境：
- 主体节点：192.168.0.101
- 镜像节点：192.168.0.102
- 见证服务器：192.168.0.103
其中，101、102和103都需要提前安装好SQL Server 1016数据库！
数据库初始化
主库备份
1 2 3 4 | create database lhrdb2; ALTER DATABASE [lhrdb2] SET RECOVERY FULL; backup database lhrdb2 TO DISK= N'd:\bk\lhrdb2.bak' with format,stats=5,compression; backup log lhrdb2 TO DISK= N'd:\bk\lhrdb2_log.bak' with format,stats=5,compression; |
备库还原
将主库的备份文件传输到备库的目录中：
1 2 | RESTORE DATABASE [lhrdb2] FROM DISK = N'd:\bk\lhrdb2.bak' with NORECOVERY,stats=5; RESTORE LOG lhrdb2 FROM DISK= N'd:\bk\lhrdb2_log.bak' WITH NORECOVERY; |
注意保持数据库为正在还原状态（norecovery）！！！
修改hosts文件
C:\Windows\System32\drivers\etc\
1 2 3 | 192.168.0.101 mssql101 mssql101.lhr.com 192.168.0.102 mssql102 mssql102.lhr.com 192.168.0.103 mssql103 mssql103.lhr.com |
共享目录
将192.168.0.101的D盘的bk目录共享出来，添加everyone权限：
配置证书
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 | -- 节点1 USE master GO -- 4. 创建数据库主密钥 -- （数据库主密钥是对称密钥，用于保护数据库中存在的证书和非对称密钥的私钥） CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456'; GO -- 5. 创建将用于加密可用性组端点的证书 CREATE CERTIFICATE MSSQL101_cert WITH SUBJECT = 'server192.168.0.101 certificate for Availability Group' GO -- 6. 主副本：使用证书创建 AlwaysOn AG 端点并进行身份验证 CREATE ENDPOINT Endpoint_AvailabilityGroup AUTHORIZATION [sa] STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MSSQL101_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL ); GO -- 7. 主副本：将证书导出到文件 BACKUP CERTIFICATE MSSQL101_cert TO FILE = '\\192.168.0.101\bk\MSSQL101_cert.cer'; GO -- 节点2 USE master GO -- 4. 创建数据库主密钥 -- （数据库主密钥是对称密钥，用于保护数据库中存在的证书和非对称密钥的私钥） CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456'; GO -- 5. 创建将用于加密可用性组端点的证书 CREATE CERTIFICATE MSSQL102_cert WITH SUBJECT = 'server192.168.0.102 certificate for Availability Group' GO -- 6. 主副本：使用证书创建 AlwaysOn AG 端点并进行身份验证 CREATE ENDPOINT Endpoint_AvailabilityGroup AUTHORIZATION [sa] STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MSSQL102_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL ); GO -- 7. 主副本：将证书导出到文件 BACKUP CERTIFICATE MSSQL102_cert TO FILE = '\\192.168.0.101\bk\MSSQL102_cert.cer'; GO -- 节点3 USE master GO -- 4. 创建数据库主密钥 -- （数据库主密钥是对称密钥，用于保护数据库中存在的证书和非对称密钥的私钥） CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456'; GO -- 5. 创建将用于加密可用性组端点的证书 CREATE CERTIFICATE MSSQL103_cert WITH SUBJECT = 'server192.168.0.103 certificate for Availability Group' GO -- 6. 主副本：使用证书创建 AlwaysOn AG 端点并进行身份验证 CREATE ENDPOINT Endpoint_AvailabilityGroup AUTHORIZATION [sa] STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MSSQL103_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL ); GO -- 7. 主副本：将证书导出到文件 BACKUP CERTIFICATE MSSQL103_cert TO FILE = '\\192.168.0.101\bk\MSSQL103_cert.cer'; GO -- 保证每个节点都有3个节点的证书（在每个节点都执行） CREATE CERTIFICATE MSSQL101_cert FROM FILE = '\\192.168.0.101\bk\MSSQL101_cert.cer'; GO CREATE CERTIFICATE MSSQL102_cert FROM FILE = '\\192.168.0.101\bk\MSSQL102_cert.cer'; GO CREATE CERTIFICATE MSSQL103_cert FROM FILE = '\\192.168.0.101\bk\MSSQL103_cert.cer'; GO |
配置数据库镜像
在镜像节点192.168.0.102，输入命令如下：
1 | ALTER DATABASE lhrdb2 SET PARTNER = N'TCP://MSSQL101.lhr.com:5022'; |
在主体节点：
1 2 3 4 | ALTER DATABASE lhrdb2 SET PARTNER = N'TCP://MSSQL102.lhr.com:5022'; -- 添加见证服务 ALTER DATABASE lhrdb2 SET WITNESS = N'TCP://MSSQL103.lhr.com:5022'; |
执行完成后，镜像正常启动！
带故障转移的数据库镜像配置完成。
主体节点：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
镜像节点：
打开数据库镜像监视器
故障转移测试
可以关闭主库101，然后会发现，主库自动切换到102上了。
主库已经切换到102上了：
当重新启动主库后，并不会切换回去：
总结
数据库镜像不足的地方在于作为镜像的数据库是无法访问的，无法分担主体的压力，所以微软在SQL Server 1012版中加入了AlwaysOn,AlwaysOn可以通过配置只读路由访问辅助副本，从而分担主要副本服务器的压力。在域环境下配置数据库镜像比较容易；在非域环境下配置数据库镜像需要配置凭证还需要对凭证加密，相对于域环境下复杂一点；由于是测试所以这里都是以域管理员进行创建，实际生产环境中需要控制权限。如果开启镜像出错检查三台服务器的1433,5022端口是否被防火墙阻止了。
注意：数据库服务的启动用户必须是域用户。
