跟著熱點整理一下遇到過的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總結的，如果有版本問題，期待和各位大佬學習。