Share via

Using SSMA Project Setting to Customize Database Migration

  • Article

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:

 

  • Project Information: provides project name and location where the project file is saved.
  • General : provides more granular setting for the following:
    • Conversion: settings for schema conversion

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:

 

  • Oracle: SELECT substr('SQL Server', 5) FROM dual; -- returns 'Server')

SQL Server: SELECT substring('SQL Server', 5); -- returns error 'The substring function requires 3 argument'

  • Oracle: SELECT substr('SQL Server',-6,3) FROM dual; -- return 'Ser'

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.

 

  • Migration: settings for migrating data, including option to select whether migration is done through the client machine where SSMA is installed or through server side data migration engine" where data flows directly from source to target SQL Server. Other setting options include:

 

  • Batch size : Batch size that is used during data migration. A transaction is logged after each batch.The default value is 1000.
  • Break on error : Stops all data migration if any migration error occurs.
  • Break on table error : Stops data migration of the current table if a data migration error occurs. SSMA will continue with the next table.
  • Check constraints: Specifies whether SSMA should check constraints when it inserts data into SQL Server tables.
  • Fire triggers: Specifies whether SSMA should fire insertion triggers when it inserts data into SQL Server tables.
  • Keep identity : Specifies whether SSMA will preserve identity values when it inserts data into SQL Server. If this value is false, SQL Server will assign identity values.
  • Keep nulls : Specifies whether SSMA will preserve null values in the source data when it inserts data into SQL Server, regardless of the default values that are specified in SQL Server.
  • Table lock : Specifies whether SSMA will lock tables when it inserts data into the table during data migration. If the value is false, SSMA will use row locks.

 

  • Loading system objects: setting to specify what Oracle object to be automatically loaded when connecting to Oracle.

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:

  • SYS.DBMS_OUTPUT
  • SYS.DBMS_PIPE
  • SYS.DBMS_UTILITY
  • SYS.STANDARD
  • SYS.UTL_FILE
  • SYS.DBMS_LOB
  • SYS.DBMS_SQL
  • SYS.DBMS_SESSION

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.

  • Synchronization : customize how SSMA loads and refreshes database objects, such as tables and stored procedures, into SQL Server.
  • GUI: configures how data appears on the Data tab and if to include data reports with assessment reports
  • Type Mapping: customizes how SSMA converts Oracle data types into SQL Server data types. SSMA provides flexibility for mapping data types. You can set different mapping for data type from table column, local variable, and/or argument.