环境准备:
(1)操作系统:Windows Server 2016 Datacenter
(2)SQL Server 2016
配置SQL 2016 AlwaysOn 要点
1,因为没有域所以需要在”计算机属性“添加计算机的DNS后缀。如后缀是: kk1.com
2,非账户Administrator(是管理员组的新账号也不行),需在运行下列命令
注意:如果登陆Windows Server 2016服务器的账户不是Administrator,需要先以管理员方式运行PowerShell,执行下面的命令:
new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1
3,关闭各个服务器的防火墙。
4,hosts文件路径:C:\Windows\System32\drivers\etc,用名称和IP对应,防止用名称无法对应具体服务器
192.168.63.79 WIN-VE5LU48EGRI.kk1.com 192.168.63.81 WIN-7BEPP8D305E.kk1.com 192.168.63.83 WIN-QF3PITJO4SU.kk1.com 192.168.63.79 WIN-VE5LU48EGRI 192.168.63.81 WIN-7BEPP8D305E 192.168.63.83 WIN-QF3PITJO4SU
5,既然节点没有加入域,那么就不能用域认证,只能用证书认证,因此需要创建证书和端点,下面是2个服务器,3个以上服务器,要在各服务器上创建其他节点的证书
--节点一上执行:创建主密钥/证书/端点,备份证书。 USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'JFAlwaysOnShare2016'; ----密码 GO CREATE CERTIFICATE Cert_DB01 WITH SUBJECT = 'Cert_DB01', START_DATE = '2017-12-01',EXPIRY_DATE = '2099-12-31'; GO BACKUP CERTIFICATE Cert_DB01 TO FILE = '\\JF-SQLDB01\SQLAlwaysOnShare\Cert_DB01.cer'; GO CREATE ENDPOINT [SQLAG_Endpoint] AUTHORIZATION [JF-SQLDB01\administrator] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = ALL,AUTHENTICATION = CERTIFICATE Cert_DB01, ENCRYPTION = REQUIRED ALGORITHM AES) GO --节点二上执行:创建主密钥/证书,备份证书。 USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'JFAlwaysOnShare2016'; GO CREATE CERTIFICATE Cert_DB02 WITH SUBJECT = 'Cert_DB02', START_DATE = '2017-12-01',EXPIRY_DATE = '2099-12-31'; GO BACKUP CERTIFICATE Cert_DB02 TO FILE = '\\JF-SQLDB01\SQLAlwaysOnShare\Cert_DB02.cer'; GO CREATE ENDPOINT [SQLAG_Endpoint] AUTHORIZATION [JF-SQLDB02\administrator] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = ALL,AUTHENTICATION = CERTIFICATE Cert_DB02, ENCRYPTION = REQUIRED ALGORITHM AES) GO --节点一上执行:创建节点二的证书 USE master; GO CREATE CERTIFICATE Cert_DB02 FROM FILE = '\\JF-SQLDB01\SQLAlwaysOnShare\Cert_DB02.cer'; GO --节点二上执行:创建节点一的证书 USE master; GO CREATE CERTIFICATE Cert_DB01 FROM FILE = '\\JF-SQLDB01\SQLAlwaysOnShare\Cert_DB01.cer'; GO
6,配置好故障转移后,关闭故障转移群集,再打开不自动显示群集,这时需要,用名称:如 JF-AlwaysOn1.kk1.com,群集名称是: JF-AlwaysOn1 ,连接时候要加上DNS后缀
备机,如果没有配置名称到IP对应,可以直接在群集名称上直接填IP,也可以。如(127.0.0.1, .)等待
7, 问题1:不能用共享文件夹见证,只好将第三个节点加入集群
问题2: 如果SQL配置过旧集群,加入过旧集群,删除集群后,SQL Server要重新加入到新集群(在SQL配置管理器,先取消启用AlwaysON,重启SQL再启用)
问题3: 故障转移群集连接不上集群,如JF-AlwaysOn1.kk1.com,这时可以切换到adminstrator进入win系统,就可以连接上。