How to Migrate Microsoft SQL Server to MySQL Database

As we know, Microsoft SQL Server (MS SQL) and MySQL are the most commonly used Relational Database Management Systems (RDBMSs).

But while MS SQL is a proprietary RDBMS from Microsoft, MySQL is a free and open-source RDBMS backed by Oracle. And with an increasing number of organizations transitioning from proprietary to open source software, migration from MS SQL to MySQL database is gaining momentum.

Why Migrate?

There are several reasons you might want to consider moving from MS SQL Server database to MySQL database, such as:

• Reduced Cost: Migrating to MySQL typically involves lower costs compared to MS SQL. Although, there is a free copy of MS SQL Server called Express available online, it has certain limitations. For example, it can only address up to 1 GB of RAM and imposes a database limit of only up to 10 GB. Thus, you’ll need to pay for SQL Server’s licensed edition for production deployments. But, you can use MySQL community edition for free, without such limitations

Also, the Total Cost of Ownership (TCO) of MySQL licensed edition – MySQL Enterprise Edition, is a lot less compared to MS SQL Server Enterprise Edition.



• Supports Major Operating Systems (OS) and Platforms: Unlike MS SQL, MySQL runs efficiently on all of the major OS and platform combinations including Windows, Linux and Solaris.
• Installation and Configuration: MySQL usually takes 15 minutes or less for downloading and installation. To make the installation and configuration process much easier for Windows users, Oracle offers MySQL Installer. With MySQL Installer, you can quickly install and configure MySQL products for Windows in only 3 minutes.
• Flexibility: SQL Server uses a single storage engine. However, MySQL provides developers great flexibility to choose different storage engines for different table types.

Whatever be the reason, database migration should not be taken lightly, especially when you are considering to manually migrate MS SQL to MySQL database. Manual database migration procedure can be troublesome, if not planned carefully.

In this blog, we’ll discuss the manual procedure to perform Microsoft SQL Server database to MySQL database migration. But before we proceed, let’s first assess ‘data types’ that need special attention during migration.

Data Types

MySQL supports all the important MS SQL data types. However, there are some SQL server data types that do not match with MySQL data types. Some of the major data types you’ll need to map MySQL with are as follows:

How to Manually Migrate MSSQL Server to MySQL Database?

Database migration is a multiphase process, but in this blog we will discuss two major steps involved in manual migration of MS SQL Server to MySQL Database.

Step 1: Database Schema Conversion

The schema is a visual representation of how database is structured. When migrating data from one system to another, you'll need to convert schema so that the source database structure works with the target database.

• For moving or copying the MS SQL database table's schema into MySQL, you must map data types, find NULL constraint, and determine the field that is set as a PRIMARY KEY.
• This procedure does not support conversion of indexes, foreign keys, identity columns, unique or other table constraints, and character set.

In order to understand the process of schema conversion, let’s create a sample database with a new table named ‘Table_1’. For this, open the SQL Server Migration Assistant (SSMA) and enter the following code snippet:

CREATE DATABASE [sample]
GO
USE [sample]
GO
CREATE SCHEMA [prd] AUTHORIZATION [dbo]
GO
CREATE TABLE [prd].[Table_1]
(
[column1] [int] NOT NULL,
[column2] [char](10)NULL,
[column3] [nchar](10)NULL,
[column4] [nvarchar](50)NULL,
[column5] [varchar](50)NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
([column1] ASC)ON [PRIMARY])ON [PRIMARY]
GO

Next, you will need to physically create database objects by applying the converted schema to the target database instance. For this, run the CREATE TABLE statement in the mysql client program to create a table on MySQL server. To do so, follow these steps:

• Install and run MySQL Workbench on your system.
• Once MySQL Workbench is installed and you’ve set up a connection between Workbench and the target MySQL server, click Local instance MySQL57 (as shown in the screenshot below):



• When prompted, enter password to connect to MySQL Server, and then click OK.
• In the Local instance MySQL57 window, click SQL and enter the following code snippet:

CREATE DATABASE sample;
use sample;
CREATE TABLE prdTable_1
( column1 int(11) NOT NULL,
column2 char(10) DEFAULT NULL,
column3 char(10) CHARACTER SET utf8 DEFAULT NULL,
column4 varchar(50) CHARACTER SET utf8 DEFAULT NULL,
column5 varchar(50) DEFAULT NULL,
PRIMARY KEY (column1)
);

Step 2: Data Migration

Once schema conversion process is complete, you’ll need to move the data.

The following is an example in which the Table_1 table from the prd schema is inserted manually in ‘MySQL Workbench local instance’:

INSERT INTO prdtable_1(column1,column2,column3,column4,column5)
VALUES(5,'stellar','data','Recovery','For SQL');
GO

The Best Way of Migrating MS SQL to MySQL

The best approach is to perform automatic conversion of MS SQL to MySQL database by using specialized database converter tool such as Stellar Converter for Database. The software supports database migration, by allowing offline conversion of database records and attributes in an easy and hassle free manner.

Essentially, the converter tool helps administrators and users save time and efforts in writing complex queries to move MS SQL database file to MySQL database file format.

Key Features

• Helps interconvert MS SQL (.mdf), MySQL, SQLite (.db and .sqlite) & SQL Anywhere (.db) files
• Allows conversion of single or multiple database tables
• Allows handling of large databases
• Maintains data integrity of database records

How to use Stellar Converter for Database to migrate data from MS SQL to MySQL?

The steps involved in conversion of data from MS SQL to MySQL using Stellar Converter for Database are as follows:

1. Check for Prerequisites

• MySQL must be installed on the system with default configuration.
• ibdata file must be stored at the same location as MySQL database.
• Drive used for storing MS SQL server data must have free space equivalent to the size of MySQL database.

Once you have met the prerequisites, download and install the software. For detailed information about software installation, refer to Stellar Converter for Database Installation Guide.

2. Select Source Database

From the software main interface, in Select Database window, choose MS SQL as your source database type. Next, click Browse or Search under Database Path to select the database you want to convert. And then, click Scan.

3. Check Preview of Objects that can be Converted

Once scanning is complete, the software will provide preview of objects that can be converted into MySQL database format.

4. Convert and Save the Data in Destination Database

Select the objects you want to convert from the preview window, and then click Convert on File menu. In Convert and Save Database window, select MySQL under Select Database Type. Fill in Connect to Server details, and then click Save.

The selected database will be converted successfully.

Conclusion

Organizations may develop the need to migrate from MS SQL server to MySQL because of its rich feature-set, cross-platform and open source availability, and lower cost.

While migration from one database to another can be performed manually, it can be an extremely time-consuming and error-prone process.

A better alternative is to use specialized database converter software like Stellar Converter for Database, which is specially designed to help DBAs and developers automate the process of converting a database file format to another. The software converts table records and attributes from MS SQL to MySQL database quickly, while preserving database integrity.

Was this article helpful?

YES16

NO0

About The Author

Charanjeet Kaur

Technical writer with over 7 years of experience