# 1. 服务器准备
# 配置主服务器
# 检查服务器状态
Get-Service | Where-Object { $_.Name -like ‘MSSQL*’ } | Select-Object Name, Status

Name Status
—- ——
MSSQLSERVER Running
SQLSERVERAGENT Running

# 配置备份服务器
# 检查服务器状态
Get-Service | Where-Object { $_.Name -like ‘MSSQL*’ } | Select-Object Name, Status

Name Status
—- ——
MSSQLSERVER Running
SQLSERVERAGENT Running

# 2. 网络配置
# 配置网络连接
# 检查网络状态
Test-Connection -ComputerName fgedu-prod -Count 4

Source Destination IPV4Address IPV6Address
—— ———– ———– ———–
fgedu-dr fgedu-prod 192.168.1.100 ::1
fgedu-dr fgedu-prod 192.168.1.100 ::1
fgedu-dr fgedu-prod 192.168.1.100 ::1
fgedu-dr fgedu-prod 192.168.1.100 ::1

# 3. 存储配置
# 配置存储系统
# 检查存储状态
Get-PhysicalDisk | Select-Object FriendlyName, MediaType, Size, OperationalStatus

FriendlyName MediaType Size OperationalStatus
———— ——— —- —————–
NVMe SSD 1 SSD 4294967296000 OK
NVMe SSD 2 SSD 4294967296000 OK
NVMe SSD 3 SSD 4294967296000 OK
NVMe SSD 4 SSD 4294967296000 OK

# 4. 高可用配置
# 启用Always On功能
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
EXEC sp_configure ‘hadr enabled’, 1;
RECONFIGURE;
GO

# 重启SQLServer服务
Restart-Service -Name “MSSQLSERVER”

# 5. 灾难恢复配置
# 配置日志传送
— 在主服务器上执行
EXEC master.dbo.sp_add_log_shipping_primary_database
@database = N’fgedudb’,
@backup_directory = N’D:\\SQLServer\\Backup’,
@backup_share = N’\\fgedu-prod\\Backup’,
@backup_job_name = N’LSBackup_fgedudb’,
@backup_retention_period = 4320,
@backup_compression = 1;
GO

— 在备份服务器上执行
EXEC master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = N’fgedu-prod’,
@primary_database = N’fgedudb’,
@backup_source_directory = N’\\fgedu-prod\\Backup’,
@backup_destination_directory = N’D:\\SQLServer\\Backup’,
@copy_job_name = N’LSCopy_fgedu-prod_fgedudb’,
@restore_job_name = N’LSRestore_fgedu-prod_fgedudb’,
@file_retention_period = 4320;
GO

# 1. 配置SQL Server Agent作业
USE [msdb]
GO

# 创建监控作业
EXEC dbo.sp_add_job
@job_name = N’Monitor Business Continuity’,
@enabled = 1,
@description = N’Monitor business continuity status’
GO

# 添加作业步骤
EXEC dbo.sp_add_jobstep
@job_name = N’Monitor Business Continuity’,
@step_name = N’Check Availability Groups’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.CheckAvailabilityGroups’,
@database_name = N’master’
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Monitor Business Continuity’,
@step_name = N’Check Backup Status’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.CheckBackupStatus’,
@database_name = N’master’
GO

# 添加作业调度
EXEC dbo.sp_add_jobschedule
@job_name = N’Monitor Business Continuity’,
@name = N’Every 15 Minutes’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 15
GO

# 2. 配置数据库邮件
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
EXEC sp_configure ‘Database Mail XPs’, 1;
RECONFIGURE;
GO

# 创建邮件配置文件
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘Business Continuity Alert Profile’,
@description = ‘Profile for business continuity alerts’;
GO

# 创建邮件账户
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = ‘Business Continuity Alert Account’,
@email_address = ‘bc-alerts@fgedu.net.cn’,
@mailserver_name = ‘smtp.fgedu.net.cn’;
GO

# 关联邮件账户和配置文件
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘Business Continuity Alert Profile’,
@account_name = ‘Business Continuity Alert Account’,
@sequence_number = 1;
GO

# 3. 配置告警
EXEC dbo.sp_add_alert
@name = N’Availability Group Failure’,
@message_id = 1480,
@severity = 0,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1;
GO

EXEC dbo.sp_add_notification
@alert_name = N’Availability Group Failure’,
@operator_name = N’DBA Team’,
@notification_method = 1;
GO

# 4. 配置性能监控
# 使用Performance Monitor配置性能计数器
# 监控CPU、内存、IO等指标

# 5. 配置集中监控
# 部署SQL Server Management Studio (SSMS)
# 配置中央管理服务器
# 实现集中监控

