AWS Database Blog

Accelerate your database migration journey using AWS DMS Schema Conversion

AWS Database Migration Service (AWS DMS) helps you accelerate your database migrations to AWS. It supports both homogeneous and heterogeneous migrations. Previously, you had to download the AWS Schema Conversion Tool (AWS SCT) to assess and convert your schema and code objects. Now, with AWS DMS Schema Conversion (DMS SC), you can assess, convert, and migrate your database and code objects from the AWS DMS console. As of this writing, DMS SC supports Oracle and SQL Server source databases, as well as Amazon Relational Database Service (Amazon RDS), Amazon Aurora, and Amazon Redshift target databases. More source and target platforms will be available in the future. For more information, refer to the complete list of supported sources and targets.

In this post, we demonstrate how to use DMS SC to convert an Amazon RDS for SQL Server database to Amazon Aurora PostgreSQL-Compatible Edition. We walk you through the setup and configuration of DMS SC, the assessment and conversion process, and how to find information if your conversion runs into an issue.

Solution overview

DMS SC assesses and converts your database objects. It generates an assessment summary, an estimate of the level of effort, and detailed actions for objects that can’t be automatically converted. For heterogeneous migration, DMS SC attempts to convert schema and code objects to the syntax of the target engine. If no direct conversion is possible, it lists options to remediate.

The following architecture diagram shows how you can use DMS SC to assess and convert database objects from an RDS for SQL Server database to Amazon Aurora PostgreSQL.

The DMS SC configuration process includes the following high-level steps:

  1. Prepare DMS SC
  2. Generate and view the assessment report
  3. Convert database objects
  4. Monitor the migration project in DMS SC

Prerequisites

In this section, we discuss the prerequisite steps you must complete before implementing this solution.

Set up the network for DMS Schema Conversion

DMS SC provisions schema conversion resources in a VPC and subnet that you specify. You must set up connectivity to your source and target databases. Network configuration depends on where your source and target databases are located. The RDS for SQL Server and Aurora PostgreSQL databases used in this post are on the same VPC. We provision DMS SC in the same VPC and add an ingress rule to the security groups to allow inbound connections from DMS SC to Amazon RDS for SQL Server and Amazon Aurora PostgreSQL. To learn more about other network configurations, refer to Setting up a network for DMS Schema Conversion.

Store database credentials in AWS Secrets Manager

DMS SC uses secrets stored in AWS Secrets Manager to connect to your database. For instructions to add source and target credentials to Secrets Manager, refer to Store database credentials in AWS Secrets Manager.

Create an Amazon S3 bucket

DMS SC saves items such as assessment reports, converted SQL code, and information about database schema objects in an Amazon Simple Storage Service (Amazon S3) bucket. Create an S3 bucket called dms-sc-demo with the following steps:

  1. On the Amazon S3 console, choose Buckets in the navigation pane.
  2. Choose Create bucket.
  3. Name the bucket dms-sc-demo.
  4. For AWS Region, choose the AWS Region where you are planning to launch DMS SC.
  5. Choose Enable for Bucket Versioning.
  6. Create the bucket.

Create IAM roles

DMS SC uses AWS Identity and Access Management (IAM) roles to access Amazon S3 and the database credentials stored in Secrets Manager. For this post, we create two roles.

First, create the role sc-s3-role for DMS SC to access the S3 bucket with the following steps:

  1. On the IAM console, choose Roles in the navigation pane.
  2. Choose Create role.
  3. For Trusted entity type, select AWS service.
  4. For Service or use case, choose DMS.
  5. Choose Next.
  6. On the Add permissions page, select the policy AmazonS3FullAccess. DMS SC uses an S3 bucket to store artifacts such as database schema and code objects, assessment reports, and converted SQL code.
  7. Choose Next.
  8. For Role name, enter sc-s3-role.
  9. Choose Create role.
  10. On the sc-s3-role page, choose the Trust relationships tab. This will delegate access and allow the DMS service to perform actions on the S3 bucket.
  11. Choose Edit trust policy.
  12. Edit the trust relationships for the role to use the schema-conversion.dms.amazonaws.com service principal as the trusted entity.
  13. Edit the trust policy for the role you created to include the Region name in the AWS DMS principal, for example dms.us-east-1.amazonaws.com, where us-east-1 is the Region where DMS SC will launch.
  14. Choose Update policy.

    Next, you create the role sc-secrets-manager-role for DMS SC to access Secrets Manager.
  15. Choose Roles in the navigation pane.
  16. Choose Create role.
  17. For Trusted entity type, select AWS service.
  18. For Service or use case, choose DMS.
  19. For Use case, select DMS.
  20. Choose Next.
  21. On the Add permissions page, select the policy SecretsManagerReadWrite.
  22. Choose Next.
  23. For Role name, enter sc-secrets-manager-role.
  24. Choose Create role.
  25. The AWS DMS regional service principal has the format dms.region-name.amazonaws.com. Edit the trust policy for the role you created to include the Region name in the AWS DMS principal and add trust relationships for the role to use schema-conversion.dms.amazonaws.com.

With the prerequisites complete, you’re now ready to set up the solution.

Prepare DMS Schema Conversion

In this section, we go through the steps to configure DMS SC.

Set up an instance profile

An instance profile specifies the network, security, and Amazon S3 settings for DMS SC to use. Create an instance profile with the following steps:

  1. On the AWS DMS console, choose Instance profiles in the navigation pane.
  2. Choose Create instance profile.
  3. For Name, enter a name (for example, dms-sc-profile).
  4. For Network type, in this demo we will use IPv4. DMS SC has another option called Dual-Stack mode, which supports both IPv4 and IPv6.
  5. For Virtual private cloud (VPC) for IPv4, choose Default VPC.
  6. For Subnet group, choose your subnet group (for this post, dms-sc-subnet).
  7. For VPC security groups, choose your security groups.

    As previously stated, the instance profile’s VPC security group must have access to both the source and target databases.

    8. base

  8. Specify the S3 bucket to store schema conversion metadata.
  9. Create your instance profile.

Add data providers

Data providers store database types and information about source and target databases for DMS SC to connect to. Configure data providers for the source and target databases with the following steps:

  1. On the AWS DMS console, choose Data providers in the navigation pane.
  2. Choose Create data provider.
  3. To create your target, for Name, enter a name (for example, Aurora-PostgreSQL).
  4. For Engine type¸ choose Amazon Aurora PostgreSQL.
  5. For Engine configuration, select RDS database instance.
  6. For Database from RDS, enter the IP address.
  7. For Port, enter the port number.
  8. For Database name¸ enter the name of your database.
  9. Repeat similar steps to create your source data provider.

Create a migration project

A DMS SC migration project defines migration entities, including instance profiles, source and target data providers, and migration rules. Create a migration project with the following steps:

  1. On the AWS DMS console, choose Migration projects in the navigation pane.
  2. Choose Create migration project.
  3. For Name, enter a name to identify your migration project (for example, dmasc-demo).
  4. For Instance profile, choose the instance profile you created.
  5. In the Data providers section, enter the source and target data providers, Secrets Manager secret, and IAM roles.
  6. In the Schema conversion settings section, enter the S3 URI and choose the applicable IAM role.
  7. Choose Create migration project.

Generate and view the assessment report

In this section, you generate and view the assessment report.

Launch DMS Schema Conversion

The migration project in DMS SC is serverless. AWS DMS automatically provisions the cloud resources for your migration projects, connects to the source and target databases, and fetches source and target database metadata. We use the AdventureWorks database from Microsoft throughout this post.

Launch DMS SC with the following steps:

  1. On the AWS DMS console, choose Migration projects in the navigation pane.
  2. Choose the migration project you created.
  3. On the Schema conversion tab, choose Launch schema conversion.

