locked
Link SQL to Oracle RRS feed

  • Question

  • Any one knows other than using linked servers, any other recommended way to connect SQL server to Oracle dbs?

    We will create views or functions to query Oracle.

    Thanks,


    SQLFriend

    Monday, May 6, 2013 8:22 PM

Answers

  • I you want to query Oracle tables directly from SQL Server, you will need to use linked servers, or the related OPENROWSET or OPENDATASOURCE. If you can accept other arrangements, you could use replication or what data-sync operations Oracle offers.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Fanny Liu Monday, May 13, 2013 9:01 AM
    Monday, May 6, 2013 9:35 PM
  • A SQL Server Linked Server to Oracle is just an ordinary Oracle client connection.  The impact to Oracle depends entirely on the queries you send to Oracle.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Naomi N Tuesday, May 7, 2013 2:00 AM
    • Marked as answer by Fanny Liu Monday, May 13, 2013 9:01 AM
    Tuesday, May 7, 2013 1:02 AM

All replies

  • I you want to query Oracle tables directly from SQL Server, you will need to use linked servers, or the related OPENROWSET or OPENDATASOURCE. If you can accept other arrangements, you could use replication or what data-sync operations Oracle offers.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Fanny Liu Monday, May 13, 2013 9:01 AM
    Monday, May 6, 2013 9:35 PM
  • We are wondering if using linked server to link to Oralce which is our major OLTP, would it cause oracle performance issue, will it create concurrency /lock/bandwith issues?

    SQLFriend

    Monday, May 6, 2013 11:30 PM
  • A SQL Server Linked Server to Oracle is just an ordinary Oracle client connection.  The impact to Oracle depends entirely on the queries you send to Oracle.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Naomi N Tuesday, May 7, 2013 2:00 AM
    • Marked as answer by Fanny Liu Monday, May 13, 2013 9:01 AM
    Tuesday, May 7, 2013 1:02 AM
  • Since this is an OLTP Server, I would strongly recommend using an SSIS package to move data from Oracle to your reporting server.

    You can use Oracle's Golden Gate Product http://www.oracle.com/technetwork/middleware/data-integrator/overview/best-practices-for-realtime-data-wa-132882.pdf if money is no object :).

    Another consideration is that if you are using Resource Governor functionality in SQL Server this will not be reflected on the Oracle side. You will need to consider using Resource Manager on the Oracle side.

    Thursday, May 9, 2013 4:54 AM
  • If you want CDC for Oracle, SQL Server 2012 EE has it in the box SQL Server 2012 Change Data Capture for Oracle .

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, May 9, 2013 2:01 PM