SQL Server 2022 读写分离问题整合

跟着热点整理一下遇到过的SQL Server的问题，这篇来聊聊读写分离遇到的和听说过的问题。

一、读写分离实现方法

1. 原生高可用方案

1.1 Always On 可用性组（推荐方案）

配置步骤：

-- 1. 启用Always On功能
USE [master]
GO
ALTER SERVER CONFIGURATION SET HADR_CLUSTER_TYPE = WINDOWS;
GO
​
-- 2. 创建可用性组
CREATE AVAILABILITY GROUP [AG_ReadScale]
WITH (DB_FAILOVER = ON, CLUSTER_TYPE = WINDOWS)
FOR DATABASE [YourDB]
REPLICA ON 'PrimaryServer' WITH (ENDPOINT_URL = 'TCP://PrimaryServer:5022',AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = AUTOMATIC,SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)),'SecondaryServer' WITH (ENDPOINT_URL = 'TCP://SecondaryServer:5022',AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = AUTOMATIC,SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY));

读写分离配置：

-- 配置只读路由
ALTER AVAILABILITY GROUP [AG_ReadScale]
MODIFY REPLICA ON 'SecondaryServer' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = ('SecondaryServer'))
);
​
-- 应用程序连接字符串示例
"Server=PrimaryServer;Database=YourDB;ApplicationIntent=ReadWrite;"
"Server=AG_Listener;Database=YourDB;ApplicationIntent=ReadOnly;"

1.2 日志传送（Legacy方案）

配置步骤：

-- 主服务器配置
EXEC sp_add_log_shipping_primary_database@database = N'YourDB',@backup_directory = N'\\backup\share',@backup_job_name = N'LSBackup_YourDB';
​
-- 辅助服务器配置
EXEC sp_add_log_shipping_secondary_database@database = N'YourDB',@primary_server = N'PrimaryServer',@restore_job_name = N'LSRestore_YourDB';

2. 第三方中间件方案

2.1 使用ProxySQL

配置示例：

# proxysql.cnf配置
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES
(10,'PrimaryServer',1433),  # 写组
(20,'SecondaryServer1',1433), # 读组
(20,'SecondaryServer2',1433); # 读组
​
# 读写分离规则
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES
(1,1,'^SELECT.*FOR UPDATE',10,1),  # 写操作
(2,1,'^SELECT',20,1);              # 读操作

2.2 使用HAProxy

配置示例：

# haproxy.cfg配置
frontend sql_frontbind *:1433mode tcpdefault_backend sql_write
​
backend sql_writemode tcpserver primary PrimaryServer:1433 check
​
backend sql_readmode tcpbalance roundrobinserver secondary1 SecondaryServer1:1433 checkserver secondary2 SecondaryServer2:1433 check
​
# 根据SQL注释路由
acl is_read sql_req -i -m beg "/*read*/"
use_backend sql_read if is_read

二、常见问题与解决方案

1. 数据同步延迟

问题现象：

• 读副本数据落后于主库

• 报表查询结果不一致

解决方案：

-- 1. 监控延迟
SELECT ag.name AS [AG Name],ar.replica_server_name,db_name(ds.database_id) AS [Database],ds.synchronization_state_desc,ds.log_send_queue_size,ds.redo_queue_size
FROM sys.dm_hadr_database_replica_states ds
JOIN sys.availability_replicas ar ON ds.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id;
​
-- 2. 优化方案
- 增加网络带宽（至少1Gbps）
- 调整同步提交模式为异步（对数据一致性要求不高的场景）
- 限制大事务（拆分超过100MB的事务）

2. 只读路由失效

问题现象：

• ApplicationIntent=ReadOnly的连接仍被路由到主节点

解决方案：

-- 1. 检查只读路由配置
SELECT ag.name AS [AG Name],replica_server_name,read_only_routing_url
FROM sys.availability_replicas
WHERE read_only_routing_url IS NOT NULL;
​
-- 2. 修复配置
ALTER AVAILABILITY GROUP [AG_ReadScale]
MODIFY REPLICA ON 'SecondaryServer' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = ('SecondaryServer'))
);
​
-- 3. 验证连接
-- 使用SSMS连接字符串：
"Server=AG_Listener;Database=YourDB;ApplicationIntent=ReadOnly;"

3. 临时表问题

问题现象：

• 使用临时表的查询在只读副本失败

• 错误消息："The database 'tempdb' is not accessible"

解决方案：

-- 1. 应用层修改（推荐）
- 使用表变量替代临时表
- 或使用全局临时表(##temp)
​
-- 2. 数据库配置
-- 启用辅助副本的tempdb访问（SQL 2022新特性）
ALTER AVAILABILITY GROUP [AG_ReadScale]
MODIFY REPLICA ON 'SecondaryServer' WITH (SECONDARY_ROLE(ALLOW_TEMP_TABLES=ON));

4. 负载不均

问题现象：

• 读副本间负载不均衡

• 单个副本CPU过高

解决方案：

-- 1. 配置读权重（SQL 2022新特性）
ALTER AVAILABILITY GROUP [AG_ReadScale]
MODIFY REPLICA ON 'SecondaryServer1' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_WEIGHT=3)
);
ALTER AVAILABILITY GROUP [AG_ReadScale]
MODIFY REPLICA ON 'SecondaryServer2' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_WEIGHT=1)
);
​
-- 2. 使用中间件负载均衡
- 配置ProxySQL/HAProxy的加权轮询
- 基于副本性能指标动态调整权重

三、性能优化建议

1. 连接池配置

// ADO.NET连接池优化
"Server=AG_Listener;Database=YourDB;Max Pool Size=200;Min Pool Size=20;Connection Timeout=30;"

2. 查询提示

-- 强制读操作走副本
SELECT * FROM Orders WITH (READUNCOMMITTED)
OPTION (READONLY);
​
-- 强制写操作走主库（即使连接字符串标记为ReadOnly）
SELECT * FROM Orders OPTION (READCOMMITTEDLOCK);

3. 监控指标

-- 关键性能计数器
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Log Send Queue Size','Redo Queue Size','Transactions/sec','Lock Waits/sec'
);

四、SQL Server 2022 新特性利用

1. 内存优化TempDB元数据

-- 启用特性（减少TempDB争用）
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

2. 智能查询处理

-- 启用智能查询处理
ALTER DATABASE SCOPED CONFIGURATION SET INTELLIGENT_QUERY_PROCESSING = ON;

3. 参数敏感计划优化

-- 避免参数嗅探问题
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

这些事给予SQLServer 2022总结的，如果有版本问题，期待和各位大佬学习。