What is better: SQL Server or MySQL?
Nobody should compare SQL Server and MySQL because these two DBMS are focused on different goals. Both database management systems can work with large and complicated data in efficient manner. At the same time, many companies migrate their databases from SQL Server to MySQL in order to take advantages of the destination DBMS:
- Open Source – MySQL allows access to the source code of the DBMS engine and most management tools. Every experienced database specialist can investigate how the product works and even fix minor issues.
- Cross-Platforming – MySQL database can be created in development environment on Windows machine and then deployed on Unix/Linux server with just a few minor modifications. Also, this capability provides freedom of choosing server platform.
Approaches to Migration
There are number of options to migrate database from SQL Server to MySQL, below strong and weak sides of each option are listed:
- Manual migration – database specialist migrates all database objects manually. The procedure will take a lot of efforts and may cause data loss or corruption due to human errors.
- Half-automated migration is based on standard tools provided by DBMS vendors like Microsoft Data Transformation Services (DTS) or MySQL Workbench. Those tools migrate table structures (DDL), data, indexes and constraints. Such complicated database objects like views, triggers and stored procedures are not supported in this approach and must be migrated into MySQL format manually.
- Fully automated migration process requires special commercial SQL Server to MySQL converter. This option takes minimal human efforts assuming the tool handles migration properly. However, it is important to understand that none of software solutions can migrate stored procedures with absolute accuracy due to complicated nature of those entries.
After the database migration completes, specialist responsible for this procedure must validate the results to make sure all database objects have been transferred properly.
Table structures (DDL)
SQL Server and MySQL have similar set of data types but particular types require the appropriate conversion as it is specified below:
SQL Server MySQL
BIT BOOLEAN or TINYINT(1)
DATETIMEOFFSET TIMESTAMP
IDENTITY AUTO_INCREMENT
NTEXT, NATIONAL TEXT TEXT CHARACTER SET UTF8
SMALLDATETIME DATETIME
MONEY DECIMAL(19,4)
SMALL MONEY DECIMAL(10,4)
UNIQUEIDENTIFIER CHAR(38)
SYSNAME CHAR(160)
XML TEXT
Data Migration
Data can be migrated from SQL Server to MySQL using Data Transformation and Integration Services that are part of SQL Server installation. Another approach to this procedure is a combination of the SQL Server bulk copy program (BCP) and the MySQL statement ‘LOAD DATA INFILE’ executed from any MySQL client tool. BCP exports MS SQL data into comma separate values (.csv) format that can be imported into MySQL tables using the specified query.
After data migration completes, the database specialist must verify that everything has been migrated properly via comparison number of rows in every table and random fragments of data.
More information about different aspects of database migration as well as download link for commercial SQL Server to MySQL converter can be found at: https://www.convert-in.com/mss2sql.htm