MS SQL or MySQL?
It’s hard to evaluate Microsoft SQL Server and MySQL. Both programs are useful to develop advanced, dependable and effective databases. Even so, many agencies have decided to convert MS SQL to MySQL to take benefits of that database management system:
- 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 of transferring MS SQL databases to MySQL server, each of them posses strong and weak sides:
- fully manual mode – all database objects are migrates by human manually. It’s really tedious, however 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 MS SQL 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 MS SQL 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.
It’s possible using Data Transformation Services and Integration Services which are a part of SQL Server installation. These Microsoft tools support moving SQL Server data to MySQL with no work although call for 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 be certain things are as it 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 all the necessary information about indexes is extracted, it is possible to compose MySQL statements for creating those indexes in the destination database. Examples below demonstrate how to create primary key, unique index and simple (non-unique) index:
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, …);
Note that MySQL does not support index names longer than 64 bytes. Indexes with names longer than 64 bytes must be renamed.
List of foreign key constraints with all attributes can be extracted in Microsoft SQL using system stored procedure “sp_helpconstraint”:
use database_name;
GO
sp_helpconstraint table_name;
GO
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
Syntax of CREATE VIEW statements in SQL Server is similar to MySQL, so it requires just a kind of find/replace work to convert MS SQL view definitions in MySQL format. Basically, it is necessary to replace certain conditions, operators and embedded functions with MySQL equivalents. More details on the matter can be found in the article https://www.convert-in.com/docs/mss2sql/views.htm
Stored Procedures
The saved methods are the most complex part of the migration approach and none of migration tools available can deal with it at 100%.
