MSSQL On Linux AlwaysOn

1：初始化服务器
（1）：修改主机名并重启服务器（所有节点）
注：三个节点均需修改主机名，主机名可以根据实际情况设置。
hostnamectl set-hostname MSSQL001 #修改node1主机名
hostnamectl set-hostname MSSQL002 #修改node2主机名
hostnamectl set-hostname MSSQL003 #修改node3主机名

（2）：修改主机hosts文件（所有节点）
vim /etc/hosts #编辑所有节点hosts文件添加以下内容
192.168.15.201 MSSQL001
192.168.15.202 MSSQL002
192.168.15.203 MSSQL003

注：在各个节点ping其他节点主机名，查看网络是否联通。
2：安装MSSQL Server
（1）：下载MSSQL Server On Linux YUM源（所有节点）
curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo

（2）：下载并安装MSSQL Server（所有节点）
yum -y install mssql-server
注：这个过程可能比较漫长，可以手动去镜像站点下载软件包再上传至服务器进行安装。

（3）：初始化MSSQL Server（所有节点）
/opt/mssql/bin/mssql-conf setup #启动配置，选择授权版本，设置SA(管理员)密码

版本：这里我们选择开发者版本2，开发者版本拥有企业版所有功能
语言：这里我们选择简体中文
SA ：SA是管理员账户，所以设置密码为管理员密码，一定要记住此密码

（4）：查看MSSQL Server服务状态（所有节点）
systemctl status mssql-server #查看mssql-server状态
注：默认MSSQL Server配置后会自动运行

netstat -anpt #查看端口，MSSQL Server默认使用1433端口提供服务

5）：启动MSSQL Server的SQLServer-Agent服务（所有节点）
/opt/mssql/bin/mssql-conf set sqlagent.enabled true #开启SQLServer-Agent服务
systemctl restart mssql-server #重启SQLServer

注：在SQL Server 2017 On linux一些较早版本中，SQLServer-Agent服务为独立的软件包，需要单独安装，在3015版以后合并在SQLServer主程序软件包中，成为主程序的一个功能，使用指令开启功能并重启服务器就可以使用。

（6）：设置firewalld防火墙（所有节点）
firewall-cmd --zone=public --add-port=1433/tcp --permanent #开启防火墙1433端口
firewall-cmd --reload #重载防火墙规则
setenforce 0 #关闭SELinux
sed -i ‘s/SELINUX=.*/SELINUX=disabled/g’ /etc/selinux/config #永久关闭SELinux
systemctl enable mssql-server #SQLServer开机自起

（7）：登录MSSQL Server（所有节点）
使用Microsoft SQL Server Management Studio连接数据库

服务器名称：只能输入服务器的IP地址，和windows上基于域环境有所不同
身份验证 ：只能选择SQL Server身份认证，因为Linux下没有基于域环境部署
登录名 ：MSSQL Server管理员账号，及SA
密码 ：MSSQL Server管理员密码，及SA账号的密码 
登录三个节点的MSSQL Server
注：如果未开启MSSQLServer-Agent功能，那么登陆后代理会显示×
3：配置证书
（1）：开启MSSQL Server的hadr功能（所有节点）
/opt/mssql/bin/mssql-conf set hadr.hadrenabled 1 #开启hadr功能
systemctl restart mssql-server #重启服务使其生效

（2）：启用AlwaysOn AG端点默认端口（所有节点）
firewall-cmd --zone=public --add-port=5022/tcp --permanent #打开防火墙5022端口
firewall-cmd --reload #重载防火墙使其生效

（3）：数据库创建密钥并创建端点（所有节点）
MSSQL001节点：新建查询
创建证书：
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘123456’;
CREATE CERTIFICATE MSSQL001_certificate WITH SUBJECT = ‘MSSQL001 certificate for AG’ ,EXPIRY_DATE=‘06/06/2066’;
CREATE ENDPOINT [Hadr_endpoint]
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL )
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE MSSQL001_certificate,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
)
BACKUP CERTIFICATE MSSQL001_certificate
TO FILE = ‘/var/opt/mssql/data/MSSQL001_certificate.cer’

