How to Migrate Microsoft SQL Server to MySQL Database

by Luke P. Issac on March 2, 2014

If you are using mostly open source in your enterprise, and have few MS SQL server database around, you might want to consider migrating those to MySQL database.

The following are few reasons why you might want to consider migrating Microsoft SQL Server to MySQL database:

  • To avoid huge License and support fees of MS SQL Server. In MySQL, even if you decide to use the MySQL enterprise edition, it is less expensive.
  • Unlike SQL Server, MySQL supports wide range of Operating Systems including several Linux distros, Solaris and Mac.
  • To implement a highly scalable database infrastructure
  • To take advantage of several advanced features of MySQL database that have been tested intensively over the years by a huge open source community

We can migrate MS SQL database to MySQL using migration module of “MySQL Workbench” utility.

The most easiest way to install MySQL Workbench is to install “Oracle MySQL installer for windows”, which installs several MySQL tools including the Workbench.

Download and install this MySQL Installer, which includes Workbench and other necessary connectors and drivers required for the migration.

The following is an overview of the steps involved in the migration of MsSql database to MySQL using Workbench migration wizard.

1. Take care of Prerequisites

Before starting the MySQL database migration wizard in Workbench, we need to ensure that ODBC drivers are present for connecting to the source Microsoft SQL Server database, as it is not bundled with Workbench.

Verify that the max_allowed_packet option in the MySQL server is sufficient for the largest field to be migrated.

Ensure that we can connect to both destination MySQL server database, and source MsSQL Server database with appropriate privileges that are required for migrating the data across.

In the MySQL Workbench, the migration wizard will display the following “Migration task list” that you’ll need to go through to finish the migration.

MySQL Workbench Migration Overview

2. Select Source and Target Database

First, define the source Microsoft SQL Server database connection parameter. Select “Microsoft SQL Server” from the database system dropdown list. In the parameters tab, select the DSN, and specify the username to the source database.

MySQL Workbench Migration Select Source

Next, define the destination MySQL database connection paramter. Select “Local Instance MySQL” or “Remote Instance MySQL” depending on your situation. In the parameters tab, specify the hostname or the ip-address where the MySQL database is running, the MySQL port, username. If you don’t specify the password, it will prompt you.

MySQL Workbench Migration Select Target DB

Once you specify the source and destination, all available schemas and databases will be listed. You can select the specific schema that you like to migration (or select all), and you can also specify custom schema mapping to the destination MySQL database.

MySQL Workbench Migration Select Schema

3. Migrate the Objects

In this step the Microsoft SQL Server schema objects, table objects, data types, default values, indexes, primary keys are converted. Please note that view object, function objects and stored procedures are just copied and is commented out as we will need to convert those manually.

4. Data Migration

In this step the automated copy of data is done from source to destination database for the migrated tables.

MySQL Workbench Migration Data Transfer

Please note that using the migration wizard we can only convert tables and copy data but cannot convert the triggers, views and stored procedures. We’ll have to do those manually, which we might cover in one of the future article on how to migrate MS SQL stored procedures to MySQL stored procedures.


Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 9 comments… read them below or add one }

1 Daniel March 3, 2014 at 8:36 am

Thank you, will definitely give it a try

2 Anonymous March 3, 2014 at 9:38 am

Nice one…..many thanks..

3 pathum March 3, 2014 at 9:39 am

Nice one …..many thanks.

4 Bruno March 3, 2014 at 2:34 pm

Very interesting! Thanks for sharing!

5 Jeremy March 3, 2014 at 3:05 pm

Very Relevant, thanks. The next step is how to make 3rd party apps which currently connect to SQL Server to connect to MySQL instead ? They don’t seem to use ODBC, but connect “natively” somehow to the server in a Windoze environment. :)

6 Jalal Hajigholamali March 4, 2014 at 11:01 pm

Hi,
Very useful article for migration

7 Sathish March 7, 2014 at 12:24 am

I have used SQLyog for this. SQLyog provides Import External Data to streamline the transfer of data from any ODBC compliant data source to MySQL.

8 AC July 30, 2014 at 3:45 am

Excellent.
As I am new for MsSQL and MySQL, I have some questions.

1. It is just setup one time.
2.After data migration, do you mean that we can use MySQL/PHP program to browse and update MsSQL database?

9 Xin August 4, 2014 at 2:53 pm

Thank you for your sharing.

I’m using the Workbench migration tool to migrate a MSSQL schema to MySQL. it migrates the schema successfully without any errors. However, when I tried to migrate data, I got “Not enough memory to allocate insert buffer of size 1073741824″ error.

I tried to set the bulk_insert_buffer_size to 1073741824 and 2147483648, it doesn’t work.

max_allowed_packet = 1073741824

Can you please advise what I should do? any recommendation will be appreciated.

Thank you!

Leave a Comment

Previous post:

Next post: