Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question

5 Answers 5

up vote 2 down vote accepted

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.

share|improve this answer
1  
SQL Server has MVCC as of SQL2005, although it isn't turned on by default. –  ConcernedOfTunbridgeWells Jun 17 '09 at 17:58
    
Thank you for sharing this information. I know nothing of Oracle but I have experience with SQL. I hope I'll manage and learn a lot. –  Paul Jun 17 '09 at 18:54

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.

share|improve this answer

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.

share|improve this answer
    
Thank you for your answer Paul, I'm checking the Microsoft resources as we speak. –  Paul Jun 17 '09 at 19:54

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

share|improve this answer
    
Thanks, it's a good overview over the whole problem. –  Paul Jun 22 '09 at 7:23

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

share|improve this answer
    
thanks for the info, however i need the database structure migrated aswell, not only the data. –  Paul Sep 2 '09 at 11:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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