外观
SQLServer 事务复制配置
事务复制概述
Transactional Replication（事务复制）是 SQL Server 提供的一种数据同步技术，用于将数据从一个数据库（发布服务器）复制到一个或多个数据库（订阅服务器）。事务复制基于事务日志，确保数据的近实时同步，适用于读写分离、数据分发和报表场景。
事务复制的组成部分
发布服务器 (Publisher)：
- 包含要复制的源数据库
- 负责将事务日志中的更改发送到分发服务器
分发服务器 (Distributor)：
- 存储复制元数据和事务
- 管理复制代理
- 可以与发布服务器在同一台服务器上，也可以是独立服务器
订阅服务器 (Subscriber)：
- 接收复制的数据
- 可以是只读或可更新订阅
- 支持多个订阅服务器
复制代理：
- 快照代理：生成初始快照
- 日志读取器代理：从发布服务器读取事务日志
- 分发代理：将事务分发到订阅服务器
- 队列读取器代理：用于可更新订阅（可选）
事务复制的工作原理
- 初始快照生成：快照代理生成发布数据库的初始快照，包括表结构、数据和索引
- 事务捕获：日志读取器代理监控发布数据库的事务日志，捕获符合复制条件的事务
- 事务存储：捕获的事务存储在分发数据库中
- 事务分发：分发代理将事务从分发数据库发送到订阅服务器
- 事务应用：订阅服务器应用接收到的事务，更新订阅数据库
事务复制配置步骤
步骤 1：配置分发服务器
在 SSMS 中配置分发：
- 右键点击 "复制" 节点，选择 "配置分发"
- 选择分发服务器（可以是本地或远程服务器）
- 配置分发数据库（默认使用 "distribution"）
- 配置快照文件夹（确保共享权限正确）
- 完成配置
使用 T-SQL 配置分发：
sql
-- 启用发布服务器
USE master;
EXEC sp_adddistributor @distributor = @@SERVERNAME,
@password = 'DistributorPassword';
-- 创建分发数据库
EXEC sp_adddistributiondb @database = 'distribution',
@data_folder = 'C:\SQLData',
@log_folder = 'C:\SQLLog',
@log_file_size = 2,
@min_distretention = 0,
@max_distretention = 72,
@history_retention = 48,
@deletebatchsize_xact = 5000,
@deletebatchsize_cmd = 2000,
@security_mode = 1;
-- 启用发布数据库
USE master;
EXEC sp_replicationdboption @dbname = N'AdventureWorks2019',
@optname = N'publish',
@value = N'true';步骤 2：创建发布
在 SSMS 中创建发布：
- 展开 "复制" → "本地发布"
- 右键点击选择 "新建发布"
- 选择发布数据库
- 选择发布类型："事务发布"
- 选择要发布的表、视图和存储过程
- 配置筛选器（可选）
- 配置快照代理调度
- 配置代理安全性
- 完成发布创建
使用 T-SQL 创建发布：
sql
-- 创建日志读取器代理作业
EXEC sp_addlogreader_agent @publisher = @@SERVERNAME,
@publisher_db = N'AdventureWorks2019',
@distributor = @@SERVERNAME,
@job_login = null,
@job_password = null,
@publisher_security_mode = 1;
-- 创建发布
EXEC sp_addpublication @publication = N'TransactionalPublication',
@description = N'事务发布',
@sync_method = N'concurrent_c',
@retention = 0,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@repl_freq = N'continuous',
@status = N'active',
@independent_agent = N'true',
@immediate_sync = N'false',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false',
@allow_queued_tran = N'false',
@allow_dts = N'false',
@replicate_ddl = 1,
@allow_initialize_from_backup = N'false',
@enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false';
-- 添加文章（表）到发布
EXEC sp_addarticle @publication = N'TransactionalPublication',
@article = N'SalesOrderHeader',
@source_owner = N'Sales',
@source_object = N'SalesOrderHeader',
@type = N'logbased',
@description = null,
@creation_script = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509D,
@identityrangemanagementoption = N'manual',
@destination_table = N'SalesOrderHeader',
@destination_owner = N'Sales',
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_SalesSalesOrderHeader',
@del_cmd = N'CALL sp_MSdel_SalesSalesOrderHeader',
@upd_cmd = N'SCALL sp_MSupd_SalesSalesOrderHeader';
-- 添加发布快照
EXEC sp_addpublication_snapshot @publication = N'TransactionalPublication',
@frequency_type = 1,
@frequency_interval = 1,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@job_login = null,
@job_password = null,
@publisher_security_mode = 1;步骤 3：创建订阅
在 SSMS 中创建订阅：
- 展开 "复制" → "本地发布"
- 右键点击发布，选择 "新建订阅"
- 选择分发代理位置（推送或请求订阅）
- 选择订阅服务器和数据库
- 配置分发代理安全性
- 配置同步计划
- 完成订阅创建
使用 T-SQL 创建订阅：
sql
-- 创建推送订阅
EXEC sp_addsubscription @publication = N'TransactionalPublication',
@subscriber = @@SERVERNAME,
@destination_db = N'AdventureWorks2019_Sub',
@subscription_type = N'Push',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0;
-- 添加推送订阅代理
EXEC sp_addpushsubscription_agent @publication = N'TransactionalPublication',
@subscriber = @@SERVERNAME,
@subscriber_db = N'AdventureWorks2019_Sub',
@job_login = null,
@job_password = null,
@subscriber_security_mode = 1,
@frequency_type = 64,
@frequency_interval = 1,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@dts_package_location = N'Distributor';事务复制监控与管理
复制监控
SQL Server Management Studio：
- 展开 "复制" → "复制监视器"
- 查看发布服务器和订阅服务器状态
- 监控复制代理运行情况
- 查看复制延迟和错误信息
动态管理视图 (DMVs)：
sql
-- 查看复制代理状态
SELECT
name,
status,
status_text,
last_sync_time,
next_sync_time
FROM sys.dm_repl_agents;
-- 查看复制延迟
SELECT
p.publisher_db,
p.publication,
s.subscriber_db,
s.subscriber,
DATEDIFF(second, h.run_date, GETDATE()) AS latency_seconds
FROM sys.dm_repl_heartbeats h
JOIN sys.publications p ON h.publication_id = p.publication_id
JOIN sys.subscriptions s ON h.publication_id = s.publication_id;
-- 查看复制错误
SELECT
agent_type,
agent_name,
error_id,
error_time,
error_code,
error_text
FROM msrepl_errors
ORDER BY error_time DESC;- 性能计数器：
SQLServer:Replication AgentsSQLServer:Replication Dist.|Logreader|Snapshot
复制管理任务
重新初始化订阅：
- 右键点击订阅，选择 "重新初始化订阅"
- 可以选择重新生成快照
暂停/启动复制代理：
- 在复制监视器中管理代理状态
- 使用 SQL Server Agent 管理复制作业
添加/删除文章：
- 右键点击发布，选择 "属性"
- 在 "文章" 页面添加或删除要复制的对象
修改复制配置：
- 修改发布属性
- 修改订阅属性
- 修改代理调度
事务复制优化
性能优化
优化快照生成：
- 使用并行快照代理
- 压缩快照
- 选择合适的快照存储位置
- 避免在高峰时段生成快照
优化日志读取器代理：
- 增加日志读取器代理的读取批次大小
- 确保分发数据库有足够的空间
- 定期清理分发数据库
优化分发代理：
- 增加分发代理的批处理大小
- 使用并行分发代理
- 减少订阅服务器数量
- 优化网络连接
优化订阅服务器：
- 确保订阅服务器有足够的硬件资源
- 优化订阅数据库的索引
- 考虑使用只读订阅减少锁定
高可用性优化
分发服务器高可用：
- 使用故障转移集群保护分发服务器
- 定期备份分发数据库
发布服务器高可用：
- 结合使用 Always On 可用性组
- 确保发布数据库在故障转移后仍能正常复制
订阅服务器高可用：
- 使用故障转移集群保护订阅服务器
- 考虑使用多个订阅服务器实现冗余
事务复制最佳实践
合理设计发布：
- 只复制必要的表和列
- 使用水平和垂直分区减少复制数据量
- 避免复制频繁更改的大表
配置合适的代理账户：
- 使用具有最小权限的账户
- 确保账户具有正确的共享权限
- 定期更换密码
监控复制延迟：
- 设置复制延迟告警
- 定期检查复制状态
- 及时处理复制错误
备份策略：
- 定期备份发布数据库、分发数据库和订阅数据库
- 确保备份链完整
- 测试备份恢复
定期维护：
- 定期清理分发数据库
- 重新生成快照（如有必要）
- 更新统计信息
安全配置：
- 启用复制代理加密
- 限制复制端口访问
- 审计复制活动
版本差异
| 版本 | 事务复制特性变化 |
|---|---|
| SQL Server 2012 | - 增强的复制监控 |
- 支持 Always On 可用性组集成 | | SQL Server 2014 | - 内存中 OLTP 支持事务复制
- 增强的性能 | | SQL Server 2016 | - 支持 JSON 数据类型复制
- 增强的安全性 | | SQL Server 2017 | - Linux 支持事务复制
- 增强的代理管理 | | SQL Server 2019 | - 支持大数据群集
- 增强的可用性 | | SQL Server 2022 | - 增强的性能监控
- 智能查询处理支持 |
常见问题 (FAQ)
Q1: 事务复制和 Always On 可用性组有什么区别？
A: 事务复制是基于事务日志的数据同步技术，适用于读写分离和数据分发，而 Always On 可用性组是数据库级别的高可用解决方案，适用于关键业务系统的高可用性和灾难恢复。事务复制支持多个订阅服务器，而 Always On 可用性组支持多个辅助副本。
Q2: 如何解决事务复制延迟问题？
A: 解决事务复制延迟问题的方法包括：
- 优化快照生成
- 增加代理批处理大小
- 使用并行代理
- 优化网络连接
- 减少复制数据量
- 确保服务器有足够的硬件资源
Q3: 事务复制可以复制存储过程和触发器吗？
A: 是的，事务复制可以复制存储过程和触发器。可以在发布属性的 "文章" 页面中添加存储过程和触发器。
Q4: 如何处理事务复制中的冲突？
A: 对于只读订阅，不会发生冲突。对于可更新订阅，可以使用队列读取器代理处理冲突，或配置冲突解决策略。
Q5: 事务复制支持跨版本复制吗？
A: 是的，事务复制支持跨版本复制，但需要注意版本兼容性。一般来说，订阅服务器的版本可以高于或等于发布服务器的版本，但不建议订阅服务器版本低于发布服务器版本。
Q6: 如何迁移事务复制到新服务器？
A: 迁移事务复制到新服务器的步骤包括：
- 在新服务器上配置分发
- 重新创建发布
- 重新创建订阅
- 重新初始化订阅
- 验证复制状态
Q7: 事务复制可以用于跨平台复制吗？
A: 是的，SQL Server 2017 及以上版本支持 Linux 订阅服务器，也支持与其他数据库系统的复制（通过异构订阅）。
Q8: 如何监控事务复制的延迟？
A: 可以使用复制监视器、DMVs 或性能计数器监控事务复制的延迟。建议设置延迟告警，当延迟超过阈值时及时通知管理员。
Q9: 事务复制中的快照有什么作用？
A: 快照用于初始化订阅服务器，包含发布数据库的初始数据和架构。当添加新订阅或重新初始化订阅时，需要生成快照。
Q10: 如何暂停和恢复事务复制？
A: 可以在复制监视器中暂停和恢复复制代理，或使用 SQL Server Agent 管理复制作业。也可以使用 T-SQL 命令暂停和恢复复制。
总结
Transactional Replication 是 SQL Server 中强大的数据同步技术，适用于读写分离、数据分发和报表场景。通过合理配置和优化，可以实现高效、可靠的数据同步。
建议 DBA 在使用事务复制时，注意以下几点：
- 合理设计发布，只复制必要的数据
- 配置合适的代理账户和权限
- 定期监控复制状态和延迟
- 制定完善的备份和恢复策略
- 定期进行复制维护
- 考虑结合使用其他高可用技术，如 Always On 可用性组
通过遵循最佳实践和持续优化，可以确保事务复制的稳定运行，满足业务的数据同步需求。