主从复制与高可用架构
8.1 SQL Server 复制技术
SQL Server 复制（Replication）是一套将数据从一个数据库（发布者）传输到另一个数据库（订阅者）的技术体系。与 Always On 不同，复制是逻辑复制，可以跨版本、跨平台，甚至只复制部分表或列。
8.1.1 复制拓扑中的角色
┌──────────────────────────────────────────────────────────────┐
│ SQL Server 复制拓扑 │
│ │
│ ┌──────────────┐ 发布 ┌──────────────────────┐ │
│ │ 发布者 │──────────────→│ 分发服务器（Distributor）│ │
│ │ (Publisher) │ (Publication)│ - 分发数据库 │ │
│ └──────────────┘ │ - 日志读取器代理 │ │
│ └───────────┬──────────┘ │
│ │ 分发 │
│ ┌───────────────┼──────────┐ │
│ ↓ ↓ ↓ │
│ ┌──────────────┐ ┌──────────────┐ │
│ │ 订阅者1 │ │ 订阅者2 │ │
│ │(Subscriber) │ │(Subscriber) │ │
│ │ 推送/拉取订阅 │ │ 读扩展/报表 │ │
│ └──────────────┘ └──────────────┘ │
└──────────────────────────────────────────────────────────────┘
8.1.2 快照复制（Snapshot Replication）
快照复制定期将发布者的完整数据快照应用到订阅者，适合数据量小、允许延迟的场景。
-- 配置分发服务器（通常与发布者在同一台机器）
EXEC sp_adddistributor @distributor = @@SERVERNAME, @password = 'Distrib@Password1';
EXEC sp_adddistributiondb
@database = 'distribution',
@data_folder = 'D:\SQLData',
@log_folder = 'F:\SQLLog',
@log_file_size = 2,
@min_distretention = 0,
@max_distretention = 72, -- 分发历史保留72小时
@history_retention = 48;
-- 在发布者上注册分发服务器
EXEC sp_adddistpublisher
@publisher = @@SERVERNAME,
@distribution_db = 'distribution',
@security_mode = 1, -- Windows 验证
@working_directory = '\\FileServer\ReplData';
-- 创建快照发布
EXEC sp_replicationdboption @dbname = 'YourDB', @optname = 'publish', @value = 'true';
DECLARE @publicationDB AS sysname = 'YourDB';
DECLARE @publication AS sysname = 'pub_Products_Snapshot';
EXEC sp_addpublication
@publication = @publication,
@status = N'active',
@repl_freq = N'snapshot', -- 快照模式
@independent_agent = N'true',
@allow_push = N'true',
@allow_pull = N'true',
@snapshot_in_defaultfolder = N'true';
-- 添加文章（发布的表）
EXEC sp_addarticle
@publication = @publication,
@article = N'Products',
@source_object = N'Products',
@source_owner = N'dbo',
@schema_option = 0x000000000803509D,
@identityrangemanagementoption = N'none',
@destination_table = N'Products',
@destination_owner = N'dbo',
@vertical_partition = N'false';
-- 生成初始快照
EXEC sp_startpublication_snapshot @publication = @publication;
-- 创建推送订阅（发布者主动推送到订阅者）
EXEC sp_addsubscription
@publication = @publication,
@subscriber = N'SubscriberServer',
@destination_db = N'YourDB_Replica',
@subscription_type = N'push',
@sync_type = N'automatic';
EXEC sp_addpushsubscription_agent
@publication = @publication,
@subscriber = N'SubscriberServer',
@subscriber_db = N'YourDB_Replica',
@subscriber_security_mode = 1;
8.1.3 事务复制（Transactional Replication）
事务复制实时捕获发布者的事务日志，将每个 DML 操作近实时传播到订阅者，是最常用的主从同步方案，延迟通常在秒级以内。
-- 创建事务发布
EXEC sp_addpublication
@publication = 'pub_Orders_Transactional',
@status = N'active',
@repl_freq = N'continuous', -- 连续（事务复制）
@conflict_policy = N'pub wins', -- 冲突时发布者获胜（双向复制时配置）
@allow_initialize_from_backup = N'false',
@independent_agent = N'true';
-- 添加文章（可以设置列过滤器、行过滤器）
EXEC sp_addarticle
@publication = 'pub_Orders_Transactional',
@article = N'Orders',
@source_object = N'Orders',
@source_owner = N'dbo',
-- 列过滤（只复制部分列，排除敏感字段）
@ins_cmd = N'CALL sp_MSins_dboOrders',
@del_cmd = N'CALL sp_MSdel_dboOrders',
@upd_cmd = N'SCALL sp_MSupd_dboOrders';
-- 添加行过滤器（只复制特定区域的订单）
EXEC sp_articlefilter
@publication = 'pub_Orders_Transactional',
@article = N'Orders',
@filter_name = N'fltr_Orders_North',
@filter_clause = N'RegionID = 1'; -- 只复制华北区数据
-- 监控复制延迟
SELECT
a.name AS PublicationName,
s.subscriber_db,
s.subscription_type,
h.agent_id,
h.start_time,
h.duration,
h.comments,
h.delivered_transactions,
h.delivered_commands,
h.delivery_latency
FROM msdb.dbo.MSdistribution_agents a
JOIN msdb.dbo.MSsubscriptions s ON a.id = s.agent_id
JOIN msdb.dbo.MSdistribution_history h ON a.id = h.agent_id
WHERE h.start_time = (SELECT MAX(start_time) FROM msdb.dbo.MSdistribution_history WHERE agent_id = a.id)
ORDER BY h.delivery_latency DESC;
-- 查看复制延迟（秒）
EXEC sp_replmonitorhelppublication @publisher = @@SERVERNAME;
8.1.4 合并复制（Merge Replication）
合并复制允许发布者和订阅者都可以修改数据，定期双向同步并自动解决冲突，适合移动端/分支机构场景。
-- 创建合并发布（配置简化示例）
EXEC sp_addmergepublication
@publication = 'pub_SalesData_Merge',
@retention = 14, -- 订阅保留14天
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@conflict_policy = N'pub wins', -- 冲突解决：发布者优先
@conflict_logging = N'both', -- 记录冲突日志
@publication_compatibility_level = N'100RTM';
-- 合并复制支持部分数据同步（参数化行过滤器）
-- 每个订阅者只收到自己区域的数据
EXEC sp_addmergearticle
@publication = 'pub_SalesData_Merge',
@article = N'SalesData',
@source_object = N'SalesData',
@source_owner = N'dbo',
@subset_filterclause = N'RegionID = HOST_NAME()'; -- 订阅者主机名=区域ID
-- 查看合并复制冲突
SELECT * FROM dbo.conflict_SalesData_SalesData; -- 冲突记录表
8.2 Always On 可用性组（AG）
Always On AG 是 SQL Server 2012+ 的旗舰高可用技术，提供数据库级别的高可用和读扩展，支持自动故障转移，RPO（数据丢失）可达 0（同步提交模式）。
8.2.1 AG 架构
┌───────────────────────────────────────────────────────────────────┐
│ Always On 可用性组 │
│ │
│ ┌────────────────────────────┐ │
│ │ Windows Server │ ← WSFC 集群（Windows 故障转移集群）│
│ │ Failover Cluster (WSFC) │ │
│ └────────────────────────────┘ │
│ │
│ 主副本（Primary Replica） 次副本（Secondary Replica） │
│ ┌─────────────────────────┐ ←同步→ ┌─────────────────────────┐│
│ │ Node1 (主节点) │ 日志流 │ Node2 (同步次副本) ││
│ │ - 可读写 │──────────│ - 可读（AG监听器路由） ││
│ │ - 生产流量 │ │ - 自动故障转移 ││
│ └─────────────────────────┘ └─────────────────────────┘│
│ │
│ ┌─────────────────────────┐│
│ │ Node3 (异步次副本) ││
│ │ - 异地灾备 ││
│ │ - 手动故障转移 ││
│ └─────────────────────────┘│
│ │
│ AG 监听器 (AG Listener): 192.168.1.100,1433 │
│ - 客户端连接入口 │
│ - 读写路由到主副本 │
│ - 只读路由到次副本 │
└────────────────────────────────────────────────────────────────────┘
8.2.2 配置 Always On AG（T-SQL）
-- 前提：已配置 WSFC，所有节点已启用 Always On
-- 在 SQL Server 配置管理器中启用 Always On：
-- 或 PowerShell：Enable-SqlAlwaysOn -ServerInstance "Node1" -Force
-- 在主节点（Node1）上创建可用性组
CREATE AVAILABILITY GROUP [AG_Production]
WITH (
AUTOMATED_BACKUP_PREFERENCE = SECONDARY, -- 备份优先在次副本执行
FAILURE_CONDITION_LEVEL = 3, -- 1=最宽松，5=最严格
HEALTH_CHECK_TIMEOUT = 30000, -- 30秒健康检查超时
DB_FAILOVER = ON, -- 数据库级故障转移
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0 -- 0=无要求(高性能)，1+需N个同步
)
FOR DATABASE [YourDB], [AnotherDB] -- 加入 AG 的数据库
REPLICA ON
-- 主副本
N'Node1\MSSQLSERVER' WITH (
ENDPOINT_URL = N'TCP://Node1.domain.com:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, -- 同步提交（RPO=0）
FAILOVER_MODE = AUTOMATIC, -- 自动故障转移
BACKUP_PRIORITY = 50,
PRIMARY_ROLE (ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_LIST = (N'Node2\MSSQLSERVER', N'Node3\MSSQLSERVER')),
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY, READ_ONLY_ROUTING_URL = N'TCP://Node1.domain.com:1433')
),
-- 同步次副本（同一数据中心，自动故障转移）
N'Node2\MSSQLSERVER' WITH (
ENDPOINT_URL = N'TCP://Node2.domain.com:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC,
BACKUP_PRIORITY = 100, -- 备份首选在 Node2 执行
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY, READ_ONLY_ROUTING_URL = N'TCP://Node2.domain.com:1433')
),
-- 异步次副本（异地灾备，手动故障转移）
N'Node3\MSSQLSERVER' WITH (
ENDPOINT_URL = N'TCP://Node3.domain.com:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, -- 异步（不影响主库性能）
FAILOVER_MODE = MANUAL,
BACKUP_PRIORITY = 0, -- 不参与备份
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO) -- 不允许连接
);
-- 创建 AG 监听器
ALTER AVAILABILITY GROUP [AG_Production]
ADD LISTENER N'ag-production-listener' (
WITH IP ((N'192.168.1.100', N'255.255.255.0'), (N'192.168.2.100', N'255.255.255.0')),
PORT = 1433
);
-- 在次副本（Node2、Node3）上加入 AG
ALTER AVAILABILITY GROUP [AG_Production] JOIN;
-- 先在主节点备份数据库，然后在次副本上还原并加入 AG
-- 主节点：备份
BACKUP DATABASE [YourDB] TO DISK = '\\FileServer\AGInit\YourDB.bak'
WITH FORMAT, COMPRESSION, CHECKSUM;
BACKUP LOG [YourDB] TO DISK = '\\FileServer\AGInit\YourDB_log.bak'
WITH COMPRESSION, CHECKSUM;
-- 次副本：还原并加入
RESTORE DATABASE [YourDB]
FROM DISK = '\\FileServer\AGInit\YourDB.bak'
WITH NORECOVERY, MOVE 'YourDB' TO 'D:\Data\YourDB.mdf',
MOVE 'YourDB_log' TO 'F:\Log\YourDB_log.ldf';
RESTORE LOG [YourDB]
FROM DISK = '\\FileServer\AGInit\YourDB_log.bak'
WITH NORECOVERY;
ALTER DATABASE [YourDB] SET HADR AVAILABILITY GROUP = [AG_Production];
8.2.3 AG 监控与故障转移
-- 查看 AG 健康状态（关键监控查询）
SELECT
ag.name AS AGName,
ar.replica_server_name AS ReplicaServer,
ar.availability_mode_desc,
ar.failover_mode_desc,
ars.role_desc,
ars.operational_state_desc,
ars.synchronization_health_desc,
ars.connected_state_desc,
ars.last_connect_error_description,
drs.synchronization_state_desc,
drs.synchronization_health_desc AS DBSyncHealth,
drs.log_send_queue_size / 1024.0 AS SendQueueMB,
drs.redo_queue_size / 1024.0 AS RedoQueueMB,
drs.log_send_rate / 1024.0 AS SendRateMBps,
drs.redo_rate / 1024.0 AS RedoRateMBps
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 ar.replica_id = drs.replica_id
ORDER BY ag.name, ars.role_desc;
-- 查看 AG 监听器
SELECT ag.name, agl.dns_name, agl.port, gli.ip_address, gli.ip_subnet_mask, gli.network_subnet_ip
FROM sys.availability_group_listeners agl
JOIN sys.availability_groups ag ON agl.group_id = ag.group_id
JOIN sys.availability_group_listener_ip_addresses gli ON agl.listener_id = gli.listener_id;
-- 手动故障转移（计划性切换，如维护主节点）
-- 在目标次副本上执行：
ALTER AVAILABILITY GROUP [AG_Production] FAILOVER;
-- 强制故障转移（主节点完全不可用时，数据可能丢失）
ALTER AVAILABILITY GROUP [AG_Production] FORCE_FAILOVER_ALLOW_DATA_LOSS;
-- ⚠️ 强制故障转移后，必须在原主副本恢复后执行：
-- ALTER AVAILABILITY GROUP [AG_Production] SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0);
-- 并重新同步
-- 配置只读路由（将 SELECT 连接自动路由到次副本）
-- 连接字符串需指定 ApplicationIntent=ReadOnly
-- Server=ag-production-listener,1433;Database=YourDB;ApplicationIntent=ReadOnly;
8.3 日志传送（Log Shipping）
日志传送是 SQL Server 最简单的高可用/灾备方案之一，通过定期备份/复制/还原事务日志实现异地灾备，延迟通常在分钟级。
-- 配置日志传送（主服务器端）
-- 步骤1：在主库上启用日志传送
EXEC master.dbo.sp_add_log_shipping_primary_database
@database = N'YourDB',
@backup_directory = N'\\FileServer\LogShipping\Primary',
@backup_share = N'\\FileServer\LogShipping\Primary',
@backup_job_name = N'LSBackup_YourDB',
@backup_retention_period = 4320, -- 保留3天
@backup_threshold = 60, -- 60分钟无备份则告警
@threshold_alert_enabled = 1,
@history_retention_period = 5760,
@backup_job_id = @LS_BackupJobId OUTPUT,
@primary_id = @LS_PrimaryId OUTPUT;
-- 步骤2：在次服务器上配置还原
EXEC master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = N'PrimaryServer',
@primary_database = N'YourDB',
@backup_source_directory = N'\\FileServer\LogShipping\Primary',
@backup_destination_directory = N'D:\LogShipping\Secondary',
@copy_job_name = N'LSCopy_PrimaryServer_YourDB',
@restore_job_name = N'LSRestore_PrimaryServer_YourDB',
@file_retention_period = 4320;
-- 监控日志传送状态
SELECT
primary_server,
primary_database,
secondary_server,
secondary_database,
last_backup_filename,
last_backup_date,
last_copied_filename,
last_copied_date,
last_restored_filename,
last_restored_date,
DATEDIFF(MINUTE, last_backup_date, GETDATE()) AS MinutesSinceLastBackup,
DATEDIFF(MINUTE, last_restored_date, GETDATE()) AS MinutesSinceLastRestore
FROM msdb.dbo.log_shipping_monitor_secondary
ORDER BY primary_server, primary_database;
8.4 分布式可用性组与读扩展
8.4.1 分布式 AG（SQL Server 2016+）
分布式 AG 允许跨 WSFC 集群的 AG 之间建立同步关系，实现跨数据中心的灾备。
-- 在主数据中心的 AG 上创建分布式 AG
-- （此 AG 成为分布式 AG 的主 AG）
CREATE AVAILABILITY GROUP [DAG_Production]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON
N'AG_Production' -- 主数据中心的 AG
WITH (
LISTENER_URL = N'TCP://ag-production-listener.dc1.com:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
),
N'AG_DR' -- 灾备数据中心的 AG
WITH (
LISTENER_URL = N'TCP://ag-dr-listener.dc2.com:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);
-- 在灾备数据中心的 AG 上加入分布式 AG
ALTER AVAILABILITY GROUP [DAG_Production] JOIN
AVAILABILITY GROUP ON
N'AG_Production' WITH (LISTENER_URL = N'TCP://ag-production-listener.dc1.com:5022', ...),
N'AG_DR' WITH (LISTENER_URL = N'TCP://ag-dr-listener.dc2.com:5022', ...);
8.4.2 高可用方案对比
| 方案 | RPO | RTO | 自动故障转移 | 读扩展 | 部署复杂度 | 许可 |
|---|---|---|---|---|---|---|
| Always On AG（同步） | 0 | <30s | ✅ | ✅ | 高 | Enterprise |
| Always On AG（异步） | 秒级 | 分钟级 | ❌ | ✅ | 高 | Enterprise |
| 事务复制 | 秒级 | 分钟级 | ❌ | ✅ | 中 | Standard+ |
| 日志传送 | 分钟级 | 30分钟+ | ❌ | 有限 | 低 | Standard+ |
| 数据库镜像 | 0（同步） | <30s | ✅（同步） | ❌ | 中 | Standard+ |
注意：数据库镜像（Database Mirroring）已在 SQL Server 2016 开始弃用，SQL Server 2022 中已删除，请迁移到 Always On AG。
8.4.3 Always On AG 与 RCSI 的关系
-- 次副本的可读性需要 RCSI 或 Snapshot Isolation 支持
-- 在次副本上读取时，自动使用行版本（无需在次副本上开启 RCSI）
-- 检查 AG 数据库是否启用了 RCSI（建议开启）
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'YourDB';
-- 应用连接字符串示例（使用 AG 监听器自动路由）
-- 读写操作：
-- Server=ag-production-listener,1433;Database=YourDB;Integrated Security=True;MultiSubnetFailover=True;
-- 只读操作：
-- Server=ag-production-listener,1433;Database=YourDB;Integrated Security=True;MultiSubnetFailover=True;ApplicationIntent=ReadOnly;
8.5 小结
| 技术 | 最佳适用场景 |
|---|---|
| 快照复制 | 参考数据单向同步，可接受分钟~小时级延迟 |
| 事务复制 | 近实时单向同步，读扩展，跨平台/版本 |
| 合并复制 | 离线/移动端双向同步 |
| Always On AG（同步） | 生产核心库，RPO=0，自动故障转移 |
| Always On AG（异步） | 异地灾备，读扩展报表库 |
| 日志传送 | 简单灾备，低预算场景 |
| 分布式 AG | 跨 WSFC 集群的跨数据中心灾备 |
下一章：性能监控与查询调优
文章版权声明：除非注明，否则均为边学边练网络文章，版权归原作者所有
