Microsoft SQL is one of the most popular DBMS with easy to use interface. Unfortunately, this database management system has some disadvantages like high total cost of ownership (TCO) for large projects and restrictive licensing policy. This is why some users consider migrating their databases from MS SQL to another DBMS. In order to reduce TCO they make a choice among open-source database systems.
There are two major and important open-source DBMS: MySQL and PostgreSQL. MySQL is more easy to configure and deploy than PostgreSQL, at the same time it provides most features that are expected from advanced database management systems: security, scalability, high performance, wide range of administration tools. This all makes MySQL the best choice for projects non-experienced users who require high level of reliability and data integrity.
The procedure of migrating database from SQL Server to MySQL consists of several steps:
- export table definitions from the MS SQL database as SQL DDL statements
- convert these statements into MySQL format and import to the target server
- export MS SQL data into comma separted values (CSV) files
- if it is necessary, make transformations in order to comply with MySQL format and load the data into the target database
This is how you can export table definitions from MS SQL database in form of SQL DDL statements:
- SQL 2008 and earlier allows scripting objects and data. Right-click on database name in Management Studio, select ‘Tasks’ menu, ‘Generate Scripts’ item. Go through the wizard and make sure to check ‘data’ which is false by default
- SQL 2012 and later can do the same as follows: right-click on database in Management Studio, select ‘Tasks’ menu, ‘Generate Scripts’ item. On the ‘Set scripting options’ tab click on ‘Advanced’, then select ‘data only’, or ‘data and schema’ for ‘Types of data to script’ (in the ‘General’ section)
The resulting script must be improved before loading into MySQL as follows:
- remove MS SQL specific statements (i.e. “SET ANSI_NULLS ON”, “SET QUOTED_IDENTIFIER ON”, “SET ANSI_PADDING ON”)
- replace square brackets around database object names by double quotes
- remove square brackets around types
- remove all optional keywords that are not supported by MySQL like ‘WITH NOCHECK’, ‘CLUSTERED’
- remove all reference to filegroup such as ‘ON PRIMARY’
- replace types ‘INT IDENTITY(…)’ by ‘INT AUTO_INCREMENT’
- replace Microsoft SQL query terminator ‘GO’ by MySQL one ‘;’
The data also can be exported via Microsoft SQL Management Studio as follows: right-click on database, select ‘Tasks’ menu, ‘Export Data’ item. Go through the wizard and select ‘Microsoft OLE DB Provider for SQL Server’ as data source, ‘Flat File Destination’ as the destination. After export procedure is completed, all data is exported into the specified CSV file.
If SQL Server database contains binary data, it is required to apply the workaround. Select ‘Write a query to specify the data to transfer’ option on ‘Specify Table Copy or Query’ wizard page. On the next wizard page compose SELECT-query as follows:
select , , cast(master.sys.fn_varbintohexstr(
cast( as varbinary(max))) as varchar(max)) as
Note, this trick does not work on large binary data (1MB+) because the query runs into infinite hang. The resulting CSV files are imported into MySQL database via mysqlimport tool.
The steps above are proving that database migration from Microsoft SQL to MySQL is a complicated procedure requiring a lot of efforts when doing it manually. It can cause data loss or corruption due to the human factor. Fortunately, there are special tools to automate MS SQL to MySQL conversion. One of these tools is MSSQL to MySQL by Intelligent Converters.