— 步骤2：配置本地灾备（AlwaysOn可用性组）
— 在主站点和本地灾备站点之间配置AlwaysOn可用性组
CREATE AVAILABILITY GROUP [AG_fgedudb]
FOR DATABASE [fgedudb]
REPLICA ON
N’fgedu-prod-01′ WITH (
ENDPOINT_URL = N’TCP://fgedu-prod-01:5022′,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
),
N’fgedu-dr-01′ WITH (
ENDPOINT_URL = N’TCP://fgedu-dr-01:5022′,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
);
GO

— 步骤3：配置异地灾备（日志传送）
— 从本地灾备站点到异地灾备站点配置日志传送
— 在本地灾备站点上执行
EXEC msdb.dbo.sp_add_log_shipping_primary_database
@database = N’fgedudb’,
@backup_directory = N’\\fgedu-dr-01\Backup\’,
@backup_share = N’\\fgedu-dr-01\Backup\’,
@backup_job_name = N’LSBackup_fgedudb’,
@backup_retention_period = 4320,
@backup_compression = 1,
@backup_threshold = 60,
@threshold_alert_enabled = 1,
@history_retention_period = 5760;
GO

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

— 添加辅助数据库
EXEC msdb.dbo.sp_add_log_shipping_secondary_primary
@primary_server = N’fgedu-dr-01′,
@primary_database = N’fgedudb’,
@backup_source_directory = N’\\fgedu-dr-01\Backup\’,
@backup_destination_directory = N’D:\SQLServer\Backup\’,
@copy_job_name = N’LSCopy_fgedu-dr-01_fgedudb’,
@restore_job_name = N’LSRestore_fgedu-dr-01_fgedudb’,
@file_retention_period = 4320,
@secondary_database = N’fgedudb_DR’,
@overwrite = 1,
@copy_threshold = 60,
@restore_threshold = 60,
@threshold_alert_enabled = 1,
@history_retention_period = 5760,
@monitor_server = N’fgedu-prod-01′,
@monitor_server_security_mode = 1;
GO

— 步骤4：启动日志传送作业
— 启动备份作业
EXEC msdb.dbo.sp_start_job N’LSBackup_fgedudb’;

— 启动复制作业
EXEC msdb.dbo.sp_start_job N’LSCopy_fgedu-dr-01_fgedudb’;

— 启动还原作业
EXEC msdb.dbo.sp_start_job N’LSRestore_fgedu-dr-01_fgedudb’;

— 步骤5：配置监控
— 创建监控作业
EXEC dbo.sp_add_job @job_name = N’MonitorMultiSiteDR’;
EXEC dbo.sp_add_jobstep
@job_name = N’MonitorMultiSiteDR’,
@step_name = N’Check DR Status’,
@subsystem = N’TSQL’,
@command = N’– 检查可用性组状态
SELECT
ag.name AS availability_group,
ar.replica_server_name,
ar.operational_state_desc,
ar.availability_mode_desc,
ar.failover_mode_desc,
ar.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id;

— 检查日志传送状态
EXEC msdb.dbo.sp_help_log_shipping_monitor;’,
@database_name = N’master’;
EXEC dbo.sp_add_schedule
@schedule_name = N’Every15Minutes’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 15,
@active_start_time = 000000;
EXEC dbo.sp_attach_schedule
@job_name = N’MonitorMultiSiteDR’,
@schedule_name = N’Every15Minutes’;
EXEC dbo.sp_add_jobserver
@job_name = N’MonitorMultiSiteDR’;

— 步骤6：测试多站点灾备
— 测试本地故障转移
ALTER AVAILABILITY GROUP [AG_fgedudb] FAILOVER;
GO

— 测试异地灾备
— 在异地服务器上执行
RESTORE DATABASE fgedudb_DR WITH RECOVERY;
GO

— 验证数据完整性
SELECT TOP 10 * FROM fgedu.sales;
GO

执行结果：