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:
- MySQL Workbench (opensource)
- Amazon DMS (cloud tool)
- 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”.
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:
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).
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:
2016-04-02T18:20:23 [TARGET_LOAD ]E: Failed to execute statement: 'ALTER TABLE `dbo`.`AO_38321B_CUSTOM_CONTENT_LINK` ADD CONSTRAINT `AO_38321B_CUSTOM_CONTENT_LINK_pk_AO_38321B_CUSTOM_CONTENT_LINK_ID` PRIMARY KEY ( `ID` )' [122502] ODBC general error. (ar_odbc_stmt.c:4048)
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 thru 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:
CREATE TABLE `AO_38321B_CUSTOM_CONTENT_LINK` ( `CONTENT_KEY` varchar(255) DEFAULT NULL, `ID` int(11) NOT NULL AUTO_INCREMENT, `LINK_LABEL` varchar(255) DEFAULT NULL, `LINK_URL` varchar(255) DEFAULT NULL, `SEQUENCE` int(11) DEFAULT '0', PRIMARY KEY (`ID`), KEY `index_ao_38321b_cus1828044926` (`CONTENT_KEY`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
AWS DMS generated:
CREATE TABLE `AO_38321B_CUSTOM_CONTENT_LINK` ( `CONTENT_KEY` varchar(255) CHARACTER SET ucs2 DEFAULT NULL, `ID` int(11) NOT NULL, `LINK_LABEL` varchar(255) CHARACTER SET ucs2 DEFAULT NULL, `LINK_URL` varchar(255) CHARACTER SET ucs2 DEFAULT NULL, `SEQUENCE` int(11) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
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:
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:
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):
# dbmigrate --source user:pass@sqlserverhost:1433 --target user:pass@mysqlhost:3309 --parallel 8 --verbose --overwrite
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.