SQL Server 2000 数据库同步实战：发布订阅、链接服务器与 bcp 全解析

简介这份PDF资料聚焦SQL Server 2000环境下两个数据库之间的同步问题面向需要维护多环境开发与部署一致性的数据库管理员和开发人员。内容围绕复制技术展开涵盖事务复制、合并复制与快照复制三种模式并详细梳理了同步前的准备工作包括创建同名Windows用户、配置快照共享目录、调整SQLSERVERAGENT服务启动账户、切换混合身份验证模式、互相注册远程服务器以及配置服务器别名等关键环节。资源包共1个PDF文件大小约114KB篇幅紧凑适合作为操作手册随时查阅。目前已有855人学习下载读者可从中获取发布与分发服务器的完整配置流程、新建发布与订阅的具体步骤、复制监视器的使用方法以及发布表数据筛选、订阅到期设置等实用技巧同时了解简单恢复模式下事务不丢失的实测经验帮助排查同步过程中的权限与连接问题。1. SQL Server 2000 数据库同步老库还在跑数据怎么对上生产线上还有一台 Windows Server 2003 的机器上面跑着 SQL Server 2000业务不能停但报表库、备份库、新系统都要它的数据。这种场景下数据库同步不是要不要做的问题而是怎么在不碰原库的前提下把数据搬出去。SQL Server 2000 数据库同步本质是让两个 SQL Server 实例之间的表数据保持一致——可以是同一版本之间也可以是 2000 往 2005/2008 甚至更高版本单向推送。它解决的是老系统数据孤岛问题适合还在维护遗留系统的 DBA、需要做数据迁移或实时备份的运维人员。下面按先选路子、再动手、最后避坑的顺序把发布订阅、链接服务器、bcp 导出导入这几条常见路径讲透参数和命令都能直接抄。2. 先选同步方式发布订阅、链接服务器还是 bcpSQL Server 2000 自带的同步手段不多但够用。选错方式后面全是返工。这一章先把三条主流路径的适用边界讲清楚再给出各自的落地步骤。2.1 发布订阅适合持续增量同步发布订阅Replication是 SQL Server 2000 里最正规的同步方案。它的原理是发布服务器把事务日志里的变更抓出来交给分发服务器再由分发服务器推给订阅服务器。整个过程对应用透明不需要改业务代码。SQL Server 2000 支持三种复制类型复制类型适用场景延迟对原库压力快照复制数据量小、变更不频繁高全量刷新低事务复制要求准实时、增量同步低秒级中合并复制双向同步、离线场景中高大多数同步两个库内容的需求事务复制就够了。配置入口在企业管理器里选中数据库 → 右键 → 所有任务 → 配置发布、订阅服务器和分发。注意SQL Server 2000 的复制代理是以 Windows 服务形式跑的服务账户必须有对快照共享目录的读写权限否则快照生成会失败。配置事务复制的核心步骤在分发服务器上新建分发数据库默认distribution把当前实例配置为发布服务器新建发布选事务发布勾选要同步的表新建订阅选推送订阅或请求订阅启动快照代理和分发代理推送订阅由分发服务器主动推适合订阅端网络稳定请求订阅由订阅端主动拉适合订阅端在防火墙后面。SQL Server 2000 时代没有现在这么完善的 NAT 穿透请求订阅更省心。2.2 链接服务器 触发器适合少量表、可控场景如果只需要同步几张表又不想动复制那套复杂配置链接服务器加触发器是轻量方案。原理是在原库表上建AFTER INSERT/UPDATE/DELETE触发器触发器里通过链接服务器往目标库写数据。先建链接服务器-- 在目标库所在实例上执行指向源库 EXEC sp_addlinkedserver server LNK_SOURCE, -- 链接服务器别名 srvproduct SQLServer, provider SQLOLEDB, datasrc 192.168.1.10 -- 源库 IP 或实例名 EXEC sp_addlinkedsrvlogin rmtsrvname LNK_SOURCE, useself FALSE, rmtuser sa, -- 源库登录名 rmtpassword yourpwd -- 源库密码参数说明datasrc填源实例的 IP 或机器名\实例名rmtuser建议用专用账号别直接用 sa。建好后用SELECT * FROM LNK_SOURCE.源库.dbo.源表测试连通性。然后在源表上建触发器CREATE TRIGGER trg_sync_orders ON orders AFTER INSERT, UPDATE, DELETE AS BEGIN -- 先删目标库对应行再插新行模拟 upsert DELETE FROM LNK_TARGET.目标库.dbo.orders WHERE order_id IN (SELECT order_id FROM deleted) INSERT INTO LNK_TARGET.目标库.dbo.orders SELECT * FROM inserted END逻辑说明deleted表存旧值inserted表存新值。先按deleted里的主键删目标库再把inserted插进去这样 insert、update、delete 三种操作都能覆盖。注意 SQL Server 2000 的触发器里不能直接用MERGE只能这么写。这个方案的血泪经验是触发器里跨服务器操作会拉长原库事务时间高并发下容易锁表。表数据量超过几十万行就别用了。2.3 bcp 导出导入适合一次性全量迁移如果只是把 2000 的数据整体搬到另一个库bcp 命令行工具最直接。它不走网络协议栈直接读写文件速度快。导出bcp 源库.dbo.orders out orders.dat -c -t | -r \n -S 源实例 -U sa -P yourpwd导入bcp 目标库.dbo.orders in orders.dat -c -t | -r \n -S 目标实例 -U sa -P yourpwd参数说明-c表示字符模式所有列按字符处理-t |指定列分隔符选|是因为业务数据里不太可能出现-r \n指定行分隔符。如果表里有text或image字段得用-n原生模式否则大字段会截断。bcp 的坑在于它不校验约束导入时如果目标表有外键或唯一索引冲突会直接报错中断。建议导入前先禁用约束导完再启用。提示SQL Server 2000 的 bcp 版本和服务器版本要匹配用 2005 的 bcp 连 2000 服务器可能报协议错误。3. 事务复制落地从配置分发到验证数据一致事务复制是 SQL Server 2000 同步里最值得投入的方案配好之后基本不用管。这一章把配置细节和验证方法讲全。3.1 配置分发服务器和发布在企业管理器里操作虽然直观但很多参数藏在向导里出问题不好排查。建议用存储过程配每一步都可控。第一步配置分发-- 在分发服务器上执行 EXEC sp_adddistributor distributor SERVERNAME EXEC sp_adddistributiondb database distribution, data_folder C:\Program Files\Microsoft SQL Server\MSSQL\Data, log_folder C:\Program Files\Microsoft SQL Server\MSSQL\Data, max_distretention 72, -- 分发库保留 72 小时 history_retention 48 -- 历史记录保留 48 小时max_distretention是分发库中事务保留的最长时间单位小时。设太小订阅端断线久了就追不上设太大分发库膨胀快。72 小时是常见折中值。第二步启用发布数据库EXEC sp_replicationdboption dbname 源库, optname publish, value true第三步创建发布EXEC sp_addpublication publication pub_orders, status active, sync_method concurrent, -- 快照生成时不锁表 repl_freq continuous, -- 持续同步 description 订单表同步 EXEC sp_addpublication_snapshot publication pub_orders, frequency_type 1, -- 每天 frequency_interval 1sync_method concurrent是关键参数。默认的native模式生成快照时会锁表业务高峰期直接卡死。concurrent用并发快照代价是生成慢一点但不停业务。第四步添加要同步的文章表EXEC sp_addarticle publication pub_orders, article orders, source_object orders, type logbased, -- 事务复制必须用 logbased schema_option 0x000000000803509Fschema_option控制同步哪些对象属性0x000000000803509F是常用组合同步主键、索引、默认值、检查约束。具体位含义查 SQL Server 2000 联机丛书别乱改。3.2 创建订阅并启动代理订阅分推送和请求两种。推送订阅在分发服务器上建EXEC sp_addsubscription publication pub_orders, subscriber 目标实例, destination_db 目标库, subscription_type push, sync_type automatic -- 自动同步初始快照sync_type automatic表示订阅创建后自动应用快照。如果目标库已经有数据用initialize with backup或replication support only避免重复插入。建完订阅分发代理会自动跑。用复制监视器看状态或者查分发库SELECT status, comments, time FROM distribution.dbo.MSdistribution_history WHERE agent_id ( SELECT id FROM distribution.dbo.MSdistribution_agents WHERE name LIKE %pub_orders% ) ORDER BY time DESCstatus为 2 表示成功6 表示失败。失败时comments字段有具体原因最常见的是权限问题和网络中断。3.3 验证两个库数据一致复制跑起来不代表数据对。SQL Server 2000 自带tablediff工具但 2000 版本没有得从 2005 以上版本拷过来用或者自己写校验脚本。简单校验用行数和关键字段求和-- 源库执行 SELECT COUNT(*) AS cnt, SUM(amount) AS total FROM orders -- 目标库执行同样语句对比结果行数和求和都对不上时用EXCEPT找差异行SQL Server 2000 不支持EXCEPT得用NOT EXISTS-- 在目标库执行找出源库有但目标库没有的行 SELECT order_id FROM LNK_SOURCE.源库.dbo.orders s WHERE NOT EXISTS ( SELECT 1 FROM orders t WHERE t.order_id s.order_id )更彻底的办法是给每行算哈希。SQL Server 2000 没有HASHBYTES只能用CHECKSUMSELECT order_id, CHECKSUM(*) AS row_hash FROM orders两边都算一遍按主键关联比对row_hash。CHECKSUM有碰撞概率但用于发现差异足够了。注意校验时如果源库还在写入行数会一直变。建议在业务低峰期做或者先暂停复制代理再校验。4. 避坑与排查SQL Server 2000 同步的五个翻车现场SQL Server 2000 太老了很多在新版本上不是问题的地方在 2000 上就是坑。下面五条都是实际踩过的。4.1 快照共享目录访问被拒现象快照代理启动后立刻失败日志报无法访问快照文件夹。原因SQL Server 2000 的复制代理以 SQLServerAgent 服务账户运行默认是 LocalSystem。LocalSystem 访问网络共享时用的是机器账户目标机器不认。解决把 SQLServerAgent 服务账户改成域用户并给该用户对快照共享目录默认\\分发服务器\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData完全控制权限。改完重启代理服务。4.2 订阅端报该数据库不可以执行非日志模式的大容量复制现象用 bcp 导入时提示该数据库不可以执行非日志模式的大容量复制请联系数据库所有者(dbo)。原因目标库的恢复模式是完整恢复且没有开启select into/bulkcopy选项。bcp 的快速模式需要这个选项。解决在目标库执行EXEC sp_dboption 目标库, select into/bulkcopy, true导完再关掉。注意这个选项会让日志不记录 bcp 操作导完必须做一次完整备份否则日志链断了。4.3 复制延迟越来越大现象分发库MSdistribution_history里status一直是 2但订阅端数据越来越旧。原因分发代理的-PollingInterval默认 10 秒但-CommitBatchSize默认 100。如果单条事务很大代理处理不过来积压会越来越多。解决调大-CommitBatchSize到 1000同时把-PollingInterval降到 5 秒。在分发代理属性里改或者用sp_change_subscription_properties改。改完观察分发库的MSrepl_commands表行数应该逐渐下降。4.4 字符集不一致导致乱码现象源库是Chinese_PRC_CI_AS目标库是SQL_Latin1_General_CP1_CI_AS同步过去的中文变成问号。原因排序规则不同varchar字段在跨库传输时按目标库排序规则解释。解决目标库的排序规则必须和源库一致。如果目标库已经建好只能改列类型为nvarchar或者重建库。SQL Server 2000 不支持在线改排序规则重建是唯一办法。这也是为什么同步前一定要确认两边排序规则。4.5 触发器同步导致原库死锁现象用链接服务器加触发器方案业务高峰期原库频繁死锁应用超时。原因触发器里跨服务器操作是分布式事务SQL Server 2000 的分布式事务协调器MSDTC在高并发下性能很差而且会持有原库的锁直到远程操作完成。解决把同步改成异步。触发器里只往本地临时表插数据另起一个 SQL Agent 作业定时把临时表数据推到目标库。这样原库事务不阻塞代价是同步有延迟。如果业务不能接受延迟就换事务复制方案。5. 进阶技巧用日志读取和作业链做准实时同步事务复制虽然好但 SQL Server 2000 的复制对 DDL 变更支持很差——源表加个列复制直接报错。如果业务表结构经常变复制就不合适了。这时候可以用日志读取加作业链的方式自己控制同步逻辑。核心思路是用fn_dblog读事务日志解析出 insert/update/delete 操作写到中间表再用作业定时把中间表数据应用到目标库。SQL Server 2000 的fn_dblog是未公开函数但能用。-- 读取当前数据库的日志记录 SELECT [Current LSN], Operation, [Transaction ID], [AllocUnitName], [RowLog Contents 0] AS row_data FROM fn_dblog(NULL, NULL) WHERE Operation IN (LOP_INSERT_ROWS, LOP_DELETE_ROWS, LOP_MODIFY_ROW) AND AllocUnitName LIKE %orders%Operation字段标识操作类型AllocUnitName是表名加索引名RowLog Contents 0是行数据的二进制表示。解析这个二进制需要知道表结构比较麻烦但可以只取[Current LSN]和[Transaction ID]做变更追踪具体数据还是从原表查。更实用的做法是给源表加一个last_modified时间戳列用触发器维护同步作业只查last_modified 上次同步时间的行。这样不用碰日志兼容性好。-- 同步作业里执行 DECLARE last_sync DATETIME SELECT last_sync last_sync_time FROM sync_control WHERE table_name orders INSERT INTO LNK_TARGET.目标库.dbo.orders SELECT * FROM orders WHERE last_modified last_sync UPDATE sync_control SET last_sync_time GETDATE() WHERE table_name orders这个方案的关键是last_modified列必须有索引否则每次全表扫。另外GETDATE()在 SQL Server 2000 里精度是 3.33 毫秒同一毫秒内的多条变更可能漏掉建议用DBTS或者自增版本号代替时间戳。验证同步是否完整可以定期跑一次全量比对-- 比对行数和最大版本号 SELECT COUNT(*) AS cnt, MAX(version_no) AS max_ver FROM orders两边结果一致基本可以认为同步没丢数据。不一致时用NOT EXISTS找出缺失行手动补。我自己的习惯是任何同步方案上线前先跑一周的比对作业每天凌晨自动比对一次连续七天无差异才认为稳定。SQL Server 2000 的坑太多不这么做半夜被叫起来是常事。希望帮到你。本文还有配套的精品资源点击获取