Incremental upload of data from Oracle to SQL server

• Sonia-277983

  SSC Enthusiast

  Points: 159

  More actions

  November 16, 2005 at 2:21 am

  #102931

  Our client has their data in Oracle.  We need to copy (modified, new or delete) data from Oracle to our SQL server on a daily basis and auto-schedule this process.  What is the best way to do this?

  1) Is it better to push data from Oracle to SQL server using Transparent gateway? 2) Or is it better to pull from Oracle using DTS jobs? 3) Or can we write procedures to accomplish this - In this case, procedures should be written in Oracle or SQL?  Appreciate any of your help.

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  November 16, 2005 at 5:18 am

  #604150

  One alternative is to use a database replication tool. My experiences with database replication have been very bad, as the vendor tools from MS and Oracle seem to be very fragile, buggy and tend to use a significant amount of resources. The only exception is Sybase's Replication Server, which I did use in 1999 and had no problems. The disadvantage of replication is that the target schema must be a sub-set of the source schema i.e. you must have the same tables although a sub-set of the table columns is allowed. See

  http://www.sybase.com/products/informationmanagement/replicationserver

  There are at least 3 other approaches:

  1. Refresh the data on a periodic basis (snapshot)

  2. Use a snapshot and then compare to determine changes

  3. Write your own replication tool.

  For options 2 and 3, you would still need to initialize with a snapshot.

  A snapshot would:

  1. Truncate the SQL Server table

  2. Query the Oracle tables

  3. Insert into the SQL Server table

  This can easily be handled with DTS and also has the advantage that the Oracle and SQL Server schemas would not be required to match.

  Use a snapshot and then compare to determine changes

  1. Create a staging table in SQL Server

  2. Truncate the staging table

  3. Query the Oracle tables

  4. Insert into the staging table

  5. Have SQL that insert, updates and deleted by comparing the staging and target tables.

  Write your own replication tool which works the same way as Oracle Replication:

  For each table to be replicated:

  1. In Oracle, create a table to track changes that would include action (add, change, delete) and a datetime of the change and all of the columns needed.

  2. In Oracle, write triggers for insert, update and delete that would insert into the tracking table.

  3. In SQL Server, create a tracking table that matchs the Oracle tracking table.

  4. On SQL Server, write SQL that insert, updates and deleted from the change tracking table target tables.

  The replication process would then be:

  1. In Oracle, record the current date and time.

  2. Copy the changes to a SQL Server change-tracking table from the Oracle change-tracking table that occurred before the datetime.

  3. On SQL Server, run the SQL to change the table target tables.

  4. Clean up the Oracle tracking table by deleting all rows that have a datetime prior to the copy start datetime.

  5. Truncate the SQL Server change-tracking.

  SQL = Scarcely Qualifies as a Language