# 检查服务器硬件配置
SELECT
physical_memory_kb / 1024 AS physical_memory_mb,
cpu_count,
hyperthread_ratio,
socket_count
FROM sys.dm_os_sys_info;
GO

physical_memory_kb cpu_count hyperthread_ratio socket_count
—————— ——— —————– ————
65536 16 2 2

对于容灾环境，建议配置至少64GB内存，16核CPU，以满足主备数据库的运行需求。from SQLServer视频:www.itpux.com。

2.2 SQLServer容灾自动化工具选择

常用的SQLServer容灾自动化工具包括：

1. SQL Server Management Studio (SSMS)：内置的容灾管理功能

2. PowerShell：用于编写自动化脚本

3. SQL Server Agent：用于调度自动化任务

4. 第三方监控工具：如SolarWinds、PRTG等

5. 自定义自动化脚本：根据企业需求编写的自动化脚本

更多视频教程www.fgedu.net.cn。

Part03-生产环境项目实施方案

3.1 SQLServer容灾自动化部署实施

# 创建Always On可用性组
CREATE AVAILABILITY GROUP [fgedu_dr_ag]
WITH (DB_FAILOVER = ON, AUTOMATED_BACKUP_PREFERENCE = PRIMARY)
FOR DATABASE [fgedudb]
REPLICA ON
N’FGEDU1′ WITH (
ENDPOINT_URL = N’TCP://fgedu1.fgedu.net.cn:5022′,
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
),
N’FGEDU2′ WITH (
ENDPOINT_URL = N’TCP://fgedu2.fgedu.net.cn:5022′,
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
);
GO

# 配置可用性组监听器
ALTER AVAILABILITY GROUP [fgedu_dr_ag]
ADD LISTENER N’fgedu_dr_listener’ (
WITH IP ((N’192.168.1.100′, N’255.255.255.0′)),
PORT = 1433
);
GO

# 启用SQL Server Agent
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
GO

EXEC sp_configure ‘Agent XPs’, 1;
RECONFIGURE;
GO

# 创建自动备份作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’Backup fgedudb Transaction Log’,
@enabled = 1,
@description = N’Automatically back up transaction log for fgedudb’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Backup fgedudb Transaction Log’,
@step_name = N’Back up transaction log’,
@subsystem = N’TSQL’,
@command = N’BACKUP LOG fgedudb TO DISK = ”/sqlserver/backup/fgedudb_log.bak” WITH COMPRESSION;’,
@retry_attempts = 3,
@retry_interval = 5;
GO

EXEC dbo.sp_add_schedule
@schedule_name = N’Log Backup Schedule’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 15;
GO

EXEC dbo.sp_attach_schedule
@job_name = N’Backup fgedudb Transaction Log’,
@schedule_name = N’Log Backup Schedule’;
GO

EXEC dbo.sp_add_jobserver
@job_name = N’Backup fgedudb Transaction Log’,
@server_name = N'(local)’;
GO

学习交流加群风哥QQ113257174。

Part04-生产案例与实战讲解

4.1 SQLServer容灾自动化实战案例

# 案例1：Always On自动故障转移

# 检查可用性组状态
SELECT
ag.name AS availability_group,
ar.replica_server_name AS replica,
ar.availability_mode_desc AS availability_mode,
ar.failover_mode_desc AS failover_mode,
rs.role_desc AS role,
rs.operational_state_desc AS operational_state
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states rs ON ar.replica_id = rs.replica_id;
GO

# 模拟主节点故障
# 在主节点上执行
SHUTDOWN WITH NOWAIT;
GO

# 检查故障转移后的状态
SELECT
ag.name AS availability_group,
ar.replica_server_name AS replica,
rs.role_desc AS role,
rs.operational_state_desc AS operational_state
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states rs ON ar.replica_id = rs.replica_id;
GO

availability_group replica role operational_state
—————– ———– ——– —————–
fgedu_dr_ag FGEDU1 SECONDARY ONLINE
fgedu_dr_ag FGEDU2 PRIMARY ONLINE

# 案例2：日志传送自动化

# 创建日志传送配置脚本
CREATE PROCEDURE dbo.usp_log_shipping_configure
@primary_db NVARCHAR(50),
@secondary_db NVARCHAR(50),
@backup_folder NVARCHAR(256),
@copy_folder NVARCHAR(256),
@restore_folder NVARCHAR(256)
AS
BEGIN
— 配置主数据库
EXEC master.dbo.sp_add_log_shipping_primary_database
@database = @primary_db,
@backup_directory = @backup_folder,
@backup_share = @backup_folder,
@backup_job_name = N’LSBackup_’ + @primary_db,
@backup_retention_period = 4320,
@backup_compression = 1,
@backup_threshold = 60,
@threshold_alert_enabled = 1;

