x

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

user-57 gravatar image

user-57
22 1 1 2

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

3 answers: sort newest

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

HillbillyToad gravatar image

HillbillyToad
1k 2

(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

Jonah H. Harris gravatar image

Jonah H. Harris
395 3

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

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

Paul 6 gravatar image

Paul 6
11

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x305
x64
x4

asked: Oct 29, 2009 at 08:09 PM

Seen: 1873 times

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