Home / server / Questions / 27407
Accepted
Paul
Asked: 2009-06-18 08:27:32 +0800 CST

Oracle to SQL Migration

  • 772

I'm currently starting a project for an Oracle to SQL 2008 migration. Since I'm currently reading the official SSMA Guide to Migrating from Oracle to SQL Server 2005 i'd like advice and pointers from personal experience.

Guides and articles with occured issues highlited in an organised manner, discutions and solutions would be aprecieated.

Thank you

sql-server sql-server-2005 migration sql-server-2008 oracle

5 Answers

  1. There is some non-overlapping functionality between Oracle and SQL Server and they work quite differently in many ways. For example:

    • Oracle does not have a direct equivalent to an identity column. Equivalent functionality would be done differently using sequences.

    • PL/SQL and T-SQL are quite different languages. If you have a large body of stored procedure code, be prepared to spend a substantial amonunt of time porting it. Also, t-sql idioms often differ quite substantially from PL/SQL.

      For example, T-SQL is much better at set-ops than PL/SQL and PL/SQL is much better at working with cursors (e.g. parallel looping constructs). Some things done iteratively in PL/SQL are probably better done using set ops and temp tables in T-SQL.

    • Comparisons in SQL Server are case insensitive by default. Comparisons in Oracle are case sensitive.

    • Working with table partitions in SQL Server is quite different (and somewhat more clumsy) than oracle.

    • 4

  2. Microsoft has a comprehensive set of resources to help migrating applications and databases from Oracle to SQL Server 2008. Checkout the main migration page at SQL Server 2008 Solutions - Migration - it includes a methodology for migrating, tools to help you, and some customer testimonials.

    Hope this helps.

    • 3
  3. Best Answer

    Oracle and SQL Server are very different both from an administration and database usage stance.

    Database Usage

    Get ready to spend quite a bit of time porting. Some of the issues off the top of my head:

    PL/SQL vs. T-SQL

    The languages themselves are different enough to require significant porting effort for your scripts and stored procedures. This ranges from things like different concatenation operators (|| vs. +), to changes in function names and subtleties (NVL vs. COALESCE), to conceptual differences (exception handling, case-sensitivity). Some other examples:

    Cursors vs. temp tables

    Most of the things that you did in Oracle using cursors are done in SQL Server using temporary tables.

    Sequences vs. Identities

    They are mostly equivalent, but you'll have to change the syntax of any procedures using them.

    Database Administration

    They mostly have the same features, sometimes under different names with varying side-effects. Some highlights:

    • Data (un)loading (data pump or sql*loader or exp/imp vs. DTS or bcp)
    • Data dictionaries (DBA_* vs. INFORMATION_SCHEMA)
    • Transaction log settings (Archive logging vs. Recovery Model)
    • Table types (Index Organized vs. clustered indexes)
    • Locking mechanisms (reads don't lock in Oracle, not so in SQL server)

    In short, there's a decent sized learning curve when going from Oracle to SQL Server (or vice-versa). Feel free to add to this list.

    • 2

  4. This video tutorial on topic "Migrating from Oracle to SQL Server with 80% Less Effort" might get you complete picture.

    Video URL: http://wtv.watchtechvideos.com/topic307.html

    • 1

  5. I prefer a third party applpications for migrating my data, i use data loader when i was migrating MS SQL to Foxpro it work great, and it can migrate almost any database.

    Download Free : http://www.dbload.com

    • 1