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

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

一、 前  言
　　随着企业的不断发展，企业信息化的不断深入，企业内部存在着各种各样的异构软、硬件平台，形成了分布式异构数据源。当企业各应用系统间需要进行数据交流时，其效率及准确性、及时性必然受到影响。为了便于信息资源的统一管理及综合利用，保障各业务部门的业务需求及协调工作，常常涉及到相关数据库数据实时同步处理。基于数据库的各类应用系统层出不穷，可能涉及到包括ACCESS、SQLSERVER、ORACLE、DB2、MYSQL等数据库。目前国内外几家大型的数据库厂商提出的异构数据库复制方案主要有：Oracle的透明网关技术，IBM的CCD表（一致变化数据表）方案，微软公司的出版者/订阅等方案。但由于上述系统致力于解决异构数据库间复杂的交互操作，过于大而全而且费用较高，并不符合一些中小企业的实际需求。
　　本文结合企业的实际应用实践经验，根据不同的应用类型，给出了相应的数据库实时同步应用的具体解决方案，主要包括：

　　(1)  SQLSERVER 到SQLSERVER 同步方案
　　(2)  ORACLE 到 SQLSERVER 同步方案
　　(3)  ACCESS 到SQLSERVER/ORACLE 同步方案

二、 异构数据库
　　异构数据库系统是相关的多个数据库系统的集合，可以实现数据的共享和透明访问，每个数据库系统在加入异构数据库系统之前本身就已经存在，拥有自己的DMBS。异构数据库的各个组成部分具有自身的自治性，实现数据共享的同时，每个数据库系统仍保有自己的应用特性、完整性控制和安全性控制。异构数据库的异构性主要体现在以下几个方面：
1、计算机体系结构的异构
　　各数据库可以分别运行在大型机、小型机、工作站、PC嵌入式系统中。
2、基础操作系统的异构
　　各个数据库系统的基础操作系统可以是Unix、Windows NT、 Linux等。

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

三、 数据库同步技术
　　对于异构数据库系统，实现数据共享应当达到两点：一是实现数据库转换；二是实现数据的透明访问。所谓透明访问，就是在异构数据系统中用户可以将异构分布式数据库系统看成普通的分布式数据库系统，用自己熟悉的数据处理语言去访问数据库，如同访问一个数据库系统一样。
　　但目前还没有一种广泛使用的数据定义模型和数据查询语言，实现数据的透明访问可以采用多对一转换、双向的中间件等技术。开放式数据库互连（Open DataBase Connectivity,简称ODBC）是一种用来在相关或不相关的数据库管理系统中存取数据的标准应用程序接口（API）。ODBC为应用程序提供了一套高层调用接口规范和基于动态链接库的运行支持环境。目前，常用的数据库应用开发的前端工具如Power Builder、 Delphi等都通过开放数据库互联(ODBC)接口来连接各种数据库系统。而多数数据库管理系统（如：Oracle、Sybase、SQL Server等）都提供了相应的ODBC驱动程序，使数据库系统具有很好的开放性。ODBC接口的最大优点是其互操作能力，理想情况下，每个驱动程序和数据源应支持完全相同的ODBC函数调用和SQL语句，使得ODBC应用程序可以操作所有的数据库系统。然而，实际上不同的数据库对SQL语法的支持程度各不相同，因此，ODBC规范定义了驱动程序的一致性级别，ODBC API的一致性确定了应用程序所能调用的ODBC函数种类。
　　数据库同步是在两个不同的数据库之间进行数据交换，以使得任何一个数据库的改变，会以同样的方式出现在另一个数据库里。数据库同步可以是单向的，也可以是双向的。单向同步也叫主从同步，也就是说，其中的一个数据库是主数据库，另一个数据库是从数据库。只有主数据库的改变可以被复制到从数据库里去，从数据库是被动的。
　使用数据库同步技术，用户可以将一份数据发布到多台服务器上，从而使不同的服务器用户都可以在权限的许可的范围内共享这份数据。同步技术可以确保分布在不同地点的数据自动同步更新，从而保证数据的一致性。

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

四、 局域网数据同步解决方案
(一) .SQLSERVER 到SQLSERVER同步复制 
 SQLSERVER 同类数据库之间实现同步，可直接参照Microsoft 的发布/订阅模式。具体方法步骤如下： 

　　(1)  SQL复制的基本元素包括
　　出版服务器、订阅服务器、分发服务器、出版物、文章。
　　(2)  SQL复制的工作原理
　　SQLSERVER主要采用出版物、订阅的方式来处理复制。源数据所在的服务器是出版服务器，负责发表数据。出版服务器把要发表的数据的所有改变情况的拷贝复制到分发服务器，分发服务器包含有一个分发数据库，可接收数据的所有改变，并保存这些改变，再把这些改变分发给订阅服务器。
　　(3) SQLSERVER复制技术类型
　　SQLSERVER提供了三种复制技术，分别是：1、快照复制/2、事务复制/3、合并复制。

