从ORACLE向SQL SERVER定时迁移作业的设计与实现

问题描述

某事业单位很早以前开发了一套基于Oracle7.03数据库的管理系统,工作在WINDOWS 下,采用C/S工作模式,数据库的字符集为WE8ISO8859P1。由于工作需要,需开发一套在此基础上的查询系统。为保证原系统的安全和完整性,要求查询系统不得直接使用原数据库,影响目前系统的运行。只能通过中间件技术实现查询系统对原数据库的访问,同时由于原系统在使用过程中发现数据存取的速度很慢,要求查询系统使用SQL SERVER 2000数据库进行查询。

二、解决方案分析

根据用户的需求和原系统的工作模式,可采用的方案主要有以下三种:

1、  利用SQL SERVER 的作业调度功能,定时执行数据迁移,实现数据同步。

DTS(数据转换服务)是微软从SQL SERVER 7.0 开始引入的。DTS的主要目的是在系统之间迁移数据和数据库对象。DTS原来是用作SQL SERVER OLAP服务的ETL工具。后来微软意识到DTS 不仅可以作为OLAP 服务的数据抽取和载入工具,还可以实现异种数据库间的迁移,因此扩充了DTS的功能。在SQL SERVER 2000中提供了简单易用的DTS 设计器。利用DTS设计器可以很方便地解决本文涉及的问题。但是,如果要迁移的对象比较多,利用DTS设计器的工作量就相当大了。因此,提出了第二种解决方案。

2、  利用DTS 编程实现数据的定时迁移。

该方法原理简单,但需要对 DTS 有一定的了解,性能也比较好。熟悉VBVCDELPHI等任一种编程语言,均可以利用SQL Server 提供的 DTS COM接口实现数据的迁移。

DTS 迁移规范保存在一个称为包的实体中,DTS包是基本的DTS组件的容器,这些组件包括连接、任务、转换、工作流,不同的组件完成不同的功能,它们共同构成数据迁移的实现主体。要通过DTS编程实现数据库的迁移,至少需要两个连接对象。其中,一个提供数据,一个接收数据;至少需要一个转换对象,完成数据从源到目的服务器的转换;至少需要两个任务对象,完成迁移之前的目的服务器上的数据表的删除和重建;至少需要三个工作流对象,为迁移工作设计执行的步骤。

为了实现定时执行,程序还要完成对SQL SERVER AGENT 进行编程实现迁移作业的提交和调度。由于SQL SERVER 的作业调度是通过 SQL SERVER AGENT 来管理的,因此需要在启动SQL SERVER 时同时启动SQL SERVER AGENT

3、  采用中间件技术

前面两种方案都是利用DTS,离不开SQL Server DTS。利用中间件技术,可以通过实现一服务程序,定时将数据从ORACLE服务器取出然后转换成SQL SERVER 数据库的数据格式,传入SQL SERVER。其工作原理如下图:

该技术可以通过通过ODBC OLE DB技术编程实现数据的定时获取和转换传出。对于编程的工作量较大,原因在于ORACLE SQL SERVER的数据类型的不一致必须通过类型转换实现数据的一致。同时效率也比较低。由于作为一种服务程序长驻内存,对程序的质量要求至少不得出现内存泄露,否则,可能使服务器瘫痪。不过这种方案的好处在于可以脱离SQL SERVER ,维护的工作量相对要轻一些。

比较上述的三种方案,从实现的难度上比较,第一种最低,最后一种最高。从效率上比较,最后一种最低,第一种与第二种最高。从可维护性来比较,第一种最低,最后一种最高。

综合三种方案,笔者认为第二中方案较好。发布到目标系统上,只需在现场运行一次数据迁移的任务安装程序,就能实现SQL Server 定时从Oracle服务器迁移数据。同时,所有的工作量也只是选择要迁移的数据表。该工作,如果要迁移的表是已知的,甚至可以从文件中直接读入,就能实现任务的安装。

下面介绍采用第二种方案用VB编写在SQL SERVER上能定时自动进行数据迁移任务的安装程序的方法。

三、数据迁移的实现

