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

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

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

2.2 SQLServer高可用切换性能规划

# 查看磁盘空间使用情况
EXEC xp_fixeddrives;
GO

Drive MB free
—– ——–
C 102400
D 512000
E 1024000

对于高可用环境，建议配置至少1TB的存储空间，使用RAID 10配置提高性能和可靠性。更多视频教程www.fgedu.net.cn。

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

3.1 SQLServer高可用切换部署实施

# 创建Always On可用性组
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)
);
GO

# 配置可用性组监听器
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

# 配置读取路由
ALTER AVAILABILITY GROUP [fgedu_ag]
MODIFY REPLICA ON
N’FGEDU2′ WITH (
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
);
GO

ALTER AVAILABILITY GROUP [fgedu_ag]
MODIFY REPLICA ON
N’FGEDU1′ WITH (
PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N’FGEDU2′))
);
GO

ALTER AVAILABILITY GROUP [fgedu_ag]
MODIFY REPLICA ON
N’FGEDU2′ WITH (
PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N’FGEDU1′))
);
GO

学习交流加群风哥QQ113257174。

Part04-生产案例与实战讲解

4.1 SQLServer AlwaysOn高可用切换实战

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

# 查看可用性组状态
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

availability_group replica availability_mode failover_mode role operational_state
—————– ———– —————– ————- ——– —————–
fgedu_ag FGEDU1 SYNCHRONOUS_COMMIT AUTOMATIC SECONDARY ONLINE
fgedu_ag FGEDU2 SYNCHRONOUS_COMMIT AUTOMATIC PRIMARY ONLINE

# 查看集群资源状态
Get-ClusterResource -Cluster fgedu-cluster | Select-Object Name, State

Name State
—- —–
Cluster IP Address Online
Cluster Name Online
SQL Server Online
SQL Server Agent Online

# 手动故障转移集群
Move-ClusterGroup -Name “SQL Server (MSSQLSERVER)” -Node FGEDU2

# 查看集群组状态
Get-ClusterGroup -Cluster fgedu-cluster | Select-Object Name, State, OwnerNode

Name State OwnerNode
—- —– ———
Available Storage Online FGEDU1
Cluster Group Online FGEDU1
SQL Server Online FGEDU2

风哥提示：在进行高可用切换时，应提前做好测试和演练，确保切换过程顺利。from SQLServer视频:www.itpux.com。

Part05-风哥经验总结与分享

5.1 SQLServer高可用切换运维最佳实践

1. 定期进行高可用切换演练，确保切换过程顺利

2. 监控高可用状态，及时发现并解决问题

3. 配置合理的故障转移策略，减少业务中断时间

4. 建立完善的高可用切换预案，确保在故障发生时能够快速响应

5. 定期备份数据库，确保数据安全

5.2 SQLServer高可用切换常见问题与解决方案

# 查看可用性组错误日志
SELECT
timestamp,
message
FROM sys.fn_hadr_availability_group_states()
GO

# 查看故障转移历史
SELECT
event_time,
availability_group_name,
previous_role_desc,
current_role_desc,
failure_condition_level,
failure_desc
FROM sys.dm_hadr_availability_replica_states_history;
GO

针对高可用切换失败的问题，可以通过查看错误日志和故障转移历史来定位原因，并采取相应的解决方案。更多学习教程公众号风哥教程itpux_com。