在Windows非域环境中安装使用MSSQL 2016 Always On Availability Group
Tags： Always OnAvailability GroupMSSQL可用性组非域环境高可用
对DBA而言，不需要域就可以搭建SQL Server AlwaysOn是Windows Server 2016中最令人兴奋的功能了，它不仅可以降低搭建的成本，而且还减少了部署和运维的工作量。该特性可以使用户不必额外准备一台或者两台（为了避免单点故障）AD域服务器，从而降低了部署的成本；对DBA而言，可以把更多的精力放在数据库上，而不需要去了解AD域的知识，特别是对MySQL和Oracle转型过来的DBA而言，这绝对是一个非常贴心的特性。
Windows Server 2016可以配置无域的Windows群集，因此也能够以此来配置无域的SQL Server AlwaysOn 高可用。
- 搭建MSSQL 2008R2高可用之发布订阅（数据库复制）：https://www.xmmup.com/dajianmssql-2008r2gaokeyongzhifabudingyueshujukufuzhi.html
- 搭建MSSQL 2008R2高可用之日志传输（Log Shipping）：https://www.xmmup.com/dajianmssql-2008r2gaokeyongzhirizhichuanshulog-shipping.html
- 在非域环境下搭建MSSQL 2016高可用之镜像传输：https://www.xmmup.com/zaifeiyuhuanjingxiadajianmssql-2016gaokeyongzhijingxiangchuanshu.html
架构
操作系统：Windows Server 2016 数据中心版 64位简体中文
数据库：SQL Server 2016
节点1：mssql101.lhr.com，192.168.0.101
节点2：mssql102.lhr.com，192.168.0.102
节点3：mssql103.lhr.com，192.168.0.103
虚拟IP包括：
- OS集群故障转移VIP：192.168.0.105
- SQL always on VIP（侦听器IP）：192.168.0.106
非域搭建Alwayson只是省去搭建域控那一部分，其他大同小异。
通用配置
重新生成Windows使用SID
打开克隆完的虚拟机：C:\windows\System32\Sysprep\Sysprep.exe 勾选“通用”选项即可。
关闭防火墙
关闭3台机器的防火墙。
firewall.cpl 和 wf.msc 防火墙设置
关闭自动更新
显示桌面图标
1 | rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,0 |
修改IP
每个节点的计算机不需要加入域，但需要添加DNS后缀，且每个节点的后缀必须要相同
内网网卡：ipv6去掉，禁用TCP/IP上的NetBIOS，设置DNS后缀 lhr.com
其中DNS服务器留空，防止集群转发给DNS服务器注册名称而导致报错
创建账号
所有节点创建相同的帐号、密码，且属于本地Administrators组。
考虑到操作系统已自带了administrator，所以此步骤也可以忽略，但为了安全起见，建议还是自建一个集群专用的账户，专号专用，以便日后管理和安全。
配置注册表
所有节点 打开powershell 输入：
1 | new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1 |
修改主机名
所有节点更改计算机名，加DNS后缀
修改hosts文件
C:\Windows\System32\drivers\etc\
1 2 3 4 5 6 7 8 | 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.105 LHRSQL LHRSQL.lhr.com 192.168.0.106 AGLSNR AGLSNR.lhr.com |
启用网络发现
控制面板-》网络和 Internet-》网络和共享中心-》高级共享设置-》启用网络发现
云环境绑定虚拟IP
如果你的环境是云环境，那么还需要绑定VIP到相应的云服务器才可以。
安装并配置故障转移集群
所有节点安装故障转移集群，重启机器，建WSFC集群。
也可以使用命令行来创建集群：
1 | New-Cluster –Name LHRSQL -Node mssql101.lhr.com,mssql102.lhr.com,mssql103.lhr.com -AdministrativeAccessPoint DNS -StaticAddress 192.168.0.105 |
参数说明：
–Name：集群的名字
-Node：节点，多个节点用英文逗号分隔
-StaticAddress：集群的公共IP
1 2 3 4 5 | --获取集群名 Get-Cluster --群集详情 Get-ClusterResource |
结果：
配置仲裁
基于工作组搭建的集群目前还不支持文件共享的方式做仲裁，官方建议使用磁盘做仲裁或者云见证。我这次验证的时候因为没有条件，所以就没有做仲裁了，在实际生产环境中，请大家利用故障转移集群管理器创建仲裁。
注意：
1、如果是奇数节点，这一步是不需要做的！
2、共享文件夹所在机器入域（域网络）和不入域（独立机器）都无所谓
3、生产环境不要把共享文件夹放在域控上！
我们在使用故障转移集群的时候，只用两种仲裁配置：（多数节点） 和 （多数节点和文件共享）
如果集群节点是奇数，那么使用多数节点；
如果集群节点是偶数，那么使用多数节点和文件共享 （需要配置一个共享文件夹，各个节点都能访问这个共享文件夹，并且共享文件夹所在机器不需要加入域）。
以下是配置示例：
- 配置集群仲裁
- 选择文件共享见证
- 在集群节点之外的一台服务器上创建共享文件夹
\\XIANGMU4TEST01\share，并设置 Everyone 完全控制的权限
安装SQL Server 2016
安装SQL Server 2016
安装过程略。
启用alwayson功能
在SQL Server配置管理器里启用alwayson功能，SQL Server服务的启动帐户名不用改还是用NT Service/MSSQLSERVER
注意一下，如果是用域来搭建alwayson，启动SQL服务是用域用户的，因为现在是非域环境，所以用NT Service/MSSQLSERVER即可
配置administrator登陆
配置证书验证
非域环境的alwayson实质上是用的证书来做节点之间的验证，所以这一步跟搭建镜像的步骤是一样的。
在每个节点上创建alwaysOn的通讯端点（镜像端点）。
在windows server 2016之前，配置端点的加密的方式有两种：域用户授权和证书加密，升级到windows server 2016后，如果不使用域搭建AlwaysOn，那么就只能选择证书加密的方式了。
1、创建一个共享目录，允许AlwaysOn的所有节点均可以读写该目录；
共享目录用来存放端点通讯的证书，在后续的步骤中将会用到。示例为：\\192.168.0.101\ao_share
2、创建端点并备份（创建主密钥/证书/端点，备份证书到共享文件夹中）
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 118 | -- 节点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\ao_share\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\ao_share\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\ao_share\MSSQL103_cert.cer'; GO -- 保证每个节点都有3个节点的证书（在每个节点都执行） CREATE CERTIFICATE MSSQL101_cert FROM FILE = '\\192.168.0.101\ao_share\MSSQL101_cert.cer'; GO CREATE CERTIFICATE MSSQL102_cert FROM FILE = '\\192.168.0.101\ao_share\MSSQL102_cert.cer'; GO CREATE CERTIFICATE MSSQL103_cert FROM FILE = '\\192.168.0.101\ao_share\MSSQL103_cert.cer'; GO |
问题：如果备份或还原证书的时候，报错“无法写入文件 '\192.168.0.101\share\MSSQL102_cert.cer1'。请确保您有写权限、文件路径有效以及该文件尚不存在。”
解决：可以变相临时解决：可以把文件都拷贝到本地盘，不使用共享文件夹，然后进行备份和还原即可，备份还原到本地，手续手动拷贝。
搭建always on集群
新建AG组
1 2 3 4 5 6 7 8 9 | create database lhrdb; ALTER DATABASE [lhrdb] SET RECOVERY FULL; USE lhrdb CREATE TABLE [test1]([id] INT,[name] VARCHAR(100)) INSERT INTO [test1] SELECT 1,'test' backup database lhrdb TO DISK = N'D:\bk\lhrdb_full.bak' with format,stats=5,compression; |
自动故障转移：运行在哪些节点间建立高可用（SQL Server 2016已经支持在3个节点了） 。
同步提交：AlwaysOn同步的模式，对应的还有异步模式，当选择了自动故障转移时，必须勾选同步提交；
可读辅助副本：当该节点为辅助副本时，能否接受只读请求以及以哪种方式接受只读请求（只读意向）。
!(https://pic.xmmup.com/i/img/202112311942647.png)
注意：SQL2016新增了一种数据同步首选项，叫做 “自动种子设定”，无须备份还原数据库，在主副本创建好数据库，选择自动种子设定，SQLServer会帮你在各个辅助副本创建好数据库并同步好数据，相当方便
命令行添加方式：
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 | USE [master] GO CREATE AVAILABILITY GROUP [SQLAG] WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DB_FAILOVER = ON, DTC_SUPPORT = NONE) FOR DATABASE [lhrdb] REPLICA ON N'MSSQL101' WITH (ENDPOINT_URL = N'TCP://MSSQL101.lhr.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)), N'MSSQL102' WITH (ENDPOINT_URL = N'TCP://MSSQL102.lhr.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)), N'MSSQL103' WITH (ENDPOINT_URL = N'TCP://MSSQL103.lhr.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)); GO USE [master] GO ALTER AVAILABILITY GROUP [SQLAG] ADD LISTENER N'AGLSNR' ( WITH IP ((N'192.168.0.106', N'255.255.255.0')), PORT=1433 ); GO USE MASTER; ALTER AVAILABILITY GROUP [SQLAG] JOIN ; ALTER AVAILABILITY GROUP [SQLAG] GRANT CREATE ANY DATABASE; |
添加侦听器
最后，可以使用侦听器名称来连接数据库，数据库显示已同步，配置无域AlwaysOn成功。
故障转移
可以手动，也可以自动，大家可以自行测试。
切换完成之后：
