SQL Server 2000数据库同步：复制与日志还原实战指南

简介SQL Server 2000数据库同步常因手动修改遗漏导致数据不一致。PDF文档围绕两套数据库内容保持一致整理了从复制前准备到发布订阅配置的完整流程适合DBA与开发者在多环境部署或分布式维护时参考。文档先说明同名Windows用户、共享目录、SQL代理账户及混合身份验证等前置设置再逐步讲解分发服务器建立、事务/合并复制类型选择、订阅服务器注册并补充IP访问时的别名配置和复制监视器使用可帮助按步骤操作减少遗漏。包体为1个PDF文件大小114KB属精简型操作指南便于打印或快速查阅。已有855人学习下载对初次配置SQL Server 2000复制或排查同步失败问题的读者具备较强的现场指导价值。1. SQLServer 2000数据库同步复制技术先搞清楚再动手我接过一个老系统的维护单开发库和生产库是两台SQLServer 2000两边表结构靠人肉同步结果某次上线漏改了一个字段月底对账直接对不上手工补数据补到怀疑人生。SQLServer 2000数据库同步这事最靠谱的解法不是定时导数据而是用自带的复制Replication技术让发布服务器把事务变化自动推给订阅服务器。复制分事务发布、合并发布、快照发布三种各有各的适用场景。这篇文章把我实际配置两个库同步的全过程、参数、踩坑都拆开讲适合还在维护老系统的DBA以及要把测试库和生产库保持一致的实施工程师。先说一个反直觉的结论这套东西配置过程里一半的精力花在Windows权限上而不是SQL本身的语句上。2. 同步前的六个准备项权限链路是复制命门复制的运行链路是这样的发布服务器上的SQL代理作业把表结构和数据生成到快照文件夹订阅服务器上的分发代理要去读这个共享目录还要连接分发服务器取元数据。整条链路全部走Windows身份验证和SQL Server身份验证任何一个环节的账户对不上复制就起不来。所以第一步不是建发布而是把系统层面的准备做完我按顺序列一下。2.1 同名Windows用户与快照共享目录先解释为什么要求同名同密码。SQL代理写快照文件时用的是服务启动账户的Windows凭据去访问共享目录订阅端的分发代理读快照时也一样。如果两台机器上没有一个用户名、密码完全一致的账户对端机器根本验证不过去会直接报拒绝访问。操作位置在发布服务器和订阅服务器的控制面板→管理工具→计算机管理→用户和组右键用户→新建用户建一个隶属于Administrators组的Windows账户两台机器上的用户名和密码必须完全一样。然后在发布服务器的D盘新建一个目录比如叫PUB右键属性→共享→共享该文件夹通过权限按钮把这个用户的权限设为完全控制。这里有个我踩过的坑共享权限给了还不够NTFS权限也要给两个权限按取交集生效只设共享权限不设NTFS权限照样拒绝访问。快照目录不建议放在系统盘C盘快照文件可能很大系统盘被撑满会导致整个复制挂掉。2.2 SQL代理服务的启动账户打开控制面板→管理工具→服务找到SQLSERVERAGENT服务右键属性→登录→选择此账户输入第一步创建的Windows用户名和密码。发布服务器和订阅服务器都要做这个设置只做一边必然出问题。原因很简单服务默认用本地系统账户启动跨机器访问共享目录时系统会用机器名$这个账户身份出去目标机器上根本不存在这个账户验证必然失败。顺带提一句做网络备份或后面第6章那种日志还原备库方案时MSSQLSERVER服务本身也需要改成同一个账户只改SQLSERVERAGENT是不够的。登录账户设置完后记得在服务里手动重启一下SQLSERVERAGENT让新凭据生效。2.3 混合身份验证与互相注册在发布和订阅两台服务器上打开企业管理器右键SQL实例→属性→安全性→身份验证选择SQL Server和Windows混合模式。为什么必须改复制代理之间建立连接时默认走SQL Server身份验证如果数据库只允许Windows身份验证代理连上来的时候没人给它传Windows凭据连接直接失败。然后两台服务器要互相注册。企业管理器→右键SQL Server组→新建SQL Server注册在可用服务器里输入对方的服务器名连接时选择SQL Server身份验证输入有权限的账号密码。这一步做完后订阅向导里才能通过查看已注册服务器所做的发布找到发布端。如果不用企业管理器这个界面直接用系统存储过程也能注册但老项目用图形界面更稳不容易漏参数。2.4 IP环境下的服务器别名如果网络环境里只能通过IP访问对方计算机名解析不了需要在连接端配置别名。路径是开始→程序→Microsoft SQL Server→客户端网络实用工具→别名→添加。网络库选tcp/ip服务器别名填SQL服务器名连接参数里的服务器名称填对方IP地址。注意如果修改过SQL Server端口一定要取消勾选动态决定端口手工填上实际端口号。这步经常会漏漏了的结果是订阅向导里始终找不到发布的服务器而且报错信息还很不明确第一次搞的时候我在这一步绕了将近两个小时。准备项操作位置关键参数不做的后果同名Windows账户两台机器控制面板→用户和组同名、同密码、隶属于Administrators快照目录访问被拒绝快照共享目录发布服务器D:\PUB共享权限NTFS权限均完全控制订阅端读不到快照SQLSERVERAGENT启动账户两台机器服务管理指定Windows用户而非本地系统账户跨机器写入快照失败混合身份验证SQL实例属性→安全性选择SQL Server和Windows代理连接被拒绝互相注册企业管理器→新建SQL Server注册SQL Server身份验证订阅向导找不到发布服务器别名客户端网络实用工具tcp/ipIP端口只能IP访问时查找发布失败3. 发布与分发先立分发服务器再挂发布订阅准备工作做完开始搭复制的核心骨架。分发服务器负责存复制元数据、历史记录以及调度快照和日志读取作业。第一次配置时可以让当前这台服务器既当发布服务器又当自己的分发服务器机器多的时候也可以一台专门做分发带多台发布服务器。3.1 配置发布和分发向导到底干了什么在企业管理器里展开复制右键选择配置发布、订阅服务器和分发进入向导。选分发服务器时选择使servername成为它自己的分发服务器SQL Server将创建分发数据库和日志快照文件夹按第2章准备的共享目录填入。自定义配置选择否使用默认配置直接完成。完成后当前实例里会多出一个distribution分发数据库同时多出一个distributor_admin用户这个用户是分发代理连接分发服务器的固定身份密码可以在企业管理器里改。服务器上新增加四个作业代理程序历史记录清除、分发清除、复制代理程序检查、重新初始化存在数据验证失败的订阅。这四个作业是复制跑起来后的日常维护任务不要随便停尤其是分发清除作业停了之后分发数据库的历史记录会无限膨胀。企业管理器里还会多出一个复制监视器发布、订阅、快照生成、日志读取器状态都能在里面看后面排故障基本都靠它。3.2 把其它服务器加入发布和订阅拓扑在发布服务器和分发服务器属性窗口里切换到发布服务器页点新增把网络上其它SQL Server实例加进来同时勾选允许发布的数据库类型事务或合并。再切到订阅服务器页点新增把订阅端实例加进去。新增发布服务器时到发布服务器的管理链接有个要输入密码的可选框默认勾选意思是建立到分发服务器的连接时需要输入distributor_admin的密码。测试环境里可以不勾省得每次都要输密码但生产环境不建议这么干分发库的元数据和作业调度权限是全局的裸连风险太大。3.3 第二台发布服务器如何指向已有分发服务器如果网络里已经有一台配置好分发服务的机器比如FENGYU/FENGYU新机器JIN001想当发布服务器就不需要再自己建分发。在JIN001上打开配置发布和分发向导选择分发服务器时选使用下列服务器从列表里选定FENGYU/FENGYU下一步会要求输入分发服务器distributor_admin用户的密码连续输入两次确认后面依然用默认配置完成。这个模式的优点是省资源但要注意所有复制历史记录都集中在一台分发库上分发表的数据量增长很快要确保分发服务器的磁盘够用并且那个分发清除作业的调度周期不要被人为拉长。JIN001接入FENGYU的分发服务后发布服务器上新增加一个[失效订阅清除]作业分发服务器上则会增加两条带名字的作业名字格式是发布服务器名-发布名-编号后面会说。4. 事务发布与请求订阅一条数据从发布端走到订阅端的完整链路分发服务器立起来之后就可以建发布和订阅了。先把三种发布类型搞清楚再走向导不然选错了类型后面返工很痛苦。4.1 先选发布类型事务、合并、快照发布类型同步机制适用场景硬性要求事务发布日志读取器读事务日志在订阅端重做增删改主从读分离、数据实时性高发布表必须有主键合并发布两端均可修改用rowguid标记冲突并合并多端离线写入、门店数据汇总会增加rowguid列快照发布定时把整个表结构、数据、索引生成快照覆盖数据量小、实时性要求低无特别要求我实际项目里用得最多的就是事务发布主库承担写入订阅库承担查询报表数据延迟在秒级。合并发布因为会增加字段和冲突处理逻辑老系统里改表结构牵一发动全身能不用就不用。快照发布适合配置表这类几百行的小数据定时刷新一次就够。4.2 新建发布向导带主键的表才能进事务发布在企业管理器复制→发布内容右键→新建发布。向导依次要过选择发布数据库、选择发布类型、指定订阅服务器类型选运行SQL Server 2000的服务器、指定项目。注意向导页有一句提示在事务发布中只可以发布带主键的表。这句话是硬限制事务复制靠主键在订阅端定位要更新的行没主键的表连发布项目都加不进去。如果选合并发布向导会提示会给表增加ROWGUIDCOL属性的uniqueidentifier标识符字段rowguid默认值newid()。这个新列的影响要提前知道不带列列表的INSERT语句会失败表会变大第一个快照生成的时间会变长。很多老系统上线前没意识到这一点结果发布配置完成后应用端突然报INSERT失败查半天才发现是复制加的rowguid列闹的。后面按提示填发布名称和描述自定义发布属性选否根据指定方式创建发布完成。发布成功后发布服务器上多一个[失效订阅清除]作业分发服务器上多出[REPL快照]和[REPL日志读取器]两个作业。4.3 发布属性目的表改名、筛选、订阅到期发布建立后右键发布名→属性里面有几个实用选项。项目属性的常规窗口可以指定发布目的表的名称也就是说订阅端的那张目标表可以和源表不同名这在做库表隔离时很有用。命令和快照窗口会显示复制的具体实现方式SQL Server数据库复制在本质上是把发布端的insert、update、delete操作在订阅服务器上重做一遍它不是物理文件拷贝。发布表可以做数据筛选。列筛选在项目属性的项目窗口里勾选要发布的列即可行筛选需要手工写筛选SQL语句比如只发布订单表里status1的数据。还有一个订阅到期选项可以设定订阅的有效时长比如24小时过期后订阅会被[失效订阅清除]作业清理掉。关于恢复模式文档里要求发布数据库设成完全恢复模式事务才不会丢。但我自己在测试中发现发布数据库是简单恢复模式下每10秒生成一些大事务10分钟后再收缩数据库日志期间把发布和订阅服务器上的作业都暂停恢复后并没有丢失任何事务更改。文档的说法是保守推荐实际以自己环境的验证为准别盲信。4.4 新建请求订阅初始化与分发代理调度复制→订阅→右键→新建请求订阅。向导第一步查找发布选查看已注册服务器所做的发布就能看到发布服务器上创建的发布名。指定同步代理程序登录时选择使用SQL Server身份验证输入发布服务器上distributor_admin的用户名和密码。选择目的数据库时可以选一个已存在的库也可以新建一个库作为订阅目标。允许匿名订阅选是生成匿名订阅。初始化订阅选是初始化架构和数据这一步会触发分发服务器上的[REPL快照]作业把发布表的表结构、数据、索引、约束全部生成到快照文件夹然后推送到订阅端。快照传送选使用该发布的默认快照文件夹中的快照文件前提是订阅服务器能访问发布服务器的REPLDATA共享目录访问不了就手工设置网络共享和共享权限。设置分发代理程序调度时我一般改成每五分钟调度一次而不是用默认的连续运行这样对老机器的负载更友好。向导最后要求发布服务器上运行SQLServerAgent服务确认后完成。订阅成功后订阅服务器上会新增一个类别为[REPL-分发]的作业合并复制时类别是[REPL-合并]它按之前设置的时间调度表执行同步。4.5 快照作业与日志读取器两条作业怎么配合复制配好后作业层面的配合逻辑值得说清楚。分发服务器上的[REPL快照]作业是复制的前提条件它把发布的表结构、数据、索引、约束生成到发布服务器的OS目录下文件里。这个作业在有订阅请求初始化时或者按时间表调度时才会生成快照不是配置完就一直跑的。[REPL日志读取器]作业在事务复制时一直处于运行状态它的职责是持续读取发布数据库的事务日志把变更解析成命令。合并复制时这个作业按调度时间表运行不需要常驻。所以我排查复制卡住时第一件事就是看这两个作业当前是什么状态[REPL快照]有没有报失败[REPL日志读取器]是不是还在跑比看一堆错误日志直观得多。5. 避坑断网、Msg 3724与快照异常的实战记录复制配置完成后真正的考验是各种异常场景。下面这五条是我自己在实验环境和客户现场都踩过的记录每一条都是现象→原因→解决的完整链路直接照着处理就行。5.1 发布、分发、订阅服务器断网影响半径差很远先看对比表再逐个说处理方式故障场景现象处理方式发布服务器断网/关机中断期事务没复制恢复后自动续传不用干预等恢复即可分发服务器断网发布端事务堆积日志可能膨胀订阅端反复重试设置重试次数和间隔恢复后堆积事务按顺序执行订阅服务器断网/关机影响最大可能出现快照错误甚至要求重新初始化先观察别急着重新初始化发布服务器断网时影响最小。我测试过发布服务器断网、SQL服务关闭、再重启已经设置好的复制几乎不受影响中断期间分发和订阅都接收不到事务信息恢复后从断点继续。分发服务器断网时发布服务器的事务会在本地排队堆积。如果订阅过期删除的间隔设置得很长繁忙发布数据库的事务日志会快速膨胀磁盘小的机器可能被撑爆。订阅服务器因为访问不到分发服务器会按照设定反复重试。错误信息里可以设置重试次数和重试间隔最大重试次数是9999如果每分钟重试一次可以支撑约6.9天不出错。分发服务器恢复后堆积的事务会按时间顺序在订阅机器上执行这个恢复过程可能很长——我们在普通PC机上测试58个事务、100228个命令执行完花了7分28秒。所以遇到分发服务器长时间宕机恢复后不要以为几秒钟就能追平要留足窗口。订阅服务器意外停机的案例更典型。我们实验环境里的订阅服务器从18:46意外停机第二天8:40重新启动后复制在8:40之后自动恢复正常运行了发布端堆积的事务按时间顺序续传。但复制管理器里出现了快照的错误提示提示快照可能需要重新初始化、复制可能需要重新启动。有趣的是我们并没有重新做快照初始化复制照样成功运行了。所以遇到订阅端异常停机恢复后出现快照报错先别急着删了重建大多数情况下复制自己会恢复贸然重新初始化反而会中断正在追平的堆积事务。5.2 Msg 3724删不掉曾经发布过的表现象当你试图删除或变更一个table时SQL Server报错Server: Msg 3724, Level 16, State 2, Line 1 Cannot drop the table object_name because it is being used for replication.典型情况是这张表曾经用于复制后来删除了复制配置但复制标记还残留在系统表里。原因在于复制会在sysobjects表的replinfo字段上做标记删除发布时没清理干净SQL Server就认为这张表还在被复制使用。解决办法是手工把replinfo字段清零select * from sysobjects where replinfo 0 sp_configure allow updates, 1 go reconfigure with override go begin transaction update sysobjects set replinfo 0 where replinfo 0 commit transaction go sp_configure allow updates, 0 go reconfigure with override go逻辑说明先查出所有带复制标记的表再开启allow updates配置这是SQL Server允许直接修改系统表的开关默认是关闭的。然后开一个显式事务更新sysobjects的replinfo字段把标记全部清零最后再关闭allow updates。注意最后那行rollback transaction在新版本里已经不需要了2000里如果更新后发现问题可以在commit前回滚。这套操作做完表就能正常删除或变更了。做完后回到企业管理器刷新一下确认复制监视器里没有残留的发布项。5.3 合并复制同步代理停了sp_start_job手动拉起有同事问过合并复制配置完全后同步代理停止了要在程序里重新启动它用什么命令答案是执行SQL Server代理的作业启动存储过程sp_start_jobUSE msdb EXEC sp_start_job job_name NNightly Backup把job_name换成你自己的同步作业名即可。这个命令的作用是指示SQL Server代理程序立即执行指定作业不管作业原来调度的下一次运行时间是什么时候。我在程序里调用它的场景是检测到复制作业状态不是Running后先记录错误再调用这个存储过程拉起然后发告警通知。注意sp_start_job执行完后作业是异步启动的要确认是否真正跑起来可以再查一下作业的运行状态。5.4 删除复制的顺序先订阅再发布最后禁用复制不要了也不能随便删。正确顺序是先删订阅、再删发布、最后禁用发布。如果顺序反了很容易在分发库里残留元数据下次想重建发布时各种报错。删订阅时在复制→订阅右键选中订阅直接delete。删发布时在复制→发布内容里选中发布名右键删除。最后还要做一步复制→右键→禁用发布进入禁用发布和分发向导选择在servername上禁用发布完成。这一步会把分发库、distributor_admin用户以及相关的复制作业一并清理干净。有些现场为了保留分发服务器继续服务其它发布只删订阅和发布而不禁用发布这是可以的但至少要把订阅和发布的顺序守住。我已经养成一个习惯每次调整复制拓扑前先打开企业管理器复制监视器把所有发布、订阅的状态截图存一份再动手。这样万一删出问题至少能对照着截图恢复不至于连原来有哪些发布项都记不清。6. 日志还原备库不依赖复制代理的同步与故障切换复制技术虽然强大但有时只想做一份只读的备用查询库又不想维护那么多复制代理作业。SQLServer 2000还有一个更轻的方案日志还原。思路是主数据库定期做事务日志备份备用数据库用RESTORE LOG ... WITH STANDBY把日志还原进去备用库处于只读状态随时可以查询但不能被更新。6.1 备份日志加STANDBY还原的同步链路-- 创建演示用的主数据库 CREATE DATABASE Db_test ON (NAME Db_test_DATA, FILENAME c:\Db_test.mdf) LOG ON (NAME Db_test_LOG, FILENAME c:\Db_test.ldf) GO -- 对数据库进行完全备份 BACKUP DATABASE Db_test TO DISKc:\test_data.bak WITH FORMAT GO -- 把备份还原成备用数据库STANDBY文件用于回滚未提交事务 RESTORE DATABASE Db_test_bak FROM DISKc:\test_data.bak WITH REPLACE, STANDBYc:\db_test_bak.ldf, MOVE Db_test_DATA TO c:\Db_test_data.mdf, MOVE Db_test_LOG TO c:\Db_test_log.ldf GO -- 启动SQL Agent服务供作业调度使用 EXEC master..xp_cmdshell net start sqlserveragent, no_output GO -- 创建同步作业 DECLARE jobid uniqueidentifier EXEC msdb..sp_add_job job_id jobid OUTPUT, job_name N数据同步处理 GO -- 创建作业步骤先备份主库日志再在备用库上还原 EXEC msdb..sp_add_jobstep job_id jobid, step_name N数据同步, subsystem TSQL, command N BACKUP LOG Db_test TO DISKc:\test_log.bak WITH FORMAT RESTORE LOG Db_test_bak FROM DISKc:\test_log.bak WITH STANDBYc:\test_log.ldf , retry_attempts 5, retry_interval 5 GO -- 调度每分钟执行一次 EXEC msdb..sp_add_jobschedule job_id jobid, name N时间安排, freq_type 4, freq_interval 1, freq_subday_type 0x4, freq_subday_interval 1, freq_recurrence_factor 1 GO EXEC msdb.dbo.sp_add_jobserver job_id jobid, server_name N(local) GO逻辑说明先建一个测试主库完全备份后还原成备用库MOVE参数指定物理文件位置。同步作业里命令部分先对主库做日志备份再对备用库做带STANDBY的日志还原STANDBY文件专门用来回滚并保留未提交的事务。调度部分用freq_type4表示每日调度freq_subday_type0x4表示按分钟freq_subday_interval1即每分钟执行一次。retry_attempts5表示失败后重试5次retry_interval5表示重试间隔5分钟。实际部署时主库和备用库通常不在同一台机器备份文件要放在两台机器都能访问的共享目录里然后备份和还原作业分别在主服务器和备用服务器上建立顺序是先备份再还原。测试时等待1分30秒再查备用库能看到同步建的表说明链路是通的。6.2 主库故障两种激活备用库的方式场景操作数据丢失主库损坏无法备份最新日志直接在备用库执行RESTORE LOG WITH RECOVERY丢失最近一次日志还原后的所有数据主库还能备份日志先BACKUP LOG再在备用库RESTORE LOG WITH RECOVERY不丢数据第一种情况主数据库已经损坏备份不出最新的日志直接执行带RECOVERY的还原就能让备用库可读写代价是丢失最后一次日志还原之后的所有数据。第二种情况主库还能动先在主库备份最新的事务日志再到备用库恢复日志并同时让备用库可读写这样数据一条都不丢。日志还原方案的同步延迟取决于调度频率需要更高实时性就把作业改成每10秒一次但要评估日志备份文件本身对磁盘IO的消耗。到现在我每次给老系统做同步都强制自己走一遍完整的故障演练先模拟订阅服务器停机观察复制是否自动恢复再删一次发布和订阅确认replinfo残留能清干净最后打一次日志还原的切换测试确保主库真出问题时备用库能在5分钟内顶上。这三步跑通心里才有底毕竟老系统的数据库同步出问题从来不是配置那一下而是半年后某个深夜它突然卡住的时候。希望帮到你。本文还有配套的精品资源点击获取