Skip to content

Instantly share code, notes, and snippets.

@nicolasdao

nicolasdao/aws_dms_guide.md Secret

Last active February 24, 2022 01:16
Show Gist options
  • Save nicolasdao/36630bac9aac704712dba8efc1d82f5e to your computer and use it in GitHub Desktop.
Save nicolasdao/36630bac9aac704712dba8efc1d82f5e to your computer and use it in GitHub Desktop.
Download ZIP
AWS DMS Guide. Keywords: aws dms data migration
Raw
aws_dms_guide.md

AWS DMS GUIDE

Table of contents

Overview

DMS description

Database Migration Service is an AWS service that can connect endpoints (source endpoint and target endpoint) to:

  • Migrate data which is referred to as Full load.
  • Sync data storage (e.g., AWS Aurora with AWS ElasticSearch) which is referred to as CDC (Change Data Capture).
  • Full load is supported by most DB engines (even MSSQL web edition), but CDC is not (e.g.,MSSQL Web Edition). That's because CDC requires the DB engine itself to support CDC (more about this in the Enabling Change Data Capture in your source section).
  • To migrate data, DMS requires that:
    • At least one endpoint (source or target) is hosted on AWS.
    • The DB engine of the source and target are supported. This means that DMS cannot migrate data from on-prem to another on-prem.
  • DMS can migrate data from different DB engines, as long as they are supported.

How does DMS work?

