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.
There are several reasons you might want to consider moving from MS SQL Server database to MySQL database, such as:
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.
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:
|NTEXT, NATIONAL TEXT||TEXT CHARACTER SET UTF8|
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:
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]
CREATE SCHEMA [prd] AUTHORIZATION [dbo]
CREATE TABLE [prd].[Table_1]
[column1] [int] NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
([column1] ASC)ON [PRIMARY])ON [PRIMARY]
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:
CREATE DATABASE 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)
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
The steps involved in conversion of data from MS SQL to MySQL using Stellar Converter for Database are as follows:
1. Check for Prerequisites
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.