注解：
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘zxc@123456’;
#创建数据库的主密钥，主密钥是对称密钥，用于保护数据库中存在的证书和非对称私钥
CREATE CERTIFICATE MSSQL001_certificate WITH SUBJECT = ‘MSSQL001 certificate for AG’
#创建用于加密可用性组端点的证书
CREATE ENDPOINT [Hadr_endpoint]
#创建一个名为Hadr_endpoint的端点
STATE = STARTED
#端点状态设置为已启动的
AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL )
#端点使用的协议为TCP（也可以使用HTTP协议），端点监听端口为5022，监听服务器地址为所有地址
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE MSSQL001_certificate,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
)
#绑定端点认证所使用的证书（上一步我们创建的证书），设置端点认证的算法为AES，角色为所有
BACKUP CERTIFICATE MSSQL001_certificate
TO FILE = ‘/var/opt/mssql/data/MSSQL001_certificate.cer’
#将我们创建的证书导出到指定位置（/var/opt/mssql/data/MSSQL001_certificate.cer）


（4）：将证书传输到其他节点（所有节点）
ll /var/opt/mssql/data/ #查看以cer后缀的证书

MSSQL001节点：将其证书MSSQL001_certificate.cer拷贝至MSSQL002和MSSQL003节点
scp /var/opt/mssql/data/MSSQL001_certificate.cer root@MSSQL002://var/opt/mssql/data/
scp /var/opt/mssql/data/MSSQL001_certificate.cer root@MSSQL002://var/opt/mssql/data/

ll /var/opt/mssql/data/ #在所有节点查看是否存在对方节点的证书

chown –R mssql:mssql /var/opt/mssql/data/ #在所有节点修改其他节点证书的所有者和所属组
ll /var/opt/mssql/data/ #在所有节点再次查看所有证书的权限是否正确

（5）：创建登录账号和用户，并导入证书进行授权（所有节点）
MSSQL001节点：新建查询
CREATE LOGIN AG_Login WITH PASSWORD = ‘123456’;
CREATE USER AG_Login FOR LOGIN AG_Login;
CREATE CERTIFICATE MSSQL002_certificate
AUTHORIZATION AG_Login
FROM FILE = ‘/var/opt/mssql/data/MSSQL002_certificate.cer’
CREATE CERTIFICATE MSSQL003_certificate
AUTHORIZATION AG_Login
FROM FILE = ‘/var/opt/mssql/data/MSSQL003_certificate.cer’
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [AG_Login];

注：
CREATE LOGIN AG_Login WITH PASSWORD = ‘123456’;
#创建一个名为AG_Login的登录名，并设置登录名密码
CREATE USER AG_Login FOR LOGIN AG_Login;
#为登录名AG_Login创建一个用户，名为AG_Login，在MSSQL Server中登录名和用户区别很大
CREATE CERTIFICATE MSSQL002_certificate
AUTHORIZATION AG_Login
FROM FILE = ‘/var/opt/mssql/data/MSSQL002_certificate.cer’
#创建一个证书名为MSSQL002_certificate，证书来自于我们之前导入的MSSQL002_certificate.cer证书
#并将证这个证书授权给用户名AG_Login
CREATE CERTIFICATE MSSQL003_certificate
AUTHORIZATION AG_Login
FROM FILE = ‘/var/opt/mssql/data/MSSQL003_certificate.cer’
#创建一个证书名为MSSQL003_certificate，证书来自于我们之前导入的MSSQL003_certificate.cer证书
#并将证这个证书授权给用户名AG_Login
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [AG_Login];
#将我们之前创建的端点[Hadr_endpoint]的连接权限授权给用户名AG_Login
#这段语句的意思就是AG_Login用户能使用新创建的两个证书去连接端点，而端点相当于一个连接器
#用户AG_Login能用新创建的两个证书去连接其他两台服务器

