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

There are two open-source database management systems that can manage large and advanced databases: MySQL and PostgreSQL. MySQL provides the vital features anticipated from advanced level DBMS such as for instance security, scalability, high performance, wide range of management tools. At that time, it’s simpler to configure and deploy than PostgreSQL. This makes MySQL a choice that is reasonable little and moderate size companies needing high level of reliability and information integrity.

The task of SQL Server to MySQL database migration combines the actions being following:

• MS SQL table definitions are exported through the database in kind of DDL statements
• these statements are changed into MySQL format with respect to kind that is acceptable and brought in into the target database
• SQL Server information is exported into comma values which are separate CSV) files
• necessary transformations are made throughout the data, so it is complied with MySQL format
• the info which are ensuing brought in into MySQL database

Dining table definitions could be removed from SQL Server database in kind of DDL statements the following:

• In SQL 2008 and earlier right-click on database title in Management Studio, choose ‘Tasks’ > ‘Generate Scripts’. In the wizard that is showed up check ‘data’ which is false by default
• In SQL 2012 and later right-click on database in management generally Studio, select ‘Tasks’ > ‘Generate Scripts’. On the ‘Set choices which are scripting tab regarding the appeared window click on ‘Advanced’, then select ‘data only’ or ‘data and schema’ for ‘Types of information to script’

The script should be prepared as follows before importing into MySQL database

• All SQL Server statements which can be certain “SET ANSI_NULLS ON”, “SET QUOTED_IDENTIFIER ON”, “SET ANSI_PADDING ON” should be removed
• Square brackets around item names must certainly be replaced by dual quotes
• Square brackets around types needs to be eliminated
• ‘IDENTITY(…)’ must certainly be replaced by ‘AUTO_INCREMENT’
• All keywords that aren’t supported by MySQL like ‘WITH NOCHECK’, ‘CLUSTERED’ needs to be eliminated
• All references to filegroup such as ‘ON PRIMARY’ should be eliminated
• SQL Server query terminator ‘GO’ must be replaced by MySQL equivalent ‘;’

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

Binary data requires some pre-processing before loading into MySQL. On ‘Specify Table Copy or Query’ wizard page choose ‘Write a query to specify the data to transfer’ choice. Then compose SELECT-query on the wizard that is next 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 unlimited hang. The information saved in CSV files can be imported into MySQL database using the mysqlimport tool that is standard.