Easy way to migrate MS SQL to MySQL

Step 1 of 7. Select Conversion Mode

On this wizard page users should select the migration scenario:

• Move to MySQL server directly - select this option for direct migration into database (access to the target database server is required along with necessary privileges to write into database).

  See Connect to MySQL Server article for more information.

• Store into dump file - following this approach the program exports database from SQL Server to MySQL script file. This file contains MySQL statements to create tables and to fill them with the data.

  See this article for information about how to import script files to MySQL server.

Enter the name of SQL Server or Azure SQL instance in MS SQL Server field or leave it empty if connecting to the local server. Custom port (other than 1433) can also be specified in this field as follows:

Sql_server_name,1234

where 1234 is the custom port. Then select the type of authentication to use when connecting to SQL Server:

• Windows authentication - SQL Server validates the connection via Windows user information. The user has to be authenticated by Windows before access is granted.
• SQL Server authentication - SQL Server will use the standard security validation. To use this option, specify user name and password recognized on the target MS SQL Server.

By default local connection mode is uses, it is indicated by Local radio button. To connect remotely, select Remote radio button and specify host and port values. Host can be either network name or IP address. Port is necessary only if it differs from the default 3306. If you need to specify custom port for local connection, select Remote and type localhost or 127.0.0.1 into Host box.

Finally, user name and password are entered. If the target server allows anonymous connection, these fields can be empty.

MySQL user should have sufficient privileges to create new database on the target server. Otherwise, it is only possible to import MS SQL data into an existing database.

In cause of error on this stage, refer to the following articles for workaround:

On this wizard page users should specify formatting settings for the resulting script file:

• MySQL version - determines the syntax of SQL statements. Old versions (before 3.23.6) apply more restrictions on syntax of queries. 'Old' syntax is accepted by 'new' versions of MySQL but not vice versa.
• Line breaks - specifies how to separate text lines in the dump file. 'MS Windows style' option means to use '\r\n', 'Unix style' means to use '\n' as end of line.
• Add lock for write around 'INSERT' statements - makes import of the dump file into database much faster. To use this feature in MySQL 4.0 and higher it requires global LOCK TABLES privilege and SELECT privilege on the involved tables. In MySQL 3.23 and earlier user also needs to have SELECT, INSERT, DELETE and UPDATE privileges for the tables.

Other options are self-explained. See Import MySQL dump files article to learn how to import script file to the database server.

• Overwrite the entire database
• Overwrite existing tables only
• Skip existing tables
• Merge
• Synchronize

Existing MySQL script file is always overwritten.

Specify log file to enable the program write execution traces. It may be used to analyze possible error messages and get more control over the conversion process. If no log file is specified, the logging is disabled.

• Convert views - select this checkbox to convert views
• Do not migrate the data - select this checkbox to convert only table definitions of MS SQL database. All the data will not be migrated.
• MySQL character set - tune this parameter to set up correct encoding of text values in the database. Please refer to MySQL documentation to better understand the concept of character sets and collations.
• MySQL engine type - storage engine determines basic characteristics of handling tables such as storage limits, transaction-safe, locking granularity, etc. Each of them has its own cons and pros. Please refer to MySQL documentation for the further information.
• Remove schema name - select this checkbox to not prepend target table name with MS SQL schema.
• Skip converting indexes - select this checkbox if you don't want to convert indexes

To add new table select it in Available tables list and click Add button (or double-click the highlighted item). Use Add all button to add all available tables. To remove an item from Selected tables list highlight it and click Remove button. Use Remove all button to remove all items from Selected tables list box.

Push Add Query button to add SELECT-query filtering MS SQL data that will be migrated to MySQL server. See Using Queries article for the further information.

The converter allows to edit table or query before migration. For this purpose, double click the corresponding item in Selected tables list box. If it is a table, dialog box appears to edit table attributes and to define custom type mapping. See Edit Table article for the further information.

If selected item is a query, you will see dialog box to edit SQL-statement of the query.

To add new MSSQL view for migration, highlight it in "Available queries" list and click "Add" button (or double-click the highlighted item). Use "Add all" button to add all available SQL Server views. To remove an item from "Selected queries" list, highlight it and click "Remove" button. Use "Remove all" button to remove all items from "Selected queries" list.

MSSQL-to-MySQL does not recognize dependencies for views, user should reorder them so that core views go before dependent ones. Highlight the view and use up and down arrows on the right part of the wizard page to change order of conversion.

The converter handles about 90% of syntax constructions involved into MS SQL CREATE VIEW statements. Some of the translation rules are specified in this article.