为了能在目标机上顺利实现数据迁移,将DTS包存储到SQL SERVER,在SQL SEVRE AGENT  的作业调度中采用DTSRUN 来加载和执行DTS包。这样,所有的工作只需作一次,就可将整个数据迁移的DTS包和SQL SERVER的作业发布到目标机上。

(一)算法设计

程序的流程图如下:      

(二)关键技术说明

要实现数据的迁移,必须考虑两个问题,第一、数据的迁移要求目标系统上的数据与ORACLE  数据库中的数据要一致,因此,目标数据库中的相应表必须在迁移之前被删除。所以迁移任务的第一个是对相应表执行删除的SQL 任务;第二、由于目标表被删除,迁移的数据失去寄托,因此迁移任务的第二步必须在目标系统上重建相应的表。在建表时,由于ORACLE 数据库的数据类型与SQL SERVER 不一致,因此必须进行类型转换。它们之间的对应关系和转换要求如下表:

 

Oracle

Microsoft SQL Server

CHAR

建议使用CHAR 类型。这种类型的数据由于采用固定长度存储,其存取速度在某些情况下要比使用 VARCHAR 类型快。

VARCHAR2
LONG

VARCHAR TEXT (如果在ORACLE 中的长度超过8000字节应在SQL SERVER  中使用 TEXT 类型,否则使用 VARCHAR 数据类型。)

RAW
LONG RAW

VARBINARY IMAGE. (如果在ORACLE 中的长度超过8000字节应在SQL SERVER  中使用 IMAGE 类型,否则使用 VARBINARY 数据类型。)

NUMBER

如果整型在0 255 之间,用TINYINT
如果整型在-32768 32767之间,用  SMALLINT
如果整型在-2,147,483,648 2,147,483,647 之间,用 INT
如果需要浮点数,使用 NUMERIC (ORACLE 数据列中有PRECISION SCALE).
注意:

    1、尽量不要使用 FLOAT REAL ,这是因为在转换时数据有四舍五入。

    2、如果自己的把握性不大,最好使用 NUMERIC,该类型与ORACLENUMBER 类型最接近。

DATE

DATETIME

 

(三)程序设计

1、  界面设计,如图所示:

 

2、  编码

(1) 在整个程序运行过程中,需要两个全局变量:DTS oPackage Oracle 服务器连接 oraCon

当设置好连接参数后,单击“连接Oracle服务器”,将与 ORACLE 服务器连接,并取出所有表。然后填充第一个列表框,并在下面表格中显示相应的数据(如果选中了“显示数据”)

Private Sub Command1_Click()

   Dim rst As New ADODB.Recordset

   With oraCon

      .Provider = "OraOLEDB.Oracle.1"

      .Properties("User ID") = Text2(0).Text

      .Properties("Password") = Text3(0).Text

      .Properties("Data Source") = Text1(0).Text

      .Properties("Persist Security Info") = True

      .Open

   End With

               

   With rst

      .Source = "select * from all_tables where tablespace_name<>'SYSTEM' order by owner,table_name"

      .ActiveConnection = oraCon

      .CursorType = adOpenKeyset

      .LockType = adLockOptimistic

      .Open

   End With

   RefreshGrid rst

   FillTabList rst

   rst.Close

   Command1.Enabled = False

End Sub

(2) 在第一个列表框中双击数据表列表,将选中的表加入第二个列表,如果“显示数据”被选中,将显示相应表的数据。同时显示表的结构信息。在第二个列表框中双击数据表将选中项移出该列表框。

 查看代码>>

3)选择完要迁移的数据表后,执行“生成中间件”,将在SQL SERVER 服务器中生成能复制数据的DTS 包。并设置相应的执行顺序。关键代码如下:

 查看代码>>

4)在SQL SERVER AGENT 的作业中生成能定时(本文设置为每天从早晨6:30至晚上1159每隔6个小时执行一次生成的DTS 包实现数据迁移。

Private Sub GenTaskAgent()

    Dim strCmd As String

    strCmd = ""

   

    strCmd = strCmd & " BEGIN TRANSACTION" & vbCrLf

    strCmd = strCmd & "DECLARE @JobID BINARY(16)" & vbCrLf

    strCmd = strCmd & "DECLARE @ReturnCode INT" & vbCrLf

    strCmd = strCmd & "SELECT @ReturnCode = 0" & vbCrLf

    strCmd = strCmd & "IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1" & vbCrLf

    strCmd = strCmd & "EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'" & vbCrLf

    strCmd = strCmd & "SELECT @JobID = job_id" & vbCrLf

    strCmd = strCmd & "From msdb.dbo.sysjobs" & vbCrLf

    strCmd = strCmd & "WHERE (name = N'OraToSQL')" & vbCrLf

    strCmd = strCmd & "IF (@JobID IS NOT NULL)" & vbCrLf

    strCmd = strCmd & "BEGIN" & vbCrLf

    strCmd = strCmd & "IF (EXISTS (SELECT  *" & vbCrLf

    strCmd = strCmd & "          From msdb.dbo.sysjobservers" & vbCrLf

    strCmd = strCmd & "          WHERE   (job_id = @JobID) AND (server_id <> 0)))" & vbCrLf

    strCmd = strCmd & "BEGIN" & vbCrLf

    strCmd = strCmd & "RAISERROR (N'无法导入作业“OraToSQL_Job”,因为已经有相同名称的多重服务器作业。', 16, 1)" & vbCrLf

    strCmd = strCmd & "GoTo QuitWithRollback" & vbCrLf

    strCmd = strCmd & "End" & vbCrLf

    strCmd = strCmd & "Else" & vbCrLf

    strCmd = strCmd & "EXECUTE msdb.dbo.sp_delete_job @job_name = N'OraToSQL_Job'" & vbCrLf

    strCmd = strCmd & "SELECT @JobID = NULL" & vbCrLf

    strCmd = strCmd & "End" & vbCrLf

 

    strCmd = strCmd & "BEGIN" & vbCrLf

    strCmd = strCmd & "EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'OraToSQL_Job', @owner_login_name = N'sa', @description = N'Nothing', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 3, @delete_level= 0" & vbCrLf

    strCmd = strCmd & "IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback" & vbCrLf

 

    strCmd = strCmd & "EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'CopyToSQL', @command = N'DTSRun  /s " & Text4.Text & " /u sa /p " & Text3(1).Text & _

                                  " /n OraToSql', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 2, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'C:/DTS.Log', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2" & vbCrLf

    strCmd = strCmd & "IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback" & vbCrLf

    strCmd = strCmd & "EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1" & vbCrLf

 

    strCmd = strCmd & "IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback" & vbCrLf

    strCmd = strCmd & " EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'EverDay Do', @enabled = 1, @freq_type = 4, @active_start_date = 20030305, @active_start_time = 63000, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 6, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959" & vbCrLf

    strCmd = strCmd & "IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback" & vbCrLf

    strCmd = strCmd & "EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'" & vbCrLf

    strCmd = strCmd & "IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback" & vbCrLf

 

    strCmd = strCmd & "End" & vbCrLf

    strCmd = strCmd & "COMMIT TRANSACTION" & vbCrLf

    strCmd = strCmd & "GoTo EndSave" & vbCrLf

    strCmd = strCmd & "QuitWithRollback:" & vbCrLf

    strCmd = strCmd & "IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION" & vbCrLf

    strCmd = strCmd & "EndSave:" & vbCrLf

   

    Dim oSqlCon As ADODB.Connection

    Dim oSqlCmd As New ADODB.Command

   

    Set oSqlCon = New ADODB.Connection

    With oSqlCon

      .Provider = "SQLOLEDB"

      .Properties("User ID") = Text2(1).Text

      .Properties("Password") = Text3(1).Text

      .Properties("Data Source") = Text4.Text

      .Properties("Initial Catalog") = Text1(1).Text

      .Properties("Persist Security Info") = True

      .Open

    End With

    With oSqlCmd

       .ActiveConnection = oSqlCon

       .CommandText = strCmd

       .Execute

    End With

    oSqlCon.Close

End Sub

安装程序运行完后进入SQL SERVER 企业管理器,应能在“数据转换服务”的“本地包”中发现一名为“OraToSql”的本地DTS包,同时在“SQL SERVER 代理”的“作业”中应有一“OraToSQL_Job”的作业。都可以进行修改。

四、结束语

本文介绍的数据库迁移技术,不仅适用于ORACLE

 

 

源代码见自己的邮箱

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值