工作笔记－实现跨防火墙SQL Server数据库复制

前段时间接到一个任务，需要通过互联网在两个不同的局域网之执行数据库同步任务。首次接触到这类问题，心里不免有些发悚，因为在这方面没有太多的经验，于是搜索了大量现有的解决方案和工具，但都没有满意的，于是打算自己做一个工具软件来实现这样的需求。

任务摘要

公司在本部有一套应用系统需要在分部可以查看数据，该系统是N年前使用VFP开发的，没有应用服务器，客户端直接连接SQL Server。（唉，说实在的，很鄙视，但也很无耐，那个年代嘛，已经很不容易了。）

网络环境说明

本部和分部处于两个完全不同的局域网中，本部的互联网是由母公司的网络通过电信链路接入，分部是通过网通链路接入互联网。由于母公司对互联网接入有着较严格的管理体制，本部只能通过HTTP代理服务器访问互联网，而分部的网络是自行管理的，因此有着较大的可控性。

任务分析

不是我吹牛，刚接到这个任务的时候，整个解决方案的基本框架就已经在我脑子里形成了。从任务的内容来看，由于在分部并不需要数据的更改，因此这里只需要将本部的数据定时地向分部复制即可，这样就简化了该任务的难度（擦擦汗）。有一点我非常明白，SQL Server本身提供了数据库复制工具，不过我没有对它进行深入的研究，因为我认为在这样的网络环境下，复制工具并不一定能够满足我的需要，因此我不愿意花时间在研究这个功能的使用上。由于两边服务器都在各自的防火墙内，就需要在某一边凿开一个口子，好让数据进出。前面说过了，本部的网络是受到管制的，因此只能在分部做点小动作了。我想不可能有哪个傻乎乎的家伙会把1433直接开放在互联网上吧，所以必须得在分部数据库服务器前端加设一个应用服务器，以便对所接收的数据进行一定的处理再提交到数据库中执行。

这样一来，基本框架就比较明晰，懒得做图，就文字说明一下了：在本部开发一个数据提取服务，这个服务定时从数据库中提取数据，然后把数据进行一定的处理以后向分部提交。而在分部，开发一个Web应用程序来接收本部发送过来的数据，进行一定的验证以后再提交到执行。

那么接下来的课题就是如何把数据从本部提取出来并搬到分部。最初的想法很简单，在本部的服务器上定时对数据库进行完整备份，然后将备份文件整个传送到分部，再恢复到分部的服务器上。但在看过备份文件的大小之后我立即打消了这个念头，4G，这个是什么概念啊！加上电信与网通之间的连接速度，简直不敢想象一次传输所要花费的时间。那么，完整备份不行，是否可以只传输两边数据库的差异部分，这样或许会小一些？答案当然是肯定的，再说也想不出更好的办法了。

方向定了，下一个课题就是要决定这个差异的获取办法。立刻能想到的是直接使用数据库差异备份，当然这是一个好办法，但我不喜欢使用自己没有经验和把握的技术，因为这类技术一旦出了问题我可能会束手无策，所以枪毙了这个方案。那么，到底如何来实现呢，作为一名为编码工作奉献了1X年青春的老鸟来说，我首先想到的是触发器。

关于触发器的方案，我的想法是这样的，为库中每个表都建立一个解发器，它所要完成的工作就是盯着表的操作，把增、删、改的情况通通记录在指定的一张表里。然后在数据提取服务程序中，定时去取这张操作记录表，对每一条记录都生成相应的脚本，提交到远程服务器上执行就可以了。但这里有两个问题，一是增加了触发器之后对效率和稳定性是否有影响，因为是公司的重要生产系统，不敢轻举妄动；另外一个就是表唯一性的标识问题，因为所有表的操作记录都放到了一张表里，源表中记录的唯一性很难标识，因为库中三十几张表的主键可谓是五花八门，没有统一性（郁闷啊），如果需要为每张表都添加一个RowGuid字段，又怕会影响系统的运行，天知道VFP是怎么处理的。于是，此方案又被枪毙。

头疼啊，要怎么样才能在不更改现有数据结构的情况下获取表的差异呢？接下来想到的方案是采用Trace来跟踪所有的SQL语句，然后把除SELECT以外所有的脚本一股脑儿全部发送到远程去执行。其实这种做法很鲁莽，恐怕有不可预测的后果。就在我苦苦研究Trace的过程中，在一篇文章中，突然看到了一个让我欣喜若狂的单词——tablediff.exe，Table Difference？难不成是比较表的工具？顾不上仔细看这篇文章，直接Google了这个关键字，哈哈，原来是远在天边，近在眼前啊，它是SQL Server 2005数据复制功能中自带的一个工具，用于比较两个表的差别，并可将两个表的差异自动生成脚本到指定的文件中！天哪，这不就是我正好需要的嘛，狂喜之余，整个方案也就定了下来。

