First published on MSDN on Feb 17, 2019
Authored by datamigrationteam@microsoft.com
Migrating from one database platform to another is not an easy task, but luckily Microsoft provides a set of tools to support you through this journey. One of the tools in your belt when migrating from Oracle to SQL Server is the SQL Server Migration Assistant (SSMA). SSMA does most of the heavy lifting by converting Oracle schema objects, including tables, views, packages, functions, stored procedures, etc. If you are planning to move from Oracle to SQL Server and not yet familiar with SSMA, we strongly encourage you to download this free tool and start using it now!
Those familiar with SSMA and who have been through multiple migrations probably already know that converting PL/SQL to T-SQL is not a trivial task. While SSMA gives you a fairly good baseline for the migration, quite often there are issues that need to be resolved manually. We have received user feedback on typical post-conversion activities over the past few years, and we’ve spent a lot of time brainstorming about how we can further reduce the manual effort required after SSMA initially converts a schema. After reviewing hundreds of real-world migrations, it is clear to us that most users have similar problems throughout the entire package or even the schema. Based on our learnings from these engagements, we partnered with Microsoft Research to help SSMA users address conversion issues in a more efficient way. SSMA v8.0 introduces the new Fix advisor feature designed to learn as you type.
Let’s take a more detail look at how this works. Assume that you have a stored procedure that performs the same regex substitution multiple times, something similar to the following: 
In the example above, REGEXP_REPLACE is being called multiple times with the same exact match string and substitution, just on different arguments. Since SQL Server does not currently support regular expressions and SSMA doesn’t provide an emulation for REGEXP_REPLACE, you end up with multiple conversion errors: 
Even though SSMA converts the statement and other function calls, REGEXP_REPLACE is not converted. This is representative of the typical problems you need to resolve manually after you run the conversion. You would approach this by reviewing the actual logic. Based on the regex pattern, you can see that there is no real need for regular expression, and you can just run standard string replacement function twice, for example: 
So if you fix HOME_PHONE this way and save your modification, then fix WORK_PHONE and save, soon you will notice a small “light bulb” icon next to the SSN on line 21: 
SSMA tracks your changes, identifies patterns in the changes you make, and then marks potential code changes with a light bulb indicator. To speed up the process, you can just select a “light bulb” (or use <Ctrl+.> hot key, while on line 21) and review the proposed fix: 
If the fix looks good, just select Apply (or hit <Enter>) and you are good to go! If a suggested fix doesn’t feel right – you can select Cancel (or hit <Esc>) to go back. As you make more changes manually, SSMA refines its learning to provide better suggestions over time.
Most of the learning and analysis work is happening in the background, so nothing should change in the overall UI responsiveness. However, if you want, you can disable the Fix advisor under Tools -> Project Settings -> General -> Conversion , and under the Misc category, just set “ Enable Fix Advisor ” to No .
SSMA uses Microsoft Program Synthesis (PROSE) technology to identity suggested fixes. You can find out more about PROSE at https://microsoft.github.io/prose/ . These changes are just the first step in our effort to bring ML capabilities to the database migration space. We welcome your feedback on this feature and suggestions for how we can improve it. For specific issues with the fix recommendations, please send your feedback to prose-contact@microsoft.com together with a sample code snippet. We strongly believe in this technology and will continue improving it in the future versions, so stay tuned!