数据库实时同步技术解决方案

1、-精选文档 -数据库实时同步技术解决方案一、前言随着企业的不断发展， 企业信息化 的不断深入，企业内部存在着各种各样的异构软、硬件平台，形成了分布式异构数据源。当企业各应用系统间需要进行数据交流时， 其效率及准确性、 及时性必然受到影响。 为了便于信息资源的统一管理及综合利用， 保障各业务部门的业务需求及协调工作，常常涉及到相关数据库数据实时同步处理。基于数据库的各类应用系统层出不穷，可能涉及到包括ACCESS、SQLSERVER、ORACLE、DB2 、MYSQL 等数据库。目前国内外几家大型的数据库厂商提出的异构数据库复制方案主要有：Oracle 的透明网关技术，IBM 的 CCD 表（一

2、致变化数据表）方案，微软公司的出版者/ 订阅等方案。但由于上述系统致力于解决异构数据库间复杂的交互操作，过于大而全而且费用较高，并不符合一些中小企业的实际需求。本文结合企业的实际应用实践经验，根据不同的应用类型， 给出了相应的数据库实时同步应用的具体解决方案，主要包括：(1) SQLSERVER 到 SQLSERVER 同步方案(2) ORACLE 到 SQLSERVER 同步方案(3) ACCESS 到 SQLSERVER/ORACLE 同步方案可编辑-精选文档 -二、 异构数据库异构数据库系统是相关的多个数据库系统的集合，可以实现数据的共享和透明访问，每个数据库系统在加入异构数据库系统之前

3、本身就已经存在，拥有自己的 DMBS 。异构数据库的各个组成部分具有自身的自治性，实现数据共享的同时，每个数据库系统仍保有自己的应用特性、完整性控制和安全性控制。异构数据库的异构性主要体现在以下几个方面：1 、计算机体系结构的异构各数据库可以分别运行在大型机、小型机、工作站、PC 嵌入式系统 中。2 、基础操作系统的异构各个数据库系统的基础操作系统可以是Unix 、Windows NT、Linux 等。3 、DMBS 本身的异构可以是同为关系型数据库系统的Oracle 、 SQL Server 等，也可以是不同数据模型的数据库，如关系、模式、层次、网络、面向对象，函数型数据库共同组成一个异构数

4、据库系统。三、 数据库同步技术可编辑-精选文档 -对于异构数据库系统， 实现数据共享应当达到两点： 一是实现数据库转换；二是实现数据的透明访问。 所谓透明访问， 就是在异构数据系统中用户可以将异构分布式数据库系统看成普通的分布式数据库系统，用自己熟悉的数据处理语言去访问数据库，如同访问一个数据库系统一样。但目前还没有一种广泛使用的数据定义模型和数据查询语言，实现数据的透明访问可以采用多对一转换、 双向的中间件等技术。开放式数据库互连（OpenDataBase Connectivity,简称 ODBC ）是一种用来在相关或不相关的数据库管理系统中存取数据的标准应用程序接口（API ）。ODBC

5、为应用程序提供了一套高层调用接口规范和基于动态链接库的运行支持环境。目前，常用的数据库应用开发的前端工具如Power Builder、 Delphi等都通过开放数据库互联(ODBC) 接口来连接各种数据库系统。 而多数数据库管理系统 （如： Oracle 、Sybase 、SQLServer 等）都提供了相应的 ODBC 驱动程序，使数据库系统具有很好的开放性。ODBC 接口的最大优点是其互操作能力，理想情况下，每个驱动程序和数据源应支持完全相同的ODBC 函数调用和 SQL 语句，使得 ODBC 应用程序可以操作所有的数据库系统。然而，实际上不同的数据库对SQL 语法的支持程度各不相同，因此

6、， ODBC 规范定义了驱动程序的一致性级别，ODBC API 的一致性确定了应用程序所能调用的ODBC 函数种类。数据库同步是在两个不同的数据库之间进行数据交换，以使得任何一个数据库的改变，会以同样的方式出现在另一个数据库里。数据库同步可以是单向的，也可以是双向的。 单向同步也叫主从同步， 也就是说，其中的一个数据库是主数可编辑-精选文档 -据库，另一个数据库是从数据库。 只有主数据库的改变可以被复制到从数据库里去，从数据库是被动的。使用数据库同步技术， 用户可以将一份数据发布到多台服务器上，从而使不同的服务器用户都可以在权限的许可的范围内共享这份数据。同步技术可以确保分布在不同地点的数据自