— 获取主数据库ID
DECLARE @primary_id UNIQUEIDENTIFIER;
SELECT @primary_id = primary_id FROM msdb.dbo.log_shipping_primary_databases WHERE primary_database = @primary_db;

— 配置辅助数据库
EXEC master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = @@SERVERNAME,
@primary_database = @primary_db,
@backup_source_directory = @copy_folder,
@backup_destination_directory = @restore_folder,
@secondary_server = @@SERVERNAME,
@secondary_database = @secondary_db,
@copy_job_name = N’LSCopy_’ + @primary_db,
@restore_job_name = N’LSRestore_’ + @secondary_db,
@file_retention_period = 4320;

— 启用作业
EXEC msdb.dbo.sp_update_job
@job_name = N’LSBackup_’ + @primary_db,
@enabled = 1;

EXEC msdb.dbo.sp_update_job
@job_name = N’LSCopy_’ + @primary_db,
@enabled = 1;

EXEC msdb.dbo.sp_update_job
@job_name = N’LSRestore_’ + @secondary_db,
@enabled = 1;
END;
GO

# 执行日志传送配置
EXEC dbo.usp_log_shipping_configure
@primary_db = ‘fgedudb’,
@secondary_db = ‘fgedudb_dr’,
@backup_folder = ‘/sqlserver/backup’,
@copy_folder = ‘/sqlserver/backup’,
@restore_folder = ‘/sqlserver/backup’;
GO

# 创建容灾状态监控脚本
CREATE PROCEDURE dbo.usp_monitor_dr_status
AS
BEGIN
— 检查Always On可用性组状态
SELECT
ag.name AS availability_group,
ar.replica_server_name AS replica,
rs.role_desc AS role,
rs.operational_state_desc AS operational_state,
rs.synchronization_health_desc AS synchronization_health
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states rs ON ar.replica_id = rs.replica_id;

— 检查日志传送状态
SELECT
primary_database,
secondary_server,
secondary_database,
last_backup_date,
last_copy_date,
last_restore_date,
backup_latency,
copy_latency,
restore_latency
FROM msdb.dbo.log_shipping_monitor_secondary;
END;
GO

# 创建告警作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’DR Status Alert’,
@enabled = 1,
@description = N’Monitor DR status and alert if issues found’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’DR Status Alert’,
@step_name = N’Check DR status’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.usp_monitor_dr_status;’,
@retry_attempts = 3,
@retry_interval = 5;
GO

EXEC dbo.sp_add_schedule
@schedule_name = N’DR Status Check Schedule’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 5;
GO

EXEC dbo.sp_attach_schedule
@job_name = N’DR Status Alert’,
@schedule_name = N’DR Status Check Schedule’;
GO

EXEC dbo.sp_add_jobserver
@job_name = N’DR Status Alert’,
@server_name = N'(local)’;
GO

风哥提示：定期监控容灾状态，及时发现并解决问题。from SQLServer视频:www.itpux.com。

Part05-风哥经验总结与分享

5.1 SQLServer容灾自动化最佳实践

1. 制定容灾自动化策略，明确自动化范围和目标

2. 选择合适的容灾技术方案，根据业务需求进行配置

3. 编写自动化脚本，实现容灾操作的自动化

4. 配置监控告警机制，及时发现容灾异常

5. 定期进行容灾演练，验证容灾自动化的有效性

6. 建立容灾自动化文档，记录容灾配置和操作流程

7. 对运维人员进行培训，提高容灾自动化操作技能

5.2 SQLServer容灾自动化常见问题与解决方案

# 问题1：容灾切换失败
# 解决方案：检查网络连接、权限配置、数据库状态等

# 问题2：容灾延迟过大
# 解决方案：优化网络带宽、调整备份策略、检查硬件性能

# 问题3：监控告警失效
# 解决方案：检查监控配置、测试告警机制、确保通知渠道畅通

# 问题4：自动化脚本执行失败
# 解决方案：检查脚本权限、语法错误、依赖服务状态

# 问题5：容灾演练不成功
# 解决方案：分析演练失败原因、调整容灾配置、重新演练

# 查看容灾状态
EXEC dbo.usp_monitor_dr_status;
GO

更多学习教程公众号风哥教程itpux_com。