Despite the fact that MySQL is well-known as a database solution of a prominent LAMP stack (Linux, Apache, MySQL, PHP/Perl/Python), Microsoft Windows always ranks as the #1 development platform for MySQL users in our surveys. 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 migrating from SQL Server to MySQL.  

Below, you will find an outline of the three basic steps required to move 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 PowerDesigner 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 before they can be migrated totally 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 SQL Server source metadata has been received and broken down, 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.  As you can just imagine, this process can be exceedingly time consuming and error prone if tried out manually 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 movement 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 move, aggregate, map, and transform data from SQL Server to MySQL databases.  

Microsoft helps to make available the Data Transformation Services (DTS in SQL Server 2000) and Integration Services (SQL Server 2005-12), which 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.) and then upload the data into MySQL with LOAD DATA INFILE with the same delimiter being specified.    

An alternative data migration strategy is to move all the current SQL Server objects and data from the source database to a MySQL staging database.  Once it is safe in the MySQL database server, a DBA or developer can create stored processes or other migration code that uniquely moves and controls data from the staging database into another MySQL database that can be employed for additional development or production.    

You’ll find many heavy-duty 3rd party tools on the marketplace that can carefully automate the process of migrating SQL Server database to MySQL databases. One of these types of tools is MS SQL to MySQL converter by Intelligent Converters. The software 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.