Convert SQL Server to MySQL

SQL Server vs MySQL? 

It’s hard to choose between SQL Server and MySQL. Both database management systems are useful to develop advanced, dependable and effective data warehouses. Even so, many companies, organizations and agencies have decided to convert SQL Server to MySQL to take benefits of this free and open-source DBMS:

• Total Cost of Ownership (TCO) – Enterprise edition of MySQL is priced at few times lower than Microsoft SQL and that differences became a lot more essential after Microsoft modified the SQL Server 2012 pricing from Per Processor to Per Core.

• Open Source – free access to the source code enables every specialist to understand how the system is functioning or even fix some issues without relating to the developer. It allows unlimited tuning and improvement of MySQL server.

• Cross-Platforming – it is possible to develop MySQL database on a Windows laptop and thereafter deploy on Windows or Linux server. It offers a superior and flexible experience when picking server platform.

Migration Methods 

You can find several ways to convert SQL Server to MySQL, each of them posses strong and weak sides: 

• 100% manual migration—The responsible specialist migrates all database objects and then validates the resulting database manually. This approach is really tedious, but it gives 100% control over the approach.

• half-automated solution according to free tools like Microsoft Data Transformation Services (DTS) or MySQL Workbench Migration Wizard which will help to move table structures (DDL) and the data. Indexes, constraints, views, triggers and stored procedures should be converted into MySQL format manually.

• automated migration by means of the proper commercial tools. Doing this is extremely time-saving, however everyone who take it ought to entirely trust that product. Also, neither of the migration tools are able to handle conversion of stored procedures totally as a result of complex nature of the method. So, manual rewriting of the code would be needed.

In spite of the fact which approach has been decided on, anyone in charge of database migration ought to know about potential bottlenecks and the way to validate the outcomes. 

Table structures (DDL) 

Most forms of SQL Server and MySQL are indifferent and don’t need any mapping. Below is the list of types that needs more consideration due to the fact they don’t possess direct equivalent in the target database format: 

SQL Server                MySQL
=================================================
BIT                       TINYINT(1)
DATETIMEOFFSET	          DATETIME
IDENTITY                  AUTO_INCREMENT
NTEXT, NATIONAL TEXT      TEXT CHARACTER SET UTF8
SMALLDATETIME             DATETIME
MONEY                     DECIMAL(19,4)
SMALL MONEY               DECIMAL(10,4)
UNIQUEIDENTIFIER          VARCHAR(64)
SYSNAME                   CHAR(160)
XML                       TEXT

Also, it is essential to concentrate on VARCHAR column lengths, because in SQL Server this type can contain up to 2,147,483,647 characters while MySQL VARCHAR is limited by 65,535 symbols only. 

Data Migration 

Once all SQL Server tables have been transformed into MySQL, the next thing is to move the data: extract it from SQL Server, transform and load into the destination database. 

Data Transformation Services and Integration Services can be used for this purpose. Those tools are part of the SQL Server installation, and each can convert SQL Server data to MySQL. This approach can be considered an automated conversion; however, it may require some DBA knowledge. 

For people who are unfamiliar with Microsoft Integration Services there’s another strategy that’s a mixture of the SQL Server bulk copy program (BCP) and the MySQL LOAD DATA INFILE. SQL Server BCP exports data in the source table into comma-separate values (.csv) format that can be loaded into MySQL with LOAD DATA INFILE. 

After data migration is finished, it’s greatly advised to execute follow-up checks to ensure certain things are as they ought to be. 

Indexes and Constraints 

Microsoft SQL Server 2005 and higher has system stored method that can extract details about indexes with all attributes for the current database. It can be used as follows: 

use database_name;
GO
sp_helpindex table_name;
GO

Once we extract all information about indexes, it is possible to compose the corresponding MySQL statements to recreate those indexes in the target database. Below is a demonstration of how primary key, unique and non-unique indexes can be created: 

ALTER TABLE table_name ADD PRIMARY KEY key_name (column_1, column_2, ...); 
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_1, column_2, ...); 
ALTER TABLE table_name ADD INDEX index_name (column_1, column_2, ...);

After all necessary information about foreign keys is extracted, each of them can be created in MySQL as follows: 

ALTER TABLE child_table_name 
	ADD CONSTRAINT foreign_key_name 
FOREIGN KEY(child_column_1, child_column_2, ...) 
REFERENCES 
	parent_table_name(parent_column_1, parent_column_2, ...);

Views 

CREATE VIEW statements are quite similar in SQL Server and MySQL, so it requires just a kind of find/replace work to convert SQL Server view to MySQL format. It is required to find and replace all operators and embedded functions specific to SQL Server with MySQL equivalents. 

Stored Procedures and Function

This is the most complicated part of the SQL Server to MySQL database migration. None of the migration tools can 100% automate this task. Therefore, there is no option besides manually covering the source code of each stored procedure and function to make it comply with MySQL syntax.