Microsoft SQL is one of the most widespread database management systems with simple to use interface. Sadly, this database management system has some disadvantages like the high total cost of ownership for big projects and restrictive licensing policy. This is why some users think about migrating their databases from MS SQL to a different database management system. So as to scale down the total cost of ownership they choose an alternative among open-source database systems.
There are two major and popular open-source DBMS: MySQL and PostgreSQL. MySQL is easier to set up and deploy than PostgreSQL, at the same time it provides most options that are expected from advanced database management systems such as security, scalability, high performance, a wide selection of administration tools.
This all makes MySQL the most effective alternative for projects non-experienced users who need a high level of reliability and data integrity. The transfer of data from MSSQL to MySQL isn’t that much complex and can be done by an inexperienced person if they strictly follow by the method use for transfer of database from SQL to MySQL.
The procedure of transferring 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 separated values (CSV) files
- If it’s necessary, create transformations so as to adjust to MySQL format and load the data into the target database
This is how you’ll export table definitions from MS SQL database in form of SQL DDL statements:
- SQL Server version 2008 and earlier permits scripting objects and data. Right-click on the database name in Management Studio, choose ‘Tasks’ menu, ‘Generate Scripts’ item. Go through the wizard and ensure to check ‘data’ that is false by default
- SQL Server version 2012 and later can complete the same task as follows: right-click on the database in Management Studio, choose ‘Tasks’ menu, ‘Generate Scripts’ item. On the ‘Set scripting options’ tab click on ‘Advanced’, then choose ‘data only’, or ‘data and schema’ for ‘Types of data to script’ (in the ‘General’ section)
The resulting script should 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 a quotation mark
- remove square brackets around types
- remove all optional keywords that aren’t supported by MySQL like ‘WITH NOCHECK’, ‘CLUSTERED’
- remove all reference to filegroup like ‘ON PRIMARY’
- replace types ‘INT IDENTITY(…)’ by ‘INT AUTO_INCREMENT’
- replace Microsoft SQL query terminator ‘GO’ by MySQL one ‘;’
The data can also be exported via Microsoft SQL Management Studio as follows: right-click on the database, choose ‘Tasks’ menu, ‘Export Data’ item. Go through the wizard and choose ‘Microsoft OLE DB provider for SQL Server’ as data source, ‘Flat File Destination’ as the destination. After the export procedure is completed, all data is exported into the required CSV file.
If SQL Server database contains binary data, it’s needed to apply the workaround. Select ‘Write a query to specify the data to transfer’ option on ‘Specify Table Copy or Query’ wizard page. On following wizard page compose SELECT-query as follows:
select , , cast(master.sys.fn_varbintohexstr(
cast( as varbinary(max))) as varchar(max)) as
from ;
Note, this trick doesn’t work on large binary information (1MB+) because the query runs into the infinite hang. The resulting CSV files are imported into MySQL database via MySQL import tool.
The steps above are proving that database migration from Microsoft SQL to MySQL could be a difficult procedure requiring a lot of efforts when doing it manually. It can cause data loss or corruption as a result of the human factor. Fortunately, there are special tools to automate the entire process of database conversion. One amongst these tools is MS SQL to MySQL by Intelligent Converters.
The program provides all capabilities required for this kind of converter:
- Converts schemas, data, indexes, constrains and views with all necessary attributes
- Supports all versions of Microsoft SQL Server and Azure SQL
- Supports all versions of MySQL running on Linux/Unix and Windows, MariaDB and Percona
- Option to convert the source database into MySQL script file containing SQL statements to create all required database objects and replicate the data
- Option to modify resulting table structure (rename columns, change type and attributes)
- Option to filter data using SELECT-queries
- Option to synchronize the destination database with SQL Server data
- Supports multibyte character sets
- Supports command line in order to script, schedule and automate the conversion process
