For years Microsoft Windows has been ranked 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 these facts, it makes a lot of sense for businesses to migrate their SQL Server databases to MySQL.
Basically, there are three basic steps required to convert SQL Server to MySQL:
- Dump SQL Server database (schemas, indexes, relationships, T-SQL code) into the selected format
- Design the target MySQL database (security, rules)
- Convert SQL Server database to MySQL including schemas, indexes, relationships and T-SQL code
Below all these steps to convert SQL Server to MySQL are explored in details.
Step 1: Dump SQL Server Sources
Dumping SQL Server database may be complicated if it is running as a manual procedure. Although SQL Server is supplied with easy-to-use database administration tools, manual 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 convert SQL Server to MySQL:
- Assemblies
- Types
- DDL and statement-based triggers (MySQL has row-based triggers)
- Proprietary SQL Server function calls
- Certain cases of dynamic T-SQL
Step 2: Design the Target MySQL Database
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.
There are lot of tools capable to convert SQL Server schema to MySQL 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: Convert SQL Server 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 convert data from SQL Server to MySQL.
Microsoft offers two valuable tools for simplifying SQL Server to MySQL migrations: Data Transformation Services (DTS in SQL Server 2000) and Integration Services (SQL Server 2005-12). These tools can significantly streamline the migration process, requiring minimal effort from the database administrator (DBA).
For those who might not be comfortable using Integration Services, an alternative method to convert SQL Server to MySQL involves combining the SQL Server bulk copy program (BCP) with the MySQL LOAD DATA INFILE utility.
Here’s how it works:
- Utilizing SQL Server BCP, the DBA can generate data files in which the data is delimited by a specific character, such as a comma or semi-colon.
- These data files can then be easily imported into MySQL using the LOAD DATA INFILE utility, specifying the same delimiter for accurate data parsing.
Another approach to data migration is to convert all existing SQL Server objects and data from the source database into a MySQL staging database. Once safely stored in the MySQL database server, a DBA or developer can create custom stored procedures or other migration code to efficiently move and manage data from the staging database to another MySQL database. This separate database can be used for further development or production purposes. You’ll find many heavy-duty 3rd party tools on the marketplace that can carefully automate the process of converting SQL Server to MySQL. One of these types of tools is SQL Server 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.
