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

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

一、前言随着企业的不断发展，企业信息化的不断深入，企业内部存在着各种各样的异构软、硬件平台，形成了分布式异构数据源当企业各应用系统间需要进行数据交流时，其效率及准确性、及时性必然受到影响为了便于信息资源的统一管理及综合利用，保障各业务部门的业务需求及协调工作，常常涉及到相关数据库数据实时同步处理基于数据库的各类应用系统层出不穷，可能涉及到包括ACCESS.SQLSERVER.ORACLE.DB

2、MYSQL等数据库目前国内外几家大型的数据库厂商提出的异构数据库复制方案主要有Oracle的透明网关技术IBM的CCD表一致变化数据表方案，微软公司的出版者/订阅等方案但由于上述系统致力于解决异构数据库间复杂的交互操作，过于大而全而且费用较高，并不符合一些中小企业的实际需求本文结合企业的实际应用实践经验，根据不同的应用类型，给出了相应的数据库实时同步应用的具体解决方案，主要包括SQLSERVER到SQLSERVER同步方案ORACLE至！|SQLSERVER同步方案ACCESS至!|SQLSERVER/ORACLE同步方案1521#此处HOST填的是透明网关SERVER的地址CONNECT_DATA=SID=tg4msql#此SID应和透明网关SERVER上设定的SID相同HS=OK

6、修改ORACLESERVER的初始化参数M等globaljames设为false因为我们并不使用GLOBALNAMEO然后重起数据库⑺、现在在ORACLESERVER上创建DBLINK就可以了以上测试实现通过透明网关来访问sqlserver的数据，只需要注意ORACLE访问配置问题［注意事项］:

1、OracleForSQLServer的透明网关在UNIX下无法实现，目前只支持WIN;原因很简单，SQLServer本身不支持UNIX所以Oracle也无法直接在UNIX下访问SQLServero

2、OracleFor其它数据库的透明网关，如SYBASE等有UNIX本版本的数据库在UNIX可以实现

3、Oracle透明网关软件在Oracle8i时是需要花钱另买的，大约1万$;到Oracle9i时是作为数据库的一个组件免费发布的，安装时在组件种选择即可

4、针对我们的实际应用，如果有需要实现Oracle到SQLServer的互连，我们需要另外用一台WIN下的Oracle9i来做透明网关服务器，其它UNIX下的Oracle通过这个透明网关来访问SQLServer

05、同数据库间的数据处理需使用标准SQL来实现

6、各数据库中特殊的数据类型，需要在程序中实现转换，应尽量避免使用无法转换的数据类型

（三）、Oracle与SQLServer的实时同步尽管透明网关技术实现了OQACLE与SQLSERVER的互连，但在同步应用中不便于控制同步数据的要求，特别是ORACEL与SQLSERVER存在数据类型转换时存在困难以下给出同步数据代码样例如下:为实现ORACLE到SQLSERVER的数据同步，首先需在ORACLE中建一需同步的数据视图，以便于操作访问

1、在ORACLE中创建需同步的数据视图

2、配置局域网客户端建立文件，使其能访问远程ORACLE数据库

3、在SQLSERVER中创建与ORACLE视图同构的数据库表

4、定义同步方式及步骤

5、将VBS转换成EXE文件，并将EXE程序制作成windows服务以便在后台运行

6、以下是ORACLE到SQLSERVER数据同步主要VBS示例代码；==========oracleTOsqlserver数据同步脚本=====================.检测远程oracle服务器网络是否连通，通则连接；.连接oracle;连接sqlserver;取N天的cklsz记录集；.取oracle的dataset;notinsqlserver中的inserttosqlserver;.日志写入txt文件，以日期为文件名称，只保留7天的日志;DoWhileTruedimftxtfile--创建日志文件对象，判定7天前的log文件是否存在存在则删除-Setf=CreateObjectystemObjectnsettxtfile=f.opentextfileD:\wxjl\oracleTOsqlserver\date.txtz8zTrueIff.FileExistsnD:\wxjl\oracleTOsqlserver\11date-7HtxtHThenf.DeleteFileD:\wxjl\oracleTOsqlserver\date-

71.txtendifpingoracle服务器，连通则连接orade/sqlserverip=

10.

98.

