Sql-server – How to do data migration between different schemas

oraclereplicationsql serverssis

My company is in the process of migrating a customer's data from their third party system into our own system. We currently use a java application that we have developed in house that selects data from the source (third party database), transforms it into our schema, and inserts into the target (our application database). Unfortunately there are multiple issues with this method:

  • Inefficient. Our current customer has a huge amount of data and the total time for migrating the data is nearly 6 days of processing time.
  • Customer Downtime. The data is only is current up to the time we begin the migration. This means the customer must cease use of their current system during this time, leaving us a limited amount of time to perform the migration, with little room for error.
  • Error prone. The customer's data is full of inconsistencies that need handling. We also mostly rewrite this application to fit each customer, as there is little overlap in the systems they use. This requires large amounts of developer and QA time.

This must be a fairly common problem, as companies often move between applications and need to migrate their current data. Some possible solutions I am looking into are:

  • Oracle Data Integrator.
  • SQL Server Integration Services
  • Informatica.

Our own web application is designed to use either Oracle or SQL Server, so I am open to using products used with both Oracle and SQL Server databases.

Is there a solution that would allow continuous replication of a customer's source data into our target database while transforming the data into our schema?

Best Answer

Did I understand this correctly that your customer has MSSQL Server?

Source Database MSSQL or other

You have two options:

  • Script your own replication
  • Use Oracle Golden Gate to stream the data to your Oracle Database. Golden Gate supports "zero downtime" migration.

Source Database Oracle

Additionally to the options mentioned in "Source Database MSSQL or other" you can do:

  • Use Streams, Basic or Advanced Replication Features (requires Enterprise Edition, Basic Replication is included in SE, SE1 and SE2)
  • Export/Import DataPump (this does not support continuous replication)
  • Import DataPump via database link
  • Transportable Tablespace (requires Enterprise Edition)

There are several other posibilites but I need more details to recommend you one method:

  • How big is the customer's database? Please use the view dba_tables to get this information. The size of the datafiles does not reflect the actual size of a database.
  • How fast can you copy 1GB from the customer's side to your side?
  • Do you want to convert and cleanup the data while transfering it to your side?
  • Which method did you use when it took 6 days?
Related Question