AWS Database Blog

Automating AWS DMS Migration Tasks

This blog post gives you a quick overview of how you can schedule migration tasks for the purpose of automating your migration. You create these tasks by using the AWS Database Migration Service (AWS DMS) and using native operating system tools for either Linux or Microsoft Windows.

AWS DMS helps you migrate databases to AWS quickly and securely. You can migrate your data to and from most widely used commercial and open-source databases, such as Oracle, Microsoft SQL Server, PostgreSQL. The service supports homogeneous migrations such as Oracle to Oracle, and also heterogeneous migrations between different database platforms, such as Oracle to PostgreSQL or MySQL to Oracle.

To work with AWS DMS, either your source or target database must be in AWS. Currently, DMS doesn’t support migrating from an on-premises database to another on-premises database.

The AWS DMS migration 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.

To automate migration tasks, you use the AWS Command Line Interface (AWS CLI) to perform the database migration. If you are a first-time AWS CLI user, we recommend that you read the following documentation and get accustomed to how the CLI should be used and configured:

  1. Create an AWS Identity and Access Management (IAM) user.
  2. Set up required permissions for the IAM user.
  3. Set up the roles required to use AWS DMS.
  4. Set up the AWS CLI.

Problem/Use case
On many teams, version control is a usual part of business, but databases are left out in the cold. Perhaps the team exports a massive database dump or SQL file from time to time as the definition of the database. But deploying database changes across Dev, QA, and Production environments is still an issue, and the process isn’t reliable when synced up manually.

In other cases, database data might need to be updated for dashboards or weekly or daily reports that fetch the data from the database. But this process is often something that is static and performed as a one-time task. Also, the team often has to do the process following a particular schedule, for example before office hours or before a particular time when a report is published.

In many organizations, database migration is something that needs to be done like a backup job. Migration ideally should done only in off-business hours, to avoid the load that DMS can cause in the production environment.

Solution
Currently, DMS tasks cannot be scheduled using the DMS console. To schedule DMS tasks, we need to use the native tools present in the OS (Windows or Linux) that you use to access AWS resources. This blog post shows you how to do so.

In this blog post, the tools that automate the task execution have been run on an Amazon EC2 environment with Windows and Amazon Linux distribution. The native tools used for showing task execution in Linux are available in other distributions (RedHat, Ubuntu, Oracle Linux, and so on). Native tools for showing task execution also available for almost all the versions of Windows (2003 Server onwards and from XP till 10).

Moving forward, the process that this post describes should greatly simplify automated task deployments and modification scenarios. Following is a detailed description on how to automatically execute the DMS task or schedule it for execution in Linux and Windows.

Linux

  1. Install the AWS CLI on your Linux machine from Install the AWS Command Line Interface on Linux in the AWS documentation.

    You don’t need to explicitly configure your AWS CLI using the IAM roles if you plan just to use the CLI to automate execution of the DMS tasks. Explicit configuration isn’t necessary because you’ll be including the ACCESS_KEY and SECRET_KEY values in the bash script file that includes the CLI command. However, in general, it’s a good idea to configure the CLI to run commands by using aws configure.

  2. Create a bash script file, using the following command. 
    vi task.sh
  3. In the preceding script, include the following contents. 
    #!/bin/bash