　　(4)发布/订阅方案

　　第一步：来配置出版服务器

　　1、选中指定[服务器]节点。

　　2、从[工具]下拉菜单的[复制]子菜单中选择[发布、订阅服务器和分发]命令。
　　3、系统弹出一个对话框点[下一步]然后看着提示一直操作到完成。
　　4、当完成了出版服务器的设置以后系统会为该服务器的树形结构中添加一个复制监视器。同时也生成一个分发数据库(distribution)。
　　第二步：创建出版物
　　1、选中指定的服务器。
　　2、从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令。此时系统会弹出一个对话框。
　　3、选择要创建出版物的数据库，然后单击[创建发布]。
4、在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)。
　　5、单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型，SQLSERVER允许在不同的数据库如ORACLE或ACCESS之间进行数据复制。但是在这里我们选择运行"SQLSERVER2000"的数据库服务器。
　　6、单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表。
　　7、然后[下一步]直到操作完成。当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库。
　　第三步：设计订阅

　　1、选中指定的订阅服务器。
　　2、从[工具]下拉菜单中选择[复制]子菜单的[请求订阅]。
　　3、按照单击[下一步]操作直到系统会提示检查SQLSERVER代理服务的运行状态，执行复制操作的前提条件是SQLSERVER代理服务必须已经启动。
　　4、单击[完成]。完成订阅操作。
　　完成上面的步骤其实复制也就是成功了。但是如何来知道复制是否成功了呢？这里可以通过这种方法来快速看是否成功。展开出版服务器下面的复制——发布内容——右键发布内容——属性——击活——状态然后点立即运行代理程序接着点代理程序属性击活调度把调度设置为每一天发生，每一分钟，在0：00：00和23：59：59之间。接下来就是判断复制是否成功了打开。C:/ProgramFiles/MicrosoftSQLServer/MSSQL/REPLDATA/unc/XIAOWANGZI_database_database下面看是不是有一些以时间做为文件名的文件夹差不多一分中就产生一个。如果不确定是否创建成功，打开你的数据库看在订阅的服务器的指定订阅数据库下看是否能够看到刚才所发布的表。
(二) 、Oracle与SQL   Server的互连及共享    

　　不同数据库平台的互连一般称之为数据库的异构服务，现在各大数据库之间都可以实现这样的异构互连，只是各厂商的具体实现技术不一样，如：在SQL   SERVER里面叫做LINKED   SERVER，通过ODBC实现与其它数据库的互联。  

　　而ORACLE实现异构服务的技术叫做透明网关Transparent Gateway，当然之前ORACLE还采用过通用连接技术。目前ORACLE利用透明网关可以实现和SQLSERVER、SYBASE、DB2等多种数据库的互联。

　　透明网关的体系结构也很简单，在ORACLE和SQL ERVER之间使用ORACLE透明网关服务器实现互连互通，其中透明网关服务器可以与ORACLE或SQLSERVER数据库在同一台主机上，也可以是在独立的一台主机上。
   　　测试步骤如下： 
