# 1. 启用Always On可用性组功能
ALTER SERVER CONFIGURATION SET HADR SERVICE ACCOUNT = ‘fgedu\sqlservice’;
GO

# 2. 创建数据库镜像端点
CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT=5022, LISTENER_IP=ALL)
FOR DATA_MIRRORING (
ROLE=ALL,
AUTHENTICATION=WINDOWS NEGOTIATE,
ENCRYPTION=REQUIRED ALGORITHM AES
);
GO

# 3. 创建可用性组
CREATE AVAILABILITY GROUP [fgedu_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)
),
N’FGEDU3′ WITH (
ENDPOINT_URL = N’TCP://fgedu3.fgedu.net.cn:5022′,
FAILOVER_MODE = MANUAL,
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 10,
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
);
GO

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

# 5. 加入数据库到可用性组
— 在辅助节点上执行
ALTER DATABASE [fgedudb] SET HADR AVAILABILITY GROUP = [fgedu_ag];
GO

# 1. 安装故障转移集群功能
# 在Windows Server上执行以下命令
# Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

# 2. 验证集群配置
# Test-Cluster -Node fgedu1, fgedu2, fgedu3

# 3. 创建故障转移集群
# New-Cluster -Name fgedu-cluster -Node fgedu1, fgedu2, fgedu3 -StaticAddress 192.168.1.200

# 4. 配置集群网络
# Get-ClusterNetwork | Set-ClusterNetwork -Name “Cluster Network 1” -Description “Private network for cluster communication”

# 5. 配置存储
# Add-ClusterDisk -Cluster fgedu-cluster -DiskNumber 1, 2, 3

# 6. 创建SQLServer故障转移集群实例
# 使用SQLServer安装向导创建故障转移集群实例

# 7. 验证故障转移集群配置
SELECT @@SERVERNAME AS current_instance;
GO

# 查看集群节点状态
SELECT
node_name,
status_description
FROM sys.dm_hadr_cluster_members;
GO

# 查看集群资源状态
SELECT
resource_name,
resource_state_desc
FROM sys.dm_hadr_cluster_resource_states;
GO

# 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,
ars.role_desc AS role,
ars.operational_state_desc AS operational_state,
ars.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 ars ON ar.replica_id = ars.replica_id
ORDER BY ag.name, ar.replica_server_name;
GO

# 2. 监控数据库同步状态
SELECT
db_name(drs.database_id) AS database_name,
drs.replica_server_name AS replica,
drs.synchronization_state_desc AS synchronization_state,
drs.synchronization_health_desc AS synchronization_health,
drs.last_hardened_lsn,
drs.last_redone_lsn,
drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states drs
ORDER BY db_name(drs.database_id), drs.replica_server_name;
GO

# 3. 监控故障转移集群状态
SELECT
cluster_name,
quorum_type_desc,
quorum_state_desc
FROM sys.dm_hadr_cluster;
GO

# 4. 创建高可用性监控表
CREATE TABLE dbo.fgedu_ha_monitor (
monitor_id INT PRIMARY KEY IDENTITY,
monitor_time DATETIME NOT NULL DEFAULT GETDATE(),
availability_group NVARCHAR(128) NOT NULL,
replica NVARCHAR(128) NOT NULL,
role NVARCHAR(50) NOT NULL,
operational_state NVARCHAR(50) NOT NULL,
synchronization_health NVARCHAR(50) NOT NULL,
database_name NVARCHAR(128) NOT NULL,
synchronization_state NVARCHAR(50) NOT NULL,
secondary_lag_seconds INT NULL
);
GO

# 5. 创建高可用性监控存储过程
CREATE PROCEDURE dbo.usp_monitor_ha
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO dbo.fgedu_ha_monitor (
availability_group,
replica,
role,
operational_state,
synchronization_health,
database_name,
synchronization_state,
secondary_lag_seconds
)
SELECT
ag.name AS availability_group,
ar.replica_server_name AS replica,
ars.role_desc AS role,
ars.operational_state_desc AS operational_state,
ars.synchronization_health_desc AS synchronization_health,
db_name(drs.database_id) AS database_name,
drs.synchronization_state_desc AS synchronization_state,
drs.secondary_lag_seconds
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
JOIN sys.dm_hadr_database_replica_states drs ON ars.replica_id = drs.replica_id
ORDER BY ag.name, ar.replica_server_name, db_name(drs.database_id);
END;
GO

# 6. 执行高可用性监控
EXEC dbo.usp_monitor_ha;
GO

# 7. 查看监控结果
SELECT * FROM dbo.fgedu_ha_monitor ORDER BY monitor_time DESC;
GO

# 8. 执行手动故障转移
ALTER AVAILABILITY GROUP [fgedu_ag] FAILOVER;
GO

# 9. 执行强制故障转移（仅在主节点不可用时使用）
ALTER AVAILABILITY GROUP [fgedu_ag] FORCE_FAILOVER_ALLOW_DATA_LOSS;
GO

# 1. 环境准备
# 服务器：
# fgedu1.fgedu.net.cn (主节点)
# fgedu2.fgedu.net.cn (辅助节点)
# fgedu3.fgedu.net.cn (辅助节点，异步复制)

# 2. 配置步骤

# 步骤1：启用Always On可用性组功能
ALTER SERVER CONFIGURATION SET HADR SERVICE ACCOUNT = ‘fgedu\sqlservice’;
GO