4：创建AlwaysOn AG
（1）：MSSQL001节点：创建数据库Test_AG，并完整备份数据库




（2）：MSSQL001节点：创建可用性组向导

打开主副本的服务器的“Always On高可用性”，右键点击“可用性组”，点击“新建可用性组向导”。可以根据向导提示一步一步创建可用性组。

（3）：MSSQL001节点：跳过简介

（4）：MSSQL001节点：创建可用性组Test_AG
创建一个名称为“Test_AG”的可用性组。在实际的生产环境中应根据实际情况设置相应的名称。
集群类型
Windows Server 故障转移群集：当可用性组托管在属于 Windows Server 故障转移群集的 SQL Server 的实例上时使用，以实现高可用性和灾难恢复。 适用于所有受支持的 SQL Server 版本。
EXTERNAL：当可用性组托管在由外部群集技术（例如 Linux 上的 Pacemaker）管理的 SQL Server 的实例上时使用，以实现高可用性和灾难恢复。适用于 SQL Server 2017 (14.x) 及更高版本。
NONE ：：当可用性组托管在不由群集技术管理的 SQL Server 的实例上时使用，以实现读取缩放和负载均衡。适用于 SQL Server 2017 (14.x) 及更高版本。

数据库级别运行状况检测：（次选项根据实际情况勾选）
在之前的 AlwaysOn 2012 和 2014 中，如果实例健康出现问题，将触发故障转移。如果有一个数据库有问题，只要实例OK，可用性组就不会故障转移。
在 AlwaysOn 2016 以后，勾选之后，不论是一个实例有问题，还是一个或多个数据库有问题，都会发生故障转移。

（5）：MSSQL001节点：选择可用性组的用户数据库：
在使用图形界面创建可用性组时，需要选择一个数据库，将这个数据库作为可用性组的第一个可用数据库。此数据库满足条件的先决条件时需对此数据库进行一次完整备份。此前步骤我们已经创建了一个名为“Test_AG”的数据库，并进行备份，所以此时勾选此数据库然后进行下一步。

（6）：MSSQL001节点：添加副本并进行设置
指定副本，点击添加副本，选择需要加入可用性组的MSSQLServer实例，使用sa账户（管理员账号）登录，以添加副本。此处添加两个MSSQL Server实例，MSSQL002，MSSQL003实例。

可用性模式：在 AlwaysOn 可用性组中，“可用性模式”是一个副本属性，该属性确定某一给定可用性副本是否可在同步提交模式下运行。 对于每个可用性副本，必须为同步提交模式或异步提交模式配置可用性模式。 如果主要副本配置为“异步提交模式”，则它不会等待任何次要副本将传入的事务日志记录写入磁盘（以便强制写入日志）。 如果某一给定的辅助副本配置为异步提交模式，则主副本不会等待该辅助副本强制写入日志。 如果主要副本和某一给定次要副本都配置为同步提交模式，则主要副本将等待次要副本，以便确认它已强制写入日志（除非次要副本在主要副本的会话超时期限内未能使用 ping 命令联系上主要副本）。
注：如果某一辅助副本超过了主副本的会话超时期限，则主副本将暂时切换到该辅助副本的异步提交模式。 在该辅助副本重新与主副本连接后，它们将恢复同步提交模式。

异步提交模式：异步提交模式是一种灾难恢复解决方案，适合于可用性副本的分布距离较远的情况。 如果每个辅助副本都在异步提交模式下运行，则主副本不会等待任何辅助副本强制写入日志， 而会在将日志记录写入本地日志文件后，立即将事务确认发送到客户端。主副本使用与针对异步提交模式配置的辅助副本相关的最小事务滞后运行。 如果为当前主副本配置了异步提交可用性模式，则它将通过异步方式为所有辅助副本提交事务，而不管这些副本各自的可用性模式设置如何
。
同步提交模式：同步提交模式相对于性能而言更强调高可用性，为此付出的代价是事务滞后时间增加。 在同步提交模式下，事务将一直等到辅助副本已将日志强制写入到磁盘中才会向客户端发送事务确认。 当在辅助数据库上开始数据同步时，辅助副本将开始应用来自相应的主数据库的传入日志记录。 一旦已经强制写入每个日志记录，辅助数据库就会进入 SYNCHRONIZED 状态。 此后，在日志记录写入本地日志文件之前，辅助副本会先将每个新事务强制写入。 在同步给定辅助副本的所有辅助数据库时，同步提交模式将支持手动故障转移和自动故障转移（可选）。

