Permalink
Comments
Share
The AWS Database Migration Service Schema Conversion (DMS SC) is designed to expedite the migration of your databases to AWS. With DMS SC, you can assess, convert, and migrate your database and code objects directly from the AWS Database Migration Service (AWS DMS) console. Objects that cannot be automatically converted are flagged with action items and descriptions for manual conversion to successfully complete the migration.
We’re thrilled to announce a new generative AI feature within DMS SC that allows you to leverage advanced language models to optimize and streamline your migration workflow. Currently, this feature supports migrations from Oracle and SQL Server to Amazon Relational Database Service (Amazon RDS) and Amazon Aurora PostgreSQL.
In this article, we’ll explore the key capabilities of DMS SC utilizing generative AI and how to enable this feature to provide additional recommendations that minimize manual conversion effort and time.
Overview of DMS SC Generative AI
The new generative AI functionality in DMS SC simplifies conversions by decreasing manual tasks when used alongside traditional rule-based methods. It improves the conversion process by leveraging best practices and research results within DMS SC, which are utilized as context for the large language model (LLM). This approach ensures that the outcomes from the LLM are more aligned with the overall conversion results and useful in conjunction with code generated by DMS SC in previous migrations. The following diagram illustrates the workflow of DMS SC.
The high-level steps followed by DMS SC with generative AI include the following:
- Upon initiation, DMS SC with generative AI identifies objects with eligible action items.
- The eligible statement is parsed, and the best match from the conversion rules is selected.
- Each variable or parameter is described to adhere to general DMS SC data types migration guidelines.
- Sensitive information is obfuscated before being sent to the LLM (including identifiers, comments, and literals).
- The candidate statement and supporting context (prompts) are transmitted to the LLM.
- The LLM generates a modified version of the candidate statement and context to fit the target database syntax.
- The converted statement is returned.
- The obfuscation of the converted statement is reversed.
- The target SQL is generated.
Generative AI Activities
With DMS SC generative AI, you can generate additional conversion options for various schema conversion action items related to 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 built-in functions
Prerequisites
The configuration of DMS SC encompasses several steps. For detailed guidance on planning, configuring DMS SC, and creating and launching a migration project, follow the instructions at accelerating your database migration journey using AWS DMS Schema Conversion.
Using DMS SC with Generative AI
This feature can be enabled for both new and existing DMS SC projects by following these steps:
- In the AWS DMS console, select Migration projects to access the desired Migration project as shown in the following screenshots.
- Choose the Schema conversion option and click Launch schema conversion, as illustrated in the accompanying screenshot.
- Wait for DMS SC to launch, a process which may take a few minutes depending on your database size and network configurations.
- Once your schema conversion project is active, you can select one or more objects and opt to create a migration assessment report or commence the conversion. To generate a migration assessment report, click Actions and then Assess, as depicted in the following screenshot.
- After the assessment report is generated, and if you have qualified objects for conversion using this feature, you’ll receive a report indicating issues similar to those displayed in the following screenshot.
- To initiate the conversion, select the object from the source tree, click Actions, and then Convert, as shown in the following screenshot.
- In this example, we converted without enabling the generative AI feature, resulting in the view, a1_reinvent_product_pricing_analysis, requiring manual conversion as shown in the following screenshot.
- On the Action items tab, the issue numbers are 5578 and 5340. The subsequent screenshot shows additional conversion recommendations generated by DMS.
- Next, we will convert using generative AI. You can enable or disable it at the project level, or at the object level. To turn it on at the project level, navigate to the Settings tab, and toggle on Enable Generative AI feature for conversion, as shown in the following screenshot. Click Apply.
- Select the same object and choose Convert with Generative AI, then click Convert, as shown in the following screenshot.
- Upon completion of the conversion, the result will be displayed in the Target SQL window on the right-hand side, as shown in the following screenshot.
- When you select the Action items tab, you’ll receive a notification indicating Issue 5444, as shown in the following screenshot.
- After reviewing the Target SQL, you can proceed with the next step to apply the converted objects to your target database schema.
Conclusion
In this post, we have illustrated how to enable generative AI in DMS SC to generate additional conversion recommendations for previously manual DMS SC action items. You can utilize 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 regions being added soon. For further insights, refer to this another blog post, Converting database schemas using DMS Schema Conversion.
For more authoritative information on this topic, visit this link. Additionally, for community-driven insights, check out this excellent resource on Reddit: this Reddit thread.
About the authors
Jason Miller is a Principal Database Migration Specialist with the Database Migration Accelerator team at AWS. With 35 years of experience in database and application migrations, design, development, and integration, he has over a decade of expertise in database and application modernization.
Sarah Thompson is a Principal Database Migration Specialist at AWS Database Migration Accelerator, boasting over ten years of technical experience in migrating and replicating databases and data warehouse workloads. She is dedicated to assisting customers on their cloud journey.
David Kim is a Senior Database Engineer at AWS DMS with over a decade of experience in migrating various database code to cloud databases. His focus lies in ensuring the quality of the resulting code, helping customers achieve consistent results for their migrated database code and applications.
Leave a Reply