Project Settings (Migration) (MySQLToSQL)
The Migration page of the Project Settings dialog box contains settings that customize how SSMA migrates data from MySQL to SQL Server.
The Migration pane is available in the Project Settings and Default Project Settings dialog boxes.
To specify settings for all SSMA projects, on the Tools menu, select Default Project Settings, select the project type in Migration Target Version drop down of which you want to access the settings, click General at the bottom of the left pane, and then click Migration.
To specify settings for the current project, on the Tools menu, select Project Settings, click General at the bottom of the left pane, and then click Migration.
Options
Bulk Copy
| Term | Definition |
|---|---|
| Batch Size | Specifies the batch size used during data migration. Default Mode: 1000 Optimistic Mode: 1000 Full Mode: 1000 |
| Check constraints | Specifies whether SSMA should check constraints when it inserts data into SQL Server tables. Default Mode: False Optimistic Mode: False Full Mode: False |
| Fire triggers | Specifies whether SSMA should fire insertion triggers when it adds data to SQL Server tables. Default Mode: False Optimistic Mode: False Full Mode: False |
| Keep identity | Specifies whether SSMA preserves MySQL identity values when it adds data to SQL Server. A value of False causes identity values to be assigned by the destination. Default Mode: True Optimistic Mode: True Full Mode: True |
| Keep nulls | Specifies whether SSMA preserves null values in the source data when it adds data to SQL Server, regardless of the default values that are specified in SQL Server. Default Mode: True Optimistic Mode: True Full Mode: True |
| Table lock | Specifies whether SSMA locks tables when it adds data to tables during data migration. Obtains a bulk update lock for the duration of the bulk copy operation. If the value is False, a lock is set at the row level. Default Mode: False Optimistic Mode: False Full Mode: False |
Data Modification
| Term | Definition |
|---|---|
| Invalid Dates Migration | Specifies how to migrate invalid dates with like '2007-04-23' or '2000-06-31 10:00:00' in DATE and DATETIME formats. Default Mode: Set NULL Optimistic Mode: Set NULL Full Mode: Set NULL |
| Negative TIME values Migration | Specifies how to migrate negative values like '-30:11:00' in TIME columns. Default Mode: Set NULL Optimistic Mode: Set NULL Full Mode: Set NULL |
| TIME values over 24 hours Migration | Specifies how to migrate TIME values of more than '23:59:59' in TIME columns. Default Mode: Set NULL Optimistic Mode: Set NULL Full Mode: Set NULL |
| Truncate binary values to fit into column | If Yes, SSMA truncates binary values from MySQL that do not fit into SQL table columns and generates appropriate error message. If No, the row causes an error Default Mode: No Optimistic Mode: No Full Mode: No |
| Truncate character values to fit into column | SSMA truncates character values from MySQL that do not fit into SQL table columns and generates appropriate error message. Default Mode: No Optimistic Mode: No Full Mode: No |
| Zero Dates Migration | Specifies how to migrate zero dates like '0000-00-00' or '0000-00-00 00:00:00' in DATE and DATETIME columns. Default Mode: Set NULL Optimistic Mode: Set NULL Full Mode: Set NULL |
| Zero in Dates Migration | Specifies how to migrate dates with zero parts like '2009-01-00' or '2000-00-00 11:00:00' in DATE and DATETIME columns. Default Mode: Set NULL Optimistic Mode: Set NULL Full Mode: Set NULL |
Migration Engine
| Term | Definition |
|---|---|
| Migration Engine | Specifies database engine used during Data Migration. Client side data migration refers to SSMA client retrieving the data from the source and bulk inserting that data into SQL Server. Server side data migration refers to SSMA data migration engine (bulk copy program) running on the SQL Server box as a SQL Agent job retrieving data from the source and inserting directly into SQL Server thus avoiding an extra client-hop (better performance). Default Mode: Client Side Data Migration Engine Optimistic Mode: Client Side Data Migration Engine Full Mode: Client Side Data Migration Engine |
Important
When the Migration Engine option is set to Server Side Data Migration Engine, a new Project setting option Use 32-Bit Server Side Data Migration Engine is displayed. It specifies whether 32 bit or 64 bit Bulk Copy Program (BCP) utility is used to migrate data.
Misc
| Term | Definition |
|---|---|
| Extended Data Migration Options | Shows extra data migration options for each table in separate detail tab. Default Mode: Hide Optimistic Mode: Hide Full Mode: Hide |
| On Error | Stops Data migration when an error occurs. It has three options: Stop migration: Stops data migration operation Proceed to next table: Stops data migration to the current table and proceeds to the next one Proceed to next batch: Stops data migration to the current batch and proceeds to the next one Default Mode: Proceed to the next batch Optimistic Mode: Proceed to the next batch Full Mode: Proceed to the next batch |
Parallel Data Migration
| Term | Definition |
|---|---|
| Parallel Data Migration Mode | Specifies the mode used to fork threads to enable parallel data migration. In Auto mode, SSMA chooses the number of threads (10 by default) forked to migrate data. In Custom mode, user can specify the number of threads forked to migrate data (minimum is 1 and maximum is 100). Currently, only client side data migration engine supports parallel data migration. Default Mode: Auto Optimistic Mode: Auto Full Mode: Auto |
Important
When the Parallel Data Migration Mode option is set to Custom, a new Project setting option Thread Count is displayed. It specifies number of threads used for data migration.
Spatial Data
| Term | Definition |
|---|---|
| Handling Errors | Specifies how to handle errors in migration of values of spatial data types. If 'Replace with NULL' is specified, all spatial values causing errors will be replaced with NULL. No replacement is done otherwise. Default Mode: Generate an Error Optimistic Mode: Generate an Error Full Mode: Generate an Error |
| Value Validation | Specifies how to handle invalid spatial values. If 'Try Make Valid' is specified, an attempt is being made to modify invalid values to make them valid. Default Mode: Make Valid Optimistic Mode: Do Not Change Full Mode: Make Valid |