Convert Microsoft SQL to MySQL

Being one of the most popular database management systems, SQL Server comes with high total cost of ownership (TCO) for large projects and restrictive licensing policy. This is the reason of migrating databases from MS SQL to an open-source DBMS for some companies and organizations.

There are two advanced open-source systems that can handle large and sophisticated databases: MySQL and PostgreSQL. MySQL offers the most important features expected from advanced DBMS such as security, scalability, high performance, wide range of administration tools. At the same time, it is easier to configure and deploy than PostgreSQL. This makes MySQL a reasonable choice for small and medium size companies requiring high level of reliability and data integrity.

The procedure of SQL Server to MySQL database migration combines the following steps:

• MS SQL table definitions are exported from the database in form of DDL statements
• these statements are converted into MySQL format with respect to appropriate type mapping and imported into the target database
• SQL Server data is exported into comma separated values (CSV) files
• necessary transformations are made over the data, so it is complied with MySQL format
• the resulting data is imported into MySQL database

Table definitions can be extracted from MS SQL database in form of DDL statements as follows:

• In SQL 2008 and earlier right-click on database name in Management Studio, select ‘Tasks’ > ‘Generate Scripts’. In the appeared wizard dialog check ‘data’ which is false by default
• In SQL 2012 and later right-click on database in Management Studio, select ‘Tasks’ > ‘Generate Scripts’. On the ‘Set scripting options’ tab of the appeared window click on ‘Advanced’, then select ‘data only’ or ‘data and schema’ for ‘Types of data to script’

Before importing into MySQL database, the script must be processed as follows:

• All SQL Server specific statements like “SET ANSI_NULLS ON”, “SET QUOTED_IDENTIFIER ON”, “SET ANSI_PADDING ON” must be removed
• Square brackets around object names must be replaced by double quotes
• Square brackets around types must be removed
• ‘IDENTITY(…)’ must be replaced by ‘AUTO_INCREMENT’
• All keywords that are not supported by MySQL like ‘WITH NOCHECK’, ‘CLUSTERED’ must be removed
• All references to filegroup such as ‘ON PRIMARY’ must be removed
• SQL Server query terminator ‘GO’ must be replaced by MySQL equivalent ‘;’

Microsoft SQL Management Studio allows to export the data as follows: right-click on database, select ‘Tasks’ > ‘Export Data’. Go through the wizard dialog, select data source ‘Microsoft OLE DB Provider for SQL Server’ and target ‘Flat File Destination’. At the completion of export procedure, data will be stored into the specified CSV file.

Binary data requires some pre-processing before loading into MySQL. On ‘Specify Table Copy or Query’ wizard page select ‘Write a query to specify the data to transfer’ option. Then compose SELECT-query on the next wizard page as follows:

select {non-binary column #1}, {non-binary column #2}, cast(master.sys.fn_varbintohexstr(

cast({binary column name} as varbinary(max))) as varchar(max)) as {binary column name} from {table name};

Note, the workaround does not work for binary data larger that 1MB, the query goes into infinite hang. The data stored in CSV files can be imported into MySQL database using the standard mysqlimport tool.