SQL Server 2019 for Linux 安装与配置 Always On 高可用集群实战教程原创
# SQL Server 2019 for Linux 安装与配置主从（Always On Availability Group）操作文档
# 一、环境准备
# 1. 关闭防火墙
sudo iptables -F
sudo systemctl stop firewalld
sudo systemctl disable firewalld
1
2
3
2
3
# 2. 修改 /etc/hosts 文件
sudo bash -c 'cat <<EOF >> /etc/hosts
10.10.10.1 hello-dev-dbmaster01
10.10.10.2 hello-dev-dbslave01
EOF'
1
2
3
4
2
3
4
# 3. 修改主机名
主节点 (
hello-dev-dbmaster01):sudo hostnamectl set-hostname hello-dev-dbmaster011从节点 (
hello-dev-dbslave01):sudo hostnamectl set-hostname hello-dev-dbslave011
# 二、彻底卸载旧版软件
在两台服务器上执行以下命令，彻底卸载 SQL Server 及相关软件。
sudo yum remove -y mssql-server mssql-server-ha mssql-server-agent mssql-tools unixODBC unixODBC-devel
sudo rm -rf /var/opt/mssql /etc/systemd/system/mssql-server.service /data/mssql
1
2
2
# 三、安装 SQL Server 2019
# 1. 下载 Microsoft SQL Server 2019 存储库配置文件
sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo
1
# 2. 安装 SQL Server
sudo yum install -y mssql-server
1
# 3. 配置 SQL Server
sudo mkdir -p /data/mssql
sudo chown -R mssql:mssql /data/mssql
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /data/mssql/data
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /data/mssql/log
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /data/mssql/backup
sudo /opt/mssql/bin/mssql-conf set-sa-password
sudo systemctl restart mssql-server
1
2
3
4
5
6
7
2
3
4
5
6
7
# 4. 验证 SQL Server 是否运行
systemctl status mssql-server
1
# 四、安装 SQL Server 命令行工具
# 1. 下载 Microsoft Red Hat 存储库配置文件
sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
1
# 2. 安装 mssql-tools 和 unixODBC
sudo yum install -y mssql-tools unixODBC-devel
1
# 3. 配置 PATH 环境变量
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
1
2
3
2
3
# 五、配置主从（Always On Availability Group）
# 1. 启用 Always On 功能
sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server
1
2
2
# 2. 安装 SQL Server 高可用性组件
sudo yum install -y mssql-server-ha
1
# 3. 创建数据库镜像端点用户
sqlcmd -S localhost -U SA -P 'Your_SA_Password' -Q "CREATE LOGIN dbm_login WITH PASSWORD = 'Your_DBM_Password'; CREATE USER dbm_user FOR LOGIN dbm_login;"
1
# 4. 在主节点创建证书
sqlcmd -S localhost -U SA -P 'Your_SA_Password' -Q "
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Your_DBM_Password';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate TO FILE = '/data/mssql/dbm_certificate.cer'
WITH PRIVATE KEY (FILE = '/data/mssql/dbm_certificate.pvk', ENCRYPTION BY PASSWORD = 'Your_DBM_Password');"
1
2
3
4
5
2
3
4
5
# 5. 将证书从主节点复制到从节点
sudo rsync -avz /data/mssql/dbm_certificate.* hello-dev-dbslave01:/data/mssql/
1
# 6. 在从节点上创建证书
sqlcmd -S localhost -U SA -P 'Your_SA_Password' -Q "
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Your_DBM_Password';
CREATE CERTIFICATE dbm_certificate AUTHORIZATION dbm_user
FROM FILE = '/data/mssql/dbm_certificate.cer'
WITH PRIVATE KEY (FILE = '/data/mssql/dbm_certificate.pvk', DECRYPTION BY PASSWORD = 'Your_DBM_Password');"
1
2
3
4
5
2
3
4
5
# 7. 创建数据库镜像端点
sqlcmd -S localhost -U SA -P 'Your_SA_Password' -Q "
CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE dbm_certificate, ENCRYPTION = REQUIRED ALGORITHM AES);
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];"
1
2
3
4
5
2
3
4
5
# 8. 开放端口 5022
sudo firewall-cmd --zone=public --add-port=5022/tcp --permanent
sudo firewall-cmd --reload
1
2
2
# 9. 设置数据库为完整恢复模式
sqlcmd -S localhost -U SA -P 'Your_SA_Password' -Q "ALTER DATABASE [MyTestDB_3.0] SET RECOVERY FULL;"
1
# 10. 备份数据库并复制到从节点
sqlcmd -S localhost -U SA -P 'Your_SA_Password' -Q "BACKUP DATABASE [MyTestDB_3.0] TO DISK = '/data/mssql/backup/MyTestDB_3.0_full.bak';"
sqlcmd -S localhost -U SA -P 'Your_SA_Password' -Q "BACKUP LOG [MyTestDB_3.0] TO DISK = '/data/mssql/backup/MyTestDB_3.0_log.bak';"
sudo rsync -avz /data/mssql/backup/MyTestDB_3.0_full.bak hello-dev-dbslave01:/data/mssql/backup/
sudo rsync -avz /data/mssql/backup/MyTestDB_3.0_log.bak hello-dev-dbslave01:/data/mssql/backup/
1
2
3
4
2
3
4
# 11. 在从节点还原数据库
sqlcmd -S localhost -U SA -P 'Your_SA_Password' -Q "
RESTORE DATABASE [MyTestDB_3.0] FROM DISK = '/data/mssql/backup/MyTestDB_3.0_full.bak' WITH NORECOVERY;"
sqlcmd -S localhost -U SA -P 'Your_SA_Password' -Q "
RESTORE LOG [MyTestDB_3.0] FROM DISK = '/data/mssql/backup/MyTestDB_3.0_log.bak' WITH NORECOVERY;"
1
2
3
4
2
3
4
# 12. 创建 Always On 可用性组
sqlcmd -S localhost -U SA -P 'Your_SA_Password' -Q "
CREATE AVAILABILITY GROUP [AG1] WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL)
FOR REPLICA ON
N'hello-dev-dbmaster01' WITH (ENDPOINT_URL = N'tcp://hello-dev-dbmaster01:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC),
N'hello-dev-dbslave01' WITH (ENDPOINT_URL = N'tcp://hello-dev-dbslave01:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC);"
1
2
3
4
5
2
3
4
5
# 13. 从节点加入可用性组
sqlcmd -S localhost -U SA -P 'Your_SA_Password' -Q "ALTER AVAILABILITY GROUP [AG1] JOIN WITH (CLUSTER_TYPE = EXTERNAL);"
1
# 14. 创建数据库并添加到可用性组
sqlcmd -S localhost -U SA -P 'Your_SA_Password' -Q "CREATE DATABASE [db1]; ALTER AVAILABILITY GROUP [AG1] ADD DATABASE [db1
];"
1
2
3
2
3
上次更新: 10/2/2024