Chapter 10: The AWS Schema Conversion Tool and AWS Database Migration Service

In the previous chapters, we learned about the different databases that are offered by AWS and the key features and use cases for each. Now, we are going to learn how to migrate a database to AWS and the tools that are available to help you – that is, AWS Schema Conversion Tool (SCT) and AWS Database Migration Service (DMS).

AWS SCT is a tool that allows you to assess a database's ability to migrate to AWS RDS and whether you can convert the database from a commercial database engine, such as Oracle, into an open source database. Then, it helps you convert the database objects and code for the new database engine.

AWS DMS can also help convert from one database engine into another, but its main role is to move the data within your database from on-premises to AWS or between AWS databases. It offers both bulk load and Change Data Capture (CDC) modes to support different types of migrations...

Technical requirements

To complete this chapter, you will need an AWS account with root access. Everything we will do in this chapter will be unavailable in Free Tier, which means it will cost you a small amount to follow the hands-on sections. You will also require command-line interface (CLI) AWS access. The AWS guide at https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-configure.html explains the necessary steps, but I will summarize them here:

1. Create an AWS account if you have not already done so.
2. Download the latest version of the AWS CLI from https://docs.aws.amazon.com/cli/latest/userguide/welcome-versions.html#welcome-versions-v2.
3. Create an admin user by going to https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_access-keys.html.
4. Create an access key for your administration user: https://docs.aws.amazon.com/IAM/latest/UserGuide/getting-started_create-admin-group.html#getting-started_create-admin-group-cli.
5. Run the aws configure...

Overview of SCT

AWS Schema Conversion Tool (SCT) is a downloadable program that's designed to help you change from one database engine to another. For example, you can use it to help convert database objects and database code from Oracle into PostgreSQL. It can also be used to carry out a migration assessment to both calculate the effort involved in converting the database engine and also to check for any features that are being used that are incompatible with RDS versions of the database. As we learned in Chapter 4, Relational Database Service, RDS does not support all the features of every database engine. SCT is available on Windows, Ubuntu Linux, and Fedora Linux.

SCT can carry out assessments and convert from the following database types:

• Oracle
• SQL Server
• Apache Cassandra
• Azure SQL Database
• IBM Db2
• MySQL
• PostgreSQL
• SAP/Sybase ASE

SCT can carry out assessments and convert from the following Data Warehouse types:

• Amazon...

Assessing a database using SCT

SCT allows you to assess various source databases to check how much effort it takes to convert to either an open source target database that AWS supports in RDS, Amazon DynamoDB, or Amazon Redshift, or to assess a migration from an on-premises Oracle database to RDS Oracle, or from an on-premises Microsoft SQL Server to RDS Microsoft SQL Server.

To run the assessment, you must download the SCT program for your operating system from AWS and install it. Then, you must provide the connection and login details for the source database. SCT will show you a list of all the schemas/users it finds and you can choose the ones to assess, which lets you filter out unused or old schemas.

Once the report is complete, it will show the following information:

• A summary of the source database
• The license evaluation of the source database, including an assessment of whether you can change to a standard edition license from an enterprise edition one
...

Converting a database using SCT

Once you have completed the assessments and decided on a target database, you can use SCT to assist in converting the database objects and code. SCT carries out the following tasks:

• SCT installs an extension pack that contains common functions that can be used in converted database code. This extension pack is supplied within the SCT program.
• SCT checks all DDL statements in the source database and attempts to recreate those in the language of the target. This includes tables, views, indexes, and more.
• SCT attempts to convert the database's code into the target database's code.
• For those who cannot automatically convert either using the native code of the target database or via the added extension pack functions, it provides a report that shows where the issues with the conversion are and what action you need to take to resolve them.

Figure 10.2 showed some example output of an assessment report for a SQL Server source...

Overview of DMS

AWS Database Migration Service (DMS) is a tool for moving your data between databases. DMS can be used to migrate data between databases of the same type or between different database engines. If you use it to migrate between different database engines, it will convert the data for you so that it fits the target. DMS can also create target database tables for you, if they do not already exist, in a similar way to SCT. However, it is much less powerful and only converts the minimal database objects that are required for a successful migration. You would still need to manually create most indexes, views, and database code.

DMS has three main components:

• Endpoints: These allow DMS to connect to databases that are hosted in different VPCs and on-premises.
• Replication Instance: This is an EC2 instance that's used to run the migration.
• Tasks: These are the jobs that control and manage the migration process.

To use DMS, you must provide a source...

Setting up DMS

To start a DMS task, we need to configure the DMS environment. This involves creating a replication instance, as well as the source and target endpoints, and setting up the task itself with the transformation rules we want to apply. The first component we must create is the replication instance.

Replication instances

A replication instance is an EC2 instance that's preconfigured with the DMS application code. Its role is to provide compute resources to any data conversions that need to be handled, as well as store data that's ready to be applied during a CDC mode task. The replication instance can handle multiple tasks simultaneously and can be sized according to your needs. You can monitor the performance and resource usage of a replication instance while tasks are running to help you assess whether the instance is sized correctly. You can also have multiple replication instances per account, but only one can be assigned to a task at once; there is no...

Running a DMS task via SCT

You can start a DMS task once the task has been successfully created and is showing a status of Ready. To start it, you can either click the checkbox next to it and then go to the Actions dropdown and select Restart/Resume (the naming here is ambiguous) or you can click on the task's name and go to the Actions dropdown and select Restart/Resume.

