显示/隐藏全文目录
对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
虚拟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成功。
故障转移 可以手动，也可以自动，大家可以自行测试。
切换完成之后：
