Summary: There are various reasons that compel the organizations to convert MS SQL database into MySQL. In this post, we will see how to convert data from MS SQL into MySQL database. We will also mention a powerful database conversion software that can convert MS SQL database into MySQL in a few simple steps.
Microsoft SQL Server and MySQL are among the best relational database management systems (RDBMS) but with one important difference. MS SQL is a proprietary software of Microsoft while Oracle MySQL is open-source and free. This is one of the reasons why companies migrate to MySQL to manage their data. Another important reason that drives the companies to migrate to MySQL is the cost. To be brief, looking at the top editions of these two DBMS, the Enterprise edition of Microsoft SQL Server costs more than the same edition of MySQL.
The migration process, however, is an important process that must be analyzed and planned properly. For example, an aspect to keep in mind is that the data types used by the DBMS may differ. In addition, this process involves not only data but also all the other objects, such as functions, procedures, etc.
Ways to Convert MS SQL to MySQL
There are different ways to convert or migrate data from MS SQL to MySQL.
Convert MS SQL to MySQL Manually
Data types in Microsoft SQL Server and MySQL are sometimes different. So, to avoid errors or loss of data, mapping between data types must be done. You can refer the official document to know the data type mapping between these two DBMS. For example, Varchar and NVarchar MS SQL data types can become a TEXT data type depending on the field length in MS SQL.
The first step is to convert the database schema using the correct data types. You also need to consider other aspects, such as whether the field you are considering accepts NULL values and the field has a primary key.
Since both RDMS adhere to and support much of the ANSI ISO standard, when you create the tables using the SQL ?CREATE TABLE? command, the syntax will be similar.
For example, below is the schema of the table ?Customer? in MS SQL Server.
Below is the schema of the same table in MySQL.
You can observe that:
- The Char data type became a Varchar.
- The NVarchar data type became a Varchar too.
- The syntax of the command is slightly different.
You need to prepare a script to create all the tables you want to migrate and then run the script from the MySQL Console or MySQL Workbench.
Once the schema is migrated, you can migrate the data. You can do this operation by creating a script that contains a list of ?insert into values? statements.
Convert MS SQL to MySQL using MySQL Workbench Migration Wizard
Manual conversion is an intense task, since a database can contain thousands of tables. For this reason, a dedicated data migration tool is very useful. You can use the MySQL workbench migration wizard. Follow these steps to use MySQL Workbench:
- Open MySQL Workbench and go to Database > Migration Wizard.
- A window will open asking you to install an ODBC driver. To open the ODBC manager, just click on the ?Open ODBC Administrator? button.
- You can install the following Microsoft SQL Server driver (see image).
- After that, press the Start Migration button.
- Enter the following source connection parameters:
- Database system must be set to Microsoft SQL Server.
- Connection Method must be set to ODBC (native).
- Enter the name of the server and the password.
- Press the Test Connection button to test if the connection is working.
- Press the Next button to setup the destination.
- In the target connection, set the stored connection to the local instance of MySQL.
- Then, enter the username and password, and press the ?Test Connection? button to test if the connection is working.
- Now, press the Next button.
- A list of objects of MS SQL is fetched. Press the Next button.
- A list of SQL Server databases is shown. Check the database you want to migrate, choose a Schema Name Mapping Method, and then press the Next button.
- Each SQL Server table is retrieved.
- You can choose the table you want to convert or convert all the tables.
- The migration of the table schema begins.
- You can edit and review the migrated objects.
- Choose your target creation options and press the Next button.
- The SQL scripts generated to create table in MySQL will be executed.
- You can see the details of the process of the schema creation.
- By default, data is copied online to the MySQL database.
- Data transfer from MS SQL to MySQL.
- At the end of the migration, a detailed report is shown.
MySQL Workbench Migration Wizard is a good instrument to convert MS SQL to MySQL. However, it has some limitations, such as:
- Setting up the conversion of schema and data takes a long time.
- You need technical skills on both MySQL and MS SQL DBMS.
- To perform migration, you need to have installed both MS SQL and MySQL DBMS.
- You can perform migration only on live databases.
Convert MS SQL to MySQL using Stellar Converter for Database
To overcome the limitations of the above methods, you can use a dedicated database conversion tool, such as Stellar Converter for Databases. This tool allows you to convert MS SQL database records into MySQL database format in a few simple steps. Another advantage of this tool is that only the target DBMS (MySQL) needs to be installed. Also, you can migrate live or offline databases.
Let?s see how to convert MS SQL database into MySQL with Stellar Converter for Database.
- When the tool starts, it asks for a database to be converted.
- In the Source Database, select the MS SQL option.
- Then, choose if you want to perform an offline or a live conversion. Press the Scan button.
- At the end of the scan operation, a dialog box appears showing the scan complete message. Press the OK button.
- A preview of the data is shown. So, you can perform a check.
- Press the Convert button to convert the scanned data.
- In the ?Convert and Save Database? dialog box, choose MySQL as Database Type.
- Enter the username, the host name, and the password. Then, press the Save button.
- After the conversion process, a process complete message appears.
To Conclude
Above, we have discussed different ways to convert the MS SQL database into a MySQL database. You can perform the conversion manually or use the Migration Wizard feature of the MySQL Workbench. However, these methods have some limitations. For a quick and easy migration process, you can use a powerful database conversion software, such as Stellar Converter for Database.
