Database migration from proprietary engines such as SQL Server into open source database management systems such as MySQL is a growing trend of the industry. While MySQL is a part of the famous LAMP stack (Linux, Apache, MySQL, PHP), Windows is still one of the most popular development environments for MySQL users. In view of these facts, it makes sense to migrate from popular Windows based DBMS SQL Server to MySQL.
Before entering new technology organizations usually consider commercial and technical advantages of that technology. Each business has its own methodology of such evaluation, however the main factors that lead to acceptance or ignoring are Total Cost of Ownership (TCO) and viability of the software vendor. SQL Server comes with high TCO for large data warehouses and quite restrictive licensing policy. From another hand MySQL is open-source DBMS supplied with huge collection of free management and development tools.
MySQL has become the leading DBMS for Web-based applications due to its performance, reliability and scalability. Many world’s famous companies including Facebook, Twitter, YouTube, Yahoo!, Wikipedia uses MySQL or its forks for their corporate scale data warehouses. Finally, MySQL is a part of Oracle Corporation and consequently supplied with high class support worldwide.
Migrating schema, data and indexes from SQL Server to MySQL is not a real challenge since MySQL supports all the important data types and metadata with all related properties. Anyway, it is better when migration is automated through some proper software rather than implemented manually, as the last option may cause data loss of corruption due to human errors. This article explores a few software tools that can automate database migration from SQL Server to MySQL.
MySQL Workbench
MySQL Workbench is multi-functional framework that allows database specialists to design, develop and manage MySQL database. It provides strong visualization capabilities, advanced SQL editor and comprehensive tools for miscellaneous database administration tasks including migration.
Migration tool of MySQL Workbench is designed to provide ease of use around all steps of SQL Server to MySQL migration:
- migrations can be configured, copied, edited, executed and scheduled
- users can define specific data sources and to analyze source data in advance of the migration
- users can select objects to migrate, control mappings source to destination where it is required
- there is an option to edit migration scripts and create the target schema
- post-processing events can be configured where needed
Although MySQL Workbench provides advanced capabilities specified above and the best thing it’s free and cross platform, there are some bottlenecks of SQL Server to MySQL database migration through the tool:
- possible issues with character sets (error like ‘Could not successfully convert UCS-2 string to UTF-8’). It could be fixed by installing alternate ODBC drive for MS SQL that can extract Unicode data as UTF8. However, those intermediate steps make using the tool not so comfortable
- does not convert MS SQL views into MySQL format
- possible errors are not easy to debug due to lack of comprehensive detailed logging in MySQL Workbench
Solution by Intelligent Converters
There is commercial SQL Server to MySQL converter that do not have the limitations of MySQL Workbench. The product provides high performance of database migration due to effective algorithms of low-level reading and writing data. Other features:
- all modern versions of SQL Server and MySQL are supported (including SAAS variations such Azure SQL and forks DMBS like MariaDB and Percona)
- option to merge and synchronize existing MySQL tables with MS SQL data (equivalent structures of source and destination are required)
- command line support for scripting and scheduling the database migration
- option to export the data into MySQL script file for those cases when the target server does not allow remote connections
- option to pre-process data being converted via SELECT-queries
According to corporate terms and conditions, SQL Server to MySQL converter comes with 1-year subscription for updates and unlimited 24/7 support.
