Syncing data between Oracle and non-Oracle RDBMS

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?

more ▼

asked Oct 29, 2009 at 08:09 PM in Default

avatar image

22 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Oct 29, 2009 at 08:37 PM

avatar image

Paul 6

(comments are locked)
10|1200 characters needed characters left

Hey Colin,

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.

more ▼

answered Oct 30, 2009 at 01:26 AM

avatar image

Jonah H. Harris
395 2 5

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 02, 2009 at 10:48 PM

avatar image

1k 2 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 29, 2009 at 08:09 PM

Seen: 2642 times

Last Updated: Oct 29, 2009 at 08:09 PM

Copyright 2018 Redgate Software. Privacy Policy