Migrating from MySQL to SQL Server using SSMA

Microsoft’s SQL Server Migration Assistant (SSMA) for MySQL is a free tool that you can use for migrating data from MySQL database to SQL Server or SQL Azure. The latest release (v9.3) of the tool contains some important changes, including support for MySQL 8.x and SQL Server 2022.

The SSMA for MySQL is a program developed to perform the following tasks:

• Connect to a MySQL database (the source).
• Connect to an instance of SQL Server or Azure SQL database (the target).
• Convert all the objects from MySQL to SQL Server or Azure SQL database.
• Load these objects into SQL Server or Azure SQL database.
• Migrate data to SQL Server or Azure SQL database.

Let’s see how to use this tool to migrate data from MySQL to SQL.

Step-by-Step Process to Migrate from MySQL to SQL using SSMA

You need to first install the SSMA for MySQL. Here are a few prerequisites needed to install this tool:

• Windows 7 or newer version with, at least, 4 GB of RAM.
• Windows Server 2008 or newer version with, at least, 4 GB of RAM.
• Microsoft Windows Installer 3.1 or newer version.
• Microsoft .NET Framework version 4.7.2 or higher.
• MySQL ODBC 5.1 Driver.
• Credentials to access (login) the instance of SQL Server and have sufficient permissions.

Now, look at the installation procedure. This is a wizard setup, which is quite simple.

• Go to the official Microsoft SQL Server Migration Assistant download page and press the download button to download the installer.

• Choose the version you need to install and press the Next button.

• Double-click on the downloaded file. 

• Click the Next button in the welcome window.

• Accept the Licence Agreement and press Next.

• If any prerequisites are missing, a message will appear. In this case, exit from the installation program and install the missing prerequisites. Then, run the setup again.
• Otherwise, choose the Setup Type, select Typical, and press the Next button.

• Choose to enable (or not) both telemetry and automatic update checks. Click the Install button.

• It may take a few seconds for the installation to complete.

• When installed, click on the Finish button.

Now, let’s see how this tool works.

• Run the tool and create a new project.

• Specify the project name and a path to save it. Then, choose the version of SQL Server to migrate the data to.

• In the main window, click on the Connect to MySQL button.

• Enter the server’s name, the username, and the password. Press the Connect button.

• The list of MySQL databases is shown. Press OK.

• We selected our simple demo database, called TEST_SSMA, created with a single table, from the list of MySQL databases.

• Connect to the target database by pressing the Connect to SQL Server button.

• Enter the server name, database name, user name, and password.

• Select the database to process and then press the Migrate Data to start the migration of data.

• A new connection to MySQL is needed. Press the Connect button.

• A new SQL Server connection is needed. Click Connect.

• At the end of the process, you will find your data in SQL Server.

You can also migrate other objects, such as schemas, triggers, indexes, etc. to the SQL Server.

A Better Alternative to Convert MySQL Database to SQL

Though Microsoft makes available a free tool such as SSMA, you can also use third-party dedicated tools to perform database conversion. One such tool dedicated to database conversion is Stellar Converter for Database. It can easily and quickly convert database file from one type to another. This application supports MS SQL (.mdf), MySQL, SQLite (.db and .sqlite), and SQL Anywhere (.db).

Stellar Converter for Database has some unique features, such as:

• Can convert a single table.
• Can convert an offline database.
• Interconverts between different database types.

Now let’s see the installation of the software.

• Go to the product page and download the executable by pressing the Free Download button.

• Once downloaded, double-click on the executable file.

• A welcome window appears. Press the Next button.

• Accept the licence agreement and press the Next button.

• Choose the path to install the application. Press the Next button.

• An item in the start menu folder is created. Press the Next button.

• Choose if you want to create a Desktop shortcut or the Quick Launch shortcut.

• Press the Install button to start the installation procedure.

• Stellar Converter for Database is installed. Press the Finish button.

Now let’s see how to use the software.

• Launch the program and choose the type of the source database. There are 5 options: MS Access, MS SQL, My SQL, SQL Anywhere, and SQL Lite.

• By default, data is read when the database is offline. You can choose to read data directly from the “live” database.
• Select MySQL as Data Source and Browse for files.

• Enter a MySQL database and press Scan.
• On the left panel, the structure of the MySQL file is shown.
• Press the Convert button.

• Conversion will start and proceed super easy.

Conclusion

As you have seen, Microsoft provides a free utility, named SSMA for MySQL that is dedicated to migrating databases from MySQL to SQL Server. We have discussed the step-by-step process to migrate MySQL database to SQL Server using this utility. In addition, we have mentioned an alternative utility called Stellar Converter for Database, which is also dedicated to data migration. This software interconverts MS SQL (.mdf), MySQL, SQLite (.db and .sqlite), SQL Anywhere (.db), and MS Access (.mdb and .accdb).