How to Script a Database Migration

Satheesh Subramanian is a software development engineer at Amazon Web Services.

AWS Database Migration Service (DMS) lets you migrate databases to AWS easily and securely. The process consists of setting up a replication instance, source and target endpoints, and a replication task. The replication task runs on the replication instance and migrates data from the source endpoint to the target endpoint.

You can use the AWS DMS console or the AWS CLI or the AWS SDK to perform the database migration. In this blog post, I will focus on performing the migration with the AWS CLI.

If you are a first-time AWS DMS user, then follow these steps to set up your account.

• Create an IAM user: http://docs.thinkwithwp.com/dms/latest/userguide/CHAP_SettingUp.html#CHAP_SettingUp.IAM
• Set up required permissions for the IAM user: http://docs.thinkwithwp.com/dms/latest/userguide/CHAP_Security.IAMPermissions.html
• Set up the roles required to use AWS DMS: http://docs.thinkwithwp.com/dms/latest/userguide/CHAP_Security.APIRole.html
• Set up the AWS CLI: http://docs.thinkwithwp.com/cli/latest/userguide/cli-chap-getting-set-up.html

After you have completed the setup, follow these steps to complete a simple database migration.

1. Create a replication instance.
   Use the following command to create a replication instance with the name dms-instance.
   aws dms create-replication-instance --replication-instance-identifier dms-instance --replication-instance-class dms.t2.medium --allocated-storage 50
   This command creates the replication instance on a t2.medium instance with 50 GB of allotted storage. It uses default values for other parameters. For more configuration options to use when creating a replication instance, see create-replication-instance in the AWS CLI Command Reference.
2. Describe the replication instance.
   Run the following command to describe the replication instance. The response of this command will include the status of create-replication-instance. This will help you understand the status of the instance creation.
   aws dms describe-replication-instances --filter=Name=replication-instance-id,Values=dms-instance
   Run the following command to save the ReplicationInstanceArn for use in later steps.
   rep_instance_arn=$(aws dms describe-replication-instances --filter=Name=replication-instance-id,Values=dms-instance --query 'ReplicationInstances[0].ReplicationInstanceArn' --output text)
   It will take a few minutes to create the replication instance. While that’s in progress, create the source and target endpoint objects.
3. Create the source and target endpoints.
   Run the following command to create the source and target endpoints. Provide source and target database details like the engine-name, the hostname and port and the username and password.
   aws dms create-endpoint --endpoint-identifier source-endpoint --endpoint-type source --engine-name --username --password --server-name --port
aws dms create-endpoint --endpoint-identifier target-endpoint --endpoint-type target --engine-name --username --password --server-name --port
   For more advanced options for endpoint creation, see create-endpoint in the AWS CLI Command Reference.
   Run the following commands to save the endpoint ARNs for use in later steps.
   source_endpoint_arn=$(aws dms describe-endpoints --filter="Name=endpoint-id,Values=source-endpoint " --query="Endpoints[0].EndpointArn" --output text)
target_endpoint_arn=$(aws dms describe-endpoints --filter="Name=endpoint-id,Values=target-endpoint" --query="Endpoints[0].EndpointArn" --output text)
4. Test source and target endpoints from the replication instance.
   After the replication instance is active and the endpoints have been successfully created, test connectivity from the replication instance to these endpoints. The following commands will invoke connectivity tests from the replication instance to the database endpoints:
   aws dms test-connection --replication-instance-arn $rep_instance_arn --endpoint-arn $source_endpoint_arn
aws dms test-connection --replication-instance-arn $rep_instance_arn --endpoint-arn $target_endpoint_arn
5. Describe connections to the source and target endpoints.
   The describe-connections response will contain the status of the test connection and, in the case of a failed connection, the failure message. After invoking the connectivity test, describe the connections to ensure the tests are successful. If the test connection fails for any reason, it must be fixed and retested.
   aws dms describe-connections --filter "Name=endpoint-arn,Values=$source_endpoint_arn,$target_endpoint_arn"
   Note: A failure message in the describe response will provide details for the test connection failure.
