Table of Content
    Database Converter

    Migrating from MySQL to SQL Server using SSMA


    Table of Content

      Summary: This post talks about the SQL Server Migration Assistant (SSMA) that is used for migrating MySQL database to SQL Server. It explains the step-by-step process to perform the migration using the SSMA for MySQL. It also mentions a dedicated database conversion tool that offers a quicker solution to perform MySQL to SQL Server database conversion.

      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.

      Migration Assistant for MySQL
      • Choose the version you need to install and press the Next button.
      Click Next by Choosing version
      • Double-click on the downloaded file. 
      Click on Download File
      • Click the Next button in the welcome window.
      SSMA for MySQL
      • Accept the Licence Agreement and press Next.
      Licence Agreement
      • 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.
      Select set up type as Typical
      • Choose to enable (or not) both telemetry and automatic update checks. Click the Install button.
      Click on Install
      • It may take a few seconds for the installation to complete.
      Installation Running
      • When installed, click on the Finish button.
      Tool Installed

      Now, let’s see how this tool works.

      • Run the tool and create a new project.
      Adding new project to SQL
      • Specify the project name and a path to save it. Then, choose the version of SQL Server to migrate the data to.
      Version of SQL server for migration
      • In the main window, click on the Connect to MySQL button.
      Click on Connect to MySQL button
      • Enter the server’s name, the username, and the password. Press the Connect button.
      Enter details and edit
      • The list of MySQL databases is shown. Press OK.
      Loads all user objects
      • We selected our simple demo database, called TEST_SSMA, created with a single table, from the list of MySQL databases.
      List of the 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.
      Enter Details
      • Select the database to process and then press the Migrate Data to start the migration of data.
      Migrate Data
      • A new connection to MySQL is needed. Press the Connect button.
      Adding All database details
      • A new SQL Server connection is needed. Click Connect.
      Click on Connect
      • At the end of the process, you will find your data in SQL Server.
      Edit on Tables

      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.
      Stellar Converter for Database Product Page
      • Once downloaded, double-click on the executable file.
      Click on executable file
      • A welcome window appears. Press the Next button.
      Stellar Converter for Database Setup Wizard
      • Accept the licence agreement and press the Next button.
      Licence Agreement
      • Choose the path to install the application. Press the Next button.
      Destination Path
      • An item in the start menu folder is created. Press the Next button.
      Setup for Converter for Database
      • Choose if you want to create a Desktop shortcut or the Quick Launch shortcut.
      Additional tasks
      • Press the Install button to start the installation procedure.
      Install Procedure
      • Stellar Converter for Database is installed. Press the Finish button.
      Lauching the software

      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.
      Stellar Converter for database software
      • Enter a MySQL database and press Scan.
      • On the left panel, the structure of the MySQL file is shown.
      • Press the Convert button.
      Convert option
      • 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).

      Was this article helpful?

      No NO

      About The Author

      Bharat Bhushan linkdin

      Bharat Bhushan is an experienced technical Marketer working at Stellar Data Recovery - expertise in data care. He is skilled in Microsoft Exchange Database, MSSQL Database troubleshooting & data warehousing. He is a Management Post Graduate having a strong grip in Technology & certified in SAP-SD, Oracle 10g & Informatica Powercenter 9.1.

      Related Posts

      WHY STELLAR® IS GLOBAL LEADER

      Why Choose Stellar?

      • 0M+

        Customers

      • 0+

        Years of Excellence

      • 0+

        R&D Engineers

      • 0+

        Countries

      • 0+

        PARTNERS

      • 0+

        Awards Received