51.82SetFileSys=CreateObjectCystemObject1SetWShShell=CreateObjectM“ping-n1HIP0TrueifRetCode=0Thendatetime”---pinganywherehostok!测试连接远程oQcle----e.l/MSDAORA.lconn_ora=Provider=e.l;PersistSecurityInfo=True;UserID=qyselect;Password=qy08selectl2;DataSource=hydatasetrs_ora=createobjectdsetsql_ora=select*fromHybjsb.V_QY_CKLSZnOnErrorResumeNext---出错不显示执行下一条rs_sql_oraconn_orallrsIfr=0ThenIine——connectanywheredatabasesuccessful!n连接本地sqlserverinsertsqlserverconnjocal=“PROVIDER=SQLOLEDBQATASOURCE=local;UID=sa;PWD=saHsetrsjocal=createobjectdsetn-更新30天以内的数据Nday=30dept二”卷包车间“DB=wxjLlbjbncallxcopyelse*****远程数据库连接失败！*****endifelseline（date—#####网络故障!!!!#######）endifreleaseconnectionconn_orareleaseconnectionconnjocalsetf=nothingsettxtfile=nothingsetrs_local二nothingsetrs_ora=nothing间隔5分钟循环一次300000Loop数据同步过程SubxcopyQdimrecords0——判断temp是否存在，存在则删除sql_local=nselectcountlasrecfrom“DB”.jectswherename=tempandxtype=Urs_sqlJocaLconnJocal!!records=rsjocalrecrs_ifrecords0thensqUocal=ndroptableDB.rs_sqUocaUonnJocal!!endif取当前日期前Nday天的数据插入临时表tempsqLlocal=SELECT*INTO“DB”.FROMOPENROWSETMSDAORA.lhydata;qyselect;qy08selectl2SELECT*FROMHybjsb.V_QY_CKLSZsqUocal=sqUocal+HwhereCONVERTsmalldatetimedb川date〈=+cstrdate+”andCONVERTsmalldatetimedbilldate=+cstrdate-Nday+sql」ocal二sql_local+“anddeptname二deptandstornameoffl®寄售库rs_sqlJocaLconnJocal!!2——判断是否有新记录sql_local=SELECTcountlasrecsql_local=sql_local+FROMDB.sql」ocal二sql_local+”wherebidnotinselectbidfromDB”.dbo.t_lywherelyrq=n+cstrdate+nandlyrq=+cstrdate-Nday+rs_sqUocalzconn_localzlzlrecords=rsjocaI“rec”rs_ifrecords0then——有新记录，追加记录sql_local=insertintoDB.dbo.t_lysql」ocal二sql_local+”bidlyrqdjhstornamedeptnamelyrcustnameinvclasscodeinvclassnamechbmjybjmclygglyxhlyjhgraphic!lydwjysljydjIyjesql_local=sql_local+SELECTsqLIocaI=sqIJocaI+HbidzCONVERTsmalldatetimedbilldateasdbilldatebillcodestornamedeptnamepsnnamecustnameinvclasscodezinvclassnamezinvcode/invname/invspec/invtypejnvmnecode/graphid/measnameznoutnumznpriceznoutmny

二、异构数据库异构数据库系统是相关的多个数据库系统的集合，可以实现数据的共享和透明访问，每个数据库系统在加入异构数据库系统之前本身就已经存在，拥有自己的DMBS0异构数据库的各个组成部分具有自身的自治性，实现数据共享的同时，每个数据库系统仍保有自己的应用特性、完整性控制和安全性控制异构数据库的异构性主要体现在以下几个方面

1、计算机体系结构的异构各数据库可以分别运行在大型机、小型机、工作站、PC嵌入式系统中

2、基础操作系统的异构各个数据库系统的基础操作系统可以是Unix、WindowsNT、Linux等

3、DMBS本身的异构可以是同为关系型数据库系统的Oracle.SQLServer等，也可以是不同数据模型的数据库，如关系、模式、层次、网络、面向对象，函数型数据库共同组成一个异构数据库系统

