Migrating MySQL Database to Microsoft SQL Server

Introduction
Why Use SQL Server Migration Assistant (SSMA)?
Preparing the MySQL Schema
Using SSMA to Convert and Synchronize the Schema
Running Sample Queries in SQL Server
Conclusion and Next Steps

Introduction

Manually migrating a database from MySQL to Microsoft SQL Server can be a daunting task. Luckily, with SQL Server Migration Assistant (SSMA), we can automate this process. This article provides a step-by-step guide on using SSMA to migrate your MySQL database schema to SQL Server, including schema conversion and synchronization, as well as running sample queries in Microsoft SQL Server.

If you would like to follow along, you can download the MySQL Database Schema here

You will also need to download and install Microsoft SQL Server Migration Assistant for MySQL from the official Microsoft website:

SQL Server Migration Assistant for MySQL

Let's get started!

Why Use SQL Server Migration Assistant (SSMA)?

SSMA is a powerful tool provided by Microsoft to assist in migrating databases from various platforms to SQL Server. It supports automated schema and data migration, making the transition smoother and less error-prone.

Key Benefits:

Automated Schema Conversion: SSMA converts MySQL schema to SQL Server compatible schema automatically.
Data Migration: Supports both client-side and server-side data migration.
Comprehensive Reports: Provides detailed reports on migration status and issues.

Preparing the MySQL Schema

Before migrating, ensure your MySQL schema is ready. Here is an example of a MySQL schema that we will use in this guide.

DROP DATABASE IF EXISTS employees;
CREATE DATABASE IF NOT EXISTS employees;
USE employees;

DROP TABLE IF EXISTS dept_emp, dept_manager, titles, salaries, employees, departments;

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

CREATE TABLE departments (
    dept_no     CHAR(4)         NOT NULL,
    dept_name   VARCHAR(40)     NOT NULL,
    PRIMARY KEY (dept_no),
    UNIQUE  KEY (dept_name)
);

CREATE TABLE dept_manager (
   emp_no       INT             NOT NULL,
   dept_no      CHAR(4)         NOT NULL,
   from_date    DATE            NOT NULL,
   to_date      DATE            NOT NULL,
   FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
   FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
   PRIMARY KEY (emp_no,dept_no)
);

CREATE TABLE dept_emp (
    emp_no      INT             NOT NULL,
    dept_no     CHAR(4)         NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,dept_no)
);

CREATE TABLE titles (
    emp_no      INT             NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,title, from_date)
);

CREATE TABLE salaries (
    emp_no      INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no, from_date)
);

CREATE VIEW dept_emp_latest_date AS
    SELECT emp_no, MAX(from_date) AS from_date, MAX(to_date) AS to_date
    FROM dept_emp
    GROUP BY emp_no;

CREATE VIEW current_dept_emp AS
    SELECT l.emp_no, dept_no, l.from_date, l.to_date
    FROM dept_emp d
      INNER JOIN dept_emp_latest_date l
      ON d.emp_no = l.emp_no AND d.from_date = l.from_date AND l.to_date = d.to_date;

Here is how the schema looks in MySQL Workbench:

Using SSMA to Convert and Synchronize the Schema

Download and Install SSMA: Download and install SQL Server Migration Assistant for MySQL from the official Microsoft website.
Open SSMA: Launch SQL Server Migration Assistant for MySQL.
Create a New Project: Click on File -> New Project and select MySQL as the source database type.
Connect to MySQL: Enter the connection details for your MySQL database and click Connect.
Connect to SQL Server: Enter the connection details for your SQL Server database and click Connect.
Select the MySQL Schema: Select the MySQL schema you want to migrate and click Next.
Schema Conversion: SSMA will analyze the MySQL schema and convert it to a SQL Server compatible schema. Review the conversion report and click Apply to proceed.
Synchronize Schema: SSMA will synchronize the converted schema with your SQL Server database. Review the synchronization report and click Apply to complete the migration.
Migrate Data: If you want to migrate data, you can do so by selecting the tables and clicking Migrate Data.
Verify Migration: Verify that the schema and data have been migrated successfully by querying the SQL Server database.

Here is the how the migration looks in SSMA:

And here is the Data Migration report:

Running Sample Queries in SQL Server

Now that the migration is complete, let's run a sample query in SQL Server to verify the data migration.

USE employees;
GO
SELECT * FROM employees.departments;

You should see the data from the MySQL database displayed in SQL Server.

You can run additional queries to validate the data migration and ensure that the schema conversion was successful.

Here is a sample query that joins the employees and departments tables using the dept_emp table. This query retrieves the employee number, first name, last name, and department name for each employee.

Conclusion and Next Steps

Migrating a MySQL database schema to SQL Server using SSMA is a straightforward process that involves schema conversion and synchronization. By following the steps outlined in this guide, you can ensure a smooth transition with minimal downtime.

By automating the migration process with SSMA, you can save time and effort while ensuring a successful transition to SQL Server.

Next steps include:

Data Migration: If you haven't migrated data yet, you can do so using SSMA.
Testing and Validation: Verify that the data has been migrated correctly and test the application against the new SQL Server database.
Performance Tuning: Optimize the SQL Server database for performance and scalability.

References:

SQL Server Migration Assistant for MySQL

I hope this guide has been helpful in automating the migration of your MySQL database to SQL Server. If you have any questions or feedback.

Happy Migrating!