SAP Advanced SQL Migration. Pre-migration complexity assessment real case example

One very interesting SAP Advanced SQL Migration tool capability you have probably read about before is the pre-migration complexity assessment and you may wonder how it looks like and how it can help you to take a decision and attack your database migration project. This is exactly what this blog is all about.

When you start thinking about a database migration project there are two important questions arising at the very initial steps:

– How complex is it going to be?

– What is the effort needed to do it?

The pre-migration complexity assessment performed by SAP Advanced SQL Migration will greatly help you answering those questions and will also point out to the parts where you should focus your biggest efforts. Let’s take a look at the pre-migration complexity assessment output for a very complex case we run into on January 2021 and we will see some other interesting stuff provided by this migration tool as well.

This real case was an Oracle to Hana migration and the head of the summary for this complexity assessment was:

From this info you are getting some interesting numbers to start with:

• The data model consists on 1213 tables, 751 views and 996 functions/procedures/packages
• Total number of PL/SQL lines is 555K, what is a huge number
• 91% of the SQL code is converted automatically by SAP Advanced SQL Migration (what doesn’t mean it is totally ok and working as expected)
• There are 4567 issues qualified as “more impact” and 1557 qualified as “less impact”, this is where the manual effort should be focused because some redesign/rewrite will be required.
• There are 8608 issues qualified as “least impact”
• The report says “preliminary” because the migration tool user should evaluate/interpret this summary and decide on a final conclusion to be communicated, which may be different from what is shown here.

This sounds like a lot of issues but think about these remarks:

• Issues qualified as “least impact” use to work well and smoothly with the conversion obtained by the migration tool, the comment only suggests that you need to check they are actually working as expected, in most cases nothing is needed.
• There is a big number for “more impact” and same for “less impact”, but in many cases it will be the same problem happening many times, what means the number for things to fix is much less.
• Even having those huge numbers, notice that the automatic conversion ratio is 91%, what means the migration tool has done a lot of work and will save a lot of time. Can you imagine how this would be without this migration tool ?.

But even more, the complexity assessment also provides deep details about the issues found. This is what is returned under issues with “more impact”:

As you can observe the migration tool is pointing out clearly the features that are not converted automatically into the target database SQL dialect, that is where you will have to do some redesign/rewrite. As pointed out before some of the issues are appearing hundreds of times.

For “less impact” issues we get a similar report as well:

SAP Advanced SQL Migration also provides report at SQL object level with the details about the conversion for the corresponding objects, clearly indicating what is converted automatically with a “Fully converted” indicator or what is not converted by the tool:

SAP Advanced SQL Migration tool complexity assessment is reporting an effort estimate as well, but please don’t take this as final number at all, that will depend a lot on different aspects as the notes are saying, it should be used to get general idea and to do further estimations.

Contact:  Advanced_SQL_Migration@sap.com