# 步骤2：创建数据库镜像端点
CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT=5022, LISTENER_IP=ALL)
FOR DATA_MIRRORING (
ROLE=ALL,
AUTHENTICATION=WINDOWS NEGOTIATE,
ENCRYPTION=REQUIRED ALGORITHM AES
);
GO

# 步骤3：创建测试数据库
CREATE DATABASE fgedudb;
GO

# 步骤4：备份数据库
BACKUP DATABASE fgedudb TO DISK = ‘\fgedu1\backup\fgedudb.bak’ WITH INIT;
GO

# 步骤5：在辅助节点上还原数据库
RESTORE DATABASE fgedudb FROM DISK = ‘\fgedu1\backup\fgedudb.bak’ WITH NORECOVERY;
GO

# 步骤6：创建可用性组
CREATE AVAILABILITY GROUP [fgedu_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)
),
N’FGEDU3′ WITH (
ENDPOINT_URL = N’TCP://fgedu3.fgedu.net.cn:5022′,
FAILOVER_MODE = MANUAL,
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 10,
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
);
GO

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

# 步骤8：加入数据库到可用性组
— 在辅助节点上执行
ALTER DATABASE [fgedudb] SET HADR AVAILABILITY GROUP = [fgedu_ag];
GO

# 步骤9：验证配置
SELECT
ag.name AS availability_group,
ar.replica_server_name AS replica,
ars.role_desc AS role,
ars.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 ars ON ar.replica_id = ars.replica_id;
GO

# 结果
/*
availability_group replica role synchronization_health
—————– —————– ———- ————————
fgedu_ag FGEDU1 PRIMARY HEALTHY
fgedu_ag FGEDU2 SECONDARY HEALTHY
fgedu_ag FGEDU3 SECONDARY HEALTHY
*/

# 1. 环境准备
# 服务器：
# fgedu1.fgedu.net.cn
# fgedu2.fgedu.net.cn
# 共享存储：iSCSI存储

# 2. 配置步骤

# 步骤1：安装故障转移集群功能
# 在Windows Server上执行以下命令
# Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

# 步骤2：验证集群配置
# Test-Cluster -Node fgedu1, fgedu2

# 步骤3：创建故障转移集群
# New-Cluster -Name fgedu-cluster -Node fgedu1, fgedu2 -StaticAddress 192.168.1.200

# 步骤4：配置集群网络
# Get-ClusterNetwork | Set-ClusterNetwork -Name “Cluster Network 1” -Description “Private network for cluster communication”

# 步骤5：配置存储
# Add-ClusterDisk -Cluster fgedu-cluster -DiskNumber 1, 2

# 步骤6：创建SQLServer故障转移集群实例
# 使用SQLServer安装向导创建故障转移集群实例

# 步骤7：验证故障转移集群配置
SELECT @@SERVERNAME AS current_instance;
GO

# 结果
/*
current_instance
—————-
fgedu-cluster
*/

# 步骤8：测试故障转移
# 手动执行故障转移
# Move-ClusterGroup -Name “SQL Server (MSSQLSERVER)” -Node fgedu2

# 验证故障转移结果
SELECT @@SERVERNAME AS current_instance;
GO

# 结果
/*
current_instance
—————-
fgedu-cluster
*/

# 查看集群节点状态
SELECT
node_name,
status_description
FROM sys.dm_hadr_cluster_members;
GO

# 结果
/*
node_name status_description
———– ——————–
fgedu1 Up
fgedu2 Up
*/

# 1. 故障场景：主节点网络故障

# 症状：
# – 应用无法连接到数据库
# – 主节点网络中断

# 处理步骤：

# 步骤1：确认故障
SELECT
ag.name AS availability_group,
ar.replica_server_name AS replica,
ars.role_desc AS role,
ars.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 ars ON ar.replica_id = ars.replica_id;
GO

# 步骤2：执行手动故障转移
ALTER AVAILABILITY GROUP [fgedu_ag] FAILOVER;
GO

# 步骤3：验证故障转移结果
SELECT
ag.name AS availability_group,
ar.replica_server_name AS replica,
ars.role_desc AS role,
ars.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 ars ON ar.replica_id = ars.replica_id;
GO

# 步骤4：恢复主节点
# 修复网络故障后，将主节点重新加入可用性组
ALTER AVAILABILITY GROUP [fgedu_ag] JOIN;
GO

# 2. 故障场景：数据库同步延迟

# 症状：
# – 辅助节点同步状态为SYNCHRONIZING
# – 同步延迟不断增加

# 处理步骤：

# 步骤1：查看同步状态
SELECT
db_name(drs.database_id) AS database_name,
drs.replica_server_name AS replica,
drs.synchronization_state_desc AS synchronization_state,
drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states drs
ORDER BY drs.secondary_lag_seconds DESC;
GO

# 步骤2：检查网络状态
# 测试节点间网络连接和带宽

# 步骤3：检查磁盘I/O
# 检查主节点和辅助节点的磁盘I/O性能

# 步骤4：解决同步延迟
# 如果是网络问题，修复网络连接
# 如果是磁盘I/O问题，优化存储性能
# 如果是负载问题，调整系统负载

# 步骤5：验证同步状态
SELECT
db_name(drs.database_id) AS database_name,
drs.replica_server_name AS replica,
drs.synchronization_state_desc AS synchronization_state,
drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states drs
ORDER BY drs.secondary_lag_seconds DESC;
GO