Accessing Oracle DB from SQL Server DB 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.
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).