SQL Server 2000数据库同步：发布订阅配置与排错指南

简介这份资源聚焦SQLServer 2000环境下两个数据库之间的同步问题面向需要维护多环境数据库一致性的开发与运维人员。内容围绕复制技术展开涵盖事务复制、合并复制与快照复制三种模式并给出从Windows用户权限、快照共享目录、SQL代理服务账户、身份验证模式到远程服务器注册、服务器别名配置的完整准备流程同时涉及发布与分发服务器搭建、发布订阅建立及复制监视器监控等关键环节适合具备一定SQLServer基础、需要落地数据库同步方案的读者参考。资源包共1个PDF文件约114KB内容以图文步骤与配置说明为主便于按章节查阅。目前已有855人学习下载可帮助读者理解同步原理、梳理配置顺序并规避手动改库导致的结构与数据不一致问题。1. SQL Server 2000 数据库同步两台老机器之间的数据怎么对齐手上还有 SQL Server 2000 的多半不是怀旧而是被业务绑住了。ERP、MES、老一代进销存甚至某台工控机上的报表库跑在 Windows 2000 或 Windows Server 2003 上动不了。现在的问题是总部一台、车间一台或者主库一台、备份查询库一台两边的数据要一致。SQL Server 2000 数据库同步同步两个 SQL Server 数据库的内容这件事放到今天依然有解而且不用装第三方数据库同步软件。这篇文章讲的是 SQL Server 2000 自带的复制功能也就是发布订阅。它能在两台 SQL Server 2000 实例之间把一张表或一批表的数据变更持续搬过去。适合两类人一类是维护老系统的运维另一类是被要求做数据备份或异地查询库的开发。前提是两台机器网络能通、SQL Server 代理服务能起来、账号权限给够。下面从原理到配置一步步来中间会讲清楚哪些参数必须改、哪些坑我踩过。2. 发布订阅在 SQL Server 2000 里到底怎么跑起来SQL Server 2000 的复制不是靠触发器硬写也不是靠定时全量导。它是一套基于代理的异步机制核心角色有三个发布服务器、分发服务器、订阅服务器。发布服务器是数据源头订阅服务器是数据目的地分发服务器负责在中间存变更、推变更。小规模场景里分发服务器可以和发布服务器合并到同一台机器减少一台服务器的依赖。2.1 三种复制类型怎么选快照、事务、合并SQL Server 2000 支持三种复制类型选错了后面全是麻烦。快照复制每次同步都把整张表的数据重新写一遍。适合数据量小、变更不频繁、能接受延迟的场景。比如一张配置表一天改几次用快照最省心。缺点是数据量一大每次生成快照和传输都慢网络和磁盘压力明显。事务复制先做一次快照把初始数据铺过去之后只传增量的事务日志变更。适合数据量大、要求准实时、订阅端只读或少量写的场景。比如主库写入查询库只读事务复制是首选。SQL Server 2000 的事务复制依赖分发库里的变更表代理按批次推。合并复制允许发布端和订阅端都改数据之后按规则合并。适合多站点各自录入、偶尔联网同步的场景。但冲突解决复杂SQL Server 2000 的合并复制对 schema 变更支持很差改个列类型都可能要重建发布。老系统里我不太推荐合并复制除非业务确实需要双向写。选型建议单向同步用事务复制数据量小用快照复制双向写才考虑合并复制。多数“同步两个 SQL Server 数据库的内容”的需求事务复制就够了。2.2 配置前必须确认的四个前置条件在动手点向导之前先把这四件事查清楚否则后面报错会浪费很多时间。第一SQL Server 2000 有没有打够补丁。复制功能对版本和补丁敏感SP3 和 SP4 的行为有差异。常见做法是两台机器都打到 SP4减少兼容性问题。第二SQL Server 代理服务能不能正常启动。复制依赖代理作业代理起不来发布订阅就是摆设。检查服务账号是否有网络访问权限本地系统账号在跨机器访问时经常不够。第三网络和名称解析。两台机器要能互相 ping 通最好用 IP 或能解析的计算机名。SQL Server 2000 时代 NetBIOS 名称解析问题很多如果计算机名 ping 不通直接配 hosts 文件。第四账号权限。发布服务器和订阅服务器上都要有足够的权限。常见做法是建一个域账号或者两台机器上用户名密码完全相同的本地管理员账号。SQL Server 代理用这个账号登录复制代理才能跨机器访问。提示如果两台机器不在域里用相同用户名和密码的本地账号是最省事的做法。改密码时两边要同时改否则复制会断。2.3 用向导建发布从右键菜单到第一个快照SQL Server 2000 的企业管理器里有复制向导。下面按事务复制走一遍。在发布服务器上打开企业管理器找到要发布的数据库右键 → 新建 → 发布。向导会先让你选分发服务器。如果分发和发布在同一台机器选“使自己的分发服务器”然后指定快照文件夹。这个文件夹要能被订阅服务器通过网络访问通常设成共享目录。接着选发布类型选“事务发布”。然后选要发布的表。SQL Server 2000 的事务复制要求表有主键没有主键的表不能做事务复制。如果老表没主键要么加主键要么改用快照复制。选完表后向导会让你设置快照代理计划。默认是立即运行也可以设成定时。第一次快照会把表结构和数据写到快照文件夹订阅服务器初始化时从这里拉。最后给发布起个名字完成向导。完成后在企业管理器的复制监视器里能看到发布和快照代理状态。2.4 建订阅拉模式和推模式的区别订阅分两种拉订阅和推订阅。拉订阅是订阅服务器主动去发布服务器拉数据推订阅是发布服务器把数据推到订阅服务器。拉订阅适合订阅服务器数量多、分发服务器压力大的场景因为每个订阅自己管自己的代理。推订阅适合订阅服务器少、要求集中管理的场景代理在分发服务器上跑。在 SQL Server 2000 里建订阅可以在发布服务器上右键发布 → 新建订阅也可以在订阅服务器上右键订阅 → 新建订阅。向导会问订阅服务器和订阅数据库。订阅数据库可以是已有库也可以是新建库。如果选已有库要确保里面没有同名表冲突。初始化订阅时选“立即初始化”会让快照代理生成快照然后分发代理把快照应用到订阅库。如果数据量很大第一次初始化可能跑很久建议放在业务低峰期。2.5 验证同步是否生效三张表和三句查询配置完成后怎么确认数据真的同步了我一般查三个地方。第一在发布服务器上查分发库。分发库默认叫 distribution里面有 MSrepl_commands 和 MSrepl_transactions 表能看到待分发和已分发的命令数。如果 MSrepl_commands 里堆积很多说明分发代理没跑或跑得慢。第二在订阅服务器上查订阅库的表和发布库对应表做行数和关键字段比对。简单场景可以直接 count(*)但要注意事务复制有延迟刚插入的数据不一定马上到。第三看复制监视器里的代理状态。快照代理、日志读取代理、分发代理每个都有状态和历史记录。代理报错时历史记录里会有具体错误信息比盲目查表快。-- 在发布服务器上查分发库待处理命令数 USE distribution SELECT COUNT(*) AS pending_commands FROM MSrepl_commands -- 在订阅服务器上查订阅表行数 USE 订阅库名 SELECT COUNT(*) FROM 表名 -- 在发布服务器上查发布库对应表行数做比对 USE 发布库名 SELECT COUNT(*) FROM 表名第一句查分发库积压情况pending_commands 持续增长说明分发代理有问题。第二句和第三句做行数比对但行数一致不代表内容一致关键业务表还要比对更新时间或校验和。SQL Server 2000 没有内置的 checksum 比对函数常见做法是抽几条关键记录人工核对或者写脚本按主键逐条比对。3. 同步两个 SQL Server 数据库时最容易翻车的五个地方复制配起来不难难的是让它稳定跑下去。下面五个问题我在不同项目里都遇到过按现象、原因、解决来说。3.1 快照代理报错无法访问快照文件夹现象快照代理启动后立刻失败历史记录里写“无法访问快照文件夹”或“拒绝访问”。原因快照文件夹的共享权限和 NTFS 权限没给够。SQL Server 代理账号需要对这个文件夹有读写权限订阅服务器上的分发代理也需要能通过网络读到这个共享。解决把快照文件夹设成共享共享权限给 Everyone 完全控制内网环境可以接受NTFS 权限给 SQL Server 代理账号和订阅服务器访问账号读写权限。改完后重启快照代理。3.2 分发代理报错登录失败或账号无权访问现象分发代理连不上订阅服务器报“登录失败”或“用户没有权限”。原因分发代理在分发服务器上跑用的是 SQL Server 代理账号。这个账号在订阅服务器上可能不存在或者密码不一致。解决在订阅服务器上建同名同密码的账号给足权限。或者在复制配置里显式指定连接订阅服务器用的账号。SQL Server 2000 的复制代理账号配置在发布属性里可以改。3.3 订阅库表结构被改导致同步中断现象同步跑了一段时间后突然报错说列名无效或类型不匹配。原因有人在订阅库上直接改了表结构比如加列、改列类型、删列。事务复制按发布端的 schema 推变更订阅端 schema 不一致就会失败。解决订阅库的表结构不要手动改。需要改 schema 时先在发布端改然后重新初始化订阅或重建发布。SQL Server 2000 对 schema 变更的支持很弱改列类型基本要重建。3.4 数据量大了之后分发库膨胀现象分发库的 .mdf 和 .ldf 文件越来越大磁盘快满了。原因分发库保留了大量历史事务记录默认保留时间可能很长。事务复制在分发库里的 MSrepl_commands 表会持续增长。解决调整分发库的保留期。在分发服务器属性里把“事务保留”时间改短比如从默认的 72 小时改成 24 小时。然后跑分发清理作业把旧记录清掉。清理作业是 SQL Server 2000 复制安装时自动创建的在 SQL Server 代理的作业列表里能找到。3.5 同步延迟越来越大业务查询读到旧数据现象发布端已经写入的数据订阅端几分钟甚至几十分钟后才出现。原因分发代理的轮询间隔太长或者分发代理一次处理的命令数太少或者网络带宽不够。解决调分发代理的轮询间隔。在分发代理属性里把“连续运行”设成更短的间隔比如 10 秒或 30 秒。另外可以调分发代理的批次大小让一次处理更多命令。如果网络是瓶颈考虑压缩快照或减少发布表的数量。注意调轮询间隔会增加分发服务器和订阅服务器的负载间隔太短可能让老机器 CPU 跑满。SQL Server 2000 时代的硬件本来就弱建议先观察再调。4. 不重建发布的前提下怎么把同步做稳复制配好只是开始后面要让它长期稳定。这一章讲几个进阶做法都是我在老系统上验证过的。4.1 用 T-SQL 脚本管理复制而不是只靠向导企业管理器向导适合第一次配置但后面要批量操作或重建时脚本更可靠。SQL Server 2000 提供了一组复制存储过程可以在查询分析器里直接调。比如创建一个事务发布-- 在发布服务器上执行创建事务发布 USE 发布库名 EXEC sp_addpublication publication Pub_Order, status active, repl_freq continuous, sync_method concurrent -- 添加要发布的表 EXEC sp_addarticle publication Pub_Order, article Orders, source_object Orders, type logbased, schema_option 0x00000000000000F3 -- 添加订阅 EXEC sp_addsubscription publication Pub_Order, subscriber 订阅服务器名, destination_db 订阅库名, subscription_type push, sync_type automaticsp_addpublication 的 repl_freq 设成 continuous 表示事务复制持续运行sync_method 设成 concurrent 表示快照生成时不锁表。sp_addarticle 的 type 设成 logbased 表示基于日志的事务复制schema_option 控制哪些对象属性被复制0xF3 是常用组合包含主键、默认值、检查约束等。sp_addsubscription 的 subscription_type 设成 push 是推订阅sync_type 设成 automatic 表示自动初始化。用脚本的好处是配置可以版本化重建时直接跑脚本不用再点一遍向导。老系统迁移或灾备切换时这套脚本能省很多时间。4.2 监控复制状态的三个关键查询复制跑起来后不能只看企业管理器的绿灯。我习惯用几个查询定期检查。-- 查分发库中未分发的命令数 USE distribution SELECT COUNT(*) AS undelivered FROM MSrepl_commands WHERE command_id NOT IN ( SELECT command_id FROM MSdistribution_history ) -- 查分发代理最近一次运行状态 USE distribution SELECT agent_id, status, comments, time FROM MSdistribution_history WHERE time DATEADD(hour, -1, GETDATE()) ORDER BY time DESC -- 查订阅端和发布端的行数差异示例表 USE 订阅库名 SELECT COUNT(*) AS sub_rows FROM Orders第一句查还有多少命令没分发出去数字持续大于零说明分发跟不上。第二句查分发代理最近一小时的历史comments 字段会写具体错误。第三句做行数比对但要注意事务复制有延迟刚写入的数据可能还没到订阅端。这三个查询可以做成定时作业每小时跑一次结果写到监控表里。发现异常时再人工介入比等业务报障主动。4.3 订阅端只读场景下的索引和查询优化如果订阅端是查询库读多写少可以在订阅端加索引。事务复制默认会把发布端的索引也复制过去但发布端的索引是为写入优化的查询库可能需要不同的索引。常见做法是在订阅端额外建覆盖索引针对报表查询的 where 和 join 字段。但要注意手动加的索引在重新初始化订阅时可能被覆盖。SQL Server 2000 的复制在初始化时会根据 schema_option 决定是否复制索引如果不想被覆盖可以在订阅端初始化完成后再加索引并且记录在案重建时重新执行。另外订阅端的查询要避免长时间锁表。SQL Server 2000 默认隔离级别是 read committed报表查询可能阻塞分发代理写入。可以在查询里加 with (nolock)但要注意脏读。老系统上这个取舍很常见业务能接受轻微脏读就用 nolock不能接受就调查询时间避开分发高峰。4.4 同步中断后的恢复重新初始化还是手动补数据复制中断后先判断中断了多久、积压了多少命令。如果分发库里积压的命令不多修好代理后一般能自动追平。如果积压太多或订阅端数据已经不一致就要重新初始化。重新初始化的步骤在发布服务器上右键发布 → 重新初始化订阅。这会生成新快照分发代理会把快照应用到订阅端。注意重新初始化会覆盖订阅端的表数据如果订阅端有额外写入会丢失。如果不想全量重新初始化可以手动补数据。比如订阅端缺了某几天的数据可以从发布端导出这几天的数据在订阅端插入。但手动补数据要小心主键冲突和事务一致性补完后要核对行数和关键字段。我一般优先重新初始化因为手动补数据容易漏。重新初始化虽然慢但结果可靠。如果数据量太大重新初始化时间不可接受才考虑手动补。4.5 老系统上复制和备份怎么共存SQL Server 2000 的复制和备份可以共存但要注意顺序。备份发布库时复制的事务日志读取代理也在读日志两者不冲突。但备份分发库时最好在分发代理空闲时做避免备份过程中分发库写入导致备份不一致。常见做法是发布库每天全备一次分发库每天全备一次订阅库按业务需要备份。备份文件保留周期根据磁盘空间定。老系统磁盘通常不大分发库的备份可以只保留最近几天。另外如果发布库做了日志备份事务日志截断可能会影响复制。SQL Server 2000 的事务复制依赖日志读取代理读日志如果日志被截断太快日志读取代理可能来不及读。常见做法是日志备份间隔不要太短或者监控日志读取代理的延迟发现延迟大就调大日志备份间隔。5. 一个老 DBA 的复制检查清单上面讲了配置、排错和进阶做法。最后分享一个我用了很多年的检查清单每次配完复制或接手一套老复制时按这个清单过一遍能提前发现大部分问题。检查项检查方法合格标准SQL Server 代理服务服务管理器查看两台机器都正常运行快照文件夹共享在订阅服务器上访问共享能读写分发库空间查 distribution 库文件大小剩余空间大于 30%分发代理状态复制监视器查看无报错延迟在可接受范围订阅端行数比对关键表 count(*) 比对差异在延迟允许范围内复制作业计划SQL Server 代理作业列表清理作业和刷新作业正常账号密码一致性两台机器账号比对代理账号密码一致日志空间查发布库和分发库日志日志未被填满这个清单不复杂但每次过一遍能省很多事后救火的时间。老系统的复制不像新版本有那么多自动化监控很多问题要靠人定期看。我自己的习惯是每周一早上花十分钟把清单里的关键项查一遍尤其是分发库空间和分发代理状态。有几次就是靠这个习惯提前发现分发库快满了赶在业务报障前清理掉。SQL Server 2000 的复制虽然老但只要按规矩配、定期看跑几年不出问题是可以做到的。希望帮到你。本文还有配套的精品资源点击获取