Migrate Database from SQL Server to MySQL
Reasons to Migrate
SQL Server is a popular RDBMS enriched with user-friendly and easy to learn interface, comprehensive online documentation and wide collection of tools and applications. However, a lot of organizations are migrating their databases from Microsoft SQL to MySQL due to the following advantages of the target DBMS: free license, wide range of supported OS and easy integration with web.
About Migration
Migration of schema, data and indexes to MySQL is not a challenging task because MySQL supports data types, table designs, and index structures in the way similar to SQL Server. However, some data types need to be properly mapped according to the 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
Also, SQL Server and MySQL support different set of embedded functions, so some of them must be replaced by equivalents in the destination DBMS during the migration. The table below explores such equivalents:
SQL Server MySQL
CONVERT($type, $expression) CAST($expression AS $type)
LEN($expression) LENGTH($expression)
DATEADD(year, 1, $expression) $expression + interval 1 year
DATEADD(month, 2, $expression) $expression + interval 2 month
DATEADD(day, 3, $expression) $expression + interval 3 day
GETDATE() NOW()
‘string1’ + ‘string2’ CONCAT(‘string1’, ‘string2’)
CONTAINS($expr, $templ) $expr LIKE $templ
CHARINDEX ($exp1, $exp2) LOCATE($exp1, $exp2)
Approaches to Migration
Basically, there are two options to migrate database from SQL Server to MySQL, below strong and weak sides of each option are listed.
Manual migration provides that database specialist migrates all database objects manually based on standard tools provided by DBMS vendors. For example, MS SQL Server provides Export and Import tool. It uses ODBC Drivers for conversion, so it is necessary to to create ODBC Data Source before running the migration. If Import & Export tool is launched on the same machine where MS SQL database is running, ODBC driver is automatically installed by SQL Server. MySQL ODBC driver is required as well.
Another option is fully automated migration via dedicated software. This approach requires minimal human efforts since the tool handles all aspects of migration properly. However, even this method assumes some manual post processing because none of converters can migrate such database entries as stored functions, procedures and triggers due to complicated nature of these objects.
It is expected that SQL Server to MySQL migration tool meets the following requirements:
- all modern versions of the source and destination database management systems are supported including SAAS variations such Azure SQL
- all primary database entries such as schemas, data, indexes, constraints and views are converted
- the tool should allow to customize the conversion (change name, type and attributes of any table column)
- besides the option to create new MySQL database, the program should be able to merge SQL Server data into existing tables
- support of command line would be a valuable advantage to automate the database migration
One of such tools is SQL Server to MySQL converter developed by Intelligent Converters software company. The product provides all the capabilities listed above as well as some other useful features. For those tasks requiring partial data migration the converter provides filtering via SELECT-queries. This option allows to migrate result of SELECT query as it would be a regular table. This feature can be used to limit columns or rows being migrated, implement preprocessing over the data, merge multiple tables into a single one.
The converter can migrate data from SQL Server to MySQL even if the target server does not allow remote connections. Using this option MS SQL data is exported into local MySQL script file instead of direct transfer to the target server. The script file includes MySQL statements to create all tables, indexes, constraints and to insert the data.
For both options listed above the database specialist must validate the results to make sure all database objects have been transferred properly.
