Share This Post

AWS Cloud Technology

How to Migrate an On-Premise Database to AWS

How to Migrate an On-Premise Database to AWS

Introduction

Every day more organizations are discovering the benefits of cloud services and are therefore looking to migrate their on-Premise databases to the cloud. Amazon Web Services (AWS) is one of the leading cloud services available today, and it is reasonable that most organizations will want to adopt it as their cloud service. Many a time, people complain of the tediousness of migrating to AWS, but we have decided to help in that regard. We will carefully outline the steps required in migrating an on-premise database to AWS.

Step 1: Prepare the Oracle Source Database

Migrating data from an Oracle database using AWS DMS requires you make some preparations, and we also advise you take a few other steps as good practice.

  • AWS DMS account – Ensure that you create a different account to move your data. This new account should at least have the minimum set of requirements for transferring your data. However, if your interest is only limited to testing AWS DMS on a non-production database, then any DBA account will suffice.
  • Supplemental logging – Supplemental logging has to be enabled to use DMS. You will need to issue a command to enable supplemental logging. The command is as follows.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

Also, you are required to set key-level logging for every table that is being migrated. AWS DMS then adds the supplemental logging if the following parameter is included.

addSupplementalLogging=Y

  • Source database – for your data to be migrated successfully, the AWS DMS server has to access the source database. Ensure that any firewall you have put in place gives the AWS DMS permission to replicate the server.

Step 2: Prepare Your Aurora MySQL Target Database

If you are ready to launch the Aurora MySQL, here are some things you have to consider:

  • Locate the replication instance and the Aurora MySQL instance in the same Availability Zone and VPC.
  • Create a different account with privileges for moving your data. The AWS DMS account would require the following privileges on the databases that data is being moved to.

ALTER, CREATE, DROP, INDEX, INSERT, UPDATE, DELETE, SELECT

Ensure that the AWS DMS has total access to the awsdms_control database, which contains information needed by the AWS DMS for the migration. Run this command to give the access.

ALL PRIVILEGES ON awsdms_control.* TO ‘dms_user.’

Step 3: Launch a Replication Instance

The replication instance is where the AWS DMS service connects the target database and your source from. You have to consider the following when launching the replication instance:

  • Locate the replication instance in the same Availability Zone and VPC as the target database, which is Aurora MySQL in this case.
  • If any of your target or source databases is not inside the VPC where the replication server has been launched, the replication server should be accessible publicly.
  • You should know that AWS DMS consumes a reasonable amount of CPU and memory, although it is pretty easy to scale up anytime.
  • The provided storage should be adequate for most migrations.\

Step 4: Create a Source Endpoint

A source point has to be created for AWS DMS to get easy access to your Oracle source database. This source endpoint determines all the information that is required for any sort of connection between the source database and AWS DMS from the replication server. Here are some basic requirements for this source endpoint.

  • The source endpoint you create has to be easily accessible from the server. You will need to edit the rules on your firewall to put the replication server on a whitelist. The IP address of the server is available in the AWS DMS Management Console.
  • Supplemental logging has to be enabled for changes to be captured by AWS DMS. If you want the supplemental logging to be enabled for you by AWS DMS, just add the command below to the connection attributes for the Oracle source endpoint.

addSupplementalLogging=Y

Step 5: Create a Target Endpoint

For your Aurora MySQL database to be easily accessible by the AWS DMS, a target endpoint has to be created. This endpoint defines the information that is required for the Aurora MySQL database and the DMS to be connected.

  • There should be easy access from the replication server to the target endpoint. You may need to make some changes on the security groups.
  • It the database has been created beforehand, it’s advisable that foreign key checks are disabled. Add the command below to the extra connection attributes.

Initstmt=SET FOREIGN_KEY_CHECKS=0

Step 6: Create and Run a Migration Task

The AWS DMS is instructed on the specifics of your migration by a migration task. To create this task, set the migration parameters as indicated below.

Replication and endpoints server – Select the replication server and endpoints created above.

Type of Migration – Select “migrate existing data and replication ongoing changes.” The AWS DMS then loads the source data while capturing any change made to the data. After loading, AWS DMS applies any other required change and retains the target and source databases in sync. This is done until the task ends.

Target table preparation mode – Choose “drop tables on target” if the AWS DMS is creating your tables. If not, choose “truncate”.

LOB parameters – If you are a newbie using AWS DMS, you should probably select “include LOB columns in replication, Limited LOB mode,” and then set the maximum LOB size to 16K.

Enable logging – Ensure that logging is always enabled to make debugging of migration issues easy.

Table mappings – If you want to migrate to Aurora MySQL from Oracle, it is advised that the column, table, and schema names are in lowercase. To convert them to lowercase, a custom table mapping should be created. Here is an example of how to migrate the DMS_SAMPLE and convert the column, schema, and table names to lowercase.

{

  “rules”: [

    {

      “rule-type”: “selection”,

      “rule-id”: “1”,

      “rule-name”: “1”,

      “object-locator”: {

        “schema-name”: “DMS_SAMPLE”,

        “table-name”: “%”

      },

      “rule-action”: “include”

    },

   {

      “rule-type”: “transformation”,

      “rule-id”: “6”,

      “rule-name”: “6”,

      “rule-action”: “convert-lowercase”,

      “rule-target”: “schema”,

      “object-locator”: {

        “schema-name”: “%”

      }

    },

    {

      “rule-type”: “transformation”,

      “rule-id”: “7”,

      “rule-name”: “7”,

      “rule-action”: “convert-lowercase”,

      “rule-target”: “table”,

      “object-locator”: {

        “schema-name”: “%”,

        “table-name”: “%”

      }

    },

    {

      “rule-type”: “transformation”,

      “rule-id”: “8”,

      “rule-name”: “8”,

      “rule-action”: “convert-lowercase”,

      “rule-target”: “column”,

      “object-locator”: {

        “schema-name”: “%”,

        “table-name”: “%”,

        “column-name”: “%”

      }

    }

  ]

}   

Conclusion

Well, now you have a step-by-step guide to migrating an on-premise database to AWS. Cloud computing is growing steadily, and smart businesses are joining the trend. With this tutorial, you don’t have an excuse for not taking advantage of this rapidly growing trend.

Leave a Reply

Translate »
error: Alert: Sorry... Content is protected!