1

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.

Improve this question
2

1 Answer 1

Reset to default
1

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).

Improve this answer
3
  • Thanks a lot. I am not foreseeing a large amount of users nor large queries. Thus, impacts would be minimal. Perhaps I should emphasize on the need of a "simple" solution since we don't have much knowledge in replication and the solution would have to be easily maintanable.
    – Francis P
    Mar 13, 2018 at 20:36
  • 3
    @FrancisP still you're suggesting 3 different forms of replication. Why not directly query the original data? Oracle's not really a snowflake either.
    – Gerard H. Pille
    Mar 13, 2018 at 20:45
  • @GerardH.Pille Because the Source DB is managed by an external provider so we cannot have direct access to it.
    – Francis P
    Mar 14, 2018 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.