What is SQL Server Migration Assistant (SSMA) and How to Convert MySQL to SQL Server with It?

It’s nothing new that database users search for a database migration tool to move the current one to another for conversion because of some database features or environment needs.

SQL Server Migration Assistant is such a database converter from SQL Server, Access, Db2, SAP ASE, MySQL, and Oracle to SQL Server of Microsoft.

What is SQL Server Migration Assistant?

Microsoft SQL Server Migration Assistant (SSMA) is one of the Microsoft database tools to simplify the conversion from other databases to SQL Server. The most recent SSMA for MySQL is v9.2.

Supported OS: Windows Server 2008 R2, 2012 R2, 2012, 2016, Windows 7, 8, 8.1, 10.

Supported targets: SQL Server 2012, 2014, 2016, 2017 and 2019 on Windows and Linux; Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.

Requirements for MySQL Client:

• Microsoft Windows Installer 3.0 or above;

• Microsoft .NET Framework 4.7.2 or later;

• Connection permissions to the target SQL Server instance;

• MySQL Connector/ODBC version 5.1;

• At least 4 GB RAM.

Pros

• Free database migration tool from Microsoft.

• Easy and straightforward data migration operations.

• SQL code comparison between the original and new databases.

• Synchronizes SSMA replica metabase with the target database.

• Automatically generates a detailed report about data migration steps.

• Runs a conversion report to identify possible errors during the process.

• Simple management for the converted schema that allows users to ensure migration status.

Cons

• The interface of SSMA is not so user-friendly.

• Requires knowledge of SQL, Python, or C#.

• Limited scope for only Microsoft database migration.

• Lacks additional toolsets and visualization features compared to premium products.

Migrate MySQL Database to SQL Server Using SSMA

Prerequisites:

• Support MySQL 5.6 and 5.7.

• Download and install SSMA for MySQL.

• Sufficient connectivity and permissions for both source and target databases.

Before data migration:

Download and install .NET framework for SSMA

1.     Download .NET Framework from the Windows Download page.

2.     In Windows Server, navigate to Server Manager Dashboard and choose Add roles and features.

3.     Follow the installation wizard.

Or

1.     Click the Windows key, type Windows Features, and click Enter. A window of Turn Windows features on or off shows.

2.     Choose the .NET Framework version, and click OK. Restart your computer if asked.

No need for Windows Communication Foundation (WCF) HTTP Activation and Windows Communication Foundation (WCF) Non-HTTP Activation except if you're a developer or server administrator who needs this feature.

Assess database for migration

1.     Open SSMA, on the File pane, choose New Project.

2.     Specify a project Name and Location for the project and target database. Migrate To SQL Server from the drop-down.

3.     In the Connect to MySQL window, input connection info and click Connect.

4.     Choose the source MySQL database, and click OK. Then in MySQL Metadata Explorer right-click the database to Create Report or click Create Report at the top right.

5.     See the report or open it in Excel to get details about MySQL objects and migration steps. The default report location is in the SSMAProjects report folder, like drive:Users<username>DocumentsSSMAProjectsMySQLMigrationeporteport_2022_12_21T03_20_57.

Validate the type mappings

1.     On the Tools page, choose Project Settings.

2.     Choose the Type Mapping tab.

3.     In MySQL Metadata Explorer, choose the table to change the type mapping.

Convert the schema

1.     Optionally right-click the node and choose to Add Statement to convert dynamic or ad-hoc queries.

2.     Choose the Connect to SQL Server tab. Then input the connection details, choose the target from the drop-down, and enter a name and authentication info in the window.

3.     In MySQL Metadata Explorer, right-click the database to choose Convert Schema, or choose Convert Shema at the top right.

4.     Compare the converted objects to the original ones for potential problems and solve them according to the recommendations.

5.     Compare the converted Transact-SQL to the original code and correct with the recommendations.

6.     Choose Review results and see errors in the Error List.

7.     In the File pane, choose Save Project locally to assess the source and schema offline, and remediate them before you publish the schema.

Migrate MySQL to SQL Server

There are two options to translate MySQL to SQL Server:

Client-side database migration:

In the Project Settings pane, choose Client Side Data Migration Engine. It is allowed when the target database uses SQL Express edition.

Server-side database migration:

First ensure that the instance of SQL Server has SSMA for MySQL Extension Pack, MySQL providers, and a running SQL Server Agent service. In the Project Settings pane, choose Server Side Data Migration Engine.

1.     In the SQL Server Metadata Explorer, right-click the database and choose Synchronize with Database to publish the MySQL database.

2.     See the mappings between the source and target projects.

3.     In MySQL Metadata Explorer, right-click the target database/object and choose Migrate Data or choose Migrate Data tab.

Check the database box next to it for the entire database migration, or expand the database and Tables, check the boxes for individual tables migration.

4.     See the Data Migration Report.

5.     Use SQL Server Management Studio to connect to the instance for migration validation.

After data migration:

1.     Sometimes, changes are needed to let the applications consume the target environment.

2.     Tests for MySQL to SQL Server migration.

Develop validation tests: create validation SQL queries that cover the defined scope for the source and target.

Configure a test environment: include a copy of both databases and the environment should be isolated.

Validation tests: run the tests against two databases, then review the outcomes.

Performance tests: run the tests against two databases, then review and compare the outcomes.

Backup & Restore MySQL and SQL Server in One Solution

Backup MySQL and SQL Server databases before migration is a preventive measure for data loss during the process. And not only that, backups serve as the resources for data recovery in the event of hardware failure, system downtime, or ransomware attacks.

Vinchin Backup & Recovery is a trustworthy backup solution for 10+ virtualizations (VMware, Xen, Oracle OLVM, OpenStack, etc.) and 6 mainstream databases such as MySQL, SQL Server, Oracle, PostgreSQL, Postgres Pro, and MariaDB across 100+ countries.

You can automate database backup with various efficient strategies like data deduplication and compression, retention policy, and backup options (full, incremental, differential, and archive log backup varying with databases).

Also, Vinchin users can restore a database to the original database or create a new one based on specific needs.

And rest assured that all database backups and the backup server will be guarded against ransomware with real-time IO monitoring that rejects any unauthorized visits.

Wanna give it a try? Download the 60-day free trial of the Enterprise version to explore now.

Following is the overview of MS SQL Server backup.

1.     Go to Database Backup> Backup, expand the nodes, and select the licensed target instance.

2.     Select the Target Node and Storage from the drop-down as the backup destination.

3.     Configure backup strategies such as schedules, backup types, data reduction, transmission, and more.

4.     Review and hit Submit. The job will run on schedules.

Takeaway

SQL Server Migration Assistant (SSMA) is a free database converter that imports other databases like MySQL to SQL Server (see how to migrate SQL Server to MySQL). It’s easy and intuitive to operate, though some SQL knowledge is required.

Backup MySQL, SQL Server, or together using Vinchin Backup & Recovery with a simplified management console, intelligent backup strategies, and robust security technologies.