— 步骤2：启用AlwaysOn功能
— 在两台服务器上启用AlwaysOn功能
— 重启SQLServer服务

— 步骤3：创建可用性组
— 在主站点上执行
CREATE AVAILABILITY GROUP [AG_fgedudb]
FOR DATABASE [fgedudb]
REPLICA ON
N’fgedu-prod-bj-01′ WITH (
ENDPOINT_URL = N’TCP://fgedu-prod-bj-01:5022′,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
),
N’fgedu-dr-sh-01′ WITH (
ENDPOINT_URL = N’TCP://fgedu-dr-sh-01:5022′,
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
);
GO

— 步骤4：验证可用性组状态
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;
GO

— 步骤5：测试数据复制
— 在主站点上插入测试数据
INSERT INTO fgedu.sales (
product_id,
customer_id,
sale_date,
amount,
status
) VALUES (
4,
1004,
GETDATE(),
4000.00,
‘COMPLETED’
);
GO

— 在异地站点上验证数据
SELECT TOP 10 * FROM fgedu.sales;
GO

— 步骤6：测试故障转移
— 手动故障转移到异地站点
ALTER AVAILABILITY GROUP [AG_fgedudb] FAILOVER;
GO

— 验证故障转移结果
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;
GO

— 步骤7：故障转移回主站点
ALTER AVAILABILITY GROUP [AG_fgedudb] FAILOVER;
GO

— 步骤8：配置监控
— 创建监控作业
EXEC dbo.sp_add_job @job_name = N’MonitorAGStatus’;
EXEC dbo.sp_add_jobstep
@job_name = N’MonitorAGStatus’,
@step_name = N’Check AG 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;’,
@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’MonitorAGStatus’,
@schedule_name = N’Every15Minutes’;
EXEC dbo.sp_add_jobserver
@job_name = N’MonitorAGStatus’;

执行结果：