How SSMA Estimates Manual Conversion Time

  • Article

SSMA automates conversion of most statement. There are a few features and syntax which SSMA is unable to migrate. In such situation, SSMA issues a migration error and for each error, SSMA provides estimated manual conversion time. I often asked how do we come up with the time.

image

Those manual estimation time was calculated based on the actual average time it takes by our developer team to fix the issue (exclude testing time). The manual estimation is intended to help you further quantify the complexity of the issue to help planning the database migration.

However, you should be aware of the assumption and limitation of the manual conversion time estimate:

  1. The estimated manual conversion time was calculated based on the time it takes to resolve the issue using a specific approach. Often, there are more than one approach to resolve the issue. The actual hours to complete the migration and resolving the issue depends on the approach taken. For example when encountering Oracle User Defined Type (UDT), SSMA raised an error and the estimate was calculated based on the assumption that the UDT is converted to SQL Server TVP (as described in this article). However, you could also develop custom CLR type and convert the TVP to the CLR type -- in which case the actual conversion time will be vastly different.
  2. Every migration error is assumed to be independent and the total estimated manual conversion time sums the estimate from individual errors. This may not be the case. You may have the same errors across multiple objects, in which once you resolve one error, you could copy and paste the solution to another object which would reduce the resolution time. Another example, you may have one underlying issue manifested in multiple errors. Consider the example in the screenshot above where SSMA is not able to convert INTERVAL data type. This results in the error for the function return type (line 6 on original Oracle source code) and expression to calculate the value (line 10 on the original Oracle source code). The two issues are dependent each other and the return type depends on converted expression.

In addition, the manual estimate hours depends on the skills of resources performing the migration. In order to resolve the issue, you will need to understand the original Oracle source and understand how best to redesign the statement in SQL Server. Thus, you need resource with knowledge of both Oracle and SQL Server. If you do not have resource with knowledge in both technologies, then you need to have separate resources (Oracle DBA and SQL Server developer) collaborating to resolve the issue. In this case, you need to factor the number of resources to your project planning.

I often find customers use the estimated manual conversion time as a comparative number to rank complexity between one schema/database to another.

The manual conversion time can still be useful for rough order of magnitude (ROM) estimation, but for more accurate project costing and time estimate, it is best to have the actual person(s) performing the migration to review the error list carefully, consider the consider the design approach and skill level, then refine the estimate accordingly.