The schema conversion project will be ready when the launch is complete. The left navigation tree represents the source database, and the right navigation tree represents the target database.

Generate the assessment report

Generate an assessment report with the following steps:

  1. Select the database or schema in the source data provider that you would like to assess.
  2. Choose Actions under Source data providers and choose Assess.

View the assessment report

When the assessment is complete, the assessment report is accessible in the middle pane. DMS SC provides a high-level overview of the migration effort; the Action items tab provides details on specific actions and recommendations for unconverted objects.

You can export these reports to share or view on other devices outside of DMS SC. You can export a detailed report in CSV format and the summary report in PDF format.

The location of the S3 bucket to which the assessment report will be exported is displayed in a pop-up confirmation message, as seen in the following screenshot.

Convert database objects

Select the objects you want to convert, and then choose Convert on the Actions menu to convert the source objects to the target database.

This opens up a modal window confirming your request to convert the schema. For example, it displays a message to confirm whether you would like to convert the Person schema in the AdventureWorks2019 database to Amazon Aurora PostgreSQL.

The conversion process may take some time depending on the number and complexity of the selected objects. When it’s complete, the middle pane displays information on the auto-conversion as well as any action items requiring manual resolution.

You can save the converted code to the S3 bucket that you created earlier in the prerequisite steps. To save the SQL scripts, select the object in the target database tree. Choose Save as SQL on the Actions menu.

A dialog box appears confirming your selection, along with S3 bucket details.

As a next step, download the .zip SQL file created in the previous step and validate the scripts. Look at the action items for each object in the DMS SC middle pane and perform manual updates as needed.

After you finalize the scripts, run them manually in the target database.

Alternatively, you can apply the scripts directly to the database using DMS SC. Select the specific schema in the target database, and on the Actions menu, choose Apply changes.

This will apply the auto-converted code to the target database. For objects that require action items, DMS SC flags them and provides details of action items in the middle pane.

For the items that require resolution, perform manual changes and apply the converted changes directly to the target database.

Monitor the migration project in DMS SC

If you have a large database to convert or need to drill down into issues that might arise during the conversion, you can monitor the progress of the migration project in DMS SC by using Amazon CloudWatch. The first step is to find the ARN of the DMS SC migration project and then find all the logs in the CloudWatch log groups.

You can see the log group with a name similar to dms-tasks-sct-xxxx.

When you run DMS SC, it creates multiple logs in CloudWatch, which provide details regarding the progress of the conversion, including any errors like connection failures, and more. The following screenshot shows four different types of logs generated in CloudWatch that can help you monitor conversion operations.

You can find the details of any issues by searching for keywords like Failed or Error and taking corrective actions based on the error details.

Clean up

When you’re done with the migration, you can perform a cleanup to free up resources. Complete the following steps:

  1. On the AWS DMS console, choose Migration projects in the navigation pane.
  2. Select the migration project you created and choose Delete.
  3. Choose Instance profiles in the navigation pane.
  4. Select the instance profile you created and choose Delete.
  5. Choose Data providers in the navigation pane.
  6. Select the data providers you created and choose Delete.
  7. On the Amazon S3 console, delete the bucket where the migration files were saved.

Conclusion

With DMS Schema Conversion, you can plan, assess, convert, and migrate your database under a centralized DMS. In this post, we walked through how to prepare DMS SC, create migration projects, generate and view assessment reports, and convert schema objects.

To learn more, refer to Converting database schemas using DMS Schema Conversion.

About the Authors

Nelly Susanto is a Senior Database Migration Specialist at AWS Database Migration Accelerator. She has over 10 years of technical experience focusing on migrating and replicating databases and data warehouse workloads. She is passionate about helping customers on their cloud journey.

Amit Arora is a Solutions Architect with a focus on database and analytics at AWS. He works with our financial technology and global energy customers and AWS certified partners to provide technical assistance and design customer solutions on cloud migration projects, helping customers migrate and modernize their existing databases to the AWS Cloud.