注：这里我们全部选择同步提交模式。

可读辅助副本：可读辅助副本允许对其所有辅助数据库的只读访问。 但是，可读辅助数据库并非设置为只读。 它们是动态的。 当对相应主数据库的更改应用到某一给定的辅助数据库时，该辅助数据库将更改。 对于典型的辅助副本，辅助数据库中的数据接近实时。 此外，全文索引与辅助数据库同步。 在许多情况下，主数据库和相应的辅助数据库之间的数据滞后时间只有几秒钟。

注：尽管您无法将数据写入辅助数据库，但可以在承载辅助副本的服务器实例上写入读写数据库，包括用户数据库和 tempdb 之类的系统数据库。


注：这里截图有错误，mssql002可用性模式应该也为“同步提交”。 
（7）：MSSQL001节点：端点配置
配置端点：在上一步添加完副本后，在这里会自动生成几台服务器的端点，这里需要注意，生成的端点RERL可能和每个实例真实端点URL不同，会在URL中添加DEAMON字符，需要删去相应的字段，保证端点URL的正确性。

（8）：MSSQL001节点：设置备份首选项
备份首选项：这里主要设置备份服务器实例的优先级，可以跳过，将来可以再设置。

（9）：MSSQL001节点：配置可用性组侦听器
侦听器：可用性组侦听器是客户端连接以获得数据库数据库访问权限的虚拟网络名称。侦听器相当于一个VIP，在我们后面使用Pacemaker做集群的时候，此IP地址为可用性组的VIP，当可用性组发生故障转移的时候，此IP地址会根据可用性组的主副本进行迁移。这里为配置侦听器的名称和端口和IP地址。
侦听器DNS名称：相当于侦听器的域名，其他服务器可以通过此名称解析来连接可用性组的主副本。
端口：应该和MSSQL Server实例端口保持一致。
网络模式：选择静态IP，通过添加静态IP地址为可用性组添加侦听器。

（10）：MSSQL001节点：配置只读路由（此步骤可选）
只读路由：只读路由 指的是 SQL Server 将符合条件的只读连接请求路由到可用的 AlwaysOn 可读次要副本 （即，配置为在辅助角色下运行时允许只读工作负荷的副本）的能力。 为支持只读路由，可用性组必须具备 可用性组侦听器。

使用前提：
可用性组必须拥有可用性组侦听器。
必须在辅助角色中将一个或多个可用性副本配置为接受只读（即配置为 可读次要副本）
您必须连接到承载当前主副本的服务器实例

为支持只读路由，您需要配置副本属性：
对于要支持只读路由的每个可读次要副本，你需要指定 只读路由 URL。 此 URL 仅在本地副本在辅助角色下运行时起作用。 必须根据需要在逐个副本的基础上指定只读路由 URL。 每个只读路由 URL 都用于将读意向请求路由到一个特定的可读辅助副本。 通常，向每个可读辅助副本分配一个只读路由 URL
对于要在其作为主要副本时支持只读路由的每个可用性副本，都需要指定一个 只读路由列表。 一个给定的只读路由列表仅在本地副本在主角色下运行时才起作用。 必须根据需要在逐个副本的基础上指定此列表。 通常，每个只读路由列表中将包含各只读路由 URL，并且在列表的末尾具有本地副本的 URL。
注意：读意向连接请求将被路由到当前主副本的只读路由列表上的第一个可用条目。 但支持在只读副本间的负载平衡。

