Both database management systems have their own strengths and weaknesses. However, migrating from MS SQL to MySQL can have advantages such as access to the source code, cross-platform compatibility, and the freedom to choose server platforms.
Approaches to Migration
There are several approaches to migrate a database from MS SQL to MySQL, including manual migration, half-automated migration, and fully automated migration using commercial tools. Each approach has its own strengths and weaknesses, and it is important to validate the results of the migration to ensure that all database objects have been transferred properly.
Schema Migration
The task of schema migration consists of mapping MS SQL data types into the most appropriate MySQL equivalent and translation of column attributes such as length, default values, NULL or NOT NULL flags, scale, precision, etc.
Although MS SQL and MySQL have a lot of equal data types, some of them require the appropriate conversion as it is listed in table below:
SQL Server MySQL
=================================================
BIT BOOLEAN or TINYINT(1)
DATETIMEOFFSET TIMESTAMP
IDENTITY AUTO_INCREMENT
NTEXT, NATIONAL TEXT TEXT CHARACTER SET UTF8
SMALLDATETIME DATETIME
MONEY DECIMAL(19,4)
SMALL MONEY DECIMAL(10,4)
UNIQUEIDENTIFIER CHAR(38)
SYSNAME CHAR(160)
XML TEXT
Data Migration
The migration process involves converting table structures, migrating data, and validating the results to ensure that everything has been migrated properly. Data can be migrated using Data Transformation and Integration Services or a combination of the SQL Server bulk copy program and the MySQL statement ‘LOAD DATA INFILE’.
Available Solutions
There are some migration tools for MS SQL and MySQL that support all major versions of SQL Server, including 2012, 2014, 2016, and work with Unix, Linux, and Windows MySQL, including variations such as MariaDB and Percona. Some database converters also offer the extra advantage of supporting Azure SQL, Microsoft SQL DBMS’s cloud variation.
Basic features of SQL Server to MySQL converters include:
- supporting all MS SQL data types, accurately converting table definitions, primary keys, indexes, and constraints with all necessary attributes
- intelligently processing data according to MySQL’s destination format
- converting MS SQL views into MySQL format
- supporting command line for scripting and scheduling the conversion
- allowing to overwrite, merge or synchronize MS SQL data with an existing MySQL table
- supporting Windows 7/8/10
- user-friendly interface, and full install and uninstall support.
MS SQL to MySQL conversion software’s price ranges from zero to multiple hundreds of US dollars. Free converters are not recommended because they often come without technical support and guarantees of conversion quality. The ideal performance-price proportion is found in conversion utilities with a price of around $50.
One such tool is Intelligent Converters’ MS SQL to MySQL migration tool, which has all the listed capabilities and provides additional advantages. For instance, in cases where the MySQL server does not accept remote connections, the program can export data into a SQL script file. In this approach, MS SQL data is exported into a local file containing data definition (DDL) statements to create tables with all indexes and constraints and fill them with data. Later, a database specialist can import this file into MySQL server using standard tools like phpMyAdmin.
Moreover, sometimes it is necessary to convert partial data from a table or merge data from multiple tables into a single one. For these purposes, MSSQL to MySQL converter allows converting the result of SELECT queries running against the source SQL Server database. Additionally, if there is a need to change a column name or type in the resulting database, the converter provides a feature called “custom column mapping.” This feature allows changing the name, type, default value, and NULL-attribute for any column of an MS SQL table as well as exclude certain columns from conversion.