export AWS_DEFAULT_REGION=us-east-1
export AWS_DEFAULT_OUTPUT=json
export AWS_ACCESS_KEY_ID=****************LEJA
export AWS_SECRET_ACCESS_KEY=****************qvNn
aws dms start-replication-task --replication-task-arn <task-arn> --start-replication-task-type reload-target

    In the file, include the SECRET_KEY and ACCESS_KEY values for the user that you configured the CLI with in the earlier step. Specify the REGION value based on the AWS DMS resources you have deployed. The JSON output format is optional.

  4. Save the file.
  5.  Grant permissions on the file. Following, I grant all the permissions to all the users on the file; this can be modified to fit the user’s environment. 
    sudo chmod 755 task.sh

  6. To schedule this bash script as a part of CRON job, run the following command. 
    sudo crontab -e
  7. After the preceding command runs, a file opens. For the purpose of this article, I include the following command. 
    6 6 * * * /home/ec2-user/task.sh

    So in CRON, each asterisk has the following meaning:

    ┌───────────── minute (0 - 59)
 │ ┌───────────── hour (0 - 23)
 │ │ ┌───────────── day of month (1 - 31)
 │ │ │ ┌───────────── month (1 - 12)
 │ │ │ │ ┌───────────── day of week (0 - 6) (Sunday to Saturday;
 │ │ │ │ │                                       7 is also Sunday on some systems)
 │ │ │ │ │
 * * * * *  command to execute

    In the preceding command, the bash script is scheduled to execute every day at 6:06 a.m. The following example runs a shell program called export_dump.sh at 23:45 (11:45 PM) every Saturday.

    45 23 * * 6 /home/oracle/scripts/export_dump.sh

    For more information on crontab, run the following command.

    man 5 crontab

  8. Save the file.
  9. Locate the logs related to the CRON job. Generally, they’re located in this directory: 
    /var/log/cron

    If the location doesn’t exist in the Linux distribution that you are using, then run the following command to find the logs:

    sudo grep -ic cron /var/log/* | grep -v :0


    Based on the output given by the preceding command, you can look for the logs in the indicated locations.

  10. To check if the script was executed or not, run the following command. 
    sudo grep filename /var/log/cron

    Replace filename with the name of the bash script that you created. In this case it was task. After running the preceding command, you can see the following output.

    If there are errors with the execution, you will see that in the logs. To get the last 30 lines of the log file, you can run the following command:

    sudo tail -30 /var/log/cron


    You can check this part of the log file for any messages related to your task.

Windows

    1. Install the AWS CLI on your Windows machine from Install the AWS Command Line Interface on Microsoft Windows in the AWS documentation.
    2. Configure the installed CLI by following the instructions from Configuring the AWS CLI in the AWS documentation.
    3. Once the installation and configuration is done, from the Start menu, open the Windows PowerShell ISE program.
    4. The program interface should look like the following.
    5. In the editor section, paste the following command. 
      aws dms start-replication-task --replication-task-arn <task-arn> --start-replication-task-type reload-target

    6. Save the file. For this post, the file is named ps1.
    7. From the Start menu, open Task Scheduler.
    8. From the right pane of the program, choose Create Task.
    9. On the Create Task window, fill in the following options:
      • Name: Provide a task name. For this post, we use dmstask.
      • Description (Optional): Comments about the task.
      • Security Options: Make sure that the user account used has enough privileges to run the task. Check the option Run whether user is Logged on or not. Selecting Run with Highest Privileges is optional. If you select this check box, Task Scheduler runs the task using an elevated privileges token rather than a least privileges (UAC) token. Only tasks that require elevated privileges to complete their actions should run with elevated privileges.
    10. Navigate to the Triggers tab and create a schedule for task execution, by choosing New.

      The preceding trigger makes sure that a PowerShell script executes every day from today onward at 3:20 p.m. EST. Choose OK when you’re done.
    11. Navigate to the Actions tab and choose New. Here, you can design how the script runs. Fill in the fields in the following manner.
      Action:                                      Start a program
      Program/script:                      Powershell.exe
      Add arguments (Optional): -ExecutionPolicy Bypass C:\task.ps1

      Choose OK once you’re done.
    12. On the Condition tab, make sure that you have the following options selected.
    13. When you choose OK, you are prompted to enter the password for the user account logged in to your Windows machine, or with the user account that you have set to run the task.
    14. Choose OK when you’re ready. At this point, the migration task is created.
    15. Choose the History tab for the task to see the job execution status and logs for the task.

Summary
We recommend regular monitoring of the CRON logs or Task Scheduler history to ensure that your migration tasks are executing as you want them to. Also, creating DMS event subscriptions related to task state changes should help you to get an accurate idea if the tasks execute as you expect and on schedule. Hopefully this blog post gives you a head start before embarking on your migration.

Good luck and happy migrating!

About the Author

Abhinav Singh is a database engineer in Database Migration Service at Amazon Web Services. He works with our customers to provide guidance and technical assistance on database migration projects, helping them improving the value of their solutions when using AWS.