Automate AWS CloudFormation template creation for AWS DMS tasks

Expanding on the earlier post, Create AWS CloudFormation templates for AWS DMS tasks using Microsoft Excel, this post highlights an enhanced feature of the same tool that can speed database migration.

To demonstrate this feature, we present a small command line tool written in Python. The tool takes a CSV file containing the names of tables to be migrated, Amazon Resource Names (ARNs) of AWS DMS endpoints, and DMS replication instances for use as input. After successful execution, the tool generates an AWS CloudFormation template for the required DMS tasks as output. However, this tool doesn’t address the creation of DMS endpoints and replication instances.

Prerequisites

To use this tool, you need the following resources:

Python version 2.7 or later. To install Python 2.7.15, see the org download page.
The DMS source and target endpoints, and a DMS replication instance.

Create the CSV template

First, incorporate all information about the DMS tasks to create in a CSV file. Here’s a list of the required items to create any DMS task:

DMS task name
Source endpoint
Target endpoint
Replication instance to be used
Schema name of tables to be migrated
Names of tables to be migrated
Columns to be excluded
Unique key value ranges for the Column filter (if you need any data filtrations at the table level)

Provide this information to the tool as a CSV template with the following columns. This is not an exhaustive list of the columns that can be included.

selectionType—Include/exclude value of a table for the task under the selection rule.
taskName—Name of the task. This value does not need to be unique.
taskDescription—Description of the DMS task.
schemaName—Name of the source database schema to which the tables for the DMS task indicated by the current row belong.
tableName—Tables to include or exclude. Use % to include all tables.
taskPrepMode—Action to be taken by DMS at the target database before the task begins. Values include:
- DO_NOTHING
- DROP_AND_CREATE
- TRUNCATE_BEFORE_LOAD
sourceARN—Source endpoint ARN.
targetARN—Target endpoint ARN.
repARN—Replication Instance ARN.

For more information, see Creating a Task and Using Table Mapping to Specify Task Settings. To learn more about the needed columns, see the DMS task CloudFormation template.

Settings covered by this tool

This tool covers the following DMS task settings and respective CSV file column names:

Settings	Columns	Reference
Enable Validation	validation	For more information, see Data Validation Task Settings.
Enable Lob	lobMode, fullLob, and chunkSize	For more information, see Target Metadata Task Settings.
Column Filter	filterColumn, filterCondition, startValue, and endValue	For more information, see Using Table Mapping to Specify Task Settings.
CDC	cdcStartTime and batchApplyEnabled	For more information, see Change Processing Tuning Settings.
Change Processing DDL Changes	changeProcessingDDLHandlingPolicy	For more information, see Task Settings for Change Processing DDL Handling.
Control Table Settings	controlSchema, enableHistoryTable, enableSuspendTable, and enableStatusTable	For more information, see Control Table Task Settings.
Tuning Settings	maxSubTasks	For more information, see Target Metadata Task Settings.

Generate the CloudFormation templates

To generate CloudFormation templates for your DMS tasks, download the tool from the DMS task CloudFormation template repository as an archive (.zip) file, extract the downloaded file to a folder, and take the following steps:

Make sure that you have an installed version of Python 2.7 or later.
Open a terminal window in macOS or a command window in Microsoft Windows, and navigate to the extracted tool folder.
Run the tool.

The downloaded tool ships with a sample CSV template that generates seven DMS tasks’ CloudFormation templates, called dms-input.csv.

Run the tool

When you run the tool, only two arguments are mandatory:

path—Location of the Excel template containing the DMS task details for which to generate CloudFormation templates.

type—This argument accepts one of the following values:

CDC—If the DMS tasks to create are for change data capture mode only.
Full-load—If the DMS tasks to create are for full-load mode only.
Full-load-and-CDC—If the DMS tasks to create are for full-load followed by CDC.

Use the following command to generate DMS task templates, providing the path to the CSV template:

python cf-generator.py --path [PATH_OF_THE_ CSV_FILE_TEMPLATE] --type [cdc | full-load | full-load-and-cdc]

Find the sample dms-input.json at ./input directory.

The following is an example of tool execution:

user_name$ python cf-generator.py --path input/dms-input.csv --type full-load
Created Template for dms-input : <path>/output/dms-input.json
user_name$

After you run the sample command with the provided dms-input.csv, you should see a response output in the terminal resembling the following:

The preceding example illustrates how to run the tool with the provided sample CSV template, generating CloudFormation templates for seven DMS tasks. The tool places the generated CloudFormation templates in a folder called “output”.

The tool has the following limitation: The names of schema, tables, and their columns convert into lowercase at the destination endpoint.

Summary

The tool discussed in this post has increased the productivity of multiple database migration teams by cutting down the time consumed creating CloudFormation templates for DMS tasks. We hope the tool is useful for your database migration using DMS.

About the Authors

Ismail Shaik is a consultant with AWS Professional Services. He works with AWS DMS, SCT, Aurora PostgreSQL, and Microsoft Workloads related AWS services to bring the best possible experience to their customers.

Venkata Naveen Koppula is an associate consultant with AWS Professional Services. He works with AWS DMS, SCT, Aurora PostgreSQL to bring the best possible experience to their customers.

AWS Database Blog