配置 SQL Server 2019 无域环境 AlwaysOn

自 SQL Server 2016 开始，AlwaysOn 不再依赖域环境。本文则以 Windows Server 2019 Datacenter 搭配 SQL Server 2019 Enterprise 为例，介绍无域环境的 AlwaysOn 配置过程。

注意，Windows Server 标准版不支持故障转移群集，SQL Server 标准版也不支持 AlwaysOn，SQL Server的x86版本也不支持AlwaysOn。所以建议选择 x64位企业版的SQL Server，且建议配置心跳ip网络（没有心跳也可以）

准备工作

• 第1步，整体规划

ip地址	主机名	OS版本	SQL版本	角色	可用性模式	故障转移模式
192.168.31.101	sql01	Windows Server 2019 DC	SQL Server 2019 企业版	主要	同步	自动
192.168.31.102	sql02	Windows Server 2019 DC	SQL Server 2019 企业版	辅助	同步	自动
192.168.31.103	sql03	Windows Server 2019 DC	SQL Server 2019 企业版	辅助	异步	手动

• 第2步，修改机器名，比如 节点1改为sql01、节点2改为sql02、节点3改为sql03

:: 节点1 修改为 sql01
wmic computersystem where name="%computername%" call rename name="sql01"

:: 节点2 修改为 sql02
wmic computersystem where name="%computername%" call rename name="sql02"

:: 节点3 修改为 sql03
wmic computersystem where name="%computername%" call rename name="sql03"

• 第3步，因为没有域，所以需要配置 DNS 后缀，比如 xxx.com

:: 设置DNS后缀, 示例改为 xxx.com
reg add "HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters" /v "Domain" /t REG_SZ /d "xxx.com" /f
reg add "HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters" /v "NV Domain" /t REG_SZ /d "xxx.com" /f

• 第4步，重启服务器

shutdown /r /t 0

• 第5步，关闭防火墙，配置 hosts

:: 关闭防火墙
netsh advfirewall set allprofiles state off

:: 配置hosts
type NUL > C:\Windows\system32\drivers\etc\hosts
echo 192.168.31.101 sql01.xxx.com > C:\Windows\system32\drivers\etc\hosts
echo 192.168.31.102 sql02.xxx.com >> C:\Windows\system32\drivers\etc\hosts
echo 192.168.31.103 sql03.xxx.com >> C:\Windows\system32\drivers\etc\hosts
type C:\Windows\system32\drivers\etc\hosts

• 第6步，配置几个实用小工具

curl -o wget.exe http://iso.sqlfans.cn/windows/system32/wget.exe
curl -o unzip.exe http://iso.sqlfans.cn/windows/system32/unzip.exe
move /y wget.exe c:\windows\system32\
move /y unzip.exe c:\windows\system32\

• 第7步，添加 .NET Framework 3.5 功能

cd /d c:\
wget -c http://iso.sqlfans.cn/windows/win2019/sources.zip
unzip sources.zip
dism /online /enable-feature /featurename:NetFX3 /All /Source:C:\sources\sxs /LimitAccess

• 第8步，添加故障转移群集功能

dism /online /get-features | findstr "Failover"

dism /online /enable-feature /featurename:FailoverCluster-FullServer /all
dism /online /enable-feature /featurename:FailoverCluster-Mgmt /all
dism /online /enable-feature /featurename:FailoverCluster-AdminPak /all
dism /online /enable-feature /featurename:FailoverCluster-PowerShell /all
dism /online /enable-feature /featurename:FailoverCluster-AutomationServer /all
dism /online /enable-feature /featurename:FailoverCluster-CmdInterface /all

配置 WSFS

• 第1步，登录节点1，运行 cluadmin.msc 打开故障转移集群管理工具，点击“创建群集”，添加示例3个节点

• 第2步，指定群集名称和vip地址，示例 群集名称：wincluster，群集VIP：192.168.31.222

注意，这个是windows群集的IP，与 AlwaysOn 无关

• 第3步，不要勾选“将所有符合条件的存储添加到群集”，点击“下一步”完成创建

• 第4步，点击新建的群集，右键选择“配置群集仲裁设置”

• 第5步，由于是3个节点，仲裁设置选择“使用默认仲裁配置”

安装 SQL Server 2019

• 第1步，[所有节点] 将 SQL Server 2019 企业版的安装文件 SQL_Server_2019_Enterprise_x64.iso 挂载到 G 盘，使用下面脚本进行自动化安装

mkdir c:\opt\MSSQL\Data
mkdir c:\opt\MSSQL\Log
mkdir c:\opt\MSSQL\Backup