(1)、在SQLSERVER数据库上创建测试账号和表 
数据库:  PUBS，账号cyx， 
create   table   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_NAME   =   tg4msql)   #   SID自己命名    
    (ORACLE_HOME   =   c:)    
    (PROGRAM   =   tg4msql) 
  　　(5)、在oracle   server上的tnsnames.ora中加入到透明网关的tnsname，内容如下： 
  sql2k   =  

  (DESCRIPTION   =  

  (ADDRESS_LIST   = 

 (ADDRESS   =   (PROTOCOL   =   TCP)(HOST   =   10.16.98.16)(PORT   =   1521))    #此处HOST填的是透明网关SERVER的地址

  )  

  (CONNECT_DATA   =   (SID   =   tg4msql)   )  

 #此SID应和透明网关SERVER上设定的SID相同  

  (HS=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、Oracle  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、各数据库中特殊的数据类型，需要在程序中实现转换，应尽量避免使用无法转换的数据类型。

(三) 、Oracle与SQL   Server的实时同步
　　尽管透明网关技术实现了OQACLE 与 SQLSERVER 的互连，但在同步应用中不便于控制同步数据的要求，特别是ORACEL与SQLSERVER 存在数据类型转换时存在困难。以下给出同步数据代码样例如下：
　　为实现ORACLE 到SQLSERVER 的数据同步，首先需在ORACLE　中建一需同步的数据视图，以便于操作访问。
　　1、在ORACLE中创建需同步的数据视图
　　2、配置局域网客户端建立 tnsname.ora 文件，使其能访问远程 ORACLE 数据库
　　3、在SQLSERVER 中创建与ORACLE 视图同构的数据库表
　　4、定义同步方式及步骤
　　5、将VBS转换成EXE 文件，并将EXE 程序制作成windows  服务，以便在后台运行。
　　6、以下是ORACLE 到 SQLSERVER数据同步主要VBS示例代码；

========== oracleTOsqlserver 数据同步脚本  =====================

  1. 检测远程oracle服务器网络是否连通,  通则连接;
  2. 连接oracle; 连接sqlserver；取N天的cklsz记录集；
  3. 取 oracle的dataset; not in sqlserver 中的insert to sqlserver;
  4. 日志写入txt文件，以日期为文件名称，只保留7天的日志; 

============================================================
Do While True
dim f,txtfile
----   创建日志文件对象, 判定7天前的log 文件是否存在，存在则删除！----
Set f = CreateObject("Scripting.FileSystemObject")
set txtfile = f.opentextfile("D:\wxjl\oracleTOsqlserver\"&date&".txt",8,True)
If f.FileExists("D:\wxjl\oracleTOsqlserver\"&date-7&".txt") Then
   f.DeleteFile("D:\wxjl\oracleTOsqlserver\"&date-7&".txt")

end if
------  ping oracle 服务器，连通--> 则连接 oracle/sqlserver ------------

ip="10.98.51.82"
Set FileSys   =   CreateObject("Scripting.FileSystemObject")
Set WShShell   =  CreateObject("WScript.Shell")   
RetCode   =   WShShell.Run("ping -n 1 " &IP , 0, True ) 
if   RetCode   =   0   Then
  txtfile.write(date&" "&time&" --- ping anywhere host  ok ! ") 

  --------- 测试连接远程 oracle ---- ( OraOLEDB.Oracle.1/ MSDAORA.1 )

  conn_ora  ="Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=qyselect;Password=qy08select12;Data Source=hydata" 

  set rs_ora=createobject("adodb.recordset")

  sql_ora="select * from Hybjsb.V_QY_CKLSZ"
  Err.clear

  On Error Resume Next ---  出错不显示,执行下一条

  rs_ora.open sql_ora,conn_ora,1,1

  rs_ora.close
  If Err.Number=0 Then
  txtfile.writeline("--- connect anywhere database successful ! ")
  -------- 连接本地 sqlserver, insert sqlserver
  conn_local   = "PROVIDER=SQLOLEDB;DATA  SOURCE=local;UID=sa;PWD=sa"

  set rs_local = createobject("adodb.recordset")
 -- 更新 30天以内的数据
  Nday=30

  dept="卷包车间"

  DB="wxjl_1bjb"

  call xcopy

   --------------------------------------------------------
  else

  txtfile.writeline("--- *****  远程数据库连接失败 ! ***** ")

  end if

else
  txtfile.writeline(date&" "&time&" --- #####   网络故障 !!!! ####### ")

end if
releaseconnection conn_ora
releaseconnection conn_local

set f=nothing
set txtfile=nothing
set rs_local=nothing
set rs_ora=nothing
间隔 5分钟循环一次
WScript.Sleep 300000
Loop
-------------  数据同步过程 -----------------------------------------
Sub xcopy()
dim records
(0) ------ 判断 temp 是否存在，存在则删除

  sql_local="select count(1) as rec from "&DB&".dbo.sysobjects where name=temp and xtype=U"

  rs_local.open sql_local,conn_local,1,1

  records=rs_local("rec")

  rs_local.close
  if records > 0 then

  sql_local="drop table "&DB&".dbo.temp"

  rs_local.open sql_local,conn_local,1,1

  end if
  (1) ------ 取当前日期前Nday天的数据插入临时表 temp
  sql_local="SELECT * INTO "&DB&".dbo.temp FROM OPENROWSET(MSDAORA.1,hydata;qyselect;qy08select12,SELECT * FROM 

  Hybjsb.V_QY_CKLSZ) "

  sql_local=sql_local+" where CONVERT(smalldatetime,dbilldate)<="+cstr(date)+" and CONVERT(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 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<="+cstr(date)+" and lyrq>="+cstr(date-Nday)+")"
  rs_local.open sql_local,conn_local,1,1

  records=rs_local("rec")

  rs_local.close
  if records > 0 then  ---- 有新记录, 追加记录

  sql_local="insert into "&DB&".dbo.t_ly"

  sql_local=sql_local+"(bid,lyrq,djh,storname,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,invclassname,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)+" and lyrq>="+cstr(date-Nday)+")"

  rs_local.open sql_local,conn_local,1,1

  txtfile.writeline(" ")

  txtfile.writeline(" ****** "&dept&"     --- "&date&" "&time&" ------> new Add  "+cstr(records)+" 记录.")

  else

  txtfile.writeline(" ")

  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)+" 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,1  

  records=rs_local("rec")

  rs_local.close 

  if records >0 then

  sql_local="delete "&DB&".dbo.t_ly"

  sql_local=sql_local+" 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,1 
  txtfile.writeline(" ****** "&dept&"     --- "&date&" "&time&" ------> delete "+cstr(records)+" 记录.")

  else
  txtfile.writeline(" ------ "&dept&"     --- no delete data! ") 

  end if
(4) update ----------- 更新记录
  sql_local="select count(1) as rec from  "&DB&".dbo.temp"

  rs_local.open sql_local,conn_local,1,1

  records=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.deptname,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.noutmny"

  sql_local=sql_local+" from "&DB&".dbo.t_ly AS A, "&DB&".dbo.temp AS T"

  sql_local=sql_local+" where A.lyrq<="+cstr(date)+" and A.lyrq>="+cstr(date-Nday)+""

  sql_local=sql_local+" and A.bid=T.bid"

  rs_local.open sql_local,conn_local,1,1  

  txtfile.writeline(" ------ "&dept&"     --- update data ------>  "+cstr(records)) +" 记录."

  txtfile.writeline(" ")

end sub
------------------------------------------------------------------------
（四）、ACCESS与SQL   Server的实时同步
由于ACCESS数据本身的特点，桌面数据库，不支持网络访问操作，因此要实现与SQLSERVER数据库的同步，需要进行目录共享访问设置。具体实现如下：
1、 共享ACCESS 数据库目录，并赋予一admin权限的用户；
2、 测试局域网共享访问：\\IP\目录\... ；
3、 代码实现客户机共享ACCESS文件到服务器的拷贝；
4、 在同一服务器上实现ACCESS到SQLSERVER 数据的同步复制；
5、 以下主要VBS代码实现了MDB文件复制及与SQLSERVER同步更新；
=============== ACCESS .MDB 文件实时备份过程 ==================
sub backupfile(txtfile,ip,db)
Dim Path,fso,fsofile,net
------ 初始化备份参数
source_path="\\"&ip&"\产品数码跟踪"
target_file ="D:\WEB\barcodeprinter\BACKUP\"+trim(db)&".mdb"
if ip="10.97.1.41" then
 source_path="\\"&ip&"\产品数码跟踪1"

end if
err.clear
Path="X:"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsofile = CreateObject("Scripting.FileSystemObject")
Set net = CreateObject("WScript.Network")
If Not fso.DriveExists(fso.GetDriveName(Path)) Then
 err.clear

 On Error Resume Next 出错不显示,执行下一条
 net.MapNetworkDrive Path, source_path,False,"xcopy","xcopy"
 if err.number=0 then

 txtfile.write("--->  映射成功! ")
 -------------------- 执行拷贝

 source_file=path&"\barcodeprinter.mdb"

 err.clear

 On Error Resume Next 出错不显示,执行下一条
 fsofile.copyfile source_file,target_file
 net.RemoveNetworkDrive Path,True ------  取消映射
 else

 txtfile.write("--->  映射失败! ")

 end if  
End If
If Err.Number<>0 Then
txtfile.writeline("--->   .MDB copy 失败! ")
else
txtfile.writeline("--->   .MDB copy 成功! ")
end if
set fso=nothing
set fsofile=nothing
set net=nothing
end sub
*****
==========   MDB TO SQLSERVER实时同步过程==========
sub MDBTOSQL(database,textfile1,ip)
Dim Path,fso,fsofile,net
num=right(ip,2)
db="QJCF"&num&"_BARCODEPRINTER"
db=trim(database)
Err.clear
on error resume next
conn1 = "PROVIDER=SQLOLEDB;DATA  SOURCE=local;UID=sa;PWD=sa;DATABASE="+db
set rs = createobject("adodb.recordset")
--------- insert arrange  插入操作 ----------------
sql="insert into "&db&".dbo.arrange"
sql=sql+"(id,planno,arrangeid,barrangedate,producedate,cigbrandcode,cigbrand,tradetype,arrangenum,downnum,printnum,"
sql=sql+"applynum,planfinishflag,sendbackflag,confirmflag)"
sql=sql+" select "
sql=sql+"id,planno,arrangeid,barrangedate,producedate,cigbrandcode,cigbrand,tradetype,arrangenum,downnum,printnum,"
sql=sql+"applynum,planfinishflag,sendbackflag,confirmflag from openrowset(Microsoft.Jet.OLEDB.4.0,"
sql=sql+" D:\web\barcodeprinter\backup\"&db&".mdb;admin;, arrange) "
sql=sql+"where barrangedate not in(select barrangedate from "&db&".dbo.arrange where barrangedate is not null)"
rs.open sql,conn1,1,1
rs.close
-------- update arrange  更新操作 ----------------
sql="update a set "
sql=sql+" a.printnum=b