三、数据库同步技术sql_local=sqUocal+nwherebidnotinselectbidfromDB”.dbo.t_lywhere|yrq=n+cstrdate+andlyrq=+cstrdate-Nday+rs_sqUocalzconnJocalzlzllineline”******”dept”一date““time-newAdd+cstrrecords+记录.elselinelinedept—nonewdata!endif⑶delete删除记录sql_local=selectcountlasrecfromDB.dbo.t_lynsqUocal=sqUocal+nwherelyrq=n+cstrdate+nandlyrq=+cstrdate-Nday+”sql」ocal二sql」ocal+”andbidnotinselectbidfrom”DB”.“rs_sqlJocaLconriJocaLlJrecords=rsjocaIrec”rs_ifrecords0thensql_local=deleteDB.dbo.t_lysqUocal=sql_local+wherelyrq=+cstrdate+andlyrq=n+cstrdate-Nday+nnsqUocal=sqlJocal+andbidnotinselectbidfromDB”.rs_sqlJocaLconnJocal!1Iine”******”dept”---”date””time-delete+cstrrecords+记录.elselinedept—nodeletedata!endifupdate更新记录sqUocal=selectcountlasrecfromDB.rs_sqlJocaLconnJocaLlJrecords=rsjocalHrecrs_sql_local=updateDB.dbo.t_lysql」ocal=sql_local+“setlyrq=CONVERTsmalldatetimezT.dbilldatezdjh=T.billcodezstorname=T.storname/deptname=T.deptname/custname=T.custnamezsql」ocal二sql」ocal+“invclasscode=T.invclasscode/invclassname=T.invclassname/chbm=T.invcodejybjmc=T.invnamejygg二T.invspecjyxh=T.invtypeJsql_local=sql_local+lyjh=T.invmnecodezgraphid=T.graphid/lydw=T.measname/lysl=T.noutnumJydj=T.npncelyje=T.noutmnynsql_local=sql_local+fromDB.dbo.t_lyASADB.ASTsql」ocal=sql_local+”whereA.lyrq=n+cstrdate+nandA.lyrq=+cstrdate-Nday+”sql_local=sql_local+”andA.bid=T.bidrs_sqlJocaLconnJocal!!lineHdept“---updatedataH+cstrrecords+n记录.”Iineendsub

四、ACCESS与SQLServer的实时同步由于ACCESS数据本身的特点，桌面数据库不支持网络访问操作，因此要实现与SQLSERVER数据库的同步，需要进行目录共享访问设置具体实现如下:

1、共享ACCESS数据库目录，并赋予一admin权限的用户；

2、测试局域网共享访问\MP\目录…；

3、代码实现客户机共享ACCESS文件到服务器的拷贝；

4、在同一服务器上实现ACCESS到SQLSERVER数据的同步复制;

5、以下主要VBS代码实现了MDB文件复制及与SQLSERVER同步更新；===============ACCESS.MDB文件实时备份过程subbackupfiletxtfileipdbDimPathfsofsofilenet——初始化备份参数source_path=\\ip\产品数码跟踪target_file=D:\WEB\barcodeprinter\BACKUP\+trimdb.mdbifip=

10.

97.

1.41Hthensource_path=\\ipn\T品数码跟踪踪endifPath=nX:nSetfso=CreateObjectnystemObjectnSetfsofile=CreateObjectnystemObjectSetnet=CreateObjectrkHIfNotExistsiveNamePathThenOnErrorResumeNext出错不显示执行下一条tworkDrivePathsource_path/Falsezxcopy/xcopyifr=0then映射成功！执行拷贝source_file=path\OnErrorResumeNext出错不显示才丸行下一条ilesource_fiIeztarget_fiIeeNetworkDrivePathjrue取消映射else（—映射失败！）endifEndIfIfr0ThenIinen—.MDBcopy失败！elseline—.MDBcopy成功！endifsetfso=nothingsetfsofile=nothingsetnet=nothingendsub*****==========MDBTOSQLSERVER实时同步过程subMDBTOSQUdatabase^extfilelJpDimPathfsofsofilenetnum=rightipz2db=QJCFnum”_BARCODEPRINTER”db=trimdatabaseonerrorresumenextconnl=“PROVIDER=SQLOLEDBQATASOURCE=local;UID=sa;PWD=sa;DATABASE=n+dbsetrs=createobjectdsetninsertarrange插入操作sql=insertintodb.gesql=sql+idplannoarrangeidbarrangedateproducedatecigbrandcod匕cigbrandtradetypearrangenumdownnumprintnumJsql=sql+“applynumplanfinishflagsendbackflagconfirmflag”sql=sql+Hselectnsql=sql+“idplannoarrangeidbarrangedateproducedatecigbrandcodecigbrand/tradetypearrangenumdownnumprintnumJsql=sql+“applynumplanfinishflagsendbackflagconfirmflagfromopenrowset.OLEDB.

