Migrating from Oracle to SQL Server

  • Article

[Next Post in Series: Kronos Migration]

My first experience with data migration dates from the 1970’s when a firm was moving from a Honeywell Mainframe (64KB of memory, no DASD -- just tape drives) to an IBM 4331 (2 MB of memory and DASD). In those days, computer manufacturers often had different character codes (EBCDIC :: ASCII) and sort sequences. That migration took 80% of the staff two years to complete. Fortunately, those days are far behind us.

I look at Transaction Processing Performance Council’s Top 10 Price/Performance Results for TCP-E (On-Line Transaction Processing benchmark) and I see [2011-02] that all ten of the systems are running SQL Server. With tight financial budgets, migrating to SQL Server may be the path of least budget resistance.

Several years ago, one of my former university students was a senior database administrator at Starbucks when they moved some of their databases from Oracle to SQL Server and he reported the process was very clean. Today, the process is even simpler because of the availability of SQL Server Migration Assistant for Oracle (SSMA4O) which is now at version 4.2. Recently I spent a weekend trying to identify issues that people were having with SSMA, and the majority of problems were:

  • Human-ware failing to read and do the requirements specified on the download page
  • Human issues with collation-sequences and missing language fonts
    • “I’m getting square blocks when I query the table”

On SQLServerCentral.com, I found a good article dealing with an older version with the following observation:

I have personally used SSMA in at least two migration projects this year with remarkable results in terms of precise estimations, accuracy of conversion, testing and above all, the enormous amount of time saved. Manual work was involved only in cases where tables had rows exceeding 8060 bytes in Oracle databases where alternative data-types for column definitions had to be used on SQL Server side. -- Suresh Maganti

The better news is that when you are dealing with ISV products there is much less work. ISVs that support two or more databases typically design all of their databases to use the same schema, tables and equivalent column definitions. Any procedures being used have the same name and same parameter names in all versions. This means that for the as-installed-product, it’s a simple matter of moving the data from a table in Oracle to the matching table in SQL Server – well almost. Relational database management systems use referential integrity, so moving data in the tables must be correctly sequenced. A second issue occurs with columns that use Identity() because you need to turn identity insert ON, add the rows and then turn it OFF. i.e.

SET IDENTITY_INSERT {tablename} ON -- INSERT ROWS SET IDENTITY_INSERT {tablename} OFF

I used the phrase “as-installed-product” above because some software packages are customized by users by adding tables and code. In this case, SSMA4O is used to convert these additional tables. Any embedded PL/SQL code that you have added may be easily converted into T-SQL code using SSMA.  For more information on this, see How To Convert Individual Statements Using SSMA.

Converting from Oracle to SQL Server is best done by the ISV staff for some simple reasons:

  • It’s a one-time event for your staff with rarely a payback from this specialized learning curve.
  • The ISV specialists know just about everything that could go wrong, so your conversion risk and conversion time will be minimal.

In the coming months I will be shadowing some of these ISV specialists during conversions of some client systems and reporting on issues encountered. Many folks hesitate moving to SQL Server because of anxieties over the unknown, or horror stories of past data migration with other products (I can tell a few myself); I hope to show you that these fears are unfounded for Oracle to SQL Server using SSMA4O  as illustrated by actual experiences.

If you have done a migration and care to share your experience, feel free to send me an email.