set my_SAPWD=Q8yWuLJ1q_Sth9fA
set my_USERDBDIR=c:\opt\MSSQL\Data
set my_USERDBLOGDIR=c:\opt\MSSQL\Log
set my_BACKUPDIR=c:\opt\MSSQL\Backup
set my_INSTALLSHAREDDIR=C:\Program Files\Microsoft SQL Server
set my_INSTALLSHAREDWOWDIR=C:\Program Files (x86)\Microsoft SQL Server
set my_SYSADMIN=administrator
set my_pid=2C9JR-K3RNG-QD4M4-JQ2HR-8468J

G:\Setup.exe /QS /ACTION=Install /UpdateEnabled=0 /IACCEPTSQLSERVERLICENSETERMS /PID="%my_pid%" /FEATURES=SQLEngine,Replication,FullText,Conn,BC /INSTANCENAME=MSSQLSERVER /SECURITYMODE=SQL /SQLCOLLATION="Chinese_PRC_CI_AS" /SAPWD="%my_SAPWD%" /SQLSVCACCOUNT="SYSTEM" /SQLSVCSTARTUPTYPE="Automatic" /AGTSVCACCOUNT="SYSTEM" /AGTSVCSTARTUPTYPE="Automatic" /ISSVCACCOUNT="SYSTEM" /ISSVCStartupType="Automatic" /SQLSYSADMINACCOUNTS="%my_SYSADMIN%" /SQLUSERDBDIR="%my_USERDBDIR%" /SQLUSERDBLOGDIR="%my_USERDBLOGDIR%" /SQLBACKUPDIR="%my_BACKUPDIR%" /SQLTEMPDBDIR="%my_USERDBDIR%" /SQLTEMPDBLOGDIR="%my_USERDBLOGDIR%" /INSTALLSHAREDDIR="%my_INSTALLSHAREDDIR%" /INSTALLSHAREDWOWDIR="%my_INSTALLSHAREDWOWDIR%" /INSTANCEDIR="%my_INSTALLSHAREDDIR%" /BROWSERSVCSTARTUPTYPE="Disabled" /NPENABLED="1"

• 第2步，[所有节点] 访问如下地址，下载并安装最新的 SQL Server Management Studio

https://learn.microsoft.com/en-us/ssms/sql-server-management-studio-ssms

• 第3步，[所有节点] 创建用户（设置密码永不过期），并更改SQL服务的启动账号，示例改为 sqladmin

:: 创建用户
net user sqladmin kT6f7m_wPKdW4037 /add /Y /expires:never
net localgroup administrators sqladmin /add

:: 更改SQL服务的启动账号
NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER
sc config MSSQLSERVER obj= ".\sqladmin" password= "kT6f7m_wPKdW4037"
sc config SQLSERVERAGENT obj= ".\sqladmin" password= "kT6f7m_wPKdW4037"
sc config MSSQLSERVER start= auto
sc config SQLSERVERAGENT start= auto
NET START MSSQLSERVER
NET START SQLSERVERAGENT

启用 AlwaysOn

• 第1步，[所有节点] 打开 SQL Server 配置工具，先启用 AlwaysOn 可用性组，再重启 MSSQLSERVER 服务，最后在服务器属性里看到启用HADR为True

• 第2步，[节点1] 创建一个共享，授予 sqladmin 读写权限，方便备份恢复

mkdir c:\share
net share share=c:\share /grant:sqladmin,change /remark:"sqlserver backup and restore"

配置 ENDPOINT 端点

• 所有节点：将本机的 sqladmin 账号加入sa群组，并创建 ENDPOINT 端点

DECLARE @login varchar(1024)
SET @login = @@SERVERNAME + '\sqladmin'

DECLARE @sql varchar(1024)
SET @sql = 'USE master' + CHAR(10)
SET @sql = @sql + 'CREATE LOGIN ['+@login+'] FROM WINDOWS' + CHAR(10)
SET @sql = @sql + 'ALTER SERVER ROLE sysadmin ADD MEMBER ['+@login+']' + CHAR(10)
SET @sql = @sql + 'CREATE ENDPOINT Hadr_endpoint STATE = STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)' + CHAR(10)
SET @sql = @sql + 'ALTER ENDPOINT Hadr_endpoint STATE = STARTED' + CHAR(10)
SET @sql = @sql + 'ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE = ON)' + CHAR(10)
SET @sql = @sql + 'ALTER EVENT SESSION AlwaysOn_health ON SERVER STATE = START' + CHAR(10)
SET @sql = @sql + 'GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO ['+@login+']'
print @sql

新建可用性组

• 节点1：新建数据库、可用性组、侦听器，示例 节点2（同步模式、自动故障转移）、节点3（异步模式、手动故障转移）、侦听器（192.168.31.223）

--.新建数据库, 名字 agdb01
USE master; 
CREATE DATABASE [agdb01]; 
ALTER DATABASE [agdb01] SET RECOVERY FULL; 
BACKUP DATABASE [agdb01] TO DISK = N'\\sql01\share\agdb01.bak' WITH COMPRESSION, FORMAT, STATS=5; 
BACKUP LOG [agdb01] TO DISK = N'\\sql01\share\agdb01.trn' WITH COMPRESSION, FORMAT, STATS=5; 

--.新建可用性组, 名字 ag01
USE master; 
CREATE AVAILABILITY GROUP [ag01] 
  WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY) 
  FOR DATABASE [agdb01]
  REPLICA ON 
    N'sql01' WITH (ENDPOINT_URL = N'TCP://sql01.xxx.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), SESSION_TIMEOUT = 15),
    N'sql02' WITH (ENDPOINT_URL = N'TCP://sql02.xxx.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), SESSION_TIMEOUT = 15),
    N'sql03' WITH (ENDPOINT_URL = N'TCP://sql03.xxx.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 60, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), SESSION_TIMEOUT = 15);

--.新建侦听器, 名字 listener01
USE master; 
ALTER AVAILABILITY GROUP [ag01] ADD LISTENER 'listener01' (WITH IP (('192.168.31.223','255.255.255.0')), PORT = 1433);

• 节点2：恢复数据库、加入可用性组

USE master; 
ALTER AVAILABILITY GROUP [ag01] JOIN; 
GO 
RESTORE DATABASE [agdb01] FROM DISK = N'\\sql01\share\agdb01.bak' WITH REPLACE,NORECOVERY; 
RESTORE LOG [agdb01] FROM DISK = N'\\sql01\share\agdb01.trn' WITH NORECOVERY; 
ALTER DATABASE [agdb01] SET HADR AVAILABILITY GROUP = [ag01];

• 节点3：恢复数据库、加入可用性组

USE master; 
ALTER AVAILABILITY GROUP [ag01] JOIN; 
GO 
RESTORE DATABASE [agdb01] FROM DISK = N'\\sql01\share\agdb01.bak' WITH REPLACE,NORECOVERY; 
RESTORE LOG [agdb01] FROM DISK = N'\\sql01\share\agdb01.trn' WITH NORECOVERY; 
ALTER DATABASE [agdb01] SET HADR AVAILABILITY GROUP = [ag01];

新建数据库

• 后续如有创建新库的需求，可参考如下步骤：

--1.在ag主副本上创建db
create database agdb02

--2.在ag主副本上执行
USE master; 
BACKUP DATABASE agdb02 TO DISK = N'\\sql01\share\agdb02.bak'
BACKUP LOG agdb02 TO DISK = N'\\sql01\share\agdb02.bak'
ALTER AVAILABILITY GROUP [ag01] ADD DATABASE agdb02

--3.在所有ag辅助副本上执行
USE master; 
RESTORE DATABASE agdb02 FROM DISK = N'\\sql01\share\agdb02.bak' WITH NORECOVERY 
RESTORE LOG agdb02 FROM DISK = N'\\sql01\share\agdb02.bak' WITH NORECOVERY 
ALTER DATABASE agdb02 SET HADR AVAILABILITY GROUP = [ag01]

• 注意，SQL Server 2016+ 引入 自动种子设定（Automatic Seeding），只需在主副本执行 ALTER AVAILABILITY GROUP [ag名称] ADD DATABASE [db名称]，不需要人工备份、拷贝文件、NORECOVERY 还原，集群内部自动完成全量初始化 + 后续日志同步。

--.前提1.为辅助节点设置自动种子为AUTOMATIC(在主副本上执行)
ALTER AVAILABILITY GROUP [ag名称] MODIFY REPLICA ON N'辅助节点2' WITH (SEEDING_MODE = AUTOMATIC)
ALTER AVAILABILITY GROUP [ag名称] MODIFY REPLICA ON N'辅助节点3' WITH (SEEDING_MODE = AUTOMATIC)

--.前提2.为辅助节点授予建库权限(在所有辅助副本上执行)
ALTER AVAILABILITY GROUP [ag名称] GRANT CREATE ANY DATABASE

灾难演练

• 登录主节点，通过重启 MSSQLSERVER 服务模拟服务器异常，一直 ping 侦听器地址，可以看到vip发生了飘逸，如下图示：