7、动同步更新，从而保证数据的一致性。图 1 数据库同步示意图目前国内外几家大型的数据库厂商提出的异构数据库复制方案主要有：Oracle 的透明网关技术， IBM 的 CCD 表（一致变化数据表）方案，微软公司的出版者 / 订阅者方案。国外的如土耳其中东科技大学的MIND系统，美国密歇根迪尔波恩大学、 斯坦福大学的 TSUNMMIS项目，国内的如中关村科技软件有限公司、华中科技大学的Panorama系统都有过类似系统的研究。但是由于上述系统致力于解决异构数据库间复杂的交互操作，大而全，并不符合一些中小企业的需求，如 IBM 的 CCD 表方案实现复杂且CD 表占用资源较多维护费用高昂，微软公司的出

8、版者 / 订阅者方案只能在NT 运行等。而其它的系统大多都存在此类问题。可编辑-精选文档 -四、 局域网数据同步解决方案( 一) .SQLSERVER到 SQLSERVER 同步复制SQLSERVER 同类数据库之间实现同步， 可直接参照 Microsoft的发布 / 订阅模式。具体方法步骤如下：(1) SQL 复制的基本元素包括出版服务器、订阅服务器、分发服务器、出版物、文章。(2) SQL 复制的工作原理SQLSERVER 主要采用出版物、订阅的方式来处理复制。源数据所在的服务器是出版服务器， 负责发表数据。 出版服务器把要发表的数据的所有改变情况的拷贝复制到分发服务器， 分发服务器包含有

9、一个分发数据库，可接收数据的所有改变，并保存这些改变，再把这些改变分发给订阅服务器。(3) SQLSERVER 复制技术类型SQLSERVER 提供了三种复制技术，分别是：1 、快照复制 /2 、事务复制/3 、合并复制。(4) 发布 / 订阅方案可编辑-精选文档 -第一步：来配置出版服务器1、选中指定 服务器 节点。2、从 工具 下拉菜单的 复制 子菜单中选择 发布、订阅服务器和分发 命令。3 、系统弹出一个对话框点 下一步 然后看着提示一直操作到完成。4 、当完成了出版服务器的设置以后系统会为该服务器的树形结构中添加一个复制监视器。同时也生成一个分发数据库 (distribution) 。

10、第二步：创建出版物1 、选中指定的服务器。2 、从 工具 菜单的 复制 子菜单中选择 创建和管理发布 命令。此时系统会弹出一个对话框。3 、选择要创建出版物的数据库，然后单击 创建发布 。4 、在 创建发布向导 的提示对话框中单击下一步 系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)。可编辑-精选文档 -5 、单击 下一步 系统要求指定可以订阅该发布的数据库服务器类型，SQLSERVER 允许在不同的数据库如ORACLE 或 ACCESS 之间进行数据复制。但是在这里我们选择运行"SQLSERVER2000&

11、quot; 的数据库服务器。6 、单击 下一步 系统就弹出一个定义文章的对话框也就是选择要出版的表。7 、然后 下一步 直到操作完成。当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库。第三步：设计订阅1、选中指定的订阅服务器。2 、从 工具 下拉菜单中选择 复制 子菜单的 请求订阅 。3 、按照单击 下一步 操作直到系统会提示检查SQLSERVER 代理服务的运行状态，执行复制操作的前提条件是SQLSERVER 代理服务必须已经启动。4 、单击 完成 。完成订阅操作。完成上面的步骤其实复制也就是成功了。但是如何来知道复制是否成功了呢？这里可以通过这种方法来快速看是否成功。展开出版

12、服务器下面的复制发布内容右键发布内容属性击活状态然后点立即运行代理程序接着点代理程序属性击活调度把调度设置为每一天发生，每一分钟，在 0 ：00 ：00 和 23 ： 59 ：59 之间。接下来就是判断复制是否成功了打开。可编辑-精选文档 -C:/ProgramFiles/MicrosoftSQLServer/MSSQL/REPLDATA/unc/XIAOWANGZI_database_database下面看是不是有一些以时间做为文件名的文件夹差不多一分中就产生一个。 如果不确定是否创建成功， 打开你的数据库看在订阅的服务器的指定订阅数据库下看是否能够看到刚才所发布的表。( 二) 、 Orac