方案设计

咳咳，下面要严肃点了。 :)

整个方案的关键就是tablediff了，它是一个命令行程序，通过指定的参数来比较两个表的差异，那么只要写一个程序枚举出数据库中所有的表并循环执行tablediff，就可以达到库中每一个表的对比了。但是仍旧有一个问题存在，两个库分别是在不同的防火墙后面，并且也不可能直接开放1433端口，那怎么对比呢？要解决这个问题，只需要在本部的数据库中再建一个数据库的副本，实际的比较只发生在本部的两个库之间，待比较完成之后再将生成的脚本同时更新到复本数据库和分部数据库上即可。为了描述方便，以下文字中将本部的源数据库简称为源库，复本数据库简为目标库，分部的数据库称为远程库。

如前面的基本框架所述，整个方案分为两部分，在本部用于数据提取的程序为客户端，分部接收数据的程序为服务端。客户端是一个Windows服务程序，它通过配置文件来认定源库和目标库，并且在内部设有一个定时器和一个输出分析器。当客户端启动时，程序立即进行一表复制任务，当复制任务开始前，计时器停止，复制任务结束后计时器启动，这样做的目的是为了防止重入的情况发生。输出分析器的功能是分析tablediff的输出文本，提取其中有价值的数据并整理，一方面可以组织脚本，另一方面可生成日志以便将来分析使用。服务程序还设置了一个文件系统监视器用于监视配置文件的更改，当配置文件发生更改以后立即重新加载以使配置生效，从而避免了因更改配置文件而需要重启服务的麻烦。

复制过程是这样的，开始时程序从源库和目标库中获取所有表的名称，通过对比将相同的名称取出形成表名的列表，此时该列表还不能直接使用，因为表和表之间存在着依赖性，必须将表按其依赖性进行排序，按依赖次数从高到低排列形成新的列表。之后，通过一个循环遍历此列表，在这个循环中首先执行tablediff程序，并指定将脚本输出到一个临时文件中。在每一次tablediff执行完成之后，需要打开相应的临时文件并将所有脚本读取出来并写入到同一个文件中，这样循环结束后所有表的更新脚本都被存放在了同一个脚本文件里。接下来要做的事就是将脚本文件进行一定的处理并传输到服务端，为了减小传输量，应该采用一定的压缩算法对所传输的数据进行压缩。

服务端是一个Web应用程序，在接收到客户端发出的请求时，首先对客户端的身份进行认证，在通过认证之后，将请求所附带的数据进行解压，再根据配置文件的设定把脚本文件在远程库中执行。为了避免执行时出现错误导致的数据不一致性，在执行时还应该启用事务。服务端在执行完成以后将执行结果反馈到客户端，如果有错误则返回错误的文本。

客户端在得到服务端返回的结果以后需要进行分析，如果有错误，则将错误的文本写入到日志当中以供将来参考，如果没有错误，则可将先前的脚本文件提交到目标库中执行。到此，一次复制任务完成。

方案开发

实际的开发过程与设计肯定会有一些出入，设计中的客户端Windows服务程序被改成了桌面应用程序，哈哈，因为搞成服务程序有点麻烦，再说有界面的程序看起来总会让人觉得的确是做了一点事情。好在程序设计的时候严格参照面向对象的原则进行封装，将来要改成服务并不是很大的难事。

程序的开发采用的是C#，.net 3.5，开发出来的程序与设计基本相同，当然有一些细节问题的处理是很需要花费时间的，例如客户端数据传输到服务端，因为HTTP传输二进制数据有点麻烦，我就用了文件上传的方式，折腾我好长时间。

苍天不负有心人啊，经过5天的设计和开发，终于让我赶在了国庆之前完成，经过两个星期的试运行，系统还算稳定。

注意事项

tablediff对表也是有要求的，表中必须要有一个可以标识行唯一性的字段，也就是说，必须要有主键，或者有RowGuid字段，或者有Identity字段。

后记

写完以后仔细读了读，发现废话太多，实在的东西少，呵呵，初中没毕业，加上写博经验不足，还望各位看客多多见谅。因为在这篇文章中只是想阐述一些想法，所以没有放代码，如果各位有兴趣的，请在后面留言，我会找时间专门再博一篇对具体代码的说明与大家一起分享。