Sql-server – Accessing Oracle DB from SQL Server DB

oraclereplicationsemi-sync-replicationsql serversql-server-2012

I am looking for the best and simplest way for accessing an Oracle db from an SQL Server db. Both are on separate physical servers. I am looking for a one-way access in read-only mode. SQL Server Database would be used by one system for now but would scale to be used by multiple applications in the future.

I currently have three options in mind:

Option 1: Directly accessing Materialised views

  • Every hour or so, data would be stored into Materialised views in Oracle in order to prevent impacting original DB performance.
  • SQL Server DB would access the views on-demand, using Linked servers

Option 2: Copy data to SQL Server DB

  • Every hour or so, replicated tables into SQL Server DB using CDC mechanisms.
    (Not so sure how that'll work).

Option 3: Replicate Oracle tables using SQL Server Publishing

  • Setup the Oracle DB as the distributor.
  • Setup the SQL Server as the publisher (snapshot mode).

Thank you for your advices.

Best Answer

in order to prevent impacting original DB performance.

All three options you are considering will impact performance on the Oracle DB.

If you don't want to impact performance at all, apply archivelog files from Oracle to a standby DB and query that (you could try Linked Servers first to find out if it's fast enough for you).

Related Question