显示/隐藏全文目录
对DBA而言,不需要域就可以搭建SQL Server AlwaysOn是Windows Server 2016中最令人兴奋的功能了,它不仅可以降低搭建的成本,而且还减少了部署和运维的工作量。该特性可以使用户不必额外准备一台或者两台(为了避免单点故障)AD域服务器,从而降低了部署的成本;对DBA而言,可以把更多的精力放在数据库上,而不需要去了解AD域的知识,特别是对MySQL和Oracle转型过来的DBA而言,这绝对是一个非常贴心的特性。
Windows Server 2016可以配置无域的Windows群集,因此也能够以此来配置无域的SQL Server AlwaysOn 高可用。
架构 操作系统: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
勾选“通用”选项即可。
具体参考:https://www.dbaup.com/wufawanchengyujiaruyuanyinshishitujiarudeyudesidyubenjisuanjidesidxiangtong.html
关闭防火墙 关闭3台机器的防火墙。
firewall.cpl 和 wf.msc 防火墙设置
关闭自动更新
显示桌面图标
rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,0
修改IP 每个节点的计算机不需要加入域,但需要添加DNS后缀,且每个节点的后缀必须要相同
内网网卡:ipv6去掉,禁用TCP/IP上的NetBIOS,设置DNS后缀 lhr.com
其中DNS服务器留空,防止集群转发给DNS服务器注册名称而导致报错
创建账号 所有节点创建相同的帐号、密码,且属于本地Administrators组。
考虑到操作系统已自带了administrator,所以此步骤也可以忽略,但为了安全起见,建议还是自建一个集群专用的账户,专号专用,以便日后管理和安全。
配置注册表 所有节点 打开powershell 输入:
new - itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1
修改主机名 所有节点更改计算机名,加DNS后缀
修改hosts文件 C:\Windows\System32\drivers\etc\
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集群。
也可以使用命令行来创建集群:
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
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信dbaup66,谢谢!
--获取集群名
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组
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.dbaup.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成功。
故障转移 可以手动,也可以自动,大家可以自行测试。
切换完成之后: