Migrating data from on-premises databases using AWS DMS tasks - AWS Schema Conversion Tool

Migrating data from on-premises databases using AWS DMS tasks

You can use an AWS Database Migration Service (AWS DMS) task to migrate data from your on-premises relational database to the AWS Cloud. You can create, run, and monitor these AWS DMS tasks from AWS SCT.

Creating AWS DMS tasks from AWS SCT

Use the following procedure to create an AWS DMS task in AWS SCT.

To create a data migration task
  1. In AWS SCT, after you have converted your schema, choose one or more tables from the left panel of your project.

    For performance reasons, AWS recommends that you create multiple tasks for multiple tables.

  2. Open the context (right-click) menu for your database schema, which includes tables to migrate, and then choose Create DMS task.

    The Create DMS task dialog box opens.

  3. For Task name, enter a name for the task.

  4. For Replication instance, choose your replication instance from the AWS DMS console.

    Make sure that you configured the replication instance in the AWS DMS console for the same AWS profile that you use in AWS SCT. Also, make sure that the major version of the AWS DMS task matches the version of the replication instance.

  5. For Source endpoint, choose the source endpoint for data migration.

    If you don't have an existing source endpoint, choose the plus sign ( ) on the right side of the dialog box. For Endpoint name, enter the name of your endpoint and choose OK to create a new source endpoint.

  6. For Target endpoint, choose the target endpoint for data migration.

    If you don't have an existing target endpoint, choose the plus sign ( ) on the right side of the dialog box. For Endpoint name, enter the name of your endpoint and choose OK to create a new target endpoint.

  7. For Migration type, choose one of the following:

    • Migrate existing data – Migrate your data to the target database.

    • Migrate existing data and replicate ongoing changes – Migrate your data to the target database and set up ongoing replication or change data capture (CDC).

    • Replicate data changes only – Set up ongoing replication or CDC.

  8. For Target table preparation mode, choose one of the following:

    • Do nothing – Leave data in the target database tables.

    • Drop tables on target – Drop tables in the target database and create new tables before data migration.

    • Truncate – Remove data from the target database tables before data migration.

  9. For Include LOB columns in replication, choose one of the following:

    • Don't include LOB columns – Skip large object (LOB) columns during data migration.

    • Full LOB mode – Migrate LOB columns. For this option, enter the LOB chunk size in KB.

    • Limited LOB mode – Migrate LOB columns. For this option, enter the maximum size of LOB in KB.

  10. For Logging, choose Enable to see detailed information about your task. You can use the task log to debug problems.

  11. Choose Create to create the task.

Or you can create and configure your data migration task in the AWS DMS console. For more information, see Creating a task in the AWS Database Migration Service User Guide.

Managing AWS DMS tasks from AWS SCT

Use the following procedures to manage AWS DMS tasks in AWS SCT.

To manage data migration tasks
  1. On the View menu, choose Data migration view (standard DMS).

  2. On the Data migration view tab, review your data migration tasks. AWS SCT displays the status of your AWS DMS tasks in the top grid, and the status of subtasks in the bottom grid.

    Keep in mind that your data migration task can include local and remote subtasks. Local subtasks run on your local PC and extract data from the source endpoint. Remote subtasks upload data to Amazon S3 and insert data into the target endpoint. On the Data migration view tab, you can manage only remote subtasks.

  3. Choose a task in the top grid to view task details in the bottom grid.

  4. Choose Start for a task to start that task.

Data extraction using an AWS Snowball Edge device

The process of using AWS SCT and AWS Snowball Edge has several steps. The migration involves a local task, where AWS SCT uses a data extraction agent to move the data to the AWS Snowball Edge device, then an intermediate action where AWS copies the data from the AWS Snowball Edge device to your target database in the AWS cloud.

The sections following this overview provide a step-by-step guide to each of these tasks. The procedure assumes that you have AWS SCT installed and that you have configured and registered a data extraction agent on a dedicated machine.

Perform the following steps to migrate data from a local data store to an AWS data store using AWS Snowball Edge.

  1. Create an AWS Snowball Edge job using the AWS Snowball console.

  2. Unlock the AWS Snowball Edge device using the local, dedicated Linux machine.

  3. Create a new project in AWS SCT.

  4. Install the database driver for your source database on the dedicated machine where you installed the data extraction agent.

  5. Create and set permissions for the Amazon S3 bucket to use.

  6. Import an AWS Snowball job to your AWS SCT project.

  7. Create a data migration task in AWS SCT.

  8. Run and monitor the data migration task in AWS SCT.

Step-by-step procedures for migrating data using AWS SCT and AWS Snowball Edge

The following sections provide detailed information about the migration steps.

Step 1: Create an AWS Snowball Edge job

Create an AWS Snowball job by following the steps outlined in the section Creating an AWS Snowball Edge Job in the AWS Snowball Edge Developer Guide.

Step 2: Unlock the AWS Snowball Edge device

Run the commands that unlock and provide credentials to the Snowball Edge device from the machine where you installed the AWS DMS agent. By running these commands, you can be sure that the AWS DMS agent call connects to the AWS Snowball Edge device. For more information about unlocking the AWS Snowball Edge device, see Unlocking the Snowball Edge.

For example, the following command lists the Amazon S3 bucket used by the device.

aws s3 ls s3://<bucket-name> --profile <Snowball Edge profile> --endpoint http://<Snowball IP>:8080 --recursive

Step 3: Create a new AWS SCT project

Next, create a new AWS SCT project.

To create a new project in AWS SCT
  1. Start the AWS Schema Conversion Tool. On the File menu, choose New project. The New project dialog box appears.

  2. Enter a name for your project, which is stored locally on your computer.

  3. Enter the location for your local project file.

  4. Choose OK to create your AWS SCT project.

  5. Choose Add source to add a new source database to your AWS SCT project.

  6. Choose Add target to add a new target database in your AWS SCT project.

  7. Choose the source database schema in the left panel.

  8. In the right panel, specify the target database platform for the selected source schema.

  9. Choose Create mapping. This button becomes active after you choose the source database schema and the target database.

Step 4: Install the source database driver for the AWS DMS agent on the Linux computer

For the migration to succeed, the AWS DMS agent must be able to connect to the source database. To make this possible, you install the database driver for your source database. The required driver varies by database.

To restart the AWS DMS agent after database driver installation, change the working directory to <product_dir>/bin and use the steps listed following for each source database.

cd <product_dir>/bin ./arep.ctl stop ./arep.ctl start
To install on Oracle

Install Oracle Instant Client for Linux (x86-64) version 11.2.0.3.0 or higher.

In addition, if one isn't already included in your system, you need to create a symbolic link in the $ORACLE_HOME\lib directory. This link should be called libclntsh.so, and should point to a specific version of this file. For example, on an Oracle 12c client, use the following.

lrwxrwxrwx 1 oracle oracle 63 Oct 2 14:16 libclntsh.so -> /u01/app/oracle/home/lib/libclntsh.so.12.1

In addition, the LD_LIBRARY_PATH environment variable should be appended with the Oracle lib directory and added to the site_arep_login.sh script under the lib folder of the installation. Add this script if it doesn't exist.

vi cat <product dir>/bin/site_arep_login.sh
export ORACLE_HOME=/usr/lib/oracle/12.2/client64; export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
To install on Microsoft SQL Server

Install the Microsoft ODBC Driver.

Update the site_arep_login.sh with the following code.

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/msodbcsql/lib64/

Simba ODBC Driver

Install the Microsoft ODBC Driver.

Edit the simba.sqlserverodbc.ini file as follows.

DriverManagerEncoding=UTF-16 ODBCInstLib=libodbcinst.so
To install on SAP ASE

Install the SAP ASE ODBC 64-bit client.

If the installation directory is /opt/sap, update the site_arep_login.sh with the following.

export SYBASE_HOME=/opt/sap export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$SYBASE_HOME/ DataAccess64/ODBC/lib:$SYBASE_HOME/DataAccess/ODBC/ lib:$SYBASE_HOME/OCS-16_0/lib:$SYBASE_HOME/OCS-16_0/ lib3p64:$SYBASE_HOME/OCS-16_0/lib3p

Make sure that the /etc/odbcinst.ini file includes the following entries.

[Sybase] Driver=/opt/sap/DataAccess64/ODBC/lib/libsybdrvodb.so Description=Sybase ODBC driver
To install on MySQL

