Using the SAP Advanced SQL Migration tool for Pre-Migration Complexity Assessment
NB: earlier, SAP Advanced SQL Migration was called ‘Exodus’. it was renamed in Q4 2017.
Recently I described the SAP Advanced SQL Migration tool as a new offering by SAP to help migrate custom (non-SAP) applications from a non-SAP to an SAP database.
In this blog post, let’s take a closer look at one of the most important SAP Advanced SQL Migration features, namely: the pre-migration complexity assessment.
In any migration project, the first question that needs to be answered is: how complex will this migration be?
More precisely, you’ll need to understand which technical difficulties should be anticipated. For example, are there any DBMS features used in the application-to-be-migrated which do not have a direct equivalent in the target DBMS?
It is pretty clear that this information is needed, but that is easier said than done: how can you determine exactly which SQL constructs are used in the application’s stored procedures – there may be hundreds of these, consisting of tens of thousands of lines of SQL code.
In fact, many discussions about possible migration opportunities are terminated early since there are simply too many unknowns, making it too risky to proceed. Indeed, when migrations projects fail or overrun the planned schedule, this is often caused by unexpected complexities being discovered too late in the project. Had these complexities been identified earlier, then a different migration strategy might have been chosen, or it might have been decided it was best not to start the migration project at all.
SAP Advanced SQL Migration comes to the rescue here, with its feature for performing a pre-migration complexity assessment.
This works as follows: you point SAP Advanced SQL Migration at the DBMS server hosting the application to be migrated, and it will discover what’s in that DBMS and provide a detailed report on the SQL constructs found there. This is divided in two parts: one assessment is about the database schema, the other about the server-side SQL code found in stored procedure, functions, etc.
In the output of the pre-migration complexity assessment, SAP Advanced SQL Migration will highlight SQL aspects that cannot be fully migrated automatically to the selected target DBMS and therefore represent additional migration complexity. For example, if an Oracle-based application uses before-row triggers as well as after-statement triggers, and we’re interested in migrating to SAP ASE, SAP Advanced SQL Migration will highlight the fact that ASE does not support before-row triggers (but only after-statement triggers), meaning that migrating those before-row-triggers needs additional manual work (for example, the functionality in the before-row triggers will need to be worked into the ASE-supported after-statement triggers, or implemented elsewhere in the migrated application).
In contrast, when migrating to SAP SQL Anywhere, SAP Advanced SQL Migration would not highlight any issues here since SQL Anywhere supports both of these trigger types. But when migrating to SAP IQ, which does not support triggers on IQ tables at all, SAP Advanced SQL Migration will report both trigger types as cases where migration complexities should be expected.
Based on the results of the SAP Advanced SQL Migration pre-migration complexity assessment, we’re in a much better position to assess the areas of complexity to be expected, and consequently, the level of risk of a particular migration.
But SAP Advanced SQL Migration goes one step further. It also tries to quantify the amount of effort required (in person-days) to migrate the application to the target DBMS. It does this by defining a “migration cost” (as a unit of time) for each particular SQL construct found, and multiplying this cost by the number of cases found for that SQL construct. This effort estimate is about migrating to functionally equivalent SQL code, but does not include things such as testing and performance tuning (more on that in later blog posts).
For example, let’s assume our application contains 7 before-row triggers and 3 after-statement triggers.
If we’re migrating to ASE, SAP Advanced SQL Migration will estimate 1 hour of manual work for migrating every before-row trigger to ASE (so 7*1 = 7 hours); if we’re migrating to IQ, it would estimate 2 hours per trigger, irrespective of the trigger type (so 10*2 = 20 hours). Note that SAP Advanced SQL Migration uses a higher migration cost for migrating triggers to IQ than to ASE to reflect that migrating trigger functionality to IQ is more difficult due to IQ not supporting nay triggers on IQ tables.
Also, when migrating to SQL Anywhere, SAP Advanced SQL Migration will not estimate additional time since SQL Anywhere supports both trigger types.
Lastly, SAP Advanced SQL Migration budgets 15 minutes for every trigger, irrespective of its type or target DBMS. This is to reflect the fact that some amount of manual migration work (like functional verification, syntax changes or debugging) is likely to be needed anyway.
Now, the question has to be: How reliable are the migration effort estimates by SAP Advanced SQL Migration ?
It is important to point out these effort estimates should be seen as an order-of-magnitude indicator, and not as a precise statement of work that can be put
directly into a contract.
For example, when SAP Advanced SQL Migration estimates that the functional migration of a particular application will take 40 days, that should primarily be interpreted as meaning: this won’t be possible to complete in two weeks — but it’s also not likely to take half a year.
Obviously, if sufficient time can be spent on analyzing the SAP Advanced SQL Migration estimates and the application’s SQL code in greater detail, a more realistic effort estimate may be reached.
In practice however, a large factor in a migration project will be the SQL skills and migration experience of the team performing the migration. An automated tool like SAP Advanced SQL Migration can handle a large part of the work, but ultimately every migration remains a manual effort where humans need to put it all together and address those parts that SAP Advanced SQL Migration cannot handle automatically. The quality and experience of that team may have a big impact on the actual amount of effort that needs to be spent. To reflect this, users of SAP Advanced SQL Migration can redefine the migration cost definitions as they think is best.
(for an overview of all SAP Advanced SQL Migration-related blog posts, see here)