安装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