AWS Database Blog

New – Accelerate database modernization with generative AI using AWS Database Migration Service Schema Conversion

AWS Database Migration Service Schema Conversion (DMS SC) helps you accelerate your database migration to AWS. Using DMS SC, you can assess, convert, and migrate your database and code objects from the AWS Database Migration Service (AWS DMS) console. Objects that can’t be automatically converted are marked with action items and descriptions to be manually converted to complete the migration.

Today, we’re excited to inform you about a new generative AI feature in DMS SC. You can now use advanced language models to streamline and enhance your migration workflow. As of this writing, this feature supports Oracle and SQL Server to Amazon Relational Database Service (Amazon RDS) and Amazon Aurora PostgreSQL migrations.

In this post, we discuss the key capabilities of DMS SC with generative AI and how to enable it to offer you additional recommendations to reduce manual conversion effort and time.

DMS SC generative AI overview

The new generative AI feature in DMS SC streamlines conversions by reducing manual effort when combined with a traditional rule-based approach. It enhances conversion flow using best practices and researches results made inside DMS SC and uses them as a context to the large language model (LLM). This helps to make results from the LLM more compatible with the general conversion results as well as more useful to run along code created by DMS SC during previous migrations. The following diagram shows the workflow of DMS SC.

These are the high-level steps that DMS SC with generative AI follows:

  1. Upon initiating, DMS SC with generative AI identifies the objects with eligible action items
  2. The eligible statement is parsed and the best match from the conversion rules is selected
  3. Each variable or parameter used is described to help it follow general DMS SC data types migration guidance
  4. Sensitive data is obfuscated before sending to the LLM (including data such as identifiers, comments, and literals)
  5. Candidate statement and supporting context (prompts) are sent to the LLM
  6. LLM generates a modified version of the candidate statement and context to align with the target database syntax
  7. Return the converted statement
  8. Reverse the obfuscation of the converted statement
  9. Generate the target SQL

Activities covered by generative AI

With DMS SC generative AI, you can generate additional conversion options for following schema conversion action items on select, insert, update delete, and merge statements:

Oracle:

  • 9996: Internal Converter error occurred
  • 5065: PostgreSQL doesn’t support the UPDATE statement for subqueries
  • 5068: PostgreSQL doesn’t support the DELETE statement for subqueries
  • 5071: PostgreSQL doesn’t support the INSERT statement for subqueries
  • 5077: PostgreSQL doesn’t support the PIVOT clause for SELECT statements
  • 5126: PostgreSQL doesn’t support the MODEL statement
  • 5340: PostgreSQL doesn’t support the %s function
  • 5586: Automatic conversion for queries with NOCYCLE clause not supported
  • 5098: PostgreSQL doesn’t support a ROWID

SQL Server:

  • 9996: Internal Converter error occurred
  • 7811: Unable to convert build-in functions

Prerequisites

The DMS SC configuration includes several steps. For step-by-step guidance on planning and configuring DMS SC and creating and launching a migration project, follow the instructions at Accelerate your database migration journey using AWS DMS Schema Conversion.

Using DMS SC with generative AI

This feature can be enabled for new and existing DMS SC projects with the following steps:

  1. On the AWS DMS console, choose Migration projects to open the Migration projects you want to use, as shown in the following screenshots.

  2. Select the Schema conversion Choose Launch schema conversion, as shown in the following screenshot. Choose Launch.
  3. Wait for DMS SC to launch. This process may take up to a few minutes depending on the size of your database and your network configurations.
  4. After your schema conversion project is launched, you can select one or more objects and choose to create a migration assessment report or to start the conversion. To create a migration assessment report, choose Actions and then Assess, as shown in the following screenshot.
  5. After the assessment report is created and you have eligible objects to be converted using this feature, you’ll receive a report indicating issues similar to those shown in the following screenshot.

  6. To start the conversion, select the object from the source tree. Choose Actions and then choose Convert, as shown in the following screenshot.
  7. In this example, we converted without enabling the generative AI feature. The result indicates that the view, a1_reinvent_product_pricing_analysis, needs manual conversion as shown in the following screenshot.
  8. On the Action items tab, the issue numbers are 5578 and 5340. The following screenshot shows additional conversion recommendations generated by DMS.
  9. In the next section, we will convert using generative AI. You can turn it on and off as a default at the project level, or you can turn it on at the object level. To turn it on at the project level, on the Settings tab, turn on Enable Generative AI feature for conversion, as shown in the following screenshot. Choose Apply.
  10. Select the same object and choose Convert with Generative AI, then choose Convert, as shown in the following screenshot.
  11. After the conversion is completed, you’ll receive a result displayed in the Target SQL window on the right-hand side, as shown in the following screenshot.
  12. When you select the Action items tab, you’ll receive a notification indicating Issue 5444, as shown in the following screenshot.
  13. After you have reviewed the Target SQL, you can proceed with the next step to apply the converted objects in your target database schema.

Conclusion

In this post, we showed how you can enable generative AI in DMS SC to generate additional conversion recommendations for the DMS SC action items that were previously manual. You can use DMS SC generative AI today in the US East (N. Virginia) us-east-1, US West (Oregon) us-west-2, and Europe (Frankfort) eu-central-1 AWS Regions, with more to come soon. To learn more, refer to Converting database schemas using DMS Schema Conversion.

About the authors

Mike Revitt is a Principal Database Migration Specialist working in the Database Migration Accelerator team at AWS. Mike has 35 years of experience in database and application migrations, design, development and integration and over 10 years of experience in database and application modernization.

Nelly Susanto is a Principal 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.

Anton Koliada is a Senior Database Engineer at AWS DMS. He has over 10 years of technical experience migrating different database code to cloud databases. He focuses on the quality of the resulting code to help customers achieve similar results for the migrated database code and application.