AWS Database Blog
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 such as Amazon RDS for MySQL, Amazon RDS for MariaDB, or Amazon Aurora MySQL.
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 MariaDB. 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. However, there are major architectural differences that must be considered when you’re migrating from SQL Server to MySQL. 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:
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 databasename.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 dbo by default. However, this can be changed to meet organizational, functional, or business needs. The complete table identifier in SQL Server is Databasename.Schemaname.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 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 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 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.
Creating your schema in the target MySQL database
You can start the migration by analyzing the schema objects. Here, we use the AWS Schema Conversion Tool (AWS SCT) to perform the analysis. When you start AWS SCT, create a new project with the source being SQL Server and the target being MySQL/Aurora. When you are connected, choose the name of the database that you want to migrate on the left side. Open the context (right-click) menu for the database name, and choose Convert Schema. Then choose View, Assessment Report View.
The AWS SCT assessment report provides a high-level overview and recommendations for converting the schemas. Further efforts may be required to convert your SQL Server database to MySQL/Aurora based on the initial assessment report. The following is an example of what the assessment report looks like:
The report is broken down by each object type and the manual work effort needed to successfully convert it. In general, packages, procedures, and functions have some issues to resolve. AWS SCT also tells you why you need to fix these objects and gives you hints on how to do it.
If your schema doesn’t convert automatically, here are some helpful hints to fix the issues:
- Try converting the schema as-is and manually modifying the scripts that are generated by AWS SCT before applying them to the target MySQL database.
- Ignore the objects that can’t be converted, and replace the functionality with another AWS service or by the application itself. For example, you can invoke an AWS Lambda function on Aurora MySQL to perform certain functionalities, such as sending notifications, etc.
We recommend that you browse through the schema of the target database and do a quick check of all the schema objects, such as column data types, object names, and so on. For more information about data types, see the AWS Database Migration Service documentation for the source and target data types. Also, because the conversion is from SQL Server to MySQL, one concern is that object names are uppercase in SQL Server because it supports uppercase in the default collation, whereas MySQL supports lowercase by default.
In the preceding conversion run, AWS SCT follows the database/schema naming convention mentioned in Scenario 4. It converts the source SQL Server database and schema name to one conjoined database and schema name on the target MySQL database. The dms_sample database and dbo schema are converted to the dms_sample_dbo database on the target.
Setting up an AWS DMS task to migrate your data
Now that you have the schema ready on the target, you are ready to migrate the data using AWS DMS. To use AWS DMS for full load and CDC, make sure that you’ve completed the following prep work:
- The logins for SQL Server and MySQL have the required documented permissions.
- SQL Server recovery model is set to Bulk logged or Full to capture data change (CDC).
- For additional preparation information, see the AWS DMS documentation about using SQL Server as a source in AWS DMS.
Create a replication instance
In the AWS console, open AWS Database Migration Service. First, you need to create a replication instance. A replication instance runs the AWS DMS task. This instance is an intermediary server that connects to both your source SQL Server database and your target MySQL/Aurora database. Select an appropriately provisioned replication instance (following the best practices mentioned in the whitepaper AWS Database Migration Service Best Practices).
Next, create an endpoint for your source database and another endpoint for your target database. Enter all the appropriate connection information for the SQL Server database and the MySQL database. Add the following extra connection attribute to the MySQL target endpoint to disable foreign key checks:
Make sure that you select the Refresh schemas option after a successful connection test and Run test before you finish creating each endpoint.
While creating the SQL Server endpoint, you need to provide the database name. You can provide one database at a time, and therefore the endpoint can connect to one database only. This means that you can migrate one database per task. If you want to migrate multiple databases, you need to create multiple AWS DMS tasks and follow all best practices to make sure that you don’t run into resource constraint issues. Any AWS DMS task creation can also be scripted and automated via the AWS CLI or by using AWS CloudFormation templates.
Configure the task
Now, you’re ready to create a task. Enter a task name, choose the replication instance you created, and choose the source and target endpoint. For Migration type, use Migrate existing data and replicate ongoing changes. Because you’re using AWS SCT to pre-create the schema, choose Do nothing or Truncate for Target table preparation mode.
Choose Stop After Applying Cached Changes for the Stop task after full load completes option. This stops the task temporarily after the full load completes and the cached changes are applied. Cached changes are changes that have occurred and accumulated while the full table load process was running. This is the step just before CDC is applied.
Finally, we also recommend that you select Enable logging so that you can see any errors or warnings that the task encounters and troubleshoot those issues.
Next, under Table mappings, choose which schema you want to migrate, and choose Add selection rule. Choose the JSON tab. Select Enable JSON editing and enter the required JSON string to migrate your tables. Here is an example:
From the preceding JSON string, you can choose to copy all the tables within the schema by selecting ”%” for the table-name. Here we chose four specific example tables: person, player, seat, and seat_type. We also created four separate transformation rules to do the following:
- Convert the SQL Server uppercase schema name to a lowercase schema name on the target.
- Convert the SQL server uppercase table name to a lowercase table name on the target.
- Convert the SQL Server uppercase column name to a lowercase column name on the target.
- Instead of creating/copying the dbo schema on the target, create/copy it to an existing mysqldb database.
AWS DMS provides many more transformation rules that can be applied during the migration of the SQL Server Database to MySQL. For more information, see Transformation Rules and Actions in the AWS DMS documentation.
After you copy the JSON string, choose the Guided tab, where you can see that all the schemas are auto-populated for both selection and transformation rules.
After the task is created, it starts automatically if you chose the Start task on create option when you created it. You can monitor its progress using the AWS DMS console by selecting the task and choosing the Table statistics tab. When the full load is complete and cached changes are applied, the task stops on its own.
After the full load is complete and cached changes are applied, go ahead and re-create foreign keys and secondary indexes.
The statistics provide high-level information about what tables got copied and which received errors. However, you still want to review the logs to make sure that there are no errors related to any objects or warnings on data truncation or the data itself during the conversion and migration.
Enabling the DMS task for continued replication
Now that you have the foreign keys and secondary indexes, you can enable the AWS DMS task to start the next phase, CDC. This phase applies changes in the order that they occurred in the source database. Go to the AWS DMS console and choose Tasks. Choose the task in the list and choose Start/Resume. Choose the Start option and then choose Start task.
Let the replication run until cutover. During the cutover, make sure that the application has stopped accessing the source database and that AWS DMS has replicated the last data changes to the target database. Stop the AWS DMS task in the AWS DMS console, and enable triggers on the target database. Finally, point the application to the new target.
Helpful hints
- To make your schema easier to convert, use AWS SCT to get an assessment report and iterate through the action items. You may need to generate the target schema multiple times until you come to the final version of the target MySQL schema.
- To ensure that the application query works on the new platform, test your application on your target system. AWS SCT can also convert your application queries to MySQL—for details, check out the AWS SCT documentation. Testing your application is the key to the success of heterogeneous migrations.
- Test out the preceding migration steps and streamline the process based on your on-premises environments and business requirements. These steps are just a starting point, and each database is unique in its own way.
Conclusion
This post is not intended to outline all possible steps or considerations that might be required for your particular situation. However, it should give you a good idea of how you can use AWS SCT and AWS DMS to move your SQL Server database to MySQL-compatible database engines. For more information about AWS Database Migration Service and the AWS Schema Conversion Tool, see the AWS DMS User Guide and AWS SCT User Guide.
About the Authors
Akm Raziul Islam is a consultant with a focus on Database and Analytics at Amazon Web Services. He works with our customers to provide guidance and technical assistance on various database and analytical projects, helping them improving the value of their solutions when using AWS.
Arun Thiagarajan is a database engineer with the Database Migration Service (DMS) & Schema Conversion Tool (SCT) team at Amazon Web Services. He works on DB migrations related challenges and works closely with customers to help them realize the true potential of the DMS service. He has helped migrate 100s of databases into the AWS cloud using DMS and SCT.