Generate an AWS SCT multiserver assessment report using the AWS SCT CLI

The AWS Schema Conversion Tool (AWS SCT) makes heterogeneous database migrations predictable by automatically converting the source database schema and a majority of the database code objects, including views, stored procedures, and functions, to a format compatible with the target database.

An important part of the AWS SCT is the assessment reports to help you convert your schema. Prior to AWS SCT release 647, you had to generate a database assessment report for one schema a time. With the multiserver assessment report, you can evaluate your database server fleet to generate the aggregated report. This gives you the level of effort needed to migrate to your AWS target database, and enables you to build a database migration roadmap.

A multiserver assessment report evaluates multiple servers based on input that you provide for each schema definition that you want to assess. Your schema definition contains database server connection parameters and the full name of each schema. After assessing each schema, the AWS SCT produces an aggregated assessment report for database migration across your multiple servers. This report shows the estimated complexity for each possible migration target.

In this post, we demonstrate how to configure an AWS SCT multiserver assessment report with the AWS SCT CLI, which includes configuring database drivers and memory parameters, constructing the input.csv connection file, and generating and viewing the assessment report.

Solution overview

Our use case consists of Oracle and SQL Server databases as the source. Our target databases are Amazon Relational Database (Amazon RDS) for PostgreSQL, Amazon Aurora MySQL-Compatible Edition, Amazon Aurora PostgreSQL-Compatible Edition, and Amazon Redshift.

At a high level, the solution steps are as follows:

Download and install the latest version of the AWS SCT.
Download the required database drivers.
Create a connection file for the multiserver assessment report with the source and target databases and schemas.
Create the .scts file, run the assessment project, and generate the assessment report.

The following diagram illustrates the architecture.

Download and install the latest version of AWS SCT

First, you must download and install the latest AWS SCT release either on your local computer, a server in your data center, or an Amazon Elastic Compute Cloud (Amazon EC2) instance. For step-by-step instructions, refer to Installing, verifying, and updating AWS SCT.

In our use case, we install AWS SCT on Amazon Linux hosted on Amazon EC2.

Download database drivers

For the AWS SCT to work correctly, install the JDBC drivers for your source database engines. For the multiserver assessment report, target database drivers aren’t needed.

Create a connection file for the multiserver assessment report with the source, target, and schemas

To provide connection parameters as input for the multiserver assessment report, use a CSV file as shown in the following example. In this post, we are using Oracle and MySQL as the source databases and Amazon RDS for PostgreSQL, Aurora MySQL, Aurora PostgreSQL, and Amazon Redshift as potential targets.

Name,Description,Secret Manager Key,Server IP,Port,Service Name,Database name,Source Engine,Schema Names,Use Windows Authentication,Login,Password,Use SSL,Trust store,Key store,SSL authentication,Target Engines
Sales,,,10.0.0.1,1521,,oradb,ORACLE,GAMEDAY,,sct_user,******,,,,,POSTGRESQL;AURORA_MYSQL
Marketing,,,10.0.0.2,1433,,AdventureWorks2014,MSSQL,AdventureWorks2014.dbo,,sct-user,*****,,,,,AURORA_MYSQL;AURORA_POSTGRESQL;REDSHIFT

Always check Creating a multiserver assessment report for database migration for the latest input.csv file format.

Create the .scts file, run the assessment project, and generate the assessment report

The AWS SCT CLI needs a file with the extension .scts, which is used to feed in all the input parameters for running the assessment.

After you download the drivers, you give the location of the driver within the .scts file. For more information, see Storing driver paths in the global settings.

SetGlobalSettings
   -settings: '[{
                       "name":"mssql_driver_file",
                       "value":"/home/ec2-user/msa/drivers/mssql-jdbc-9.4.0.jre11.jar"
                   },
                   {
                       "name":"oracle_driver_file",
                       "value":"/home/ec2-user/msa/drivers/ojdbc8.jar"
                   }
                   ]'
/

To generate the multiserver assessment report, use the CreateAggregatedReport call with the following arguments:

directory – The location where the aggregate report file structure is created. This parameter is mandatory.
projectName – The name of the top-level directory in the specified location. This parameter is mandatory.
connectionsFile — The CSV file with database connection parameters. This parameter is mandatory.
createProjects — Creates migration projects after generating the assessment report. This is an optional parameter. The supported values are false and true. The default value is false.
addMappingRules – Adds mapping rules to each project and saves the source database metadata in the project. addMappingRules depends on createProjects. If createProjects is missing or false, addMappingRules is ignored. This is an optional parameter. Supported values are false and true. The default value is false.

See the following code:

CreateAggregatedReport
   -directory: '/home/ec2-user/msa'
   -projectName: 'my_project’
   -connectionsFile: '/home/ec2-user/msa/input.csv'
   -createProjects: 'true'
   -addMappingRules: 'true'
/

Sample msa-cli.scts

With msa-cli.scts and input.csv as input parameters, generate the multiserver assessment report with the following AWS SCT CLI call, based on your OS.

For Linux, use the following code:

/opt/aws-schema-conversion-tool/lib/runtime/bin/java -jar /opt/aws-schema-conversion-tool/lib/app/AWSSchemaConversionToolBatch.jar -type scts -script "/home/ec2-user/msa/msa-cli.scts"

For Windows, use the following code:

C:\Program Files\AWS Schema Conversion Tool\runtime\bin\java.exe -jar "C:\Program

Files\AWS Schema Conversion Tool\app\AWSSchemaConversionToolBatch.jar" -type scts

-script "C:\Users\Documents\MSA\msa-cli.scts"

Note the following:

Verify the drivers’ path in the .scts file
The multiserver assessment report can only run with AWS SCT build 647 and above
If your source is other than what you see in the preceding sample for input.csv, check for keywords to be used for Source_Engine for other engines

Let’s look at an example of the output files.

The following screenshot shows our main directory.

The following screenshot shows our output for Oracle.

The following screenshot shows our output for SQL Server.

The following screenshot shows our projects.

AWS SCT creates a dedicated subdirectory for each of the mentioned targets, which have the respective AWS SCT analysis files including the executive summary report in PDF format.

Best practices while using the AWS SCT CLI

When implementing this solution, make sure to use the SSL connection while connecting to the source and target database. You must configure the SSL encryption on your source and target databases as a prerequisite.

Additionally, either remove the .scts input file after you run the assessment or anonymize the database credentials from the .scts input file.

Summary

With AWS SCT multi-server assessment, you can manage migrations that include multiple sources and targets in a single project. In this post, we walked through how to configure drivers, prepare a connection file, create the AWS SCT project for the multiserver assessment report, and view the report using the AWS SCT CLI.

You can also use the interactive mode to generate an assessment report for a fleet of database servers using the AWS SCT multiserver assessor.

About the Authors

Dwarka Rao is a Database Migration Specialist Solutions Architect with the Amazon Database Migration Accelerator team at Amazon Web Services. He helps customers and partners build highly scalable, available, and secure databases solutions on cloud migration projects from on-premises to AWS.

Narendra Tawar is a Database Migration Specialist Solutions Architect on the Amazon Database Migration Accelerator team, with a strong background in database migration. He has carried out many large-scale database migrations and modernizations. As a Solutions Architect, he enjoys providing technical assistance to customers that are looking to migrate and modernize their existing databases.

Cristiani Santos da Silva is a Database Migration Specialist Solutions Architect on the Amazon Database Migration Accelerator team. She works as an Amazon DMA Advisor helping customers to succeed in their migration and modernization to AWS Cloud

AWS Database Blog