CentOS环境下SQL Server自动化运维实践
在CentOS环境中，SQL Server的自动化运维可通过自动化部署、自动备份、性能监控、日常维护自动化四大核心环节实现，结合Ansible、Shell脚本、Prometheus+Grafana等工具，提升运维效率并降低人为风险。
使用红帽官方提供的microsoft.sql.server角色实现SQL Server的单实例或集群部署，自动完成系统配置、依赖安装、软件部署及服务启停。
ansible-collection-microsoft-sql、rhel-system-roles集合（yum install -y ansible-collection-microsoft-sql rhel-system-roles）。inventory文件内容为：[sqlgroup]
localhost ansible_connection=local
- hosts: localhost
vars:
mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula: true
mssql_accept_microsoft_cli_utilities_for_sql_server_eula: true
mssql_accept_microsoft_sql_server_standard_eula: true
mssql_password: 'YourSecurePassword@123'
mssql_edition: 'Developer' # 可选Enterprise/Standard等
mssql_datadir: "/data/mssql/1433/database"
mssql_logdir: "/data/mssql/1433/log"
roles:
- microsoft.sql.server
ansible-playbook -i inventory mssql.yml，自动完成SQL Server部署（约30秒完成单实例安装）。通过Shell脚本调用sqlcmd工具执行备份命令，结合Cron定时任务实现定期自动备份，确保数据安全。
sudo yum install -y mssql-tools unixODBC-devel。#!/bin/bash
BACKUP_DIR="/var/opt/mssql/backup"
DATE=$(date +%Y%m%d)
DB_NAME="YourDatabaseName"
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.bak"
LOG_FILE="$BACKUP_DIR/backup_${DATE}.log"
# 执行备份命令
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'YourStrongPassword' -Q "BACKUP DATABASE [$DB_NAME] TO DISK = '$BACKUP_FILE' WITH FORMAT, INIT, STATS = 10" -b -o "$LOG_FILE"
# 清理7天前的备份
find "$BACKUP_DIR" -name "${DB_NAME}_*.bak" -type f -mtime +7 -exec rm -f {} \;
chmod +x /opt/sql_backup.sh。crontab -e，添加每日凌晨2点执行的定时任务：0 2 * * * /opt/sql_backup.sh
通过Prometheus采集SQL Server性能指标，Grafana展示可视化仪表盘，实现实时监控与告警。
docker run -d --name prometheus -p 9090:9090 -v /opt/prometheus.yml:/etc/prometheus/prometheus.yml prom/prometheus
docker run -d --name grafana -p 3000:3000 grafana/grafana
/opt/prometheus.yml，添加mssql-exporter任务（需提前部署awaragi/prometheus-mssql-exporter镜像）：scrape_configs:
- job_name: 'sqlserver'
static_configs:
- targets: ['mssql-exporter:9300'] # mssql-exporter容器地址
通过Shell脚本封装常用维护命令，实现数据库一致性检查、索引优化、日志清理等操作的自动化。
sqlcmd执行DBCC CHECKDB命令，示例如下：#!/bin/bash
DB_NAME="YourDatabaseName"
LOG_FILE="/var/opt/mssql/maintenance/checkdb_${DB_NAME}.log"
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'YourStrongPassword' -Q "DBCC CHECKDB([$DB_NAME]) WITH NO_INFOMSGS, ALL_ERRORMSGS;" -b -o "$LOG_FILE"
#!/bin/bash
DB_NAME="YourDatabaseName"
LOG_FILE="/var/opt/mssql/maintenance/rebuild_index_${DB_NAME}.log"
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'YourStrongPassword' -Q "
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = STRING_AGG('ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' REBUILD WITH (ONLINE = ON);', CHAR(13))
FROM sys.dm_db_index_physical_stats(DB_ID('$DB_NAME'), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
JOIN sys.tables t ON i.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE ps.avg_fragmentation_in_percent > 30;
EXEC sp_executesql @sql;" -b -o "$LOG_FILE"
#!/bin/bash
LOG_DIR="/var/opt/mssql/log"
LOG_FILES=$(ls -t "$LOG_DIR"/*.log | tail -n +11)
for file in $LOG_FILES; do
rm -f "$file"
done
通过以上方案，CentOS环境下的SQL Server可实现部署标准化、备份定时化、监控可视化、维护自动化，显著提升运维效率，降低数据库故障风险。
