Last week I had a very interesting engagement where I had to come up with a high level estimate for an Oracle migration to SQL Server using the SQL Server Migration Assistant (SSMA) for Oracle. I was worried about this because I hadn’t used SSMA in several years, but I thought I’d try setting up an Azure VM for Oracle, and see if I could connect to that and run some tests. Long story short – I was able to easily create an Oracle server, but I couldn’t figure out how to connect SSMA to it. I think the problem was in the Oracle drivers I was using. I will eventually follow up on that, but I ran out of time when the engagement started!

But fortunately for me, it turned out to be really easy to get SSMA up and running on my client’s computer. They already had SQL Server installed, and the Oracle Client tools, so it was a simple installation. Once I realized I needed to use the 32-bit tools because the customer had installed the 32-bit Oracle client, I was able to connect to their server, and the SSMA tool read all of the Oracle metadata. TIP: Make sure you can connect via the Oracle Client before you install the SSMA client!! That way you save yourself some install/uninstall problems. TIP2: Reboot after installing the Oracle Client and before installing the SSMA tool, and ignore the error you get when it says it can’t find the Oracle drivers. That worked for me.

Once I was connected to both Oracle and SQL, reading the meta data and building the reports for my client were pretty straight forward. Select the Oracle schema, right click, and Create Report. I copied the resulting report folders to a shared drive, and used the mainindex.hmtl file to navigate through the findings.

I’m really impressed with the quality of the conversion effort that the SSMA tool provides. I was able to quickly migrate tables, indexes, constraints, and stored procedures from five Oracle schemas in a short amount of time. About 1/3 of my objects didn’t convert automatically, but the SSMA tool identifies exactly where the problems are, and allows you to fix those lines manually. I reviewed the code for each of my schemas, and was able to provide a pretty high level of confidence that the estimated migration times were reasonable.

I will provide a more detailed blog of how to use SSMA once I figure out how to connect to my Azure machine, but here are the takeaways from my efforts last week:

• Oracle uses cursors differently than SQL Server. Yes, they both can do row-by-row processing, but I saw many other uses of CURSORS in the Oracle code that were going to convert to row-by-row processing sprocs in SQL if the SSMA tool was used to migrate the code. If your current Oracle code uses CURSOR statements, you might wan to double check any automated migrations to make sure you didn’t just turn your processing into row by row processing.
• I recommended that my customer add time to the migration to review data type conversions. I noticed that a lot of Oracle numeric data type conversions converted to DECIMAL, when an integer data type may have been more effective. This can lead to over-allocating data fields, and for larger systems this can impact performance.
• There seems to be an issue with the MERGE command in Oracle converting to a MERGE in SQL Server. I’m not sure, what it is, but many of my Oracle MERGE statements did not migrate.
• XML does not appear to convert work.
• Watch out for cross-schema foreign keys, these do not convert when you migrate each schema to its own database.

Other than those issues, the majority of the Oracle in my customer’s system seems to have converted pretty well! More later on how I can connect to an Oracle Azure server!!

Picture of the day:

It seems like a good day to share one of my favorite pictures from France. This is from December, 2013. Peace.

Eiffel Tower, as seen through the Peace Wall.