MySQL and SQL Server are hard to compare. Each of these DBMS has its own cons and pros and can handle huge, complicated data databases requiring safe simultaneous access.
However, numerous organizations intend to migrate their databases from SQL Server to MySQL in order to use the following advantages:
- Affordable Ownership: MySQL’s Enterprise edition is a few times less expensive than SQL Server, and this disparity grew more noticeable when Microsoft switched per processor pricing to per core.
- Open Source: Opportunity to access and modify the source code allows any developer or database professional to learn how the DBMS works and to resolve errors without involving the vendor. This feature also can be used to infinitely customize and tune MySQL server.
- Cross-Platforming It is very convenient when MySQL application or database is developed on a personal laptop running on Windows and then deployed on Linux or Unix server without any modification of the code. Such capability gives users, developers and database administrators incredible flexibility and freedom of choosing platform for the server.
Strategies of Migration
Nowadays database professionals use few methods to convert SQL Server to MySQL distinguished in terms of efforts and level of control over the process:
- Fully manual mode – every database object is manually migrated by a specialist. Although it’s very time-consuming, the database specialist can control and validate results of the migration at every stage
- Partially automated solution based on free tools that can assist with data and table structures migration. This approach requires specialist responsible for migration convert SQL Server constraints, indexes, views and T-SQL code
- Suitable commercial solutions that can automate most of the conversion routines. It is extremely important to choose the product carefully to guarantee smooth and safe processing of all major database objects. Also, the code of triggers, stored functions and procedures still needs to be converted from SQL Server to MySQL manually due to high complexity of the migration procedure.
Despite of the approach chosen to convert SQL Server to MySQL the responsible specialist must know all possible bottlenecks and have necessary skills in assessment and validation.
Table structures (DDL)
Basic data types of SQL Server and MySQL are equal and so do not need to be converted. The list below contains types that need to be safely mapped in the suitable equivalent (based on range of accepted values) before loading into the destination database:
| SQL Server | MySQL |
| BIT | TINYINT(1) |
| *DATETIMEOFFSET | DATETIME |
| IDENTITY | AUTO_INCREMENT |
| MONEY | DECIMAL(19,4) |
| NTEXT, NATIONAL TEXT | TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci |
| SMALLDATETIME | DATETIME |
| SMALLMONEY | DECIMAL(10,4) |
| SYSNAME | VARCHAR(128) |
| XML | TEXT |
| UNIQUEIDENTIFIER | VARCHAR(64) |
* Conversion of DATETIMEOFFSET to DATETIME requires some preprocessing through this expression:
DATEADD(“hh”, DATEDIFF(“hh”,((DT_DBTIMESTAMP)DateColumn), DateColumn), ((DT_DBTIMESTAMP)DateColumn))
Data Migration
Following the conversion of all table definitions from SQL Server to MySQL, the data must be extracted from SQL Server before being migrated using the extract-transform-load (ETL) technique, converted to comply with MySQL syntax and then imported into the target database.
The Data Transformation Services that is the standard component of SQL Server can be used for data migration. This Microsoft tool does not require extensive DBA knowledge and makes it easy to convert SQL Server to MySQL.
There is an alternative method that combines the MySQL LOAD DATA INFILE and the SQL Server bulk copy program (BCP) for individuals who are unfamiliar with Microsoft Integration Services. Data from the source table is exported by this tool into comma-separated values (.csv) format, which MySQL can then load using LOAD DATA statement.
Despite of the migration method chosen for the data, It is extremely important to carefully validate the result to ensure the integrity after migration is finished.
Indexes and Constraints
System procedure sp_helpindex allows to extract indexes with all attributes for the specified SQL Server table:
| USE name_of_the_database GO sp_hepindex name_of_the_table GO |
After all index properties are extracted, the next step is to compose MySQL statements for building the same entries in the destination database. The statements below demonstrate creation of primary key, unique and regular indexes in MySQL:
| ALTER TABLE name_of_the_table ADD PRIMARY KEY key_name (column_1, column_2, …); ALTER TABLE name_of_the_table ADD UNIQUE INDEX index_name (column_1, column_2, …); ALTER TABLE name_of_the_table ADD INDEX index_name (column_1, column_2, …); |
Do not forget about MySQL limitation on max length of index that is 64 bytes.
Foreign keys with all attributes are listed using SQL Server stored procedure “sp_helpconstraint”:
| USE name_of_the_database GO sp_helpconstraint name_of_the_table GO |
Those extracted foreign keys are generated in MySQL through the following statement:
| 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
Views are converted from SQL Server to MySQL through number of find/replace iterations as the syntax of CREATE VIEW statements is similar in these two DBMS. In essence, MySQL equivalents must be used to replace specific SQL Server operators, conditions, and system functions.
Comments are closed, but trackbacks and pingbacks are open.