SQL Server to MySQL Migration Guide

When it comes to building complex and efficient databases, Microsoft SQL Server and MySQL are both capable products that provide a variety of features and functionalities. However, organizations have started to move from MS SQL to MySQL because of its advantages, such as being open-source, offering a lower total cost of ownership, and being cross-platform.

Migrating SQL Server or Azure SQL databases to MySQL can be done through multiple approaches, including a fully manual mode, half-automated solutions using free tools like Microsoft Data Transformation Services (DTS) or MySQL Workbench Migration Wizard, and automated migration using appropriate commercial tools. Regardless of the approach chosen, it is important to be aware of potential bottlenecks and to validate the results.

During the migration process, special attention should be given to table structures (DDL) and data migration. The conversion of data types between MS SQL or Azure SQL and MySQL formats requires careful consideration, and VARCHAR column lengths should be adjusted to fit MySQL’s limitations. Additionally, certain SQL Server or Azure SQL types require special attention as they do not have a direct equivalent in MySQL, as shown in the table below:

It is important to note that these types may be supported by MySQL, but they have different names and may require additional processing during migration. Therefore, it is crucial to plan the migration process carefully and test thoroughly to ensure that the data is accurately transferred and properly functioning after migration. MS SQL or Azure SQL types requiring special attention as they do not have direct equivalent in MySQL: datetimeoffset, hierarchyid, money, small money, uniqueidentifier, xml.

The straight forward data migration can be done through Microsoft Data Transformation Services or Integration Services, or a combination of SQL Server bulk copy program and MySQL LOAD DATA INFILE. These methods can migrate MS SQL data to MySQL, however it requires some skills in database administration and cannot be applied for another kind of database entries.

Schemas, Indexes, constraints and foreign keys can be extracted in form of create statements, but it must be converted into MySQL format before loading into the destination database. 

Syntax of CREATE VIEW statements used in SQL Server or Azure SQL is similar to MySQL and its forks such as MariaDB and Percona. So converting views just requires to translate particular operators and embedded functions into MySQL equivalents. 

Stored procedures, functions and triggers are most complicated part of SQL Server to MySQL database migration. Migration of these objects requires deep knowledge of SQL language of both DBMS. 

Although, there is no solution to completely automate conversion of all database entries specified above from MS SQL or Azure to MySQL and its forks, there are software tools that can do a part of the job.

For example, MS SQL to MySQL converter provided by Intelligent Converters is capable of automatically migrating schemas, data, indexes, constraints, and views, making it suitable for even the most complex and sizable MS SQL databases. The converter excels at performing necessary data transformations and converting object definitions with high performance. Additionally, it can merge or synchronize data between an existing database and a new MySQL database.