This post provides an overview of how you can migrate your Microsoft SQL Server database to a MySQL-compatible database engine.
The following are the two main parts of a database migration:
Schema conversion: Converting the schema objects is usually the most time-consuming operation in a heterogeneous database migration. It is the foundation of the database and must be handled in a well-planned manner. If the schema conversion is done appropriately, a major milestone for the heterogeneous migration is complete.
Data migration: The base data elements are like the building blocks for the schema foundation mentioned previously. If the foundation is laid properly, arranging these blocks is comparatively simpler during the migration when best practices are followed.
In this post, we go over how to use the AWS Schema Conversion Tool (AWS SCT) and the AWS Database Migration Service (AWS DMS) to migrate a SQL Server database to popular MySQL-based database engines like Aurora MySQL, MySQL, and Maria DB. In this post, we refer to all these database engines as MySQL. When it comes to migrating a SQL Server database to a MySQL-compatible database, the database object semantics between SQL Server and MySQL are similar For example, in MySQL, “database” and “schema” both share the same meaning. There are no functional differences between a database and a schema. The following example illustrates this point:
mysql> create schema test01;
Query OK, 1 row affected (0.01 sec)
mysql> create database test01;
ERROR 1007 (HY000): Can’t create database ‘test01’; database exists
As the example shows, “database” and “schema” are synonymous and don’t have any separate meaning. When it comes to referring to a table within a database, the complete table identifier in MySQL looks like database name.tablename.
On the other hand, SQL Server does convey a different meaning and functionality to the “database” and “schema” keywords. In SQL Server, a database is the main container that holds all objects, data, and log files. A schema is an object within the specific database that logically groups the other database objects together. SQL Server uses the schema name do by default. However, this can be changed to meet organizational, functional, or business needs. The complete table identifier in SQL Server is Database name. Schema name.tablename.
Keeping that explanation in mind, there can be four different scenarios when it comes to migrating a SQL Server database and schema to a MySQL-compatible database. You can follow one of these scenarios (or a combination) based on your application and database requirements.