SUPER GOTCHA: DMS reserves the CIDR block 192.168.0.0/24 (I have no clue why but this is detailed at https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Troubleshooting.html#CHAP_Troubleshooting.General.ReservedIP). For this reason, do NOT create a host your endpoints using IPs in that CIDR block.

  • There are 3 actors in a data migration process involving DMS:
    1. Source: The DB that holds the data to be migrated/replicated.
    2. Replication servers: EC2 instances (supports multi-AZs deployment for high-availability) that host the DMS processes calles Replication Tasks.
    3. Target: The DB that receives the migrated data.
  • DMS can replicate the data using two processes:
    • Full load: Moving a data from the source to the target.
    • CDC (Change Data Capture): Capturing real-time changes via the native source API transation logs and applying those changes to the target. Those two processes can be combined during a data migration. It usually works in 3 steps:
      1. Table Full load and CDC start together. However, the CDC does not apply the changes to the target yet (logic as the full load is not over yet). Instead, the CDC caches those transaction on the replication server.
      2. When the full load is finished, the cached CDC are applied.
      3. The cached CDC have been applied, the CDC keeps going to keep the source and target in sync.
  • The 3 steps process described above explains the EC2 instances must be choosed carefully. The CDC process is the one that will predominantly determine how big and fast your EC2 must be. Indeed, the CDC keeps changes in memory. If it runs out of memory, it will use the disk, which will create synchronization delays. If the DMS console show a CDCLatencyTarget higher than the CDCLatencySource, this probably means that the target cannot ingest the DMS writes fast enough.
  • LOBs (Large Object Binary) can seriously degrade the DMS migration/sync performance. DMS supports 2 modes for LOBs:
    • Full LOB mode: All LOB are transferred sequentially, which can create problems if some are really big.
    • Limite LOB mode: Allows to define a Max LOB Size and will dramatically improve performances by truncating LOBs that exceed that threshold.
  • DMS does not manage schema migration. You either have to manage that yourself, or use tools such as AWS SCT (Schema Conversion Tool)

Compatibility

MSSQL

These notes applies to DMS v3.4.3 (March 2021)

Though the official AWS documentation states that MSSQL Web Edition is not supported if it is hosted on RDS (it is if hosted on EC2), a PoC invalidated this. In reality, MSSQL Web Edition (tested with 2012 version) supports the full load feature, but not the CDC.

To support CDC, MSSQL must be upgraded to Enterprise or Developer Edition. The doc mentions 2016-2019, but the PoC still worked for 2012.

DMS pricing

The actual DMS service is free, but its hosting is not. DMS must be hosted on EC2 instances which you pay for.

  • EC2: You pay based on the instance size: https://aws.amazon.com/dms/pricing/.
  • Data transfer:
    • Free:
      • Getting data in AWS.
    • Not free:
      • Getting data outside of AWS.
      • Moving data in AWS in different AZs or regions.

General guidance

Be aware of the DB engine conventions

Because every DB engine uses different naming conventions or default settings, doing a one to one migration may cause unexpected side-effects. For example, MSSQL supports any type of letter case table and field names, and uses dbo for its default schema, while PostgreSQL only supports lowercase table and field names and uses public for its default schema. Migrating an MSSQL table with no transformation to PostgreSQL may work, but the PostgreSQL queries will be more verbose. Instead of writting:

SELECT age, gender FROM my_table

You will have to write:

SELECT "Age", "Gender" FROM dbo."MyTable"

MSSQL conventions

  • Default schema: dbo
  • Letter case: any

PostgreSQL conventions

  • Default schema: public
  • Letter case: lowercase

MySQL conventions

  • Default schema: mydb
  • Letter case: lowercase

Getting started

Prerequisites

Enabling Change Data Capture in your source

To use AWS DMS' CDC feature, the source DB is required to support CDC, which is not always the case. To check whether a DB engine supports CDC, logon to your DB engine and try to toggle this feature using the table below:

DB Engine Supported version CDC process name Command type
MSSQL Enterprise and Developer MS-CDC SQL
MySQL All binlogs Config file
PostgreSQL All test_decoding plugin Plugin

Except MSSQL, setting up CDC on an RDS database is trivial. It is automatically toggled when the Backup retention period is set to something greater than 0 and change logs are retained for at least 24 hours.

Ref: Creating tasks for ongoing replication using AWS DMS

Enabling MS-CDC for MSSQL Enterprise Edition

WARNING: Restoring an MSSQL DB from a snapshot reset the metadata, which means that CDC will be disabled. If you're restoring from a snapshot that had CDC enabled, you'll have to re-enable it again by following the steps below.

  1. Enable CDC(1) on the DB:
exec msdb.dbo.rds_cdc_enable_db 'your_db_name'
  1. Enable CDC on the specific table:
EXECUTE sys.sp_cdc_enable_table @source_schema = N'SchemaName', @source_name =N'TableName', @role_name = NULL;
  1. Increase the retention period for the transactions in the T-Log(2).
EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@pollinginterval = 3599;

To test whether CDC is enabled on MSSQL, run the following statement:

SELECT name, is_cdc_enabled FROM sys.databases

IMPORTANT: If you stop the task and resume the task after one hour, it might fail because the T-Log has been truncated and AWS DMS doesn't have the required log sequence numbers (LSNs) because of the issue mentioned previously. To prevent this, follow these steps:

  1. Stop the capture job:
use [DBName]
exec sys.sp_cdc_stop_job;
  1. Stop the AWS DMS task, and wait for all remaining activities to stop.
  2. Resume the DMS task and wait for it to sync by monitoring the AWS DMS task’s source latency.
  3. Restart the capture job:
use [DBName]
exec sys.sp_cdc_start_job;

(1) The standard MSSQL stored-procedure to enable MS-CDC is sys.sp_cdc_enable_db, but it requires sysadmin privilege (sa), which is not accessible when MSSQL is hosted on RDS. That's why AWS installs the rds_cdc_enable_db stored-procedures.

(2) When configuring ongoing replication for a SQL Server instance, it's a best practice to set the pollinginterval to retain changes for one day (86400 seconds). However, there is a known issue with some versions of SQL Server, so if the value of pollinginterval is set to more than 3599 seconds, then the value resets to the default, which is five seconds. When this happens, the T-Log entries are purged before AWS DMS reads them. To see which versions are still impacted by this issue, see the Microsoft documentation for Incorrect results occur when you convert "pollinginterval" parameter from seconds to hours in sys.sp_cdc_scan in SQL Server.

Ref: How can I use AWS DMS to migrate from an Amazon RDS DB instance that is running SQL Server?

Troubleshooting

MSSQL: Error AlwaysOn BACKUP-ed data is not available

This is a bug in AWS DMS v3.4.3. Switch to 3.4.2.

Ref: https://dba.stackexchange.com/questions/282508/aws-dms-task-getting-failed-after-completion-with-error-as-alwayson-backup-ed-da

FAQ

How to enable CloudWatch logging?

This is not set up by default, and can greatly help diagnozing errors. To set it up:

  1. Make sure your task is stopped or in failed mode.
  2. Create an IAM role allowing DMS to access CloudWatch:
    1. Browse to IAM, select Roles in the menu and click on the Create role button.
    2. Choose AWS service and DMS for the service, the click Next.
    3. Browse the AmazonDMSCloudWatchLogsRole role. Select it and click Next.
    4. Enter this exact role name: dms-cloudwatch-logs-role then save.
  3. Click on the Database migration tasks menu and select your task.
  4. Select the CloudWatch metrics tab, and click on the Modify task logging button.
  5. Tick the Enable CloudWatch logs checkbox and click Save.

When that's done, a new View logs button appears in the top-right corner of the task dashboard.

Annex

Mapping rules example

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "1",
      "object-locator": {
        "schema-name": "dbo",
        "table-name": "GWWeatherData"
      },
      "rule-action": "include",
      "filters": [
        {
          "filter-type": "source",
          "column-name": "WeatherDataID",
          "filter-conditions": [
            {
              "filter-operator": "ste",
              "value": "2736513"
            }
          ]
        }
      ]
    },
    {
      "rule-type": "transformation",
      "rule-id": "2",
      "rule-name": "2",
      "rule-target": "schema",
      "object-locator": {
        "schema-name": "dbo"
      },
      "rule-action": "rename",
      "value": "public",
      "old-value": null
    },
    {
      "rule-type": "transformation",
      "rule-id": "3",
      "rule-name": "3",
      "rule-target": "table",
      "object-locator": {
        "schema-name": "dbo",
        "table-name": "GWWeatherData"
      },
      "rule-action": "rename",
      "value": "conditions",
      "old-value": null
    },
    {
      "rule-type": "transformation",
      "rule-id": "4",
      "rule-name": "4",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "dbo",
        "table-name": "GWWeatherData",
        "column-name": "datetime"
      },
      "rule-action": "rename",
      "value": "time",
      "old-value": null
    },
    {
      "rule-type": "transformation",
      "rule-id": "5",
      "rule-name": "5",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "dbo",
        "table-name": "GWWeatherData",
        "column-name": "LocationID"
      },
      "rule-action": "rename",
      "value": "location",
      "old-value": null
    },
    {
      "rule-type": "transformation",
      "rule-id": "6",
      "rule-name": "6",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "dbo",
        "table-name": "GWWeatherData",
        "column-name": "AirTemp"
      },
      "rule-action": "rename",
      "value": "temperature",
      "old-value": null
    },
    {
      "rule-type": "transformation",
      "rule-id": "7",
      "rule-name": "7",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "dbo",
        "table-name": "GWWeatherData",
        "column-name": "WeatherDataID"
      },
      "rule-action": "remove-column",
      "value": null,
      "old-value": null
    }
  ]
}

References

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment