DMS Schema Conversion in AWS Database Migration Service (AWS DMS) is accessible from the AWS DMS console and AWS Command Line Interface (AWS CLI). It is a fully managed feature of AWS DMS with which you can assess and convert databases. In this second post in a multipart series, we share how to automate creating DMS Schema Conversion components and performing migration activities, such as assessment and conversion. See the first post in this series for guidance about preparing the prerequisites for DMS Schema Conversion configuration, configuring DMS Schema Conversion, and performing migration activities through the AWS DMS console.
One of the many key benefits of using the AWS CLI, particularly for large-scale database migrations, is the ability to automate and streamline the migration process. In this post, we demonstrate how to use DMS Schema Conversion to assess an Amazon Relational Database Service (Amazon RDS) for SQL Server database and convert it to Amazon Aurora PostgreSQL-Compatible Edition. We walk you through how to automate the setup and configuration of DMS Schema Conversion components, generate an assessment report, convert database storage and code objects, export the converted code to Amazon Simple Storage Service (Amazon S3), and apply the converted code to the target database.
Solution overview
The DMS Schema Conversion CLI is available in AWS CLI version 2. Use the following command in Command Prompt to see which version you currently have installed:
aws --version
aws-cli/2.17.38 Python/3.11.9 Darwin/24.3.0 exe/x86_64
For information about how to install AWS CLI version 2, see Getting started with the AWS CLI.
The following are the key components of DMS Schema Conversion:
- Instance profile – An instance profile defines the network and security that DMS Schema Conversion uses. It also determines where the internal DMS Schema Conversion resources will be launched.
- Data providers – Data providers store the database type and information about source and target connection details for DMS Schema Conversion to connect to.
- Migration project – A migration project is the foundation for all DMS Schema Conversion migration activities. It’s where you define migration entities, including instance profile, source and target data providers, and migration rules.
First, we walk through how to use the AWS CLI to create the key DMS Schema Conversion components. We then cover the commands to perform migration activities, including assessment and conversion of database objects. In this post, we use us-east-1 for the AWS Region and the default AWS profile:
export AWS_DEFAULT_REGION='us-east-1'
export AWS_PROFILE=default
For information about how to configure environment variables for the AWS CLI, see Configuring environment variables for the AWS CLI.
Create an instance profile
An instance profile specifies the network and security configuration used by DMS Schema Conversion and determines where the internal DMS Schema Conversion resources will be deployed. As part of the instance profile creation process, you specify the instance profile name, network type, subnet group, and security group that the DMS Schema Conversion should use.
aws dms create-instance-profile
--publicly-accessible
--network-type IPV4
--instance-profile-name "ip-demo"
--description "Instance profile sql to Aurora PG"
--subnet-group-identifier "sc2"
--vpc-security-groups sg-052b3xxx
--query 'InstanceProfile.InstanceProfileArn'
--output text
#Output return Instance Profile arn
arn:aws:dms:us-east-1:123456789012:instance-profile:JX6FWLF6T5E4JJXXD5YCDOCABI
Create a data provider
The data provider defines metadata about the database type and connection information about source and target databases use by DMS Schema Conversion. In this step, you will create a data provider by specifying its name, database engine type, and database settings, including database server name, port, and SslMode for DMS Schema Conversion to connect to the database.
#Create Source (SQL Server) Data Provider
aws dms create-data-provider
--data-provider-name "demo-sql"
--description "sql server"
--engine sqlserver
--settings "{"MicrosoftSqlServerSettings": {"ServerName": "tsw.c3wkj7xxxx.us-east-1.rds.amazonaws.com", "Port": 1433, "DatabaseName": "tsw"}}"
--query 'DataProvider.DataProviderArn'
--output text
#Return of source data provide arn
arn:aws:dms:us-east-1:123456789012:data-provider:MVPJGML3DJBEJJMYVBZSGRYSMA
#Create Target (Aurora PostgreSQL) Data Provider
aws dms create-data-provider
--data-provider-name "demo-pg"
--description "Aurora PostgreSQL"
--engine "aurora-postgresql"
--settings "{"PostgreSqlSettings": {"ServerName": "apg1-instance-1.c3wkj7xxxx.us-east-1.rds.amazonaws.com", "Port": 1234, "DatabaseName": "postgres", "SslMode": "none"}}"
--query 'DataProvider.DataProviderArn'
--output text
#Output return of target data provider arn
arn:aws:dms:us-east-1:123456789012:data-provider:L72LHMJATZB2TJZIQXMVM5ORM4
Create a migration project
The DMS Schema Conversion migration project coordinates the integration of key components, including the instance profile, source and target data providers, and migration rules. To create the migration project by using an AWS CLI command, start by specifying a project name. Then, associate the instance profile the project should use. Next, specify the source and target data providers along with their secrets and an AWS Identity and Access Management (IAM) role for AWS Secrets Manager. Finally, set the Amazon S3 path for the assessment report and exported converted code, and provide the IAM role for the Amazon S3 bucket.
aws dms create-migration-project
--migration-project-name "demo-sql-apg"
--instance-profile-identifier "arn:aws:dms:us-east-1:123456789012:instance-profile:JX6FWLF6T5E4JJXXD5YCDOCABI"
--source-data-provider-descriptors "{"DataProviderIdentifier": "arn:aws:dms:us-east-1:123456789012:data-provider:MVPJGML3DJBEJJMYVBZSGRYSMA", "SecretsManagerSecretId": "arn:aws:secretsmanager:us-east-1:123456789012:secret:sql-aiml-9Wmj68", "SecretsManagerAccessRoleArn": "arn:aws:iam::123456789012:role/aws-dms-sc-oltp"}"
--target-data-provider-descriptors "{"DataProviderIdentifier": "arn:aws:dms:us-east-1:123456789012:data-provider:L72LHMJATZB2TJZIQXMVM5ORM4", "SecretsManagerSecretId": "arn:aws:secretsmanager:us-east-1:123456789012:secret:apg1-Lzc3TT", "SecretsManagerAccessRoleArn": "arn:aws:iam::123456789012:role/aws-dms-sc-oltp"}"
--description description
--schema-conversion-application-attributes "{"S3BucketPath": "s3://amzn-s3-demo-bucket", "S3BucketRoleArn": "arn:aws:iam::123456789012:role/aws-dms-sc-oltp"}"
--query 'MigrationProject.MigrationProjectArn'
--output text
#Output project arn
arn:aws:dms:us-east-1:123456789012:migration-project:HYY6WUCDVRG43NTF2JK5URI6II
Create a source selection rule
We’ve covered the AWS CLI commands to create DMS Schema Conversion components. Now let’s look at the commands to perform migration activities. The first step is to create a source selection rule, which defines database objects you want DMS Schema Conversion to include in the migration process. The following is the sample selection rule, which you can modify as needed to fit your use case.
cat > source_rules.json << EOF
{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"server-name": "tsw.c3wkj7xxxx.us-east-1.rds.amazonaws.com",
"database-name": 'tsw',
"schema-name": 'dbo'
},
"rule-action": "explicit"
]}
EOF
With this rule in place, you can proceed to the next step: assessing and converting your database objects.
Create an assessment report
To initiate the assessment, use the start-metadata-model-assessment command. Provide the migration project Amazon Resource Name (ARN) as well as the source selection rule you created in the previous section:
aws dms start-metadata-model-assessment
--migration-project-identifier arn:aws:dms:us-east-1:123456789012:migration-project:35TRQ7CYINHXXK7HQYMD2BUS64
--selection-rules file://source_rules.json
#Response
{
"RequestIdentifier": "44fc9838-146f-41d8-b13c-923b3cc704c2"
}
To check the progress of the assessment, use the describe-metadata-model-assessment command:
aws dms describe-metadata-model-assessments
--migration-project-identifier arn:aws:dms:us-east-1:123456789012:migration-project:35TRQ7CYINHXXK7HQYMD2BUS64
--query 'Requests[0].Status' --output text
#Response
IN_PROGRESS
SUCCESS
This is the full list of operation statuses: IN_PROGRESS, SUCCESS, FAILED, DUPLICATE, RETRY, CANCELING, CANCELLED.
Export the assessment report
After you’ve created the assessment report, use the export-metadata-model-assessment command to export the report to Amazon S3. The summary report in PDF format provides an estimate of the migration complexity. DMS Schema Conversion also offers the option to generate a detailed report in CSV format, which includes objects that require manual action as well as recommendations.
The following code shows how to export a summary report in PDF format. If you would prefer a detailed report as opposed to a summary, set assessment-report-type to csv.
aws dms export-metadata-model-assessment
--migration-project-identifier arn:aws:dms:us-east-1:123456789012:migration-project:35TRQ7CYINHXXK7HQYMD2BUS64
--selection-rules file://source_rules.json
--file-name "assessment report"
--assessment-report-types "pdf"
#Assessment reports are available in S3
Exporting metadata model assessment...
{
"PdfReport": {
"S3ObjectKey": "demo-sql-apg/report.pdf",
"ObjectURL": "https://sc-123456789.s3.amazonaws.com/demo-sql-apg/report.pdf"
}
}
{
"CsvReport": {
"S3ObjectKey": "demo-sql-apg/report.zip",
"ObjectURL": "https://sc-123456789.s3.amazonaws.com/demo-sql-apg/report.zip"
}
}
Perform the conversion
Use start-metadata-model-conversion when you’re ready to perform the conversion. Provide the migration project ARN as well as the source selection rule you created earlier:
aws dms start-metadata-model-conversion
--migration-project-identifier arn:aws:dms:us-east-1:123456789012:migration-project:35TRQ7CYINHXXK7HQYMD2BUS64
--selection-rules file://source_rules.json
You can use the describe-metadata-model-conversions command to check the progress of the conversion:
aws dms describe-metadata-model-conversions
--migration-project-identifier arn:aws:dms:us-east-1:123456789012:migration-project:35TRQ7CYINHXXK7HQYMD2BUS64
--query 'Requests[0].Status' --output text
#Response
IN_PROGRESS
SUCCESS
Export the converted code as SQL
When the conversion is complete, the converted code will be available within the migration project. You can either export it to SQL, or apply it directly to the target database (see the next section to learn how to apply it). Before exporting or applying, you first need to define the database objects in the target selection rule. The following code is an example of the target selection rule, which you can modify as needed to fit your use case.
cat > target_rules.json << EOF
{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"server-name": " apg1-instance-1.c3wkj7xxxx.us-east-1.rds.amazonaws.com",
"schema-name": 'tsw_dbo'
},
"rule-action": "explicit"
}
]
}
EOF
Now you can use the start-metadata-model-export-as-script command to export the converted code as SQL. Include the migration project, target selection rule, origin specified as target, and file name:
aws dms start-metadata-model-export-as-script
--migration-project-identifier arn:aws:dms:us-east-1:123456789012:migration-project:35TRQ7CYINHXXK7HQYMD2BUS64
--selection-rules file://target_rules.json
--origin TARGET
--file-name "SaveAsSQL"
To check the progress of the export, use the describe-metadata-model-export-as-script command:
aws dms describe-metadata-model-exports-as-script
--migration-project-identifier arn:aws:dms:us-east-1:123456789012:migration-project:35TRQ7CYINHXXK7HQYMD2BUS64
--query 'Requests[0].Status' --output text
#Response
IN_PROGRESS
SUCCESS
Apply the converted code to the target database
To apply the converted code to the target database, use the start-metadata-model-export-to-target command. Provide the migration project ARN and target selection rule that you’ve created earlier:
aws dms start-metadata-model-export-to-target
--migration-project-identifier arn:aws:dms:us-east-1:123456789012:migration-project:35TRQ7CYINHXXK7HQYMD2BUS64
--selection-rules file://target_rules.json
--overwrite-extension-pack
To check the progress of the apply, use describe-metadata-model-export-to-target:
aws dms describe-metadata-model-exports-to-target
--migration-project-identifier arn:aws:dms:us-east-1:123456789012:migration-project:35TRQ7CYINHXXK7HQYMD2BUS64
--query 'Requests[0].Status' --output text
#Response
IN_PROGRESS
SUCCESS
Clean up post-migration
When the migration is complete, you can clean up the DMS Schema Conversion Resources:
#Cleanup
# Delete migration project
aws dms delete-migration-project
> --migration-project-identifier arn:aws:dms:us-east-1:123456789012:migration-project:HYY6WUCDVRG43NTF2JK5URI6II
#Response
{
"MigrationProject": {
"MigrationProjectName": "demo-sql-apg",
"MigrationProjectArn": "arn:aws:dms:us-east-1:123456789012:migration-project:HYY6WUCDVRG43NTF2JK5URI6II",
"MigrationProjectCreationTime": "2024-08-01T17:29:05.681257+00:00",
"SourceDataProviderDescriptors": [
{
"SecretsManagerSecretId": "arn:aws:secretsmanager:us-east-1:123456789012:secret:sql-aiml-9Wmj68",
"SecretsManagerAccessRoleArn": "arn:aws:iam::123456789012:role/aws-dms-sc-oltp",
"DataProviderName": "demo-sql",
"DataProviderArn": "arn:aws:dms:us-east-1:123456789012:data-provider:MVPJGML3DJBEJJMYVBZSGRYSMA"
}
],
"TargetDataProviderDescriptors": [
{
"SecretsManagerSecretId": "arn:aws:secretsmanager:us-east-1:123456789012:secret:apg1-Lzc3TT",
"SecretsManagerAccessRoleArn": "arn:aws:iam::123456789012:role/aws-dms-sc-oltp",
"DataProviderName": "demo-pg",
"DataProviderArn": "arn:aws:dms:us-east-1:123456789012:data-provider:L72LHMJATZB2TJZIQXMVM5ORM4"
}
],
"InstanceProfileArn": "arn:aws:dms:us-east-1:123456789012:instance-profile:JX6FWLF6T5E4JJXXD5YCDOCABI",
"InstanceProfileName": "ip-demo",
"TransformationRules": "{"rules":[]}",
"SchemaConversionApplicationAttributes": {
"S3BucketPath": "s3://amzn-s3-demo-bucket",
"S3BucketRoleArn": "arn:aws:iam::123456789012:role/aws-dms-sc-oltp"
}
}
}
#Delete source data provider
aws dms delete-data-provider --data-provider-identifier arn:aws:dms:us-east-1:123456789012:data-provider:MVPJGML3DJBEJJMYVBZSGRYSMA
#Response
{
"DataProvider": {
"DataProviderName": "demo-sql",
"DataProviderArn": "arn:aws:dms:us-east-1:123456789012:data-provider:MVPJGML3DJBEJJMYVBZSGRYSMA",
"DataProviderCreationTime": "2024-08-01T17:27:12.902435+00:00",
"Engine": "sqlserver",
"Settings": {
"MicrosoftSqlServerSettings": {
"ServerName": "tsw.c3wkj7xxxx.us-east-1.rds.amazonaws.com",
"Port": 1433,
"DatabaseName": "tsw",
"SslMode": "none"
}
}
}
}
#Delete Target data provider
aws dms delete-data-provider --data-provider-identifier arn:aws:dms:us-east-1:123456789012:data-provider:L72LHMJATZB2TJZIQXMVM5ORM4
#Response
{
"DataProvider": {
"DataProviderName": "demo-pg",
"DataProviderArn": "arn:aws:dms:us-east-1:123456789012:data-provider:L72LHMJATZB2TJZIQXMVM5ORM4",
"DataProviderCreationTime": "2024-08-01T17:27:12.902435+00:00",
"Engine": "aurora-postgresql",
"Settings": {
"MicrosoftSqlServerSettings": {
"ServerName": "apg1-instance-1.c3wkj7xxxx.us-east-1.rds.amazonaws.com",
"Port": 1234,
"DatabaseName": "postgres",
"SslMode": "none"
}
}
}
}
#Delete Instance Profile
aws dms delete-instance-profile --instance-profile-identifier arn:aws:dms:us-east-1:123456789012:instance-profile:JX6FWLF6T5E4JJXXD5YCDOCABI
#Response
{
"InstanceProfile": {
"InstanceProfileArn": "arn:aws:dms:us-east-1:123456789012:instance-profile:JX6FWLF6T5E4JJXXD5YCDOCABI",
"KmsKeyArn": "arn:aws:kms:us-east-1:123456789012:key/e26404b5-853a-4a33-95b9-99ef043cd207",
"PubliclyAccessible": true,
"NetworkType": "IPV4",
"InstanceProfileName": "ip-demo",
"InstanceProfileCreationTime": "2024-08-01T17:25:33.128750+00:00",
"SubnetGroupIdentifier": "sc2",
"VpcSecurityGroups": [
"sg-052b3xxx"
]
}
}
Summary
A key benefit of using the DMS Schema Conversion with the AWS CLI, especially for large-scale database migrations, is the ability to automate and streamline the migration process. In this post, we showed how to use the AWS CLI to set up key DMS Schema Conversion components, including the instance profile, data provider, and migration project. We walked through the migration workflow of creating selection rules, generating assessment report, performing the conversion, exporting the converted code, and applying the converted code to the target database. This solution helps automate and standardize the migration process – reducing manual effort and minimizing risk. It is well-suited where teams need to migrate multiple workloads simultaneously. If you’re planning a large-scale migration, we encourage you to leverage this solution to streamline your efforts and accelerate the migration.
To learn more about how to assess and convert database object using DMS SC CLI, see AWS DMS CLI Reference Guide.
About the authors
