蒂法2,姜维传下载,克拉玛依二手房
# 中国东部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
第一次创建vm时,会生成template,然后下载保存下,修改其中的参数值后,就可以方便地创建配置类似的vm。vm的配置主要有:
模板和参数文件太长,就不展示了。可以在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都需要修改配置,如下只是一台的配置示例
# 找出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相关软件。
yum update -y yum install -y pacemaker pcs fence-agents-all resource-agents fence-agents-azure-arm reboot
# 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
vi /etc/hosts 172.17.2.8 sql19n1 172.17.2.9 sql19n2 172.17.2.10 sql19n3
# 设置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
# 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 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和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
# 打开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} }
sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1 sudo systemctl restart mssql-server
# t-sql,每个实例 alter event session alwayson_health on server with (startup_state=on); go
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.*
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' );
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;
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
use [master] go create login [pacemakerlogin] with password= n'1qaz@wsx' go alter server role [sysadmin] add member [pacemakerlogin]; go
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
alter availability group [ag1] join with (cluster_type = external); go # auto_seeding功能需要的权限 alter availability group [ag1] grant create any database; go
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
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;
pcs resource create agcluster ocf:mssql:ag ag_name=ag1 meta failure-timeout=30s master notify=true
# 禁用fencing pcs property set stonith-enabled=false # 创建vip pcs resource create virtualip ocf:heartbeat:ipaddr2 ip=172.17.2.7
pcs constraint colocation add virtualip agcluster-master infinity with-rsc-role=master
pcs constraint order promote agcluster-master then start virtualip # 查看当前的约束 pcs constraint show --full
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 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
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论