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.