# 1. 测试准备
# 制定测试计划
# 准备测试环境
# 通知相关人员
# 备份生产数据

# 2. 测试执行
# 模拟服务器故障
# 测试故障转移
# 测试灾难恢复
# 验证服务可用性

# 3. 测试验证
# 验证数据库状态
# 验证数据完整性
# 验证应用功能
# 验证业务流程

# 4. 测试评估
# 评估测试结果
# 分析测试中发现的问题
# 提出改进建议
# 更新业务连续性计划

# 5. 环境恢复
# 恢复生产环境
# 清理测试环境
# 总结测试经验

# 测试类型

测试类型 测试频率 测试时长 测试目的
————————————————————
故障转移测试 每月 2-4小时 测试故障转移功能
备份恢复测试 每季度 4-8小时 测试备份恢复功能
灾难恢复测试 每半年 8-16小时 测试完整灾难恢复流程
业务连续性测试 每年 1-2天 测试完整业务连续性计划

# 测试场景：服务器故障

# 1. 测试准备
# 检查可用性组状态
SELECT
ag.name,
ar.replica_server_name,
ars.role_desc,
ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;
GO

# 2. 测试执行
# 模拟主服务器故障
Stop-Service -Name “MSSQLSERVER” -Force

# 监控故障转移过程
# 检查可用性组状态
SELECT
ag.name,
ar.replica_server_name,
ars.role_desc,
ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;
GO

# 3. 测试验证
# 检查新主服务器状态
SELECT
@@SERVERNAME AS server_name,
SERVERPROPERTY(‘ProductVersion’) AS version;
GO

# 检查数据库状态
SELECT
name,
state_desc,
recovery_model_desc
FROM sys.databases
WHERE name = ‘fgedudb’;
GO

# 验证数据完整性
DBCC CHECKDB(‘fgedudb’) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

# 测试应用连接
# 测试应用功能

# 4. 测试评估
# 记录故障转移时间
# 分析测试中发现的问题
# 提出改进建议

# 5. 环境恢复
# 启动原主服务器
Start-Service -Name “MSSQLSERVER”

# 执行回切操作
ALTER AVAILABILITY GROUP [fgedu-ag] FAILOVER;
GO

# 检查回切结果
SELECT
ag.name,
ar.replica_server_name,
ars.role_desc,
ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;
GO

# 1. 每日维护任务
# 检查高可用状态
SELECT
ag.name,
ar.replica_server_name,
ars.role_desc,
ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;
GO

# 检查备份状态
SELECT
database_name,
type,
backup_finish_date,
backup_size
FROM msdb.dbo.backupset
WHERE database_name = ‘fgedudb’
ORDER BY backup_finish_date DESC
TOP 10;
GO

# 检查监控告警
SELECT
alert_name,
message_id,
severity,
enabled
FROM msdb.dbo.sysalerts;
GO

# 2. 每周维护任务
# 测试故障转移
ALTER AVAILABILITY GROUP [fgedu-ag] FAILOVER;
GO

# 测试备份恢复
RESTORE DATABASE fgedudb FROM DISK = ‘D:\\SQLServer\\Backup\\fgedudb_full.bak’ WITH REPLACE,
MOVE ‘fgedudb’ TO ‘D:\\SQLServer\\Test\\fgedudb.mdf’,
MOVE ‘fgedudb_log’ TO ‘D:\\SQLServer\\Test\\fgedudb_log.ldf’;
GO

# 检查存储状态
Get-PhysicalDisk | Select-Object FriendlyName, MediaType, Size, OperationalStatus

# 3. 每月维护任务
# 检查业务连续性计划
# 更新业务连续性文档
# 培训团队成员
# 评估系统性能

# 4. 季度维护任务
# 全面业务连续性测试
# 评估和更新业务连续性策略
# 检查和更新监控配置
# 备份和清理监控数据

# 5. 年度维护任务
# 全面灾难恢复测试
# 评估和更新业务连续性计划
# 审计和合规检查
# 技术升级和优化

# 维护计划自动化

# 创建维护作业
USE [msdb]
GO

EXEC dbo.sp_add_job
@job_name = N’Daily Business Continuity Maintenance’,
@enabled = 1,
@description = N’Daily business continuity maintenance tasks’
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Daily Business Continuity Maintenance’,
@step_name = N’Check High Availability’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.CheckHighAvailability’,
@database_name = N’master’
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Daily Business Continuity Maintenance’,
@step_name = N’Check Backup Status’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.CheckBackupStatus’,
@database_name = N’master’
GO

EXEC dbo.sp_add_jobschedule
@job_name = N’Daily Business Continuity Maintenance’,
@name = N’Daily Schedule’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_start_time = 20000
GO