Install MySQL Connector/ODBC for Linux, version 5.2.6 or higher.

Make sure that the /etc/odbcinst.ini file contains an entry for MySQL, as in the following example.

[MySQL ODBC 5.2.6 Unicode Driver] Driver = /usr/lib64/libmyodbc5w.so UsageCount = 1
To install on PostgreSQL

Install postgresql94-9.4.4-1PGDG.<OS Version>.x86_64.rpm. This package contains the psql executable.

For example, the postgresql94-9.4.4-1PGDG.rhel7.x86_64.rpm package is required for Red Hat 7.

Install the ODBC driver postgresql94-odbc-09.03.0400-1PGDG.<OS version>.x86_64 or higher for Linux, where <OS version> is the OS of the agent machine.

For example, the postgresql94-odbc-09.03.0400-1PGDG.rhel7.x86_64 client is required for Red Hat 7.

Make sure that the /etc/odbcinst.ini file contains an entry for PostgreSQL, as in the following example.

[PostgreSQL] Description = PostgreSQL ODBC driver Driver = /usr/pgsql-9.4/lib/psqlodbc.so Setup = /usr/pgsql-9.4/lib/psqlodbcw.so Debug = 0 CommLog = 1 UsageCount = 2

Step 5: Configure AWS SCT to access the Amazon S3 bucket

For information on configuring an Amazon S3 bucket, see Buckets overview in the Amazon Simple Storage Service User Guide.

Step 6: Import an AWS Snowball job to your AWS SCT project

To connect your AWS SCT project with your AWS Snowball Edge device, import your AWS Snowball job.

To import your AWS Snowball job
  1. Open the Settings menu, and then choose Global settings. The Global settings dialog box appears.

  2. Choose AWS service profiles, and then choose Import job.

  3. Choose your AWS Snowball job.

  4. Enter your AWS Snowball IP. For more information, see Changing Your IP Address in the AWS Snowball User Guide.

  5. Enter your AWS Snowball Port. For more information, see Ports Required to Use AWS Services on an AWS Snowball Edge Device in the AWS Snowball Edge Developer Guide.

  6. Enter your AWS Snowball access key and AWS Snowball secret key. For more information, see Authorization and Access Control in AWS Snowball in the AWS Snowball User Guide.

  7. Choose Apply, and then choose OK.

Step 7: Creating a Local & AWS DMS task

Next, you create the end-to-end migration task. The task includes two subtasks. One subtask migrates data from the source database to the AWS Snowball Edge appliance. The other subtask takes the data that the appliance loads into an Amazon S3 bucket and migrates it to the target database.

