MySQL to SQLServer Converters
There are many stories about database migration from MS SQL to MySQL motivated by benefits of the open source DBMS. However, some organizations may decide to switch from cloud solutions back to on premise database management system or host server infrastructure on the Windows platform. In these cases, task of database migration from MySQL to SQL Server must be solved.
There are multiple options available for this procedure. First, MySQL databases can be migrated to SQL Server using the Microsoft SQL Server Migration Assistant for MySQL (SSMA for MySQL) tool. This tool is specifically designed to automate the migration process for users. In this article, we will provide a detailed, step-by-step guide on how to use SSMA for MySQL to perform the conversion. Additionally, we will also explore an alternative solution that can offer a quick and efficient way to convert your database.
To begin the database migration, open the SSMA for MySQL tool. From the initial window, select the ‘File’ menu and click on ‘New Project.’ In the appeared dialog box, specify a name for the conversion project and choose a location to save it. Navigate through ‘Migrate To’ drop-down list to select the appropriate SQL Server version.
Next, navigate to the ‘Connect to MySQL’ tab and provide the necessary parameters to establish a connection with your source (MySQL) database that you wish to convert. Once you have entered the correct information, click ‘Connect. The tool will then display a window showing the list of objects in your MySQL database. From this list, select the objects that you want to migrate to your MS SQL Server database and click ‘OK.’
Before proceeding with the conversion, it is important to verify that the data type mapping between MySQL and MS SQL is accurate. Although MySQL supports various SQL standard data types such as numeric types, string types, date and time types, etc., some data types in MySQL may differ from those in SQL Server. To ensure the proper mapping of data types, follow these steps:
In the SSMA for MySQL tool, select the ‘Tools’ option and click on ‘Project Settings.’ From the ensuing dialog box, select the ‘Type Mappings’ tab. To modify the data type mapping of a table, choose a table from the ‘MySQL Metadata Explorer.’
Performing the database schema conversion is a critical step when migrating from MySQL to SQL Server. In this step, you will convert the MySQL database objects into equivalent SQL Server objects and load the database schema information into the SSMA for MySQL metadata. Click on the ‘Connect to SQL Server’ and take the following steps in the ‘Connect to SQL Server’ dialog box:
- Choose ‘SQLServer’ as the Server name.
- Select an existing database from the ‘Database’ drop-down list or enter a new name to create a database on the target SQLServer or Azure SQL.
- Provide the authentication details, and then click ‘Connect.’
- Under the ‘MySQL Metadata Explorer,’ right-click on the MySQL database that you want to convert and select ‘Convert Schema.’
- Verify that the converted objects match the original objects once the conversion is complete.
Now all the prerequisites are fulfilled and everything is set up for migration itself. To complete MySQL to MS SQLdatabase migration, follow these steps:
- Right-click on the SQL database that you want to convert and select ‘Synchronize with Database.’ This action will publish the chosen MySQL database to an instance of SQL Server.
- Review and confirm the mapping between the MySQL source project and the SQL destination. Then, click ‘OK.’
- To convert the database or object, right-click on it, and select ‘Migrate Data.’
- The selected MySQL database objects will then be converted to MS SQL.
As you can see, there is a lot of steps required to launch database migration using this way. Next approach is fully automated as it is relied on the dedicated software tool known as MySQL to SQLServer converter. It is developed by Intelligent Converters, a software vendor being focused on database migration and synchronization for years.
The product works with all version of on premises MySQL running on Unix, Linux or Windows platforms. It also supports MariaDB and Percona forks as well as cloud (DBaaS) variations of DBMS. All version of SQL Server and Azure SQL are supported. MySQL to SQL Server converter can edit tables before migration customizing name, type and other properties of any column or excluding particular columns from migration. Existing MS SQL tables can be merged or synchronized with MySQL data, primary key or unique index is required for this purpose.
Finally, the option of exporting MySQL database into T-SQL script file may be help when the target SQL Server does not allow remote connections. Installation pack of MySQL to SQL Server converter contains command-line version of the tool can be used to schedule and scrip the database migration.