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.

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

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.

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') It will take a few minutes to create the replication instance. While that’s in progress, create the source and target endpoint objects.

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")target_endpoint_arn=$(aws dms describe-endpoints --filter="Name=endpoint-id,Values=target-endpoint" --query="Endpoints[0].EndpointArn")

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_arnaws dms test-connection --replication-instance-arn $rep_instance_arn --endpoint-arn $target_endpoint_arn

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.

Create a replication task. If the test connections are successful, use the following command to create the task:aws dms create-replication-task --task-identifier replication-task-1 --source-endpoint-arn $source_endpoint_arn --target-endpoint-arn $target_endpoint_arn --replication-instan+ce-arn $rep_instance_arn --migration-type --table-mappings file:///tmp/table-mappings --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:

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

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") 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"

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.

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}"

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

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

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_arnaws dms delete-endpoint --endpoint-arn $target_endpoint_arn

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