Replication across heterogenous databases

Question

Is it possible using SQL Server Replication to replicate data to AND from (bi-directional) Oracle and SQL Server? The schemas are completely different. In real-time would be a bonus.

Have already investigated Oracle Golden Gate, which seemed to do the job, although the licence cost is not insignificant!

I wondered if anyone has had any experience in replicating data across different schemas, and what other tools they employed? I realise this is a bit of an open-ended question but any advice and previous experiences would be most useful.

Thanks

Duncan

Answer 1

I recently had to create a solution to import periodically lots of data from different databases (most of the time from Oracle databases) to a SQL Server database (a data warehouse). To do so, I used SQL Server Integration Service to create a package able to import, transform and insert the data as I wanted (since it was from heterogeneous sources too). This software comes with SQL Server and the version 2005 and superior is really easy to use (graphical programming). In your case, you could trigger your created services when needed. I am not sure it is the best solution since you would need to create a SSIS service for each direction (from Oracle to SQL Server and from SQL Server to Oracle).