Migrate from SQL Server to MySQL

While MySQL is a well-known DBMS of a prominent LAMP stack (Linux, Apache, MySQL, PHP/Perl/Python), Windows is often ranking as the top development platform for MySQL. In fact, Windows also ranks high than any Linux distribution as a deployment platform among the MySQL Community Edition users. Considering that a lot of users deploy MySQL on Windows for production, it makes a lot of sense to discover the business and technological grounds for SQL Server to MySQL database migration.

This article explores the three basic steps required to migrate from SQL Server to MySQL:

Step 1: Record SQL Server Sources

Recording a current SQL Server database framework can be complicated if it is experimented with by standard manual procedures. Although SQL Server has a really good metadata dictionary to work with, the procedure of manually extracting all metadata (table, column, index, etc.) can be very time and work intensive. The ideal method is to use am automated reverse engineering procedure that automatically manages all applicable metadata that is required for transformation. A good 3rd party data modeling tool can also be applied for this, and there are a number of good products on the market, such as Sybase’s/Quest’s Power Designer and Embarcadero’s ER/Studio, that assist the reverse engineering of several data sources including SQL Server.

Away from code-related objects like stored processes, a few SQL Server objects that can not be moved in a one-to-one arrangement consists of: Synonyms and Security Roles.

The following items require specialized focus when planning to migrate from SQL Server to MySQL:

• Assemblies
• Types
• Data definition language and statement-based triggers (MySQL has row-based triggers)
• Proprietary SQL Server function calls
• Certain cases of dynamic T-SQL

Step 2: Develop the MySQL Targets

As soon as the metadata has been extracted from SQL Server database, the next move is to develop the MySQL target database. This essentially comprises of converting the source objects and their properties (like column datatypes) to MySQL fits.It is important to understand that manual approaches to migrate from SQL Server to MySQL can be exceedingly time consuming and error prone as most databases will have thousands of object attributes that must be transformed.

Remember that lots of data modeling tools have the capability to transform a SQL Server schema to a MySQL schema with only a couple of mouse clicks. Obviously, the models can be modified if need be. The automatic conversion and movement carried out by development and modeling tools like MySQL Workbench, for SQL Server to MySQL data objects is a tremendous time-saver and can lead to lots of productivity increases for a database migration team.

Step 3: Perform the migration to MySQL

The moment the source SQL Server metadata has been recognized and the MySQL target database created, the next phase is to operate the actual data movement procedure. The extract, transform, and load (ETL) phase can be quite detailed based upon what an individual wants to achieve. There are lots of heavy-duty 3rd party ETL tools on the marketplace that provide overwhelming independence in just how to aggregate data, map types, and migrate from SQL Server to MySQL.

Microsoft helps to make available the Data Transformation Services (DTS in SQL Server 2000) and Integration Services (SQL Server 2005-12), that can help enhance any SQL Server migration to MySQL. Microsoft’s built-in migration tools works with transferring SQL Server data to MySQL with small work being expected on the part of the DBA.

For individuals making use of SQL Server but who’re not comfortable with the Integration Services, it is easy to transfer data from SQL Server to MySQL with a combination of BCP(bulk copy program)provided by MS SQL and LOAD DATA INFILE statement provided by MySQL.

A database administrator can help to create data files with SQL Server BCP wherever the data is delimited by a correct character (such as a comma, semi-colon, etc.).The final stage of data migration from SQL Server to MySQL is to import the data into the target database with LOAD DATA INFILE with the same delimiter being specified.

Also, you’ll find many heavy-duty 3rd party tools on the marketplace that can carefully automate the process of database migration. One of the tools to migrate from SQL Server to MySQLis offered by Intelligent Converters software company. The program transforms table definitions, data, indexes and foreign keys with all necessary attributes, views. Additionally, it allows command line to script and arrange the conversion process.