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?
asked Oct 29 '09 at 08:09 PM in Default
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
answered Oct 29 '09 at 08:37 PM
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.
answered Oct 30 '09 at 01:26 AM
Jonah H. Harris
If you're going TO Oracle, then Migration Workbench via SQL Developer would be a good place to start. It's not a silver bullet, but it's pretty powerful and easy to step through.
answered Nov 02 '09 at 10:48 PM