Migrate from Microsoft SQL to MySQL |
| [SQL Server to MySQL Converter] [Types Mapping] [Converting Queries] [Stored Procedures] |
It is difficult to compare Microsoft SQL Server and MySQL. Both products can be used to build complex, stable and efficient databases. However, many organizations have migrated their MS SQL databases to MySQL server in order to take advantages of MySQL:
There are several approaches to moving MS SQL databases to MySQL server, each of them has its strong and weak sides:
Despite of the fact which approach has been chosen, the person responsible for database migration should know about possible bottlenecks and how to validate the results.
Valid conversions of data types between MS SQL and MySQL format are listed here (allowed mappings are marked green, prohibited or dangerous - orange): Microsoft SQL to MySQL Types Mapping
Below is the list of types that requires more attention since they do not have direct equivalent in the target database format:
SQL Server MySQL ================================================= IDENTITY AUTO_INCREMENT NTEXT, NATIONAL TEXT TEXT CHARACTER SET UTF8 SMALLDATETIME DATETIME MONEY DECIMAL(19,4) SMALL MONEY DECIMAL(10,4) UNIQUEIDENTIFIER BINARY(16) SYSNAME CHAR(256)
Also, it is necessary to pay attention to 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.
Once all MS SQL tables have been converted to MySQL, the next step is to migrate the data: extract it from SQL Server, transform and load into the destination database.
It can be done using Data Transformation Services and Integration Services that are included in SQL Server installation. These Microsoft tools support moving SQL Server data to MySQL with little effort although require some DBA knowledges.
For those who are not familiar with Microsoft Integration Services there is another approach that is a combination of the SQL Server bulk copy program (BCP) and the MySQL LOAD DATA INFILE. SQL Server BCP exports data from the source table into comma-separated values (.csv) format that can be loaded into MySQL with LOAD DATA INFILE.
After data migration is complete it is greatly recommended to perform follow-up checks to ensure everything is as it should be.
Microsoft SQL Server 2005 and higher has system stored procedure that can extract information 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, ...);
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 int article Converting MS SQL Queries to MySQL
The stored procedures are the most complicated part of the migration process and none of database converters on the market can handle it at 100%. The following article will help you to convert stored procedures from SQL Server to MySQL manually: Convert MS SQL Stored Procedures to MySQL
Among all tools to convert Microsoft SQL databases into MySQL format MSSQL-to-MySQL developed by Intelligent Converters has near-to-top position by scope of features and very attractive price/quality ratio.
Although it is very easy to use, MSSQL-to-MySQL converter provides enough capabilities to migrate very large and complex SQL Server databases. The result of migration process is very accurate because the converter does all necessary data transformation, supports all MS SQL types and attributes, converts all indexes and foreign key constraints, handles multibyte character sets and much more. The only limitation of the program is that it does not migrate stored procedures and triggers. If a customer needs more control over the conversion or just doesn't have direct access to the destination MySQL server, MSSQL-to-MySQL can export data into MySQL dump file (SQL statements to create tables, indexes, constraints and fill tables with data). Click here to learn more about this database converter.
Have any questions? Contact us