SQL Server Performance

oracle 9i to Sql server 2008 R2 migration

Discussion in 'ALL SQL SERVER QUESTIONS' started by nivarshn, Apr 2, 2013.

  1. nivarshn New Member

    Hi, We are having oracle 9i as database and migrating to sql server 2008.
    Our oracle 9i database connects to another oracle databases and these databases are having different oracle version(9.2.0.8.0, 9.2.0.7.0,10.2.0.5.0,11.2.0.2.0,9.2.0.6.0,10.2.0.4.0,8.1.7.4.0 )
    I do have two queries :
    1) how to connect from Sql server 2008 R2 to different Oracle databases
    2) How will other oracle databases connect to Sql server 2008 R2 database
  2. Shehap MVP, MCTS, MCITP SQL Server

    For building a linked server between SQL Server and Oracle , we are usually install an appropriate Oracle client provider like Oracle 11g R2 client provider either 64 bit or 32 bit according to OS Server platform of SQL Server
    , then you can create a linked server from Oracle to SQL Server as follow:

    EXEC sp_addlinkedserver @server, @srvproduct, @provider, @datasrc

    Ex:
    EXEC sp_addlinkedserver'Oracle_test', 'Oracle', 'OraOLEDB.Oracle', 'Oracle_test'

    Then map Oracle username and password with an appropriate SQL Server login using the below script:

    EXEC sp_addlinkedsrvlogin@rmtsrvname, @useself, @rmtuser, @rmtpassword

    Ex:

    EXEC sp_addlinkedsrvlogin'Oracle_test', 'sa', 'DWH','dwh'

    But please pay attention for some concerns to boost connectivity performance from SQL Server to Oracle

    ·Configure the Oracle OLEDB provider “OraOLEDB” with allow in process feature by expanding linked server >>>provider >>OraOLEDB >>>double click>> Enable Allow in process feature
    ·Installing the same Oracle client provider version as Oracle instance as recommended by Oracle DBAs to prompt connectivity performance
    ·Testing linked server connectivity and comparing its results with the performance connectivity results of SQLPLUS as follow : CMD>>>SQLPLus Oracle_test@dwh/dwh, if they are not the same, please let me know to help here

    You can try it and let me know your input

Share This Page