在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)

前文

• 假定您对azure和sql server ha具有基础知识
• 假定您对azure cli具有基础知识
• 目标是在azure linux vm上创建一个具有三个副本的可用性组，并实现侦听器和fencing配置

环境

• sql server 2019 developer on linux
• azure vm fencing agent
• azure cli实现部分配置
• centos 7.7 azure vm，分别sql19n1,sql19n2,sql19n3，位于同一vnet

步骤

• 为vm创建资源组和可用性集

# 中国东部2创建资源组
az group create --name sql-demo-rg --location chinaeast2

# 创建用于vm人availability set，配置2个容错域，2个更新域
az vm availability-set create \
    --resource-group sql-demo-rg \
    --name aglinux-availabilityset \
    --platform-fault-domain-count 2 \
    --platform-update-domain-count 2

• 使用template部署3台vm

第一次创建vm时，会生成template，然后下载保存下，修改其中的参数值后，就可以方便地创建配置类似的vm。vm的配置主要有：

• 使用前面的可用性集
• 使用同一个子网
• ip使用standard
• ssh public key配置

模板和参数文件太长，就不展示了。可以在azure portal上自行获取。

# 如下是sql19n2的配置，修改参数文件后，直接可以用于创建sql19n3
templatefile="./templatefile"
paramfile="./vmparams-sql19n2.json"
az deployment group validate --name sql19n2vm \
     -g sql-demo-rg --template-file $templatefile --parameters $paramfile

• 配置vm使用固定内网ip和公网dns label

三台vm都需要修改配置，如下只是一台的配置示例

# 找出nic和ip的信息
az network nic list -g sql-demo-rg --query "[].{nicname:name,configuration:ipconfigurations[].{ipname:name,ip:privateipaddress,method:privateipallocationmethod}}" -o yaml

# 修改privateipallocationmethod为static
az network nic ip-config update -g sql-demo-rg --nic-name sql19n1152 --name ipconfig1 --set privateipallocationmethod=static

# 找出pbulic ip名称
az network public-ip list -g sql-demo-rg --query "[].name" -o tsv

# 配置public ip的dns name,只能使用数据和小字字母
az network public-ip update -g sql-demo-rg -n sql19n1ip851 --dns-name sql19n1

• 安装ha相关软件包

最好先更新一下系统的软件包，再安装ha相关软件。

yum update -y
yum install -y pacemaker pcs fence-agents-all resource-agents fence-agents-azure-arm
reboot

• 为群集和sql server开放防火墙端口

# pacemaker和corosync的端口
# tcp: ports 2224,3121,21064,5405
# udp: port 5405
firewall-cmd --add-port=2224/tcp --permanent
firewall-cmd --add-port=2224/tcp --permanent
firewall-cmd --add-port=21064/tcp --permanent
firewall-cmd --add-port=5405/tcp --permanent
firewall-cmd --add-port=5405/udp --permanent

# sql server端口和ag镜像端口
# tcp: 1433,5022
firewall-cmd --add-port=1433/tcp --permanent
firewall-cmd --add-port=5022/tcp --permanent
firewall-cmd --reload

• 添加hosts记录

vi /etc/hosts
172.17.2.8      sql19n1
172.17.2.9      sql19n2
172.17.2.10     sql19n3

• 创建pacemaker群集

# 设置pacemaker的默认用户密码，三台vm上
passwd hacluster

# 设置pacemaker和pcsd自启动在三台vm上
systemctl enable pcsd
systemctl start pcsd
systemctl enable pacemaker

# 创建群集,在master节点
sudo pcs cluster auth sql19n1 sql19n2 sql19n3 -u hacluster 
sudo pcs cluster setup --name agcluster sql19n1 sql19n2 sql19n3 --token 30000 --force
sudo pcs cluster start --all
sudo pcs cluster enable --all
# 查看群集状态
pcs status
# 在三个节点上修改quorum的expected-votes为3，其实三节点群集默认为3
# 设置表示，群集存活需要3票，这个修改只影响当前running群集，不会变成群集的永久性配置保存下来
pcs quorum expected-votes 3

• 在azure上为fencing agent配置servic princinpal

# 1. 创建 aad app,成功后记录下相应的appid
 az ad app create --display-name sqldemorg-app --identifier-uris http://localhost \ 
 --password "1qaz@wsx3edc" --end-date '2030-04-27' --credential-description "sql19 ag secret"
 
# 2. 创建aad app的service principal
az ad sp create --id <appid>

# 3. 将service principal分配到vm对应的管理role,对每个vm都要执行
# 我这里分配的是owner role,这不是安全的做法。应该使用自定义一个role，只给最小权限
# 自定义role需要azure订阅是pp1或者pp2级别