Once the job is running, you can monitor its progress on the Table statistics tab.

Figure 10.9 – DMS table statistics

In the preceding screenshot, the table did not migrate successfully, with DMS reporting a mismatch between the source and the target after migration. To investigate this mismatch, you will need to inspect any CloudWatch logs that were created by DMS, as well as the source and target database logs. We will learn more about monitoring DMS in the next section. If you run a large DMS job with multiple tables, you will see that multiple tables are converted and migrated...

Monitoring and tuning DMS

DMS offers a large number of options for logging and monitoring, depending on your needs. In general, for any new job or one that has not been tested, you should enable full debugging monitoring so that you can trace any errors. Once a job has been completed successfully, you can reduce monitoring on subsequent runs to minimize costs as all monitoring logs are charged for.

The following monitoring and logging tools are available to you:

• DMS Console: You can use the DMS GUI to check the status of the current tasks.
• AWS CLI: You can run awscli commands to obtain the current task's status.
• AWS Simple Notification Service (SNS): You can configure SNS events to notify you via email of any changes to your tasks.
• CloudWatch: DMS can be configured to send enhanced logs to CloudWatch, allowing you to trace errors.
• Time Travel Logs: DMS Time Travel stores information about CDC changes that have been made, allowing you to see the exact...

Running a DMS task via SCT

SCT and DMS can work together to handle both schema conversion and data migration. Using a data extraction agent, SCT allows you to monitor and control DMS jobs via the SCT interface. To do this, you must configure an AWS service profile to use, which requires an AWS Access Key and an AWS Secret Key for a user who has permissions to create DMS tasks. Once that profile has been added, SCT will be able to create DMS tasks for you. The SCT DMS interface can only work with tasks and cannot create endpoints or replication instances, so these will need to be configured before you try to migrate. The following screenshot shows the Global settings view, which appears when you have correctly configured an AWS Access Key and profile:

Figure 10.11 – AWS Global settings for AWS service profiles

To use DMS within SCT, you need to install a data extraction agent. This agent can also be used for complex SCT migrations, such as from Apache...

Converting and migrating a database to AWS

Now that we've learned the theory behind how SCT and DMS work to assess, convert, and migrate a database, let's practice our knowledge in a hands-on lab. In this lab, we are going to create an RDS Microsoft SQL Server database as our source and convert and migrate it to an RDS MySQL database.

AWS Account Costs

RDS SQL Server and DMS are chargeable by AWS and are not covered by the free tier. This lab aims to let you learn about these tools, which are major areas within the DB Specialty exam, while keeping costs as low as possible.

Setting up

Before we can start any conversion and migration, we need a source and target database. To give us a schema to work with, we will use the Microsoft SQL Server Northwinds sample data, which is available here: https://github.com/microsoft/sql-server-samples/blob/master/samples/databases/northwind-pubs/instnwnd.sql.

First, we will create an RDS SQL Server database and restore the...

Summary

In this chapter, we learned how to use AWS Schema Conversion Tool and AWS Database Migration Service to migrate databases to AWS. We learned how to use SCT to assess whether a database needs to be converted into a different database engine, as well as how to assess it for migration to RDS. After that, we learned how to use DMS to migrate the data to our target database using full load and CDC tasks.

We also learned about AWS Snowball and how it works with the SCT data extraction agent. This allows you to migrate large or complex databases using a storage device to reduce network bandwidth and speed up a migration.

SCT, DMS, and migration strategies are major topics within the DB Specialty exam, so covering these tools and techniques in depth will have greatly enhanced your skills going into the exam.

In the next chapter, we are going to learn how to use automation techniques, including two data handling and querying tools that are offered by AWS: AWS Athena and AWS...

Cheat sheet

This cheat sheet summarizes the main key points from this chapter:

• SCT is used to assess and convert a database from one engine into another for a heterogeneous migration.
• SCT can also be used to assess a homogenous migration in RDS to highlight features that are not supported in your source database.
• Once you have converted a schema using SCT, you can use DMS to migrate the data into the empty schema and tables.
• DMS can also be used to create a schema in your target database, but you have no control over the data types that are created in the table.
• DMS can be used in both full load and CDC mode.
• To run a DMS task, you need a replication instance, a source endpoint, and a target endpoint.
• You can run multiple tasks to migrate a single source database to improve performance.
• For very large databases or complex conversions, you can use AWS Snowball Edge with SCT and DMS to assist with the migration.

Review

Let's test our knowledge of the contents of this chapter with some example exam questions:

1. You have been hired to migrate an Amazon EC2 instance that's running Oracle Database Standard Edition to an RDS for Oracle DB instance. The database is used for critical production services and the business can only provide a 5-minute outage window. How can you achieve this?
   1. Configure Oracle Real Application Clusters on the EC2 instance with the RDS DB instance as one of the nodes. Once the EC2 and RDS DB instances are in sync, switch over from Amazon EC2 to Amazon RDS and update the application connection string.
   2. Export the Oracle database from the EC2 instance using Oracle Data Pump to an S3 bucket and import it into Amazon RDS. Shut down the application until the restore is complete. Change the database connection string and then restart the application.
   3. Create an AWS DMS task with the EC2 instance as the source and the RDS DB instance as the destination. Stop the application...

Further reading

AWS DMS Studio – Getting Started: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_DMSStudio_GettingStarted.html.