How to migrate a MS SQL Server database (from SQL Server Management Studio) to MySQL with MySQL Workbench

How to migrate a MS SQL Server database (from SQL Server Management Studio) to MySQL with MySQL Workbench

There's nothing so boring as the data migration, specially with old formats that almost nobody uses today for sane reasons. In case that your task is migrating a MS SQL Server database to MySQL, it's your lucky day as there's a pretty easy way to achieve this.

In this article, we'll explain you how to migrate a MS SQL Server based database to MySQL (MariaDB) with MySQL Workbench.

Requirements

Before getting started with the database migration, you will need the following software on your windows machine:

Having said that, let's get started !

1. Identify the database that you are trying to migrate

As first step, you need to verify that the database that you want to migrate is exposed on your SQL Server instance. The easiest way to do it is through the SSMS tool. SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, as well as build queries and scripts.

Open SSMS and access the database engine with the default Windows authentication (or from the connection that you want to access):

SQL Server Management Studio SSMS Login

Connect to the server and explore the databases in the object explorer. In our case, we want to export the my_database database, that, as you can see, is available in the Databases directory:

SQL Server Management Studio Database Viewer

Now that you know that the database is accesible in the server, we are going to start with the migration using MySQL Workbench.

2. Start with the migration on MySQL Workbench

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.

Start MySQL workbench and access the migration wizard from the toolbar (in this tutorial, we are using MySQL Workbench 8.0):

MySQL Workbench Migration Wizard

After opening the wizard, click on start migration:

Start Migration MySQL Workbench

This will open the source selection form. Here you will need to select SQL Server as the source relational database management system (RDBMS). A relational database management system (RDBMS) is a collection of programs and capabilities that enable IT teams and others to create, update, administer and otherwise interact with a relational database, which most of them use the Structured Query Language (SQL) to access the database. In our case, as we mentioned on the article, we'll migrate from a Microsoft SQL Server database to a MySQL database, so it will be the source. In our example, we have the MS SQL Server configured in the same computer and it's authenticated automatically with the Windows Authentication, if your server is hosted remotelly, you will need to change the parameters according to your needs. You can test the connection:

Source RDBMS MySQL Workbench Migration

And if it succeeds, you can continue with the next step of configuring the target RDBMS, which means MySQL. As mentioned at the beginning of the article, we assume you have already a MySQL server configured and running in the background, in our case we use the MySQL server of Xampp, which allows you to start/stop it with a control panel. The idea is basically to know the credentials to access the running MySQL instance, which in Xampp is accesible with root as user and an empty password:

Target RDBMS MySQL Workbench

If you are connecting to another MySQL server, you may configure it with SSH keys etc. Now that both connections are stablished, you may proceed with the next step that fetches a list of schemas from the source RDBMS. This will create a toggable list with all the databases on the SQL Server instance, where you have to select the database that you want to migrate (as well the Schema Name Mapping Method):

MySQL Workbench Schema

In our case, we just want to work with the my_database database, so we'll extract only that database. Now, if you proceed and everything works as expected, you will see now the Source Objects window, which basically allows you to filter which tables you want to migrate or not, normally, we would like to migrate them all:

MySQL Workbench source tables

Click on next and you will see the migration page, which allows you to check previously the MySQL script of every table and if there are warnings or errors that you need to fix manually, they'll be highlighted in the list. For example, in our case, we have a warning (when importing it will be an error) that specifies a problem with the migration, if we read the code, we will see a syntax error incompatibility with the VISIBLE keyword. In our example, just removing that keyword from the lines will allow us to import the scripts without any issue:

Manual Editing SQL Script Migration MySQL Workbench

After fixing manually the warnings and applying changes, you will be able to export finally the structure of the database either into a file (.sql) or creating the database on the target RDBMS (MySQL Server). In our case, is easier to import it directly into the server, so we'll pick the Create schema in target RDBMS (you can export it into a file if you want):

Create schema in target RDBMS MySQL Workbench

Before MySQL workbench starts with the migration, it will check again for errors and warnings, if there's still any, you need to fix them manually again:

Fix MySQL Workbench Syntax Error

Click on Recreate Object, this will lead you to the previous step in order to build the schema once again, click on next and if everythings succeeds you will see, everything should be marked as correct:

MySQL Workbench Success Schema Import

Finally, all that is left is the data. We already migrated the structure of the database, so now we need to transfer all the data:

MySQL Workbench Transfer Data

As we are working with both servers in the same computer, we can make an online copy of the data from the SQL Server database to the MySQL database. Click on next and the data migration will begin:

Migrate SQL Server Database Data to MySQL

Once it finishes, you will now have the original SQL database in MySQL format in the server. You can access now your MySQL server, where you will find the database available.

Happy coding !

This could interest you

Become a more social person