总结：”只读路由URL”是指当前面服务器实例作为辅助副本时候，要将其作为只读副本，那么访问其实例的路由URL是什么。当服务器实例设置“只读路由URL“后，最下面“可用副本”就会出现相应的服务器实例，就可以通过下面配置只读路由列表。只读路由列表指当前面的服务器实例作为主副本时，有哪些服务器可以作为只读路由，可以设置多个，在正常情况下设置为除本服务器实例外其他实例。我下面的配置其实就是关闭了只读路由。因为只读路由对数据库请求有要求，不规范会报错。所以这一栏的设置不用设置不用管。

（11）：MSSQL001节点：选择数据库同步首选项
自动种子设定：创建具有自动种子设定的可用性组时，SQL Server 将自动为该组中每个数据库创建次要副本。 你不再需要手动备份和还原次要副本。
注：
在 SQL Server 2016 中，自动种子设定要求数据和日志文件路径在参与可用性组的每个 SQL Server 实例上均相同。 在 SQL Server 2017 中，你可以使用不同的路径，但是 Microsoft 建议，当所有副本都承载于相同的平台（例如 Windows 或 Linux）时，使用相同的路径。 跨平台可用性组针对副本具有不同的路径。
可用性组种子设定通过数据库镜像端点进行通信。 打开每台服务器上镜像终结点端口的入站防火墙规则。
可用性组中的数据库必须处于完整恢复模式。 数据库需要具有最新的完整备份和事务日志备份。 这些备份文件不用于自动种子设定，但需要这些文件才可将数据库包含到可用性组中。

（12）：MSSQL001节点：配置验证
验证：会检验目前所有设置是否正确，若不通过，则需要返回到之前步骤去进行检查。
摘要：此可用性组之前的所有设定的摘要。


（13）：MSSQL001节点：配置生效
结果：如果有问题需要回到之前的步骤进行处理，一般在验证步骤通过了，此处都不会有太大的问题。

（14）：MSSQL001节点：查看Test_AG可用性组状态
可用性组已经添加完成，目前可以在各个节点查看。

（15）：修改Hosts文件
将三个实例的主机名以及侦听器IP写入安装Microsoft SQL Server Management Studio 安装主机的hosts文件。

5：配置Pacemaker
（1）：安装Pacemaker软件包。（所有节点）
yum install -y pacemaker pcs fence-agents-all resource-agents

Pcs : 专用于pacemaker+corosync的设置工具,有CLI和web-based GUI界面
Pacemaker：Linux环境中使用最为广泛的开源集群资源管理器
resource-agents ：Pacemaker服务管理器的一组脚本，可与多个服务交互以在高可用性环境中运行
fence-agents-all：红帽fence-agents是所有受支持的fence-agents的集合。

（2）：配置Pacemaker的防火墙（所有节点）
firewall-cmd --add-service=high-availability --zone=public --permanent
firewall-cmd --zone=public --add-port=2224/tcp --permanent
firewall-cmd --zone=public --add-port=3121/tcp --permanent
firewall-cmd --zone=public --add-port=5405/udp --permanent
firewall-cmd --reload

（3）：启动pcs守护进程并设置开机自启（所有节点）
systemctl start pcsd
systemctl enable pcsd
systemctl status pcsd

（4）：设置pacemaker和corosync开机自启（所有节点）
systemctl enable pacemaker
systemctl start pacemaker

systemctl enable corosync
systemctl start corosync

5）：安装mssql-server-ha（所有节点）
yum install -y mssql-server-ha

（6）：修改hacluster用户密码（所有节点）
注：所有节点密码设置相同
Pacemaker安装时创建的hacluster用户
passwd hacluster

7）：主服务器：在Linux集群3个节点之间设置身份验证。
注：需要输入我们刚才设置的hacluster账户的密码
选择一台服务器作为pacemaker的主服务器，这里我们选择MSSQL001
pcs cluster auth mssql001 mssql002 mssql003 -u hacluster

