Migrating a SQL Server Database to a MySQL-Compatible Database Engine
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.
Migration scenarios
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.
Scenario 1:
Default dbo schema names
If the default schema in SQL Server is dbo, you can simply choose to not use the dbo schema during the migration to a MySQL database. The SQL Server database name can be translated to a MySQL database name. For example:
SQL Server: Db1.dbo.table1
MySQL: Db1.table1
(dbo can be dropped off easily during the database conversion in MySQL)
The application code must be changed accordingly to reflect the complete table identifier hierarchy changes.
Scenario 3:
Same table names under different schemas
In this scenario, the same table names are under different schemas in the same database:
Db1.schema1.table1
Db1.schema2.table1
In this case, omit the database names and use schema names as a database name on the MySQL side instead. This avoids the confusion of the same table names under the same database.
MySQL:
Db1.table1
Db2.table1
Scenario 2:
Custom schema names
If there is a custom schema name, you must decide if only the database name or only the schema name needs to be translated to the MySQL database name. For example:
Db1.schema1.table1
db2.schema2.table2
Depending on the application code, the schema or the database name can still be omitted when you are converting the database or the schemas to a MySQL database. In this case, it would be something like the following:
Db1.table1
Db1.table2
Or
Schema1.table1
Schema2.table1
Scenario 4:
Joining database and schema names
In this scenario, both the database name and schema name from SQL Server can be joined to create a database on the MySQL side. AWS SCT follows this path. For example:
SQL Server: db1.schema1.table1
MySQL: db1_schema1.table1
Here, both database and schema names are joined with an underscore to create a MySQL database on the target.
Overall migration strategy
To migrate the database from one engine to another (here you are migrating SQL Server to a MySQL-compatible engine), the following steps are suggested:
- Create your schema in the target database.
- Drop secondary indexes on the target database and disable triggers. AWS DMS does a table-by-table load during the full load phase, and disabling foreign keys is important for that to work. This is done using the target endpoint extra connection attribute mentioned later in this post.
- Set up an AWS DMS task to replicate your data—full load and change data capture (CDC).
- Configure the task to stop before applying cached changes and add secondary indexes on the target database.
- Resume the AWS DMS task for CDC.
- Enable triggers after the cutover.
How to migrate from MySQL to MariaDB on Linux in five steps
You’ve decided to move from MySQL to MariaDB, but you’re a little nervous. The simplest method follows these steps:
- Update your software repositories list with the MariaDB repos
- Update your Linux package manager with the new repos
- Stop MySQL
- Install MariaDB with your package manager
- Go back to work because you’re done
Before you do anything, make sure you have current backups. You’ll want MariaDB to be the same or higher version than your MySQL server. MySQL major releases are 5.0, 5.1, 5.5, and 5.6. MariaDB versions are 5.1, 5.2, 5.3, 5.5, and 10.
Author: Ramakumari Kesanakurthi – Web Developer
Source: aws.amazon.com