6. Create a replication task.
   If the test connections are successful, use the following command to create the task:
   aws dms create-replication-task --replication-task-identifier replication-task-1 --source-endpoint-arn $source_endpoint_arn --target-endpoint-arn $target_endpoint_arn --replication-instance-arn $rep_instance_arn --migration-type full-load --table-mappings file:///tmp/table-mappings --replication-task-settings file:///tmp/task-settings
   This command assumes you have table mappings and task settings files in the temp directory (in Linux/Mac OS). For information about how to specify the file input for various platforms, see Loading Parameters from a File in the AWS Command Line Interface User Guide.To decide which values to use for task settings, table mappings, and migration types, see the following:
   • Task Settings for AWS Database Migration Service Tasks
   • Migration Methods for AWS Database Migration Service
   • Using Table Mapping with an AWS Database Migration Service Task to Select and Filter Data

   For more advanced options for replication task creation, see create-replication-task in the AWS CLI Command Reference.

7. Describe the replication task.
   Task creation will take a few minutes. After the task is created, describe the task and make sure it is ready to be executed.
   aws dms describe-replication-tasks --filters "Name=replication-task-id,Values=replication-task-1"
   Run the following command to save the replication task ARN for use in later steps:
   replication_task_arn=$(aws dms describe-replication-tasks --filters "Name= replication-task-id,Values=replication-task-1" --query "ReplicationTasks[0].ReplicationTaskArn" --output text)
   Run the following command if you want to just retrieve the status of the task:
   aws dms describe-replication-tasks --filters "Name=replication-task-arn,Values=$replication_task_arn" --query "ReplicationTasks[0].Status"
8. Start the replication task.
   Run the following command to start the task after it is ready to be executed:
   aws dms start-replication-task --replication-task-arn $replication_task_arn --start-replication-task-type start-replication
   For all available options for the start-replication-task command, see start-replication-task in the AWS CLI Command Reference.
9. Monitor the progress of the replication task.
   After you start the task, it’s very important to monitor its progress. Run the following commands to keep track of the task progress.
   To monitor the overall task-level statistics, run the following command:
   aws dms describe-replication-tasks --filters "Name=replication-task-arn,Values=$replication_task_arn" --query "ReplicationTasks[0].ReplicationTaskStats"
   To monitor the table-level statistics, run the following command:
   aws dms describe-table-statistics --replication-task-arn $replication_task_arn
   To monitor the task status itself, run the following command:
   aws dms describe-replication-tasks --filters "Name=replication-task-arn,Values=$replication_task_arn" --query "ReplicationTasks[0].{Status:Status,StopReason:StopReason}"
10. Stop the replication task.
    You can stop the migration after data is completely migrated from source to target. Run the following command to stop the migration task:
    aws dms stop-replication-task --replication-task-arn $replication_task_arn
11. Delete the replication task.
    If you don’t want to keep the task, run the following command to delete it:
    aws dms delete-replication-task --replication-task-arn $replication_task_arn
12. Delete the source and target endpoints.
    If you no longer need the endpoints, run the following commands to delete them:
    aws dms delete-endpoint --endpoint-arn $source_endpoint_arn
aws dms delete-endpoint --endpoint-arn $target_endpoint_arn
13. Delete the replication instance.
    After your migration is complete, run the following command to delete the replication instance:
    aws dms delete-replication-instance --replication-instance-arn $rep_instance_arn

This was a simple migration. For more advanced database migrations, see the AWS Database Migration Service User Guide.

For migration best practices, see AWS Database Migration Service Best Practices.

And in case something goes wrong, see Troubleshooting AWS Database Migration Service Tasks.

Sign in to the AWS DMS console to easily create and manage database migration tasks here: https://console.thinkwithwp.com/dms/home