（8）：主服务器：创建名为LinuxSQLServerHACluster的集群
pcs cluster setup --name LinuxSQLServerHACluster mssql001 mssql002 mssql003

注：主服务器为上一步操作选择的服务器
创建集群需要添加相应的三个节点进集群
（9）：主服务器：启动Linux集群服务。
pcs cluster start --all

（10）：资源隔离（所有节点）
注：资源隔离的目的是在配置资源的过程中引起服务中断时不会发生数据损坏。
pcs property set stonith-enabled=false

（11）：配置资源级策略（所有节点）
注：上面设置禁用。集群根据资源的当前故障计数和迁移阈值决定是否在粗尝试在同一节点上启动。
pcs property set start-failure-is-fatal=false

12）：Pacemaker创建MSSQL Server登录账号（所有节点）
注：创建账号后还需要授予之前创建的可用性组Test_AG权限。

USE master
GO
CREATE LOGIN pacemakerLogin WITH PASSWORD = ‘123456’;
GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::Test_AG TO pacemakerLogin
GRANT VIEW SERVER STATE TO pacemakerLogin

注：创建账号后还需要授予之前创建的可用性组Test_AG权限。

（13）：配置Pacemaker登录凭证。(所有节点)
echo “pacemakerLogin” >> /var/opt/mssql/secrets/passwd
echo “123456” >> /var/opt/mssql/secrets/passwd
chmod 400 /var/opt/mssql/secrets/passwd

（14）：主节点：查看集群状态
pcs status --full

注：如果不正常，用Pacemaker用户登录查看权限，如果查询结果为空，则授予全部权限再重启
（15）：Pacemaker创建Always On Availability组资源（任意节点）
pcs resource create MSSQLServer_Test_AG ocf:mssql:ag ag_name=Test_AG master notify=true

MSSQLServer_Test_AG：Pacemaker 集群资源的名称（可随意，生成环境应设置有意义的名称）
ocf:mssql:ag ：由mssql-server-ha提供的Open Cluster Framework（OCF）资源代理的名称
ag_name=Test_AG ：上一节配置的AlwaysOn AG 的可用性组的名称
master ：将资源定义为 master/slave 克隆资源

（16）:为AlwaysOn AG侦听器创建虚拟IP地址资源（任意节点）
pcs resource create AGListener_VIP ocf💓IPaddr2 ip=192.168.15.25 cidr_netmask=24

AGListener_VIP :虚拟IP地址资源名称
ocf💓IPaddr2：管理虚拟IPv4地址的Open Cluster Framework（OCF）资源代理的名称
ip=192.168.15.25 ：AlwaysOn AG 的可用性组的侦听器IP
cidr_netmask=24 ：AlwaysOn AG 的可用性组的侦听器子网掩码位数

（17）：将AlwaysOn AG资源配置在与虚拟IP地址资源相同的计算机上运行（任意节点）
pcs constraint colocation add AGListener_VIP MSSQLServer_Test_AG-master INFINITY with-rsc-role=Master

AGListener_VIP ：虚拟IP地址资源的名称
MSSQLServer_Test_AG-master：Pacemaker 集群AlwaysON AG资源的克隆别名
INFINITY ：分配给资源约束的分数
with-rsc-role=Master：约束的附加属性;这意味着此约束与主克隆相关联
注：由于Always On Availability Group侦听器名称只能将客户端应用程序重定向到主副本，因此可用性组和侦听器名称必须始终在同一群集节点中运行

（18）：配置集群资源顺序（任意节点）
pcs constraint order promote MSSQLServer_Test_AG-master then start AGListener_VIP

注：此步骤类似于windows集群中的投票机制，目的在于当集群拥有两个以上的节点，且发生故障转移时，应该选择哪一个节点作为新的主节点
（19）：查看AlwaysOn AG集群配置状态（任意节点）
pcs status

（20）：主节点：查看集群VIP
ip a

6：验证故障转移
（1）：主节点服务器重启
reboot

（2）：查看集群IP转移