4.0zsql=sql+D:\web\barcodeprinter\backup\8tdb.mdb;admin;/arrangesql=sql+wherebarrangedatenotinselectbarrangedatefrom“db”.gewherebarrangedateisnotnullrs.opensqlconnl!!rs.closeupdatearrange更新操作sql=updateaset”sql=sql+a.printnum=b.printnum/a.applynum=b.applynumza.planfinishflag=b.planfinishflag/a.sendbackflag=b.sendbackflag/a.confirmflag=b.confirmflagIIsql=sql+nfromdb.gea”sql=sql+openrowset.OLEDB.

4.0zD:\web\barcodepnnter\backup\db.mdb;admin;/arrangebsql=sql+wherea.barrangedate=b.barrangedateanda.id二b.id对于异构数据库系统，实现数据共享应当达到两点:一是实现数据库转换;二是实现数据的透明访问所谓透明访问，就是在异构数据系统中用户可以将异构分布式数据库系统看成普通的分布式数据库系统，用自己熟悉的数据处理语言去访问数据库，如同访问一个数据库系统一样但目前还没有一种广泛使用的数据定义模型和数据查询语言，实现数据的透明访问可以采用多对一转换、双向的中间件等技术开放式数据库互连OpenDataBaseConnectivity简称ODBC是一种用来在相关或不相关的数据库管理系统中存取数据的标准应用程序接口APIODBC为应用程序提供了一套高层调用接口规范和基于动态链接库的运行支持环境目前，常用的数据库应用开发的前端工具如PowerBuilder.Delphi等都通过开放数据库互联ODBC接口来连接各种数据库系统而多数数据库管理系统如Oracle、Sybase.SQLServer等渚R提供了相应的ODBC驱动程序使数据库系统具有很好的开放性ODBC接口的最大优点是其互操作能力，理想情况下，每个驱动程序和数据源应支持完全相同的ODBC函数调用和SQL语句，使得ODBC应用程序可以操作所有的数据库系统然而，实际上不同的数据库对SQL语法的支持程度各不相同，因此，ODBC规范定义了驱动程序的一致性级别，ODBCAPI的一致性确定了应用程序所能调用的ODBC函数种类数据库同步是在两个不同的数据库之间进行数据交换，以使得任何一个数据库的改变，会以同样的方式出现在另一个数据库里数据库同步可以是单向的也可以是双向的单向同步也叫主从同步，也就是说，其中的一个数据库是主数sql=sql+nandconvertchar10/a.barrangedate/120convertchar10getdate-7z120IIrs.opensqlzconnllz2rs.closedeletehistoryarrange删除操作sql=deletedb.gewhereconvertchar10zbarrangedatez120convertchar10getdate-365120”sqLconnlL2rs.close五结束语尽管目前几家主流数据库厂商提供了数据库间数据的互连及访问方法，但从实际的应用经验来看，其同步交互操作较为复杂的，不便于维护；结合企业的实际应用，有针对性的设计同步解决方案不失为一种很好的方式据库，另一个数据库是从数据库只有主数据库的改变可以被复制到从数据库里去，从数据库是被动的使用数据库同步技术，用户可以将一份数据发布到多台服务器上从而使不同的服务器用户都可以在权限的许可的范围内共享这份数据同步技术可以确保分布在不同地点的数据自动同步更新，从而保证数据的一致性源系统图1数据库同步示意图目前国内外几家大型的数据库厂商提出的异构数据库复制方案主要有Oracle的透明网关技术，IBM的CCD表（一致变化数据表）方案，微软公司的出版者/订阅者方案国外的如土耳其中东科技大学的MIND系统，美国密歇根-迪尔波恩大学、斯坦福大学的TSUNMMIS项目，国内的如中关村科技软件有限公司、华中科技大学的Panorama系统都有过类似系统的研究但是由于上述系统致力于解决异构数据库间复杂的交互操作大而全并不符合一些中小企业的需求如IBM的CCD表方案实现复杂且CD表占用资源较多维护费用高昂微软公司的出版者/订阅者方案只能在NT运行等而其它的系统大多都存在此类问题

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