az role assignment create --assignee <appid> --role owner \
--scope /subscriptions/<subscription-id>/resourcegroups/<resourcegroup-name>/providers/microsoft.compute/virtualmachines/sql19n1

• 创建azure的stonith 设备

我使用的是azure china，所以需要指定cloud=china，如果使用global azure不需要指定此参数。
执行 fence_azure_arm -h，查看此资源代理的更多帮助信息

pcs property set stonith-timeout=900
pcs stonith create rsc_st_azure fence_azure_arm login="<applicationid>" passwd="<serviceprincipalpassword>" resourcegroup="<resourcegroupname>" tenantid="<tenantid>" subscriptionid="<subscriptionid>" power_timeout=240 pcmk_reboot_timeout=900 cloud=china

• 安装sql 2019及工具

# 安装 sql 2019和ha 资源代理
sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo
sudo yum install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup
sudo yum install mssql-server-ha

# 安装 mssql-tools
sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
sudo yum install -y mssql-tools unixodbc-devel
# 将mssql-tools目录加入到apath,方便使用
echo 'export path="$path:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export path="$path:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

# 安装 mssql-cli
sudo rpm --import https://packages.microsoft.com/keys/microsoft.asc
sudo curl -o /etc/yum.repos.d/mssql-cli.repo https://packages.microsoft.com/config/rhel/7/prod.repo
sudo yum install mssql-cli

# 查看sql 状态
systemctl status mssql-server

如果您熟悉 sql server相关的powershell，建议将powershell也安装上，并安装sqlserver module。对sql server的配置，使用powershell会方便很多

yum install powershell -y
pwsh
install-module sqlserver
# 查看sql相关的命令
get-command -module sqlserver

• 配置ag

• 创建powershell 函数方便后续执行t-sql

# 打开powershell的 profile文件，如果不存在需要则需要创建
vi /root/.config/powershell/microsoft.powershell_profile.ps1

# 将如下函数加入 到 profile文件中，每次打开pwsh时就可以直接调用
# 函数有两个参数，$sql表示需要执行的t-sql，最好使用here-string以避免字符转义问题
# $servers表示目标实例，数组类型。默认值为当前环境中的三个实例
function run-sql ($sql,$servers=("sql19n1","sql19n2","sql19n3"))
{
        $secpasswd = "1qaz@wsx"|convertto-securestring -asplaintext -force
        $cred=new-object system.management.automation.pscredential -argumentlist 'sa', $secpasswd
        $sql
        "---------"
        foreach($svr in $servers) {"running t-sql on $svr..."; invoke-sqlcmd -serverinstance $svr -credential $cred -query $sql}
}

---

• 启用 hadr功能，每个实例

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

• 启动ag extened event session

# t-sql,每个实例
alter event session  alwayson_health on server with (startup_state=on);
go

• 在主副本实例上创建证书，这个证书用于验证mirroring endpoint通信。将证书和私钥复制到其它节点上的相同的目录位置。授予mssql用户访问权限

create master key encryption by password = '1qaz@wsx';
go
create certificate dbm_certificate with subject = 'dbm';
go
backup certificate dbm_certificate
   to file = '/var/opt/mssql/data/dbm_certificate.cer'
   with private key (
           file = '/var/opt/mssql/data/dbm_certificate.pvk',
           encryption by password = '1qaz@wsx'
       );

# 复制证书和私钥到辅助副本主机sql19n2和sql19n3
cd /var/opt/mssql/data
scp dbm_certificate.* root@sql19n2:/var/opt/mssql/data/
scp dbm_certificate.* root@sql19n3:/var/opt/mssql/data/

# 辅助副本节点上修改权限
cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*

• 在辅助副本实例中创建master key并导入证书

create master key encryption by password = '1qaz@wsx';
go
create certificate dbm_certificate
    from file = '/var/opt/mssql/data/dbm_certificate.cer'
    with private key (
    file = '/var/opt/mssql/data/dbm_certificate.pvk',
    decryption by password = '1qaz@wsx'
            );

• 创建ag的镜像端口，注意防火墙和nsg配置端口例外

create endpoint [hadr_endpoint]
    as tcp (listener_port = 5022)
    for database_mirroring (
	    role = all,
	    authentication = certificate dbm_certificate,
		encryption = required algorithm aes
		);
go
alter endpoint [hadr_endpoint] state = started;

• 创建三个副本，同步模式的ag，主副本实例上执行

