Migrating data from MySQL to Microsoft SQL Server (and away from MySQL slowness)

I hit a stumbling block with MySQL that has made me seriously rethink using it in future. A simple JOIN query between two tables (one with 1k rows, 1 with 660k rows) was taking a very long time in MySQL. I had a look at indexing etc but couldn’t get the execution time down below 220 seconds on the test machine, no matter how the query was structured. I even considered adding redundant columns to the dataset to allow for indexing using integers, rather than varchars. While trying a few things I decided to test the exact same query on the same data set in Microsoft SQL Server Express 2005, where it took 4 seconds (1 second after the query had been cached).

The difference is obviously massive, far more than I was expecting. I then ran a few tests using DISTINCT, GROUP BY and COUNT(*) and noticed that in every instance, MySQL was slower than SQL Server, even on identical test machines. Considering the database is expected to grow massively over the next year we decided that it would be sensible to consider alternatives to MySQL. We already have a license for SQL Server so the decision was pretty easy (also, all my testing is currently done using SQL Server Express 2005).

Migrating the data across is not easy using .sql exports from MySQL Workbench as the data set is large (60mb of .sql) and the output file needs editing for SQL Server compatibility. Annoyingly SQL Server Express 2005 doesn’t support multiple INSERT VALUES queries (please upgrade to SQL Server 2008!) making it even more difficult.

I found a post on CodeProject by Niklas Henricson that explains how easy it is to migrate tables from MySQL to SQL Server using an ODBC connector from MySQL. You can download the ODBC connector from MySQL and create a connection which can then be easily used in SQL Server Management Studio to transfer data between MySQL and SQL Server. A summary of Niklas’ steps are:

  • Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools. Under the tab labelled as “System DSN”, press the “Add” button.
  • On the “Create New Data Source” dialog that appeared, choose MySQL ODBC 5.1 Driver and then press the “Finish” button.
  • After that, a MySQL connection configuration dialog will appear. Add your MySQL database account information in it, preferably the “root” account which has full access to your databases in MySQL. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else.
  • Press the “Test” button to ensure your connection settings are set properly and then the “OK” button when you’re done.
  • In this state, you are ready to establish a link towards MySQL database from your Microsoft SQL Server Management Studio. Open a query window and run the following SQL statement:

EXEC master.dbo.sp_addlinkedserver
@server = N’MYSQL’,
@srvproduct=N’MySQL’,
@provider=N’MSDASQL’,
@provstr=N’DRIVER={MySQL ODBC 5.1 Driver}; SERVER=my.server.ip.or.name; _
DATABASE=databasename; USER=username; PASSWORD=password; OPTION=3′

  • In the query window, run the following SQL statement to import table tablename from the MySQL database databasename, into the database in Microsoft SQL called SQLServerdatabasename.

SELECT * INTO SQLServerdatabasename.dbo.tablename
FROM openquery(MYSQL, ‘SELECT * FROM databasename.tablename’)

The potential for ODBC based migration of data is brilliant and openquery can be scripted to run across all the database tables. I am currently looking at a way of bulk importing an entire database from MySQL to SQL Server and will update this post in the future.

There are a few things to be wary of, such as keys, data types, creation of identity specifications etc.. but this can all be sorted once the data is migrated across. The speed is pretty good and shouldn’t be too much of a concern as this is a one time operation.

7 thoughts on “Migrating data from MySQL to Microsoft SQL Server (and away from MySQL slowness)

  1. Pingback: How to Migrate Data from MySQL to Microsoft SQL Server « Ralph Cavalier's Oracle Blog

  2. hi James: I am just curious were you able to find a solution for importing an entire database from MySQL to SQL Server?

    My problem right now is how to move(or copy) the database(or just two tables) from Linked Servers (MySQL) to my SQL SERVER 2008 database.

    Thank you

  3. Hi James:

    Thank you for the reply. The two tables i wanted to copy from MySQL to SQL Server 2008 have more than 1GB per table.

    in addition, the MySQL version they used before is 3.1

    Thanks again

  4. Hi, what is the purpose of adding “System DSN”… then on the script where should I assign “System DSN”?
    Thanks!!

  5. MySQL ODBC driver must be installed on SQL Server machine or on SQL Management studio IDE machine?? that might be my error… since here all is at same machine

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>