Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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).

share|improve this answer
    
How robust is the SSIS approach when you're dealing with remote servers connected via VPN? –  ggponti May 6 '09 at 15:47
    
I haven't dealt with that situation - though it's as robust as anything else (it uses the same underlying access libraries). It also supports fairly thorough logging of problems (such as to a local SQL database), so that if there were VPN issues, you'd know that there was an error, and what it was (i.e., couldn't access the remote server). –  Harper Shelby May 6 '09 at 18:07

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.