How to automatically push data from SQL Server to Oracle?

Question

I have users entering data in SharePoint (Running on SQL Server), but my application to view that data will be an Oracle Apex app running on Oracle, obviously. How do I have the data be pushed into the Oracle db automatically?

Answer 1

First off, are you sure that you need to replicate the data to Oracle? Oracle Heterogeneous Services allows you to create a database link in Oracle that connects to a non-Oracle database using ODBC (assuming you use the Transparent Gateway for ODBC which is free). Your APEX application could then query and report on data that is in SQL Server by issuing queries that run over the database link. Tim Hall has a good article (though it's a bit dated and some of the components have been renamed, the general approach is still the same) on configuring Heterogeneous Services.

If you do need to replicate the data, you can create materialized views in Oracle that query the objects in SQL Server using the database link you created with Heterogeneous Services and schedule those materialized views to refresh on a regular basis. The materialized views will need to do a complete refresh, though, which means that every row will need to be copied from SQL Server to Oracle every time there is a refresh. That generally limits the frequency with which you can realistically have refreshes happen. If you need the data to be replicated to the Oracle database and you need to send incremental changes so that the Oracle side doesn't lag too far behind, you can use Streams from a non-Oracle database to an Oracle database but that involves a lot more work.

Answer 2

In SQL Server you can setup linked servers that allow you to view data from other db's. You might see if Oracle has something similar, if not the same. Alternatively, you could use the sql's integration services to push the data over to an oracle table. Unfortunately I only know how to setup linked servers in SQL Server and I don't have a lot of experience with ssis to tell you how to do that, but those are the first two options I can think of that you might explore further.

Here's a link I found that might be helpful as well: http://www.dba-oracle.com/t_connecting_sql_server_oracle.htm

Answer 3

There's no way to do it "automatically" that I know of that will work across DBMS. ETL tools like Sql Server Integration Services might help but there's going to be a loading delay (as it will have to poll for changes). You could build some update triggers on the SharePoint database tables but that's going to turn into a support nightmare.