Comparing Microsoft SQL Server and MySQL is a challenging task as both products are capable of building complex, stable, and efficient databases. However, many organizations have shifted from MS SQL to MySQL due to various advantages, such as lower total cost of ownership, open-source nature, and cross-platforming.
There are multiple approaches to migrating MS SQL databases to MySQL, including fully manual mode, half-automated solutions based on free tools like Microsoft Data Transformation Services (DTS) or MySQL Workbench Migration Wizard, and automated migration using appropriate commercial tools. Regardless of the approach, the person responsible for migration should be aware of potential bottlenecks and 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 and MySQL format requires careful consideration, and VARCHAR column lengths should be adjusted to fit MySQL’s limitations.
Table below contains the list of MS SQL types requiring special attention as they do not have direct equivalent in MySQL:
SQL Server MySQL
=================================================
IDENTITY AUTO_INCREMENT
NTEXT, NATIONAL TEXT TEXT CHARACTER SET UTF8
SMALLDATETIME DATETIME
MONEY DECIMAL(19,4)
SMALL MONEY DECIMAL(10,4)
UNIQUEIDENTIFIER BINARY(16)
SYSNAME CHAR(256)
The data migration can be accomplished using Microsoft tools such as Data Transformation Services and Integration Services, or a combination of SQL Server bulk copy program and MySQL LOAD DATA INFILE. This approach allows to migrate data from SQL Server to MySQL with small efforts, however it requires some DBA skills.
Indexes, constraints, and foreign keys can be extracted from MS SQL and then converted into MySQL statements that are loaded into the target database.
Syntax of SQL Server CREATE VIEW statements is similar to MySQL. Therefor migration of views just requires to convert particular conditions, operators and embedded functions that are not supported in MySQL.
Conversion of stored procedures, functions and triggers from SQL Server to MySQL is the most complicated part of the overall database migration. It required deep knowledge of SQL language of both MS SQL and MySQL dialects and none of there is no way to automate this translation.
Intelligent Converters software company offers SQL Server to MySQL converter that can automate migration of schemas, data, indexes, constraints and views. It has sufficient capabilities to migrate large and complicated MS SQL databases with high performance handling all the necessary data transformations and conversion of database objects definitions. The converter can migrate MS SQL data into new MySQL database as well as merge or sync the contents of an existing database.
Despite of the selected approach, database migration from MS SQL to MySQL requires careful planning, attention to details, and validation of results to ensure a successful migration.
