Why Migrate?
Although it is notcorrect to compareSQL Server and MySQL, many companies and organizations prefer open source solutions as the way to reduce total cost of ownership.Bothdatabase management systems can handle large and complicated data,however there are some advantages of MySQL that could be reason to migrate from SQL Server to MySQL:
- Open Source – MySQLprovides full access to the source code of the DBMS engine and most management tools. Every experienced database specialistcan investigate how the product works and even customize it for particular needs.
- Tight integration with web –MySQL is perfectly combining with PHP that is one of the most popular languages for web programming
- Cross-Platforming – MySQL database can be created in development environmenton Windows machine and then deployed on Unix/Linux server with just a few minor modifications.Also, this capability provides freedomof choosing server platform.
Approaches to Migration
There are number ofoptions to migrate from SQLServer to MySQL, below strong and weak sides of each option are listed:
- Manual migration– this approach provides database specialist migratingall database entries 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 to migrate from SQL Server to MySQL process table structures (DDL), data, indexes and constraints. However, 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 requiresspecial commercial tools. This optionrequires minimal human effortsbecause the tool makes all necessary transformation.However, it is important to understand that no one SQL Server to MySQL converter can migrate stored functions, procedures and triggers with absolute accuracy due to complicated nature of those entries.
After the database migration completes,specialist responsible for this proceduremust validate the results to make sure all database objects have been transferred properly.
Table structures (DDL)
SQL Server and MySQL have similarset 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
To migrate the data from SQL Server to MySQLdatabase specialists can useTransformation 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 SQL Server 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 mustverify that everything has been migrated properly via comparison number of rows in every table and random fragments of data.
Database Converters
Another method to migrate from SQL Server to MySQL in fully automated mode is to use special software designed for this purpose known as database converters. One of this tools is designed by Intelligent Converters, a software vendor that develops products for database migration and synchronization of all popular DBMS since 2001. Their tool to migrate from SQL Server to MySQL offers the following capabilities:
- All modern versions of on premise and cloud SQL Server and MySQL are supported
- Schemas, data, indexes, constraints, foreign keys, views, stored procedures, functions and triggers are migrated
- Option to filter data for migration through SELECT-queries
- Option to customize attributes of tables and columns before migration
- Command line support
More information about how to migrate from SQL Server to MySQL and related software solutions can be found at the official site of Intelligent Converters.
