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

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

2、CD 表(一致变化数据表)方案，微软公司的出版者 / 订阅等方案。但 由于上述系统致力于解决异构数据库间复杂的交互操作， 过于大而全而且费用较 高，并不符合一些中小企业的实际需求。本文结合企业的实际应用实践经验， 根据不同的应用类型， 给出了相应的 数据库实时同步应用的具体解决方案，主要包括：(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、数据库共 同组成一个异构数据库系统。数据库同步技术对于异构数据库系统，实现数据共享应当达到两点：一是实现数据库转换； 二是实现数据的透明访问。所谓透明访问，就是在异构数据系统中用户可以将异 构分布式数据库系统看成普通的分布式数据库系统， 用自己熟悉的数据处理语言 去访问数据库，如同访问一个数据库系统一样。但目前还没有一种广泛使用的数据定义模型和数据查询语言，实现数据的透明访问可以采用多对一转换、双向的中间件等技术。开放式数据库互连(Open DataBase Connectivity,简称ODBC)是一种用来在相关或不相关的数据库管理 系统中存取数据的标准应用程序接口( API)。ODBC为应

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

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

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

8、。四、局域网数据同步解决方案（一）.SQLSERVER 到 SQLSERVER 同步复制SQLSERVER同类数据库之间实现同步，可直接参照Microsoft 的发布/订阅 模式。具体方法步骤如下:(1) SQL复制的基本元素包括出版服务器、订阅服务器、分发服务器、出版物、文章(2) SQL复制的工作原理SQLSERVER主要采用出版物、订阅的方式来处理复制。源数据所在的服务器是出版服务器，负责发表数据。出版服务器把要发表的数据的所有改变情况 的拷贝复制到分发服务器，分发服务器包含有一个分发数据库，可接收数据的所 有改变，并保存这些改变，再把这些改变分发给订阅服务器。(3) SQLSERVER

9、复制技术类型1、快照复制/2、事务复制SQLSERVER提供了三种复制技术，分别是:/3、合并复制。(4)发布/订阅方案第一步：来配置出版服务器1、选中指定服务器节点2、从工具下拉菜单的复制子菜单中选择发布、订阅服务器和分发命令3、系统弹出一个对话框点 下一步 然后看着提示一直操作到完成。4、当完成了出版服务器的设置以后系统会为该服务器的树形结构中添加 一个复制监视器。同时也生成一个分发数据库 (distribution) 。第二步：创建出版物1、选中指定的服务器。2、从工具菜单的 复制子菜单中选择 创建和管理发布 命令。此时系统 会弹出一个对话框。3、选择要创建出版物的数据库，然后单击 创建

10、发布 。4 、在创建发布向导 的提示对话框中单击 下一步 系统就会弹出一个对话框。 对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布 (其他两个大家可以去看看帮助 )。5、单击 下一步 系统要求指定可以订阅该发布的数据库服务器类型， SQLSERVER允许在不同的数据库如 ORACLE或ACCESS之间进行数据复制。 但是在这里我们选择运行 SQLSERVER2000 的数据库服务器。6、单击 下一步 系统就弹出一个定义文章的对话框也就是选择要出版的表。7、然后 下一步 直到操作完成。当完成出版物的创建后创建出版物的数 据库也就变成了一个共享数据库。第三步：设计订阅1、选中

11、指定的订阅服务器。2、从工具下拉菜单中选择 复制子菜单的请求订阅 。3、按照单击下一步操作直到系统会提示检查 SQLSERVER代理服务的 运行状态，执行复制操作的前提条件是 SQLSERVER代理服务必须已经启动。4、单击 完成 。完成订阅操作。完成上面的步骤其实复制也就是成功了。 但是如何来知道复制是否成功了 呢？这里可以通过这种方法来快速看是否成功。展开出版服务器下面的复制 发布内容右键发布内容属性击活状态然后点立即运行代 理程序接着点代理程序属性击活调度把调度设置为每一天发生，每一分钟，在 0：00：00 和23：59：59 之间。接下来就是判断复制是否成功了打开。 C:/Progra

12、mFiles/MicrosoftSQLServer/MSSQL/REPLDATA/unc/XIAOWAN GZI_database_database 下面看是不是有一些以时间做为文件名的文件夹差不 多一分中就产生一个。 如果不确定是否创建成功， 打开你的数据库看在订阅的服 务器的指定订阅数据库下看是否能够看到刚才所发布的表。（二） 、 Oracle 与 SQL Server 的互连及共享不同数据库平台的互连一般称之为数据库的异构服务， 现在各大数据库之间 都可以实现这样的异构互连，只是各厂商的具体实现技术不一样，如：在SQL SERVER里面叫做LINKED SERVER,通过ODBC实现与其

13、它数据库的 互联。而 ORACLE 实现异构服务的技术叫做透明网关 Transparent Gateway ,当 然之前 ORACLE 还采用过通用连接技术。 目前 ORACLE 利用透明网关可以实现 和 SQLSERVER、SYBASE、DB2 等多种数据库的互联。透明网关的体系结构也很简单，在 ORACLE和SQL ERVER之间使用 ORACLE 透明网关服务器实现互连互通,其中透明网关服务器可以与 ORACLE 或SQLSERVER数据库在同一台主机上，也可以是在独立的一台主机上。测试步骤如下：、在SQLSERVER数据库上创建测试账号和表数据库：PUBS，账号cyx，create t

14、able t (c char(10);(2) 、测试用数据库和透明网关在同一台机器上，透明网关在oracle 默认安装时是不安装的，所以如果你想用需要选择这一选项。(3) 、安装透明网关for sql server的软件后，可以在$ORACLE_HOME 下看到 tg4msql 目录，编辑$ORACLE_HOME/tg4msql/admin/inittg4msql.sql 文件确认这一行正确：HS_FDS_CONNECT_INFO=SERVER=IP;DATABASE=pubs(4) 、修改透明网关 server 上的 listener.ora ，在 SID_LIST 中加入以下内容：(SID

15、_NAME =tg4msql) # SID 自己命名(ORACLE_HOME= c:)(PROGRAM =tg4msql)(5)、在 oracleserver 上的 tnsnames.ora 中加入到透明网关的 tnsname ，内容如下：sql2k =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 6)(PORT=1521)#此处HOST填的是透明网关 SERVER的地址)(CONNECT_DATA= (SID = tg4msql) )#此SID应和透明网关SERVER上设定的SID相同(HS

16、=OK)(6) 、修改 ORACLE SERVER的初始化参数，将 global_names 设为 false , 因为我们并不使用 GLOBAL NAME 。然后重起数据库。(7) 、现在在ORACLE SERVER上创建DB LINK就可以了。以上测试实现通过透明网关来访问 sql server 的数据，只需要注意ORACLE 访问配置问题。注意事项：1、Oracle For SQLServer 的透明网关在 UNIX 下无法实现，目前只支持WIN ；原因很简单， SQLServer 本身不支持 UNIX ，所以 Oracle 也无法直接在 UNIX 下访问 SQL Server 。2、O

17、racle For其它数据库的透明网关，如 SYBASE等有UNIX本版本的 数据库在 UNIX 可以实现。3、Oracle 透明网关软件在 Oracle 8i 时是需要花钱另买的， 大约 1 万$； 到 Oracle 9i 时是作为数据库的一个组件免费发布的， 安装时在组件种选择即可4 、针对我们的实际应用，如果有需要实现 Oracle 到 SQL Server 的 互连，我们需要另外用一台 WIN 下的 Oracle 9i 来做透明网关服务器，其它 UNIX 下的 Oracle 通过这个透明网关来访问 SQL Server 。5、同数据库间的数据处理需使用标准 SQL 来实现。6、各数据库

18、中特殊的数据类型，需要在程序中实现转换，应尽量避免使 用无法转换的数据类型。（ 三） 、Oracle 与 SQL Server 的实时同步尽管透明网关技术实现了 OQACLE 与 SQLSERVER 的互连，但在同步 应用中不便于控制同步数据的要求，特别是 ORACEL 与 SQLSERVER 存在数据 类型转换时存在困难。以下给出同步数据代码样例如下：为实现 ORACLE 到 SQLSERVER 的数据同步，首先需在 ORACLE 中建 一需同步的数据视图，以便于操作访问。1、在 ORACLE 中创建需同步的数据视图2 、配置局域网客户端建立 tnsname.ora 文件，使其能访问远程 O

19、RACLE 数据库3 、在 SQLSERVER 中创建与 ORACLE 视图同构的数据库表4 、定义同步方式及步骤以便在后台运行。6、以下是ORACLE至U SQLSERVER数据同步主要VBS示例代码;= oracleTOsqlserver数据同步脚本 =1. 检测远程 oracle 服务器网络是否连通 , 通则连接 ;2. 连接 oracle; 连接 sqlserver ;取 N 天的 cklsz 记录集;3. 取 oracle 的 dataset; not in sqlserver 中的 insert to sqlserver;4. 日志写入 txt 文件，以日期为文件名称，只保留 7

20、天的日志 ;Do While Truedim f,txtfile 创建日志文件对象 , 判定 7 天前的 log 文件是否存在，存在则删除！Set f = CreateObject(Scripting.FileSystemObject)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

21、 ping oracle 服务器， 连通 - 则连接 oracle/sqlserver ip=2Set FileSys = CreateObject(Scripting.FileSystemObject)Set WShShell = CreateObject(WScript.Shell)RetCode = WShShell.Run(ping -n 1 &IP , 0, True )if RetCode = 0 Thentxtfile.write(date& &time& - ping anywhere hostok ! ) 测试连接远程 oracle ( OraOLEDB.

22、Oracle.1/ MSDAORA.1 )conn_ora =Provider=OraOLEDB.Oracle.1;Persist SecurityInfo=True;User ID=qyselect;Password=qy08select12;DataSource=hydataset rs_ora=createobject(adodb.recordset)sql_ora=select * from Hybjsb.V_QY_CKLSZErr.clearOn Error Resume Next -出错不显示 ,执行下一条rs_ora.open sql_ora,conn_ora,1,1rs_ora

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

24、*远程数据库连接失败 ! * )end if网络故障 ! #elsetxtfile.writeline(date& &time& - # )end ifreleaseconnection conn_orareleaseconnection conn_localset f=nothingset txtfile=nothingset rs_local=nothingset rs_ora=nothing间隔 5 分钟循环一次WScript.Sleep 300000Loop 数据同步过程 Sub xcopy()dim records(0)判断 temp 是否存在，存在则删除sql_local=sele

25、ct count(1) as rec from &DB&.dbo.sysobjects where name=temp and xtype=Urs_local.open sql_local,conn_local,1,1records=rs_local(rec)rs_local.closeif records 0 thensql_local=drop table &DB&.dbo.temprs_local.open sql_local,conn_local,1,1end if(1) 取当前日期前 Nday 天的数据插入临时表 tempsql_local=SELECT * INTO &DB&.db

26、o.temp FROM OPENROWSET(MSDAORA.1,hydata;qyselect;qy08select12,SELECT FROMHybjsb.V_QY_CKLSZ) sql_local=sql_local+ whereCONVERT(smalldatetime,dbilldate)= +cstr(date-Nday)+曲烟sql_local=sql_local+ and deptname=&dept& and storname 寄售库 rs_local.open sql_local,conn_local,1,1(2) 判断是否有新记录sql_local= SELECT cou

27、nt(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=+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_lysql_local=sql_

28、local+(bid,lyrq,djh,storname,deptname,lyr,custname,inv classcode,invclassname,chbm,lybjmc,lygg,lyxh,lyjh,graphid,lydw,lysl,lydj,l yje)sql_local=sql_local+ SELECT sql_local=sql_local+ bid,CONVERT(smalldatetime,dbilldate) asdbilldate,billcode,storname,deptname,psnname,custname,invclasscod e,invclassna

29、me,invcode,invname,invspec,invtype,invmnecode,graphid,m easname,noutnum,nprice,noutmnysql_local=sql_local+ FROM &DB&.dbo.tempsql_local=sql_local+ where bid not in (select bid from&DB&.dbo.t_ly where lyrqtxtfile.writeline( )txtfile.writeline( * &dept&new Add +cstr(records)+记录 .)elsetxtfile.writeline(

30、 )txtfile.writeline( &dept& - no new data! )end if(3) delete 删除记录sql_local=select count(1) as rec from &DB&.dbo.t_lysql_local=sql_local+ where lyrq=+cstr(date-Nday)+)&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

31、sql_local=sql_local+ where 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& - &date& &time&elsetxtfile.writeline( &dept&- no delete data! )end if(4) update 更新记录sql_local=select count(1) as rec f

32、rom &DB&.dbo.temprs_local.open sql_local,conn_local,1,1records=rs_local(rec)rs_local.closesql_local=update &DB&.dbo.t_lysql_local=sql_local+ set sql_local=sql_local+lyrq=CONVERT(smalldatetime,T.dbilldate),djh=T.billcode,storname=T.storname,deptname=T.deptname,custname=T.custname,sql_local=sql_local+

33、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.noutmnysql_local=sql_local+ from &DB&.dbo.t_ly AS A, &DB&.dbo.tempAS Tsql_

34、local=sql_local+ where A.lyrq=+cstr(date-Nday)+sql_local=sql_local+ and A.bid=T.bidrs_local.open sql_local,conn_local,1,1txtfile.writeline( &dept&- update data +cstr(records) + 记录 .txtfile.writeline( ) end sub(四)、 ACCESS 与 SQL Server 的实时同步由于 ACCESS 数据本身的特点，桌面数据库，不支持网络访问操作，因此要 实现与 SQLSERVER 数据库的同步，需要

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

36、BACKUP+trim(db)&.mdbif ip=1 then产品数码跟踪 1end 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 Next 出错不显示

37、,执行下一条net.MapNetworkDrive Path, source_path,False,xcopy,xcopyif err.number=0 thentxtfile.write（-映射成功 ! ） 执行拷贝 source_file=path&barcodeprinter.mdb err.clearOn Error Resume Next 出错不显示 ,执行下一条取消映射fsofile.copyfile source_file,target_filenet.RemoveNetworkDrive Path,True elsetxtfile.write（-映射失败 ! ）end ifEn

38、d IfIf Err.Number0 Thentxtfile.writeline(- .MDB copy失败! )elsetxtfile.writeline(- .MDB copy成功! )end ifset fso=nothingset fsofile=nothingset net=nothingend sub*= MDB TO SQLSERVER 实时同步过程 =sub MDBTOSQL(database,textfile1,ip)Dim Path,fso,fsofile,netnum=right(ip,2)db=QJCF&num&_BARCODEPRINTERdb=trim(database)Err.clearon error resume nextconn1 =PROVIDER=SQLOLEDB;DATA SOURCE=local;UID=sa;PWD=sa;DATABASE=+dbset rs = createobject(adodb.recordset) insert arrange插入操作 sql=insert into &db&.dbo.arrangesql=sql+(id,planno,arrangeid,barrangedate,produced