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 – Best practices for SQL Server to Oracle data synchronization
oraclesql server
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).