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
- 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.
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.
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:
| SQL Server | MySQL |
| VARCHAR(max | LONGTEXT |
| SQL_VARIANT | BLOB |
| IDENTITY | AUTO_INCREMENT |
| NTEXT, NATIONAL TEXT | TEXT CHARACTER SET UTF8 |
| SMALLDATETIME | DATETIME |
| DATETIMEOFFSET | TIMESTAMP |
| MONEY | DECIMAL(19,4) |
| UNIQUEIDENTIFIER | BINARY(16) |
| SYSNAME | CHAR(256) |
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.
NOTES:- 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
While manually migrating one database to another gives you complete control over the migration process, it has certain limitations, such as:
- Manually converting databases with thousands of object properties can be very time-consuming.
- Moving large volumes of data involves risk of data loss.
- It can be an overwhelming and challenging task for novices.
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.
7 min read
