Though, MySQL is popularly recognized as database solution for the popular LAMP stack (Linux, Apache, MySQL, PHP/Perl/Python), the MS Windows constantly ranks as the #1 development interface for MySQL users according to our surveys. Considering the fact that a lot of users deploy MySQL on Windows platforms for production, it’s wise to research the business and technical purposes for migrating from SQL Server to MySQL.
The figure beneath describes the three simple measures to migrate from SQL Server to MySQL:
Step 1: Record SQL Server Resources
Recording a current SQL Server database framework can be tough if it’s tried out with conventional manual procedures. Even though the SQL Server contains a great metadata dictionary to use, the entire process of manually dragging all metadata (table, column, index, etc.) is ultimately time and labor intensive. The most effective way is to try using an automated reverse engineering procedure that automatically catalogs all appropriate metadata that is required for conversion. A superior 3rd party data modeling tool can be utilized for this process, you will find a range of good products in the marketplace, such as Sybase’s/Quest’s PowerDesigner and Embarcadero’s ER/Studio, that also work with reverse engineering of numerous data sources including SQL Server.
Beyond code-related objects like stored procedures, a couple of SQL Server objects that can’t be migrated within a one-to-one move consist of: Synonyms and Security Functions.
Below are products that require exceptional attention before they could be totally migrated from SQL Server to MySQL:
- Data definition language statements (special attention to types and default values)
- Proprietary SQL Server function calls
- Certain cases of dynamic T-SQL
Step 2: Create the MySQL Targets
As soon as the SQL Server source metadata has been received and digested, you need to to create the MySQL target database. This simply entails converting the source objects and their properties (such as column datatypes) to MySQL complements. As one can possibly imagine, this method is often rather frustrating and error-prone if tried out manually because so many databases are going to have a large number of object properties that need to be converted.
Keep in mind that many data modeling tools have the option to transform a SQL Server schema to a MySQL schema with simply a few clicks of the mouse. Obviously, one can possibly tweake the models if necessary. The automatic conversion and migration carried out by the development and modeling tools including MySQL Workbench, for SQL Server to MySQL data objects is a big time saver and can also lead to a great deal of work flow gains for any database migration team.
Step 3: Carry out the Migration to MySQL
As soon as the source SQL Server metadata is understood as well as the MySQL target database developed, you will need to carry out the specific data migration process. The stage can takes care of the extract, transform, and load (ETL) can be a bit complex based upon what you want to achieve. You’ll find so many heavy-duty 3rd party ETL tools out there that provide extreme flexibility in exactly how you can move, aggregate, map, and transform data from SQL Server to MySQL databases.
Microsoft offers you Data Transformation Services (DTS in SQL Server 2000) and Integration Services (SQL Server 2005-12), that can assist you in accomplishing any SQL Server migration to MySQL. The built-in Microsoft’s migration tools support the transferring of SQL Server data to MySQL with no work as recommended on the part of the DBA.
For anyone who is not familiar with the integration services of and currently using SQL Server, be advice that it’s possible to transfer data from SQL Server to MySQL by using a mixture of the SQL Server bulk copy program (BCP) as well as MySQL LOAD DATA INFILE utility. For example, database administrator can develop data files with SQL Server BCP where the fields are delimited by an appropriate character (such as a comma, semi-colon, etc.) then load the data into MySQL with LOAD DATA INFILE with the exact same delimiter being specified.
An alternative data migration technique is to move all the current SQL Server objects and data from the source database to a MySQL staging database. Once it’s secured within the MySQL database server, a DBA or developer can develop stored methods as well as other migration code that uniquely moves and manipulates data within the staging database into another MySQL database which will be put to use in further development or production.
You’ll find so many heavy-duty 3rd party tools out there that could thoroughly automate the process of migration SQL Server database to MySQL databases. Among these tools is MS SQL to MySQL converter by Intelligent Converters. The system converts table definitions, data, indexes and foreign keys along with necessary attributes, views. Furthermore, it supports command line to script and schedule the conversion procedure.
