Organizations may develop the need to migrate from MS SQL server to MySQL because of its rich feature-set, cross-platform availability, open source nature and lower cost. This post will outline different methods that can be used to move MS SQL databases to MySQL server.
Migration Methods to Move Data from MS SQL to MySQL
Method1 – Manual Method
You may choose to migrate data from SQL Server to MySQL manually by using SQL server command-line utility – the Bulk Copy Program (or BCP). The utility, combined with MySQL LOAD DATA INFILE utility, helps in bulk export and import of data from SQL Server to MySQL.
Essentially, a database administrator (DBA) can use BCP to export data from SQL Server and then load the data into MySQL by using LOAD DATA INFILE utility. To use these utilities, the user needs to create a destination table, having the same or equivalent schema in MySQL. To do so, perform these steps:
Step 1: Open SQL Enterprise Manager, and then right-click the database you want to convert
Step 2: Next, click Tasks > Generate Scripts
This will create a SQL script for all the tables. Convert the script to MySQL compatible file format, and then run ‘CREATE TABLE’ commands on MySQL database for matching tables to SQL server versions.
Next, export the data from MS SQL by using the following command:
bcp Yourdatabasename..TableName out Table1.dat -q -c -T -S ServerName -r \0 -t !\t!
This will create a file – named Table1.dat, with fields separated by ![tab]! and rows separated by \0 NUL characters.
Now copy the .dat file into a temporary folder (tmp) on MySQL server. Once the file is copied, load its data into MySQL database by using the following command:
LOAD DATA INFILE ‘/tmp/TblName.dat’ INTO TABLE TblName FIELDS TERMINATED BY ‘!\t!’ LINES TERMINATED BY ‘\0’;
NOTE: Make sure that the table (TblName in this example) is already created in MySQL database.
Method2 – Use Microsoft Built-in Tools
Microsoft provides several built-in tools, such as Data Transformation Services (DTS in SQL Server 2000) and Integration Services (for SQL Server 2005–12) to make the transition from MSSQL to MySQL easier. However, these two methods involve the length migration process, so we will discuss about how to use MySQL Workbench Migration Tool to perform the migration.
The MySQL Workbench Migration Tool provides a quick way to migrate tables, objects and data from the MS SQL server database to MySQL. It takes less time and effort in moving data from MS SQL Server to MySQL, as it helps automate the migration process.
Visit the official website of MySQL for downloading and installing the MySQL Workbench tool. Once installed, follow these steps to migrate data from MS SQL Server to MySQL with MySQL Workbench tool:
Step 1: Launch MySQL Workbench
Step 2: From the MySQL Workbench interface, click the Database tab, and then select Migration Wizard
Step 3: In Overview page, click Start Migration.
NOTE: Make sure to read the Prerequisites section for a smooth migration.
Step 4: In the Source Selection page, select Microsoft SQL Server under Source RDBMS Connection Parameters. Provide information about ODBC drivers and other connection parameters, and then click Next.
Step 5: In the Target Selection window, set required parameters to connect to MySQL server instance, and then click Test connection to verify you can connect to it. Click Next.
Step 6: A Fetch Schema List page will open with a list of the schemas retrieved from the source as well as target databases.
NOTE: This is an automated informational step that provides details about connection related errors and general log.
Step 7: Click Next.
Step 8: In the Schema Selection page, select database schema you want to migrate, and then click Next.
Step 9: In Reverse Engineer Source page, you can see reverse engineering of the selected (source) schema metadata in progress (as shown in the screenshot below):
NOTE: During this stage, Migration Wizard is retrieving information about the source database objects including table names, table columns, views, primary and foreign keys, etc.
Once the process is complete, a message log is displayed. Click Next to continue.
Step 10: In Source Objects page, the objects of the source database available for migration are displayed. Select the objects you want to migrate and hit Next.
NOTE: The table objects, available for migration, are selected by default. You can also select ‘View objects’ and ‘Routine objects’ you wish to migrate. But, not automatic migration is available for View and Routine objects. You will have to write MySQL equivalent code to migrate these objects.
Step 11: The Migration page will show the log of the migration process. View the log and then click Next.
Step 12: In Manual Editing page, View select box (on top right side of the page) provides list of three sections you can edit.
Step 13: The Target Creation Options page allows you to create schema by either adding it to target database or creating an SQL script file, or both. Click Next to proceed with the migration process.
Step 14: The Create Schema page displays the newly created schema. View the schema creation log and press Next.
Step 15: The Create Target Results page displays a list of the objects created. It also provides details about errors (if any exist) during the object creation process. Check the details and click Next.
Step 16: In Data Transfer and Migration Setup page, choose the provided option to continue with the migration process.
Step 17: In Bulk Data Transfer page, leave the default options selected and click Next.
Step 18: The Migration Report page will present summary of the complete migration process. Click Finish to exit the Migration Wizard.
NOTE: If you’ve selected the online copy, you can view migrated data within the MySQL Workbench SQL editor.
There are several methods available to migrate data from MS SQL Server to MySQL database. You can use the Bulk Copy Program (BCP.exe) to manually migrate the database, but it can be a time-consuming process and involves data loss risk.
Another method is to use Microsoft built-in tools such as SSIS or Workbench Migration Wizard. SSIS is an excellent tool that can efficiently handle complex migration task, but it comes with some learning curve and caveats. Compared to SSIS, Workbench Migration Wizard is relatively easy to understand and work with. However, it is not an ideal tool to perform complex migration. Also, it requires manual conversion of table attributes like indexes, views, etc., which can be time-consuming.
However, by using a specialized database file converter software such as Stellar Converter for Database can reduce the manual efforts and time required to perform the migration. The software automate the process of converting a database file format to another.