13、le 与 SQLServer 的互连及共享不同数据库平台的互连一般称之为数据库的异构服务，现在各大数据库之间都可以实现这样的异构互连，只是各厂商的具体实现技术不一样，如：在SQLSERVER里面叫做 LINKEDSERVER，通过 ODBC 实现与其它数据库的互联。而 ORACLE 实现异构服务的技术叫做透明网关Transparent Gateway，当然之前 ORACLE 还采用过通用连接技术。 目前 ORACLE 利用透明网关可以实现和 SQLSERVER、 SYBASE、 DB2 等多种数据库的互联。透明网关的体系结构也很简单，在ORACLE 和 SQL ERVER 之间使用ORACLE

14、 透明网关服务器实现互连互通，其中透明网关服务器可以与ORACLE或 SQLSERVER 数据库在同一台主机上，也可以是在独立的一台主机上。测试步骤如下：(1) 、在 SQLSERVER数据库上创建测试账号和表数据库 : PUBS，账号 cyx ，可编辑-精选文档 -createtablet(cchar(10);(2) 、测试用数据库和透明网关在同一台机器上，透明网关在 oracle 默认安装时是不安装的，所以如果你想用需要选择这一选项。(3) 、安装透明网关forsqlserver 的软件后，可以在 $ORACLE_HOME下看到 tg4msql目录，编辑$ORACLE_HOME/tg4ms

15、ql/admin/inittg4msql.sql文件确认这一行正确：HS_FDS_CONNECT_INFO="SERVER=IP;DATABASE=pubs"(4) 、修改透明网关 server 上的 listener.ora，在 SID_LIST 中加入以下内容：(SID_NAME=tg4msql)#SID 自己命名(ORACLE_HOME=c:)(PROGRAM=tg4msql)(5) 、在 oracleserver 上的 tnsnames.ora中加入到透明网关的tnsname ，内容如下：sql2k=(DESCRIPTION=(ADDRESS_LIST=可编辑-精选

16、文档 -(ADDRESS=(PROTOCOL=TCP)(HOST= 1521)# 此处 HOST 填的是透明网关 SERVER的地址)(CONNECT_DATA=(SID=tg4msql)# 此 SID 应和透明网关 SERVER 上设定的 SID 相同(HS=OK)(6) 、修改 ORACLESERVER的初始化参数，将 global_names设为 false ，因为我们并不使用GLOBALNAME 。然后重起数据库。(7) 、现在在 ORACLESERVER上创建 DBLINK 就可以了。以上测试实现通过透明网关来访问sqlserver 的数据，只需要注意ORACLE 访问配置问题。注意

17、事项 ：可编辑-精选文档 -1、Oracle For SQLServer的透明网关在 UNIX 下无法实现，目前只支持WIN ；原因很简单， SQLServer 本身不支持 UNIX ，所以 Oracle 也无法直接在UNIX 下访问 SQL Server 。2、OracleFor 其它数据库的透明网关，如SYBASE 等有 UNIX 本版本的数据库在 UNIX 可以实现。3、Oracle 透明网关软件在Oracle8i 时是需要花钱另买的， 大约 1 万 $；到 Oracle 9i时是作为数据库的一个组件免费发布的，安装时在组件种选择即可。4 、针对我们的实际应用，如果有需要实现Oracle

18、到 SQLServer 的互连，我们需要另外用一台WIN 下的 Oracle9i 来做透明网关服务器，其它UNIX 下的 Oracle 通过这个透明网关来访问SQLServer 。5 、同数据库间的数据处理需使用标准SQL 来实现。6 、各数据库中特殊的数据类型，需要在程序中实现转换，应尽量避免使用无法转换的数据类型。( 三) 、Oracle 与 SQLServer 的实时同步尽管透明网关技术实现了OQACLE 与 SQLSERVER 的互连，但在同步应用中不便于控制同步数据的要求，特别是ORACEL 与 SQLSERVER 存在数据类型转换时存在困难。以下给出同步数据代码样例如下：可编辑-精

19、选文档 -为实现 ORACLE 到 SQLSERVER 的数据同步，首先需在 ORACLE中建一需同步的数据视图，以便于操作访问。1 、在 ORACLE 中创建需同步的数据视图2 、配置局域网客户端建立tnsname.ora文件，使其能访问远程ORACLE 数据库3 、在 SQLSERVER 中创建与 ORACLE 视图同构的数据库表4 、定义同步方式及步骤5 、将 VBS 转换成 EXE 文件，并将 EXE 程序制作成 windows服务，以便在后台运行。6 、以下是 ORACLE 到 SQLSERVER 数据同步主要 VBS 示例代码；= oracleTOsqlserver数据同步脚本 =

20、1. 检测远程 oracle 服务器网络是否连通 , 通则连接 ;2. 连接 oracle; 连接 sqlserver ；取 N 天的 cklsz 记录集；3. 取 oracle 的 dataset; not in sqlserver中的 insert to sqlserver;可编辑-精选文档 -4. 日志写入 txt 文件，以日期为文件名称，只保留7 天的日志 ;=Do While Truedim f,txtfile- 创建日志文件对象 , 判定 7 天前的 log 文件是否存在，存在则删除！-Set f = CreateObject("Scripting.FileSystemO

21、bject")set txtfile =f.opentextfile("D:wxjloracleTOsqlserver"&date&".txt",8,True)If f.FileExists("D:wxjloracleTOsqlserver"&date-7&".txt") Thenf.DeleteFile("D:wxjloracleTOsqlserver"&date-7&".txt")end if-ping oracl

22、e服务器，连通 ->则连接oracle/sqlserver -Set FileSys=CreateObject("Scripting.FileSystemObject")可编辑-精选文档 -Set WShShell=CreateObject("WScript.Shell")RetCode=WShShell.Run("ping -n 1 " &IP , 0, True )ifRetCode=0Thentxtfile.write(date&" "&time&" - pin

23、g anywhere hostok ! ")-测试连接远程Info=True;User ID=qyselect;Password=qy08select12;DataSource=hydata"set rs_ora=createobject("adodb.recordset")sql_ora="select * from Hybjsb.V_QY_CKLSZ"Err.clearOn Error Resume Next -出错不显示 ,执行下一条rs_ora.open sql_ora,conn_ora,1,1可编辑-精选文档 -rs_ora

24、.closeIf Err.Number=0 Thentxtfile.writeline("- connect anywhere database successful ! ")-连接本地sqlserver, insert sqlserverconn_local="PROVIDER=SQLOLEDB;DATASOURCE=local;UID=sa;PWD=sa"set rs_local = createobject("adodb.recordset")- 更新 30 天以内的数据Nday=30dept="卷包车间 "

25、DB="wxjl_1bjb"call xcopy-可编辑-精选文档 -elsetxtfile.writeline("- *远程数据库连接失败! * ")end ifelsetxtfile.writeline(date&" "&time&" - #网络故障! #")end ifreleaseconnection conn_orareleaseconnection conn_localset f=nothingset txtfile=nothingset rs_local=nothingset

26、rs_ora=nothing间隔 5 分钟循环一次可编辑-精选文档 -WScript.Sleep 300000Loop-数据同步过程-Sub xcopy()dim records(0) -判断 temp是否存在，存在则删除sql_local="select count(1) as rec from "&DB&".dbo.sysobjects wherename=temp and xtype=U"rs_local.open sql_local,conn_local,1,1records=rs_local("rec")rs

27、_local.closeif records > 0 thensql_local="drop table "&DB&".dbo.temp"可编辑-精选文档 -rs_local.open sql_local,conn_local,1,1end if(1) - 取当前日期前 Nday 天的数据插入临时表 tempsql_local="SELECT * INTO "&DB&".dbo.temp FROMOPENROWSET(MSDAORA.1,hydata;qyselect;qy08selec

28、t12,SELECT *FROMHybjsb.V_QY_CKLSZ) "sql_local=sql_local+" whereCONVERT(smalldatetime,dbilldate)<="+cstr(date)+" andCONVERT(smalldatetime,dbilldate)>= "+cstr(date-Nday)+""sql_local=sql_local+" and deptname="&dept&" and storname<>曲烟

29、寄售库"rs_local.open sql_local,conn_local,1,1(2) - 判断是否有新记录sql_local=" SELECT count(1) as rec "可编辑-精选文档 -sql_local=sql_local+" FROM "&DB&".dbo.temp "sql_local=sql_local+"where bid not in (select bid from"&DB&".dbo.t_ly where lyrq<=&qu

30、ot;+cstr(date)+" andlyrq>="+cstr(date-Nday)+")"rs_local.open sql_local,conn_local,1,1records=rs_local("rec")rs_local.closeif records > 0 then-有新记录 , 追加记录sql_local="insert into "&DB&".dbo.t_ly"sql_local=sql_local+"(bid,lyrq,djh,stor

31、name,deptname,lyr,custname,invclasscode,invclassname,chbm,lybjmc,lygg,lyxh,lyjh,graphid,lydw,lysl,lydj,lyje)"sql_local=sql_local+" SELECT "sql_local=sql_local+" bid,CONVERT(smalldatetime,dbilldate) as可编辑-精选文档 -dbilldate,billcode,storname,deptname,psnname,custname,invclasscode,inv

32、classname,invcode,invname,invspec,invtype,invmnecode,graphid,measname,noutnum,nprice,noutmny"sql_local=sql_local+" FROM "&DB&".dbo.temp"sql_local=sql_local+" where bid not in (select bid from"&DB&".dbo.t_ly where lyrq<="+cstr(date)+&quo

33、t; andlyrq>="+cstr(date-Nday)+")"rs_local.open sql_local,conn_local,1,1txtfile.writeline(" ")txtfile.writeline(" * "&dept&"- "&date&" "&time&" ->new Add"+cstr(records)+"记录 .")elsetxtfile.writelin

34、e(" ")txtfile.writeline(" - "&dept&"- no new data! ")可编辑-精选文档 -end if(3) delete -删除记录sql_local="select count(1) as rec from"&DB&".dbo.t_ly"sql_local=sql_local+" where lyrq<="+cstr(date)+" andlyrq>="+cstr(date

35、-Nday)+""sql_local=sql_local+" and bid not in (select bid from"&DB&".dbo.temp)"rs_local.open sql_local,conn_local,1,1records=rs_local("rec")rs_local.closeif records >0 thensql_local="delete "&DB&".dbo.t_ly"sql_local=sql_l

36、ocal+" where lyrq<="+cstr(date)+" and可编辑-精选文档 -lyrq>="+cstr(date-Nday)+""sql_local=sql_local+" and bid not in (select bid from"&DB&".dbo.temp)"rs_local.open sql_local,conn_local,1,1txtfile.writeline(" * "&dept&"-

37、"&date&" "&time&" ->delete "+cstr(records)+"记录 .")elsetxtfile.writeline(" - "&dept&"- no delete data! ")end if(4) update -更新记录sql_local="select count(1) as rec from"&DB&".dbo.temp"rs_local.o

38、pen sql_local,conn_local,1,1records=rs_local("rec")rs_local.close可编辑-精选文档 -sql_local="update "&DB&".dbo.t_ly"sql_local=sql_local+" set "sql_local=sql_local+"lyrq=CONVERT(smalldatetime,T.dbilldate),djh=T.billcode,storname=T.storname,deptname=T.dept

39、name,custname=T.custname,"sql_local=sql_local+"invclasscode=T.invclasscode,invclassname=T.invclassname,chbm=T.invcode,lybjmc=T.invname,lygg=T.invspec,lyxh=T.invtype,"sql_local=sql_local+"lyjh=T.invmnecode,graphid=T.graphid,lydw=T.measname,lysl=T.noutnum,lydj=T.nprice,lyje=T.noutm

40、ny"sql_local=sql_local+" from "&DB&".dbo.t_ly AS A, "&DB&".dbo.tempAS T"sql_local=sql_local+" where A.lyrq<="+cstr(date)+" andA.lyrq>="+cstr(date-Nday)+""可编辑-精选文档 -sql_local=sql_local+" and A.bid=T.bid"r

41、s_local.open sql_local,conn_local,1,1txtfile.writeline(" - "&dept&"- update data->"+cstr(records) +"记录 ."txtfile.writeline(" ")end sub-（四）、 ACCESS 与 SQLServer 的实时同步由于 ACCESS 数据本身的特点，桌面数据库，不支持网络访问操作，因此要实现与 SQLSERVER 数据库的同步，需要进行目录共享访问设置。 具体实现如下：1 、 共享

42、 ACCESS 数据库目录，并赋予一admin权限的用户；2 、 测试局域网共享访问：目录 . ；3 、 代码实现客户机共享ACCESS 文件到服务器的拷贝；4 、 在同一服务器上实现ACCESS 到 SQLSERVER 数据的同步复制；可编辑-精选文档 -5 、 以下主要 VBS 代码实现了 MDB 文件复制及与 SQLSERVER 同步更新；= ACCESS .MDB文件实时备份过程=sub backupfile(txtfile,ip,db)Dim Path,fso,fsofile,net-初始化备份参数source_path="产品数码跟踪 "target_file

43、="D:WEBbarcodeprinterBACKUP"+trim(db)&".mdb"source_path="产品数码跟踪1"end iferr.clearPath="X:"Set fso = CreateObject("Scripting.FileSystemObject")Set fsofile = CreateObject("Scripting.FileSystemObject")可编辑-精选文档 -Set net = CreateObject("WScript.Network")If Not fso.DriveExists(fso.GetDriveName(Path) Thenerr.clearOn Error Resume