Migrating On-Premise Database to AWS RDS with AWS Database Migration Service

Migrating databases to the cloud is becoming increasingly popular as organizations look to reduce data center costs and leverage the flexibility of cloud infrastructure. One popular cloud database service is Amazon Relational Database Service (RDS), which makes it easy to set up, operate, and scale relational databases in the AWS cloud.

A handy tool for migrating on-premise databases to RDS is the AWS Database Migration Service (DMS). DMS allows you to migrate databases to AWS quickly and securely, without the need to manually provision AWS resources or write migration scripts.

In this post, we'll walk through how to use DMS to migrate an on-premise SQL Server database to an RDS MySQL instance.

Prerequisites

• An on-premise SQL Server database to migrate
• Access to AWS, with proper IAM permissions to interact with RDS and DMS
• An Amazon RDS MySQL instance created in your target VPC
• A user with full access on both databases.
• Read Limitations and plan accordingly.

DMS Limitations

AWS Database Migration Service does not automatically migrate everything from the source database. Here are some key limitations to be aware of:

• Indexes and Keys: Indexes, foreign keys, and other constraints are not copied over by DMS. These will need to be recreated manually on the target RDS database after migration.
• Stored Procedures and Functions: Custom stored procedures, functions, and triggers are not migrated. These will need to be moved over with a data dump export/import.
• Partial Data Migration: DMS migrates entire databases. Tables cannot be selectively migrated. For partial migrations, you will need to create empty target tables then use DMS to populate them.

To migrate database objects not handled by DMS: An alternative to recreating database objects manually is to migrate the schema ahead of the data replication.

To do this:

• Use mysqldump with the --no-data flag to export only the schema of the source database:

  mysqldump -u root -p --no-data --routines --triggers --events source_db > schema.sql
  

• Restore this schema backup to the target RDS database before starting the DMS task:

  mysql -u root -p target_db < schema.sql
  

• When creating the DMS task, under "Table Mapping," choose "Do nothing" for the table action. This will prevent DMS from trying to recreate the tables.
• Run the DMS migration task to populate the data into the pre-created RDS schema.

This allows the source schema to be fully migrated ahead of time while still using DMS for the data migration.

So in summary, use DMS to migrate your core data, then handle supplementary objects through dump/restore and manual recreation. Plan your migration architecture accordingly!

Steps

1. Launch a DMS Replication Instance

First, we need to launch an AWS DMS replication instance that will manage and perform the actual data migration. This is simply an EC2 instance running the DMS software.

In the AWS Management Console, go to the DMS dashboard and click "Create Replication Instance". Choose a name and description, and select "dms.t2.micro" for the instance class. Make sure the VPC matches the VPC where your target RDS instance resides. Use all other default settings and launch the instance.

It may take several minutes for the instance to fully initialize.

2. Create Source and Target Endpoints

Once the replication instance is available, we need to create source and target endpoints that point to the source SQL Server database and target RDS MySQL instance respectively.

In DMS, click "Endpoints" in the left sidebar. Click "Create Endpoint" and select "Source endpoint". Use the following settings:

• Endpoint identifier: src-sqlserver
• Source engine: Microsoft SQL Server
• Server name: [server name/IP of on-prem SQL Server]
• Port: 1433
• User name and password: [credentials to connect to SQL Server]

Repeat the same process to create a target endpoint for the RDS MySQL instance:

• Endpoint identifier: tgt-mysql
• Target engine: Amazon RDS
• Server name: [RDS MySQL endpoint]
• Port: 3306
• Database name: [name of target database in RDS]
• User name and password: [credentials to connect to RDS MySQL]

Test both endpoints to validate the connections.

3. Create and Run the Migration Task

With the endpoints configured, we can now create a "Migration Task" that will actually migrate the data.

In DMS, click "Database Migration Tasks" in the left sidebar. Click "Create Task". Use these settings:

• Task identifier: sqlserver-to-mysql
• Replication instance: [the replication instance created earlier]
• Source endpoint: src-sqlserver
• Target endpoint: tgt-mysql
• Migration type: Migrate existing data and replicate ongoing changes

In the Table Mappings section, RDS does not allow you to change the system-defined databases like mysql, performance_schema, sys, and so on. To skip the system-defined database and migrate only the databases you want to migrate, use the following in json editor modify the includes according to your database name you want to migrate to RDS:

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "522616160",
      "rule-name": "522616160",
      "object-locator": {
        "schema-name": "name-of-first-db-to-migrate-from-on-prem",
        "table-name": "%"
      },
      "rule-action": "include", # include means it will migrate this db and all tables
      "filters": []
    },
    {
      "rule-type": "selection",
      "rule-id": "522591176",
      "rule-name": "522591176",
      "object-locator": {
        "schema-name": "name-of-second-db-to-migrate-from-on-prem",
        "table-name": "%"
      },
      "rule-action": "include",
      "filters": []
    },
    {
      "rule-type": "selection",
      "rule-id": "522583572",
      "rule-name": "522583572",
      "object-locator": {
        "schema-name": "name-of-third-db-to-migrate-from-on-prem",
        "table-name": "%"
      },
      "rule-action": "include",
      "filters": []
    },
    {
      "rule-type": "selection",
      "rule-id": "512810701",
      "rule-name": "ExcludeSystemSchemas",
      "object-locator": {
        "schema-name": "mysql",
        "table-name": "%"
      },
      "rule-action": "exclude", #- it means it does not migrate mysql DB
      "filters": []
    },
    {
      "rule-type": "selection",
      "rule-id": "512810702",
      "rule-name": "ExcludePerformanceSchema",
      "object-locator

": {
        "schema-name": "performance_schema",
        "table-name": "%"
      },
      "rule-action": "exclude",
      "filters": []
    },
    {
      "rule-type": "selection",
      "rule-id": "512810703",
      "rule-name": "ExcludeSys",
      "object-locator": {
        "schema-name": "sys",
        "table-name": "%"
      },
      "rule-action": "exclude",
      "filters": []
    },
    {
      "rule-type": "selection",
      "rule-id": "512810704",
      "rule-name": "ExcludeInformationSchema",
      "object-locator": {
        "schema-name": "information_schema",
        "table-name": "%"
      },
      "rule-action": "exclude",
      "filters": []
    }
  ]
}

Finally, click "Create Task" to save the task details. Select the task from the list, and click "Start Task" to kick off the migration!

The task will now copy all data from the source SQL Server database to the target RDS MySQL instance. Check the task status and logs to monitor progress.

Once replication reaches the "Load complete" status, the full data migration is finished! You can then stop the replication task and direct your applications to connect to the new RDS database.

And that's it! With just a few clicks in DMS, you've successfully migrated your on-premise database to AWS RDS in the cloud. DMS handles all the heavy lifting of migration for you - no need for custom scripts or manual processes.