Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Migrate From MS SQL Server to MySQL

DZone's Guide to

Migrate From MS SQL Server to MySQL

This posts walks you through several methods and tools you can use, and the pitfalls you can avoid, when migrating data from MS SQL servers to MySQL.

· Database Zone
Free Resource

MongoDB Atlas is a database as a service that makes it easy to deploy, manage, and scale MongoDB. So you can focus on innovation, not operations. Brought to you in partnership with MongoDB.

In this blog series, I will share my experiences as I migrate commercial databases (i.e., Microsoft SQL or Oracle) to open source (MySQL). More specifically, we will look at how you can migrate from MS SQL Server to MySQL.

For this first blog post I've chosen Jira database as an example, and used three different tools to migrate Jira database in Microsoft SQL Server to MySQL:

  1. MySQL Workbench (open source).
  2. Amazon DMS (cloud tool).
  3. Ispirer MnMTK 2015 (commercial tool).

When I started my research, I was under the impression that Jira database would be easy to migrate (no stored procedures, no triggers, etc.). It turned out that there were some problems that I was able to fix.

One of the reasons I chose Jira as opposed to some standard MS SQL database (such as AdventureWorks2014) is that it is a non-standard choice. Most of the software vendors use standard databases to test their software, and it works perfectly on those standard databases. Jira is not a usual choice and will be closer to real life.

MySQL Workbench

MySQL Workbench supports Microsoft SQL Server migration. The migration is straightforward except the issues with character sets. I have experienced the error "Could not successfully convert UCS-2 string to UTF-8."

workbench_errors

It turns out (with the help of Martin Brennan's blog) that we will need to use "ODBC (FreeTDS)" drive for MS SQL, and enable sending Unicode data as UTF8:

workbench_drive_params

After changing those settings, I was able to successfully migrate Jira database from MS SQL to MySQL.

Advantages and disadvantages:

  • Plus: free and open source tool, multi-platform.
  • Plus: successful migration for Jira.
  • Plus: supports multi-threaded migrations (increase worker tasks if needed, default value is 2)
  • Minus: needed some tweaks to work with character sets.
  • Minus: not very easy to debug errors.

Amazon DMS

AWS Database Migration Service supports migrating from MS SQL to MySQL, but the actual migration method is different from other tools. It uses the source database (MS SQL server in this case) replication feature to stream the data to the target database (MySQL). Amazon DMS starts a temporary "migration" instance that is used to stream data. Both the source and destination database can be in AWS (EC2 or RDS) or outside AWS (no restriction).

amazon_dms

The important limitation for MS SQL migration: It only works with MS SQL Server versions that support replication (subscription service). It doesn't work with MS SQL Express Edition. Also, if the subscription service is not enabled the DMS can’t even see the schema(s) to migrate (full list of limitations for MS SQL Server migration).

I've also gotten errors around the "constraint" name being too long:

The problem here is that "AO_38321B_CUSTOM_CONTENT_LINK_pk_AO_38321B_CUSTOM_CONTENT_LINK_ID" is too long a string for MySQL. At the same time, this name does not really matter as this is the PRIMARY KEY.

After changing the "constraint" name in MS SQL to smaller strings, I could migrate all tables to MySQL.

Amazon DMS notes: Amazon DMS lets you migrate from a database located anywhere (not necessarily in AWS) to another database located anywhere (not necessarily in AWS) — however, the traffic will go through AWS. Thus the migration path is fastest and the most beneficial if either the source or target (or both) instances are in AWS (for example, ec2 or rds instances).

Advantages and disadvantages:

  • Plus: successful migration for Jira.
  • Plus: Multithreaded execution (this is a huge advantage for migrating large databases).
  • Plus: In addition to migration, you can also use a replication link between the SQL Server and the new MySQL to fetch the new changes. This is a huge advantage when migrating a large database with lots of traffic and tight downtime requirements.
  • Minus: Replication should be enabled to perform a migration, which means that migrating from SQL Server Express isn’t supported.
  • (Can be plus and minus): All traffic is going through a cloud environment.

Potential Issues

I’ve figured out that the table structures generated by Workbench and Amazon DMS are different. For example:

Workbench generated:

AWS DMS generated:

In AWS DMS version, the index on CONTENT_KEY is missing and ID is not declared as auto_increment. However, the Workbench "lost" the character set.

At the same time, I was able to start Jira on top of two versions of the MySQL database (migrated by using Workbench and Amazon DMS).

Ispirer MnMTK 2015

Ispirer MnMTK 2015 toolkit is a commercial (not open source) software application that lets you migrate from MS SQL Server to MySQL (among other databases). Ispirer has provided me with a demo license so I can test the migration.

I was able to migrate the Jira database from MS SQL to MySQL with the Ispirer SQLWays Wizard:

sql_ways_wizard

One issue with this process is that SQL Ways Wizard relies on the MySQL command line utility ("MySQL"), which should be in the path. If you do not have MySQL installed on the migration machine, or it is not in the path, the migration will fail:

sql_ways_no_path

To fix simply add the MySQL "bin" directory to the path. In addition, you can use the SQL Ways Wizard to generate scripts and run those scripts on the destination host where the utilities are installed.

Advantages and disadvantages:

  • Plus: Successful migration for Jira.
  • Plus: Support from SQL ways: can work on fixing potential migration issues (requires paid license).
  • Plus: Can convert stored procedures, triggers, foreign key constraints.
  • Minus: Commercial, not open source software.
  • Minus: Only runs on Windows (however, target machine for the database migration can be Linux).
  • Minus: No multi-treaded migration support (can manually run multiple instances of SQL Ways).

Conclusion

All tools I tried finally worked, but at the same time, I was surprised with the number of issues I found. Migrating a very simple database (no stored procedures, no triggers, no foreign key constraints) should be easier.

Another surprise was that all tools are focused on a nice GUI with "next" buttons. For migrating one database to another, I would prefer using a command line tool interface (may be similar to Percona toolkit or iconv):

Actually, Ispirer MnMTK does have a command line migration utility included, but it only works on Windows.

Until somebody develops a better command line tool, any of the above solutions will help you migrate from MS SQL Server to MySQL.

MongoDB Atlas is the best way to run MongoDB on AWS — highly secure by default, highly available, and fully elastic. Get started free. Brought to you in partnership with MongoDB.

Topics:
server ,microsoft sql server ,jira ,database ,migration ,mysql ,sql

Published at DZone with permission of Alexander Rubin, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}