1、快照复制/

2、事务复制

13、合并复制4发布/订阅方案第一步来配置出版服务器

1、选中指定［服务器］节点

2、从［工具］下拉菜单的［复制］子菜单中选择［发布、订阅服务器和分发］命令

3、系统弹出一个对话框点［下一步］然后看着提示一直操作到完成

4、当完成了出版服务器的设置以后系统会为该服务器的树形结构中添加一个复制监视器同时也生成一个分发数据库distribution第二步创建出版物

1、选中指定的服务器

2、从［工具］菜单的［复制］子菜单中选择［创建和管理发布］命令此时系统会弹出一个对话框

3、选择要创建出版物的数据库，然后单击［创建发布］

4、在［创建发布向导］的提示对话框中单击［下一步］系统就会弹出一个对话框对话框上的内容是复制的三个类型我们现在选第一个也就是默认的快照发布其他两个大家可以去看看帮助

5、单击［下一步］系统要求指定可以订阅该发布的数据库服务器类型，SQLSERVER允许在不同的数据库如ORACLE或ACCESS之间进行数据复制但是在这里我们选择运行SQLSERVER2000的数据库服务器

6、单击［下一步］系统就弹出一个定义文章的对话框也就是选择要出版的表

7、然后［下一步］直到操作完成当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库第三步设计订阅L选中指定的订阅服务器

2、从［工具］下拉菜单中选择［复制］子菜单的［请求订阅］o

3、按照单击［下一步］操作直到系统会提示检查SQLSERVER代理服务的运行状态，执行复制操作的前提条件是SQLSERVER代理服务必须已经启动

4、单击［完成］完成订阅操作完成上面的步骤其实复制也就是成功了但是如何来知道复制是否成功了呢？这里可以通过这种方法来快速看是否成功展开出版服务器下面的复制一一发布内容——右键发布内容——属性——击活——状态然后点立即运行代理程序接着点代理程序属性击活调度把调度设置为每一天发生，每一分钟，在0:00:00和23:59:59之间接下来就是判断复制是否成功了打开C:/ProgramFiles/MicrosoftSQLServer/MSSQL/REPLDATA/unc/XIAOWANGZI_database_database下面看是不是有一些以时间做为文件名的文件夹差不多一分中就产生一个如果不确定是否创建成功，打开你的数据库看在订阅的服务器的指定订阅数据库下看是否能够看到刚才所发布的表

（二）、Oracle与SQLServer的互连及共享不同数据库平台的互连一般称之为数据库的异构服务，现在各大数据库之间都可以实现这样的异构互连，只是各厂商的具体实现技术不一样，如在SQLSERVER里面叫做LINKEDSERVER，通过ODBC实现与其它数据库的互联而ORACLE实现异构服务的技术叫做透明网关TransparentGateway当然之前ORACLE还采用过通用连接技术目前ORACLE利用透明网关可以实现和SQLSERVER、SYBASE.DB2等多种数据库的互联透明网关的体系结构也很简单，在ORACLE和SQLERVER之间使用ORACLE透明网关服务器实现互连互通，其中透明网关服务器可以与ORACLE或SQLSERVER数据库在同一台主机上，也可以是在独立的一台主机上测试步骤如下⑴、在SQLSERVER数据库上创建测试账号和表数据库PUBS账号cyxcreatetabletcchar10;

2、测试用数据库和透明网关在同一台机器上，透明网关在racle默认安装时是不安装的，所以如果你想用需要选择这一选项⑶、安装透明网关forsqlserver■的软件后可以在$ORACLE_HOME下看到tg4msql目录，编辑$ORACLE_HOME/tg4msql/admin/文件确认这一行正确HS_FDS_CONNECT_INFO=SERVER=IP;DATABASE=pubs

4、修改透明网关server上的，在SID.LIST中加入以下内容SID_NAME=tg4msql#SID自己命名ORACLEJHOME=c:PROGRAM=tg4msql

5、在oracleserver上的中加入到透明网关的tnsname内容如下sql2k=DESCRIPTION=ADDRESS_LIST=ADDRESSPROTOCOLTCPHOST

10.

16.

98.16PORT。