Create AWS CloudFormation templates for AWS DMS tasks using Microsoft Excel
This blog post talks about a tool that automates creation of AWS CloudFormation templates for AWS Database Migration Service (AWS DMS) tasks. If you are looking for how to create CloudFormation templates for your DMS tasks and don’t have knowledge of CloudFormation, read on.
Many times during database migration activities where AWS DMS is involved, you encounter the common task of creating AWS CloudFormation templates to provision DMS tasks. Creating AWS CloudFormation templates requires knowledge of writing CloudFormation templates in either JSON or YAML.
Good news! You don’t need to know how to write CloudFormation templates at all, and still you can create CloudFormation templates to provision AWS DMS tasks. All you need is some basic knowledge of Microsoft Excel and how to run a Python script using a terminal or command window.
In this post, we present a small command line tool written in Python. The tool takes a Microsoft Excel workbook having names of tables to be migrated, Amazon Resource Names (ARNs) of DMS endpoints, and DMS replication instances to be used as input. The tool generates CloudFormation templates for required DMS tasks as output. However, this tool doesn’t address creation of DMS endpoints and replication instances.
Prerequisites and assumptions
To use this tool, you need the following:
- Microsoft Excel 2016 or later installed on your machine.
- Python, version 2.7 or later. To install Python 2.7.15, go to The Python.org download page.
- The xlrd Python module. If you don’t have it installed already, install it using pip from the command prompt or terminal with this command:
pip install xlrd
- Make sure that both the DMS source and target endpoints and DMS replication instance are already created.
About the tool
All information about DMS tasks that need to be created should be incorporated in the Excel template. Before jumping into the details for the Excel template, let’s look at the list of items that are needed to create a DMS task.
The basic information needed to create a DMS task is this:
- 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
You provide this information to the tool as an Excel template.
About the Excel template
The Excel template to provide as input to the tool should include two worksheets named
DMS-Tags. The rest of each worksheet represents a DMS task whose CloudFormation template you plan to create.
DMS-Tasks sheet contains information about DMS tasks to be generated by the tool. To illustrate, if you plan to generate CloudFormation templates for two DMS tasks, there are two rows in the sheet. There is one row for each DMS task, which holds the information mentioned in each column, as described following.
For this sheet, provide details of DMS tasks, that is DMS endpoints, replication instance, and so on, in the designated columns of this sheet as described following.
|Name||Name of the DMS task. This name should be unique among DMS tasks; no duplicates are allowed.|
|Description||Description of the DMS task.|
|SourceEndPointARN||ARN of the DMS source endpoint.|
|TargetEndPointARN||ARN of the DMS target endpoint.|
|RIARN||ARN of the DMS replication instance.|
|CDCStartTime||Start time of change data capture. This attribute is mandatory if the task migration mode is CDC. In the case of FULL-LOAD mode, the tool ignores this attribute value.|
|Schema Name||Name of the source database schema that the tables for the DMS task indicated by the current row belong to.|
DMS-Tags sheet, you detail the AWS resource tags to attach to the DMS tasks whose CloudFormation templates the tool creates. As in the sample Excel template provided, the following table illustrates two tags,
Resource Owner and
EnvironmentType. The table also shows provided values for these tags, to be associated with all DMS tasks defined in the
|Tag name||Tag value|
DMS Tasks sheets
By now, you probably see that the Excel template’s two sheets,
DMS-Tags, contain metadata for DMS tasks. Specifically, they contain metadata for the tasks for which the tool is to create CloudFormation templates.
If there are N number of CloudFormation templates to create for DMS tasks, then the Excel should have N+2 worksheets. In other words, it needs one worksheet for each DMS task and two worksheets for metadata of all of the tasks, which are
The name of the worksheet for each DMS task should match the name mentioned in the name column in the
DMS Tasks sheet details
The name of each DMS task’s details sheet should match the name mentioned in the name column in the
All tables to be part of the DMS task should be listed in this sheet. This sheet has two columns (apart from
Sno column, kept for future development purposes),
Table Name and
Excluded Columns, described following.
In some situations, certain columns should not be migrated. Note those columns as comma-separated column names in the
Excluded Columns column in the sheet.
|Column name||Column value|
|Table Name||Name of the table to be part of the DMS task.|
|Excluded Columns||Columns names of the table whose columns should be excluded by DMS during either full-load or CDC mode. If nothing is mentioned here, all columns are considered for data migration. Column names should be separated by commas.|
Generating DMS CloudFormation templates
To generate CloudFormation templates for your DMS tasks, download the tool from this Amazon GitHub Repository in the form of archive file (.zip), extract the downloaded archive file to a folder, and take the following steps:
- Make sure that you have an installed version of Python 2.7 or later.
- Make sure that you have an installed version of the xlrd Python module. If not, install it using pip with this command:
pip install xlrd
- Open a terminal window in Mac OS or a command window in Microsoft Windows, and navigate to the folder where the tool is extracted.
- Run the tool.
The downloaded tool ships with a sample Excel template that generates two DMS tasks’ CloudFormation templates,
Running the tool
When you run the tool, only two arguments are mandatory. Those are
path and type, whose details are mentioned following.
|Argument name||Argument value|
|path||Location of the Excel template that contains DMS task details for which CloudFormation templates need to be generated.|
This argument accepts three different values:
Use the following command with to generate DMS task templates, providing the path to the Excel template:
Following is an example of tool execution.
The preceding example illustrates running the tool with the provided Excel template, and CloudFormation templates for two DMS tasks are generated. The tool places them in a folder named
output, which is one of the subfolders where the main tool’s archive is extracted.
The tool has the following limitations:
- The names of schema, tables, and their columns are transformed into lowercase at the destination endpoint.
- Currently, the tool supports only one schema for each DMS task.
We found that during the migration of one customer database having 500-plus tables, shuffling tables from one DMS task to the other became a common exercise. This proved true because the number of partitions in a few tables grew significantly in the production/staging environment compared to the dev environment. From the requirement to shuffle tasks, the idea of creating this tool was born.
This tool drastically reduces the amount of time required to create CloudFormation templates for DMS tasks.
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.