To create the end-to-end migration task
  1. Start AWS SCT, choose View, and then choose Database Migration View (Local & DMS).

  2. On the View menu, choose Data Migration view (other). The Agents tab appears. If you have previously registered agents, AWS SCT displays them in a grid at the top of the tab.

  3. Choose Register.

    After you register an agent with an AWS SCT project, you can't register the same agent with a different project. If you're no longer using an agent in an AWS SCT project, you can unregister it. You can then register it with a different project.

  4. Choose DMS data agent, and then choose OK.

  5. Enter your information on the Connection tab of the dialog box:

    1. For Description, enter a description of the agent.

    2. For Host Name, enter the host name or IP address of the computer of the agent.

    3. For Port, enter the port number that the agent is listening on.

    4. For Password, enter the password for your agent.

    5. Choose Register to register the DMS agent with your AWS SCT project.

  6. On the View menu, choose Main view.

  7. In the left panel that displays the schema from your source database, select database objects to migrate. Open the context (right-click) menu for the schema, and then choose Create Local & DMS task.

  8. Add your task information.

    For this parameter Do this

    Task name

    Enter a name for the task.
    Agent Choose DMS Agent.
    Replication Instance Choose the AWS DMS replication instance that you want to use.
    Migration Type

    Choose the type of migration:

    • Choose Migrate existing data to migrate the contents of the chosen schema. This process is called a full load in AWS DMS.

    • Choose Migrate existing data and replicate ongoing changes to migrate the contents of the chosen schema and capture all ongoing changes to the database. This process is called full load and CDC in AWS DMS.

    • Choose Replicate data changes only to migrate ongoing changes to the database. This process is called CDC only in AWS DMS.

    Target table preparation mode

    Choose the preparation mode you want to use:

    • Truncate – Tables are truncated without affecting table metadata.

    • Drop tables on target – The tables are dropped and new tables are created in their place.

    • Do nothing – Data and metadata of the target tables aren't changed.

    IAM role Choose the predefined AWS Identity and Access Management (IAM) role that has permissions to access the Amazon S3 bucket and the target database. For more information about the permissions required to access an Amazon S3 bucket, see Prerequisites when using Amazon S3 as a source for AWS DMS.
    Compression format

    Choose whether to have uploaded files compressed:

    • GZIP – Files are compressed before loading. This is the default option.

    • No Compression – Extracts are faster, but take more space.

    Include LOB columns in replication

    Choose whether to include large object (LOB) columns in replication:

    • Don't include LOB columns – Skip LOB columns in replication. This is the default option.

    • Limited LOB mode – Include LOB columns in replication.

    Logging Choose Enable to have Amazon CloudWatch create logs for the migration. You incur charges for this service. For more information about CloudWatch, see How Amazon CloudWatch works in the Amazon CloudWatch User Guide.
    Description Enter a description of the task.
    S3 Bucket Enter the name of an Amazon S3 bucket configured for this AWS Snowball Edge job in the AWS Snowball Edge console
    Use AWS Snowball Edge Select this check box to use AWS Snowball Edge. If this box is not checked, then data is uploaded directly to the Amazon S3 bucket.
    Job Name Choose the AWS Snowball Edge job name that you created.
    Job S3 bucket Enter the Amazon S3 bucket where you want to upload data from the AWS Snowball Edge.
  9. Choose Create to create the task.

Step 8: Running and monitoring the AWS SCT task

You can start the Local & DMS Task when all connections to endpoints are successful. Make sure that your AWS DMS agent can connect to the source database, the staging Amazon S3 bucket, and the AWS Snowball device.

You can monitor the AWS DMS agent logs by choosing Show log. The log details include agent server (Agent log) and local running task (Task log) logs. Because the endpoint connectivity is done by the server (since the local task is not running and there are no task logs), connection issues are listed under the Agent log tab.

Permissions for running AWS DMS tasks

To create, run, and manage AWS DMS tasks in AWS SCT, make sure that you grant the following required permissions to your user:

  • dms:CreateEndpoint – to create an endpoint using the provided settings.

  • dms:CreateReplicationTask – to create a replication task using the specified parameters.

  • dms:DeleteReplicationTask – to delete the specified replication task.

  • dms:DescribeConnections – to see the the status of the connections that have been made between the replication instance and an endpoint.

  • dms:DescribeReplicationInstances – to see the information about replication instances for your account in the current region.

  • dms:DescribeReplicationTasks – to see the information about replication tasks for your account in the current region.

  • dms:DescribeEndpointTypes – to see the information about the type of available endpoints.

  • dms:DescribeEndpoints – to see the information about the endpoints for your account in the current region.

  • dms:DescribeTableStatistics – to see the table statistics on the database migration task, including table name, rows inserted, rows updated, and rows deleted.

  • dms:StartReplicationTask – to start the replication task.

  • dms:StopReplicationTask – to stop the replication task.

  • dms:TestConnection – to test the connection between the replication instance and the endpoint.

  • logs:DescribeLogGroups – to see the list of the the specified log groups.

  • logs:DescribeLogStreams – to see the list of the log streams for the specified log group.

  • logs:FilterLogEvents – to filter the log events from the specified log group.

  • logs:GetLogEvents – to see the log events from the specified log group.

The following code example shows you how to grant these permissions to your user.

{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "dms:CreateEndpoint", "dms:CreateReplicationTask", "dms:DeleteReplicationTask", "dms:DescribeConnections", "dms:DescribeReplicationInstances", "dms:DescribeReplicationTasks", "dms:DescribeEndpointTypes", "dms:DescribeEndpoints", "dms:DescribeTableStatistics", "dms:StartReplicationTask", "dms:StopReplicationTask", "dms:TestConnection", "logs:DescribeLogGroups", "logs:DescribeLogStreams", "logs:FilterLogEvents", "logs:GetLogEvents" ], "Resource": [ "*" ] } ] }