My first IT project was a data conversion for Fashion Bug. Since then, I have been on many conversions both as an in-house developer and as a consultant. The last few conversions have been moving Oracle Databases to SQL Server. Here are 10 pitfall mitigations ranging from process to technical and presented in a mostly chronological order.

1. Underestimating Scope

We all know these are big complicated projects. You will have to document what needs to be converted. Database, source data, downstream applications, reporting tools, etc. They also require a significant amount of QA time. Microsoft SQL Server Migration Assistant can help with converting schema and code. Since you will also need additional resources for the conversion, now is the perfect time to partner with a firm that has experience in database conversions. This will help you start off in the right direction.

Most, if not all companies, undertake a database conversion to save money. The upfront cost and effort can seem intimidating; but, the ROI is usually worth it. However, some companies have third-party apps or other requirements that cause the scope to exceed the desired cost or duration. See this licensing comparison and you will have a place to start on your Cost Benefit Analysis. Everyone should have buy-in on this because you will want to keep your eye on the goal during the conversion project.

2. Taking the lowest bid

“If you think it’s expensive to hire a professional to do the job, wait until you hire an amateur.” Red Adair

This is most likely a database that supports your core business. Delays, bugs, and poor performance will affect your business in the short and long term. In translation, you should translate into your native language. Likewise, having SQL Server professionals who know Oracle is much better than Oracle developers who know a little T-SQL converting your core application. Make sure you are comfortable with the company’s methodology as well as their technical expertise.

3. Limited SME availability

You are the Subject Matter Expert. Both, technically and in business knowledge. You are trying to keep the company running while this project is going on; but, the conversion project will also need a significant portion of your time. Project Managers often allocate 30–50% of the SME’s time for the project; but, we often see 10–20% during the project. Make sure the stakeholders know the requirements of their people up front. Delays in access to SMEs will slow down development and cause rework including time to refresh the developers' understanding of the requirements.

4. No centralized documentation

A project of this scope will generate a lot of documentation. This should be centralized so a single version of the truth can be maintained and tracked. A few of the types of documentation to maintain are object conversion lists and status, SME Q&A, system documentation (pre and post-conversion), code templates, bug tracking, and on and on. If you get asked a question and your answer is let me check my emails, consider starting a project artifact.

5. Bad precision in calculations i.e. division

This will most likely come up during QA. Your view may be straightforward and SSMA converted it without errors; but, you still get the wrong answer. You may have a calculation that truncates decimal places but the source system has them. I.e “select 12 /10” SQL will return 1; but, Oracle returns 1.2. You will have to force SQL to calculate the precision you desire.

6. Not automating Materialized View Refreshes

Materialized Views are great for performance. They also tend to grow in complexity over time. You will have to simulate these with SQL Views and Tables. I would also suggest you set up a control table to track refresh order and facilitate the batch or on-demand refresh. Here is a little Oracle query to show the first three levels of an MV.

select dr.mview_name, dr.DetailOBJ_name, dr2.DetailOBJ_name as SecondLevelView, dr3.DetailOBJ_name as ThirdLevelView
from ALL_MVIEW_DETAIL_RELATIONS dr
left join ALL_MVIEW_DETAIL_RELATIONS dr2
on dr.DetailOBJ_name = dr2.Mview_name
left join ALL_MVIEW_DETAIL_RELATIONS dr3
on dr2.DetailOBJ_name = dr3.Mview_name

7. Relying on SSMA’s UDF for Oracle Functions

SSMA will generate SQL functions to emulate Oracle functions. These will get you up and running quickly. However, the performance cost is where you pay for it. I had a view that used the Greatest and Least functions among others over a large table (75+ million rows). The view ran for 55 minutes. After I converted it to use CASE statements, it ran in 24 seconds. Budget time to convert SSMA UDFs to native SQL statements for performance improvements.

8. Taking an SSMA error as the final answer

SSMA will help you automate most of your conversion. But, it will still produce conversion errors. SQL Server continues to add functions and features that may take some time for SSMA to implement. Sometimes, there are straightforward solutions. You will develop a list (and put it in the centralized document repository) that is specific to your database. A few examples:

XMLAGG to STRING_AGG
“Subquery factoring clause conversion is not supported” to reformat CTE in SQL syntax
Constants in a GROUP BY can be removed in SQL
LAG OVER PARTITION is supported in SQL

9. Not using a data compare tool

Simple row counts and cross-footing some amounts are a good data validation during the nightly batch. However, you will need to compare billions of data points during a conversion. There are tools out there that will compare schema and data. Also, companies that offer data conversion may provide their own tool with the necessary expertise. This should be getting set up from the beginning of the project. QA is a highly iterative process and it will take time to build the profiles and scripts needed for testing.

10. Not starting daily batch runs as soon as possible

Taking from Agile methodology, you want the feedback cycle to be as short as possible. Create your batch jobs after a couple of feeds have been developed. Continually add jobs to the batch as they are developed (Continuous Integration). This will not only test your data; but, help you establish security and performance benchmarks. You may not be allowed to pull from production for performance reasons. You can always use a backup and run one day behind.

Conclusion

Data migration projects are not an everyday undertaking. By having an experienced team and minimizing the risks upfront, you will have a much smoother project.

Let us know your issues and keys to success.

How to Avoid these 10 Pitfalls during an Oracle to SQL Server Migration