locked
Oracle 7.3.X on NT4.0 to SQL Server 2012 RRS feed

  • Question

  • I am needing some direction on how to migrate an Oracle 7.3.X database running on Windows NT 4.0 to a MS SQL Server 2012 running on Windows Server 2008. I have never attempted to do anything like this before and really don't know where to start. Any help would be greatly appreciated.
    Tuesday, July 15, 2014 4:52 PM

Answers

  • Hello,

    The following tool automates the migration process but it requires Oracle client 9.0 or later:

    http://www.microsoft.com/en-us/download/details.aspx?id=42655

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Proposed as answer by Shanky_621MVP Sunday, July 20, 2014 9:41 AM
    • Marked as answer by Sofiya Li Friday, July 25, 2014 9:39 AM
    Tuesday, July 15, 2014 5:29 PM
  • Hi,

    As Alberto’s post, you can use the SQL Server Migration Assistant (SSMA)  v5.3  for Oracle  to implement the migration. Because SSMA v5.3 requires Oracle client 9.0 or later version, I recommend you to upgrade your Oracle 7.3.X to Oracle 9.0 or later version firstly, then install the SSMA v5.3 for Oracle. 

    In addition to installing SSMA, you also need to install SSMA components on the target SQL Server machine. These components include the SSMA extension pack, which supports data migration, and Oracle providers to enable server-to-server connectivity.

    The basic steps of migrating oracle databases to SQL Server are as follows.
    1. Create a New SSMA Project.
    2. Connect to Oracle (loads metadata).
    3. Connect to SQL Server.
    4. Map Oracle database schemas to SQL Server database schemas.
    5. Convert Oracle database schemas into SQL Server.
    6. Load converted database objects into SQL Server (synchronize).
    7. Migrate data to SQL Server.

    For more details, you can follow review this blog: How To Migrate Oracle’s Sample HR Schema to SQL Server.


    Thanks
    Lydia Zhang

    • Proposed as answer by Shanky_621MVP Sunday, July 20, 2014 9:41 AM
    • Marked as answer by Sofiya Li Friday, July 25, 2014 9:39 AM
    Wednesday, July 16, 2014 8:37 AM
  • I would like to add a point here that SSMA would try to migrate oracle code and database as much as possible to SQL Server equivalent but its not guaranteed that always it will migrate 100 %. First run the reports to actually what all stored procs or queries might fail for such exclude those Procs and queries during migration and then after migration manually write new code in TSQL.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Sunday, July 20, 2014 9:43 AM

All replies

  • Hello,

    The following tool automates the migration process but it requires Oracle client 9.0 or later:

    http://www.microsoft.com/en-us/download/details.aspx?id=42655

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Proposed as answer by Shanky_621MVP Sunday, July 20, 2014 9:41 AM
    • Marked as answer by Sofiya Li Friday, July 25, 2014 9:39 AM
    Tuesday, July 15, 2014 5:29 PM
  • Hi,

    As Alberto’s post, you can use the SQL Server Migration Assistant (SSMA)  v5.3  for Oracle  to implement the migration. Because SSMA v5.3 requires Oracle client 9.0 or later version, I recommend you to upgrade your Oracle 7.3.X to Oracle 9.0 or later version firstly, then install the SSMA v5.3 for Oracle. 

    In addition to installing SSMA, you also need to install SSMA components on the target SQL Server machine. These components include the SSMA extension pack, which supports data migration, and Oracle providers to enable server-to-server connectivity.

    The basic steps of migrating oracle databases to SQL Server are as follows.
    1. Create a New SSMA Project.
    2. Connect to Oracle (loads metadata).
    3. Connect to SQL Server.
    4. Map Oracle database schemas to SQL Server database schemas.
    5. Convert Oracle database schemas into SQL Server.
    6. Load converted database objects into SQL Server (synchronize).
    7. Migrate data to SQL Server.

    For more details, you can follow review this blog: How To Migrate Oracle’s Sample HR Schema to SQL Server.


    Thanks
    Lydia Zhang

    • Proposed as answer by Shanky_621MVP Sunday, July 20, 2014 9:41 AM
    • Marked as answer by Sofiya Li Friday, July 25, 2014 9:39 AM
    Wednesday, July 16, 2014 8:37 AM
  • I would like to add a point here that SSMA would try to migrate oracle code and database as much as possible to SQL Server equivalent but its not guaranteed that always it will migrate 100 %. First run the reports to actually what all stored procs or queries might fail for such exclude those Procs and queries during migration and then after migration manually write new code in TSQL.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Sunday, July 20, 2014 9:43 AM
  • I really appreciate your suggestions, but I have a few more questions because of my ignorance.  Does the SSMA get installed on the NT 4.0 box or on my SQL Server box? I guess I am asking because I don't completely understand the process.
    Monday, July 28, 2014 5:05 PM
  • As an alternative why not buid a nice new SQL 2012 box, and use its Import Wizard to pull in the data from Oracle creating tables as it goes. Recreate the logins. Then cut /paste/fix the SQL code etc.
    Thursday, July 31, 2014 2:55 PM