SSMA provides a project setting that allow you to customize how the conversion is done. This post describes the project setting options available for you as well as scenarios where you want to customize the setting.
SSMA project setting options are categorized into the following:
There are many options for schema conversion, to simplify the selection, you can select "setting mode" that include predefined selection of the project setting. SSMA comes with three setting mode: default, optimistic and full. Optimistic mode contains the setting which leverages native SQL Server syntax as much as possible but there is a chance that in some cases those syntax may not give the same results in SQL Server. For example, in Optimistic mode, Oracle's substr() function is converted to SQL Server's substring() function. However, there are situation where substring() gives a different result:
SQL Server: SELECT substring('SQL Server', 5); -- returns error 'The substring function requires 3 argument'
SQL Server: SELECT substring('SQL Server',-6,3); -- return ''
If you select full mode, the substring is converted to SSMA's custom emulation function to give closer result to Oracle's function. Default mode selects the common option for most migration projects.
Converting system objects consumes system resources and takes time. To improve performance, SSMA selects only the most frequently used system objects, as shown in the following list:
If your Oracle objects refer to additional system objects, you should select those objects. If you do not select the system objects that are referenced by your Oracle database objects, SSMA will report conversion errors. If you receive conversion errors caused by missing system objects, select the missing objects in this dialog box. You can then repeat the conversion as necessary.