create availability group [ag1]
     with (db_failover = on, cluster_type = external)
     for replica on
         n'sql19n1' 
 	      	with (
  	       endpoint_url = n'tcp://sql19n1:5022',
  	       availability_mode = synchronous_commit,
  	       failover_mode = external,
  	       seeding_mode = automatic,
  	       secondary_role(allow_connections = all)
  	       ),
         n'sql19n2' 
  	    with ( 
  	       endpoint_url = n'tcp://sql19n2:5022', 
  	       availability_mode = synchronous_commit,
  	       failover_mode = external,
  	       seeding_mode = automatic,
  	       secondary_role(allow_connections = all)
  	       ),
  	   n'sql19n3'
         with( 
  	      endpoint_url = n'tcp://sql19n3:5022', 
  	      availability_mode = synchronous_commit,
  	      failover_mode = external,
  	      seeding_mode = automatic,
  	      secondary_role(allow_connections = all)
  	      );
go
alter availability group [ag1] grant create any database;
go

• 为pacemaker创建sql登录并授权,每个实例

use [master]
go
create login [pacemakerlogin] with password= n'1qaz@wsx'
go
alter server role [sysadmin] add member [pacemakerlogin];
go

• 将pacemaker的login信息保存到本地文件

echo "pacemakerlogin" >> /var/opt/mssql/secrets/passwd
echo "1qaz@wsx" >> /var/opt/mssql/secrets/passwd

# 只允许root读取
chown root:root /var/opt/mssql/secrets/passwd
chmod 400 /var/opt/mssql/secrets/passwd

• 将辅助副本加入到ag, 辅助副本执行

alter availability group [ag1] join with (cluster_type = external);
go
# auto_seeding功能需要的权限
alter availability group [ag1] grant create any database;
go

• 如果您不希望pacemakerlogin具有sysadmin的权限，可以将之从sysadmin中移除，并授予如下权限。每个实例

alter server role [sysadmin] drop member [pacemakerlogin]
go
grant alter, control, view definition on availability group::ag1 to pacemakerlogin;
go
grant view server state to pacemakerlogin;
go

• 添加数据库到ag,主副本执行

create database [db1];
go
alter database [db1] set recovery full;
go
backup database [db1]
   to disk = n'nul';
go
alter availability group [ag1] add database [db1];
go

• 可用性数据库状态

select * from sys.databases where name = 'db1';
go
select db_name(database_id) as 'database', synchronization_state_desc from sys.dm_hadr_database_replica_states;

• 在pacemaker群集中配置ag

---

• 创建ag资源，ag_name要指定为之前创建ag名称

pcs resource create agcluster ocf:mssql:ag ag_name=ag1 meta failure-timeout=30s master notify=true

• 创建虚拟ip资源

# 禁用fencing
pcs property set stonith-enabled=false

# 创建vip
pcs resource create virtualip ocf:heartbeat:ipaddr2 ip=172.17.2.7

• 创建 colacation constraint，vip和master必需在同一个节点上启动

pcs constraint colocation add virtualip agcluster-master infinity with-rsc-role=master

• 创建 ordering constraint，vip要先于master副本资源启动

pcs constraint order promote agcluster-master then start virtualip

# 查看当前的约束
pcs constraint show --full

• 重新启用stonith并查看群集状态

pcs property set stonith-enabled=true
pcs status
# 我的环境中的状态信息
---------------------------------------
cluster name: agcluster
stack: corosync
current dc: sql19n3 (version 1.1.20-5.el7_7.2-3c4c782f70) - partition with quorum
last updated: wed apr 29 04:24:50 2020
last change: wed apr 29 04:24:45 2020 by root via cibadmin on sql19n1

3 nodes configured
5 resources configured

online: [ sql19n1 sql19n2 sql19n3 ]

full list of resources:

 rsc_st_azure   (stonith:fence_azure_arm):      started sql19n1
 master/slave set: agcluster-master [agcluster]
     masters: [ sql19n1 ]
     slaves: [ sql19n2 sql19n3 ]
 virtualip      (ocf::heartbeat:ipaddr2):       started sql19n1

daemon status:
  corosync: active/enabled
  pacemaker: active/enabled
  pcsd: active/enabled

• 测试failover和fencing

# 手动failover
pcs resource move agcluster-master sql19n2 --master
pcs status

# 手动 failover会生成一个constraint，避免ag资源再回到原来的节点
# 如果希望ag后续还能 failover回来，需要手动删除之
pcs constraint show --full
pcs constraint remove cli-prefer-agcluster-master

# 尝试fencing群集节点，每个节点都试一下
# 如下命令的fencing只是重启node,如果要安全关闭node，使用--off参数
pcs stonith fence sql19n3 --debug