Does anyone have experience of this, perhaps using replication or log-shipping technologies? How easy is it to set up? What technologies did you use? Do you need specialist consultancy to get it up-and-running? What 'gotcha's would you point out to someone trying to do this for the first time?
Answer by Paul 6 ·
I've used SQL Server 2000 Data Transformation Services and SQLServer 2005 Integration Services, but any ETL could work.
Suggestions: 1. You need to make sure you handle the changes only (faster) 2. Create a template that you can reuse for all your tables (repeatable) 3. Setup logging within your package so you can validate they are working. 4. Setup email notifications so you get emails when a package fails. 5. Run & Schedule using SQL Server Agent Jobs
Answer by Jonah H. Harris ·
As each system offers different synchronization methods, for a non-generalized answer, you'd have to mention the desired non-Oracle DBMS you intend to sync with.
Personally, I've worked with data synchronization between Oracle and SQL Server, PostgreSQL, EnterpriseDB, Ingres, and MySQL.
SQL Server and EnterpriseDB are fairly easy as they support linking directly to Oracle. Postgres has a couple ways to link, but they're slow and unintuitive. MySQL and Postgres synchronization with Oracle can also be done using Lou Fangxin's asyncdata Perl script.
As for commercial products that can do this, I've found that GoldenGate was excellent. Though, I'm not sure about what Oracle's going to do with it now that they own it.