Home / server / Questions / 5308
Accepted
ggponti
Asked: 2009-05-07 05:44:56 +0800 CST

Best practices for SQL Server to Oracle data synchronization

  • 772

I have some tables in a SQL Server database that I want to make available to an Oracle database.

Keeping in mind that data integrity is the top priority, what are some best practices for keeping the data in sync?

sql-server oracle

2 Answers

  1. Best Answer

    This is the sort of thing that DTS/SSIS is commonly used for. One major warning, though, is that the out-of-box components for loading to an Oracle database are unacceptably slow for row counts over about 1000 (less if you need really fast loads). If I were doing this on a regular basis (every hour or so, for example), I'd probably use SSIS to create a package, but my load to Oracle would be through SQL*Loader (it's a ton faster). As for the data integrity piece, it's not too difficult to use SSIS to do the verification of the data, assuming you have relatively straightforward foreign key relationships. If you have extremely complex integrity constraints, you'll be better off creating a stored proc on the destination DB that you call with the SQL Server data after loading it into a staging table (this is the approach that Oracle EBS uses, so it's almost got to be a best practice by definition).

    • 3

  2. We have a similar situation, and use Golden Gate software.

    http://www.goldengate.com

    It's been really reliable and - keeps transactional integrity - can survive server downtime on either side

    It's also low overhead, since it tails the journal files and reconstructs the transactions, rather than performing queries on the source database.

    • 2