Modernize your code solution accelerator

MENU: USER STORY | QUICK DEPLOY | SUPPORTING DOCUMENTATION

User story

Overview

Welcome to the Modernize your code solution accelerator, designed to help customers transition their SQL queries to new environments quickly and efficiently. This accelerator is particularly useful for organizations modernizing their data estates, as it simplifies the process of translating SQL queries from various dialects.

When dealing with legacy code, users often face significant challenges, including the absence of proper documentation, loss of knowledge of outdated languages, and missing business logic that explains functional requirements.

The Modernize your code solution accelerator allows users to specify a group of SQL queries and the target SQL dialect for translation. It then initiates a batch process where each query is translated using a group of Large Language Model (LLM) agents. This automation not only saves time but also ensures accuracy and consistency in query translation.

Technical Key features

Below is an image of the solution accelerator:

Use case / scenario

Companies maintaining and modernizing their data estates often face large migration projects. They may have volumes of files in various dialects, which need to be translated into a modern alternative. Some of the challenges they face include:

• Difficulty analyzing and maintaining legacy systems due to missing documentation
• Time-consuming process to manually update legacy code and extract missing business logic
• High risk of errors from manual translations, which can lead to incorrect query results and data integrity issues
• Lack of available knowledge and expertise for legacy languages creates additional effort, cost, and reliance on niche skills

By using the Modernize your code solution accelerator, users can automate this process, ensuring that all queries are accurately translated and ready for use in the new modern environment.

For an in-depth look at the applicability of using multiple agents for this code modernization use case, please see the supporting AI Research paper.

The sample data used in this repository is synthetic and generated using Azure Open AI service. The data is intended for use as sample data only.

Solution architecture

Agentic architecture

This diagram double-clicks into the agentic framework for the code conversion process. The conversion uses an agentic approach with each agent playing a specialized role in the process. The system gets a list of SQL files which are targeted for conversion. 

Step 1: The system loops through the list of SQL files, converting each file, starting by passing the SQL to the Migrator agent. This agent will create several candidate SQL files that should be equivalent. It does this to ensure that the system acknowledges that most of these queries could be converted in a number of different ways. Note that the processing time can vary depending on Azure OpenAI service and cloud services.

Step 2: The Picker agent then examines these various possibilities and picks the one it believes is best using criteria such as simplicity, clarity of syntax, etc.

Step 3: This query is sent to the Syntax checker agent which, using a command line tool designed to validate SQL syntax, checks to make sure the query should run without error.

• Step 3n: If the Syntax checker agent finds potential errors, it then in Step 3n sends the query to a Fixer agent which will attempt to fix the problem. The Fixer agent then sends the fixed query back to the Syntax checker agent again. If there are still errors, the Syntax checker agent sends back to the Fixer agent to make another attempt. This iteration continues until, either there are no errors found, or a max number of allowed iterations is reached. If the max number is hit, error logs are generated for that query and stored in its Cosmos DB metadata. 

Step 4: Once the SQL is found to run without errors, it is sent for a final check to the Semantic checker agent. This agent makes sure that the query in the new syntax will have the same logical effects as the old query, with no extra effects. It can find edge cases which don’t apply to most scenarios, so, if it finds an issue, this issue is sent to the query logs, and the query is generated and the file will be present in storage, but its state will be listed as “warning”.  If no semantic issues are found, the query is generated and placed into Azure storage with a state of success.

QUICK DEPLOY

Prerequisites

To deploy this solution accelerator, ensure you have access to an Azure subscription with the necessary permissions to create resource groups and resources. Follow the steps in Azure Account Set Up

Check the Azure Products by Region page and select a region where the following services are available:

• Azure AI Foundry
• Azure OpenAI Service
• Embedding Deployment Capacity
• GPT Model Capacity

Here are some example regions where the services are available: East US, East US2, Japan East, UK South, Sweden Central.

This accelerator can be deployed with or without authentication.

• To install with authentication requires that the installer have the rights to create and register an application identity in their Azure environment. After installation is complete, follow the directions in the App Authentication document to enable authentication.
• Note: If you install with authentication, all processing history and current processing will be performed for your specific user. If you deploy without authentication, all batch history from the tool will be visible to all users.

Configurable Deployment Settings

When you start the deployment, most parameters will have default values, but you can update the following settings:

Setting	Description	Default value
Azure Region	The region where resources will be created.	East US
Resource Prefix	Prefix for all resources created by this template. This prefix will be used to create unique names for all resources. The prefix must be unique within the resource group.	None
AI Location	Location for all AI services resources. This location can be different from the resource group location	None
Capacity	Configure capacity for gpt-4o.	5k

[Optional] Quota Recommendations

By default, the GPT model capacity in deployment is set to 5k tokens.

We recommend increasing the capacity to 200k tokens for optimal performance.

To adjust quota settings, follow these steps

⚠️ Warning: Insufficient quota can cause application errors. Please ensure you have the recommended capacity or request for additional capacity before deploying this solution.

Deployment Options

Pick from the options below to see step-by-step instructions for: GitHub Codespaces, VS Code Dev Containers, Local Environments, and Bicep deployments.

Deploy in GitHub Codespaces

GitHub Codespaces

You can run this solution using GitHub Codespaces. The button will open a web-based VS Code instance in your browser:

1. Open the solution accelerator (this may take several minutes):

   

2. Accept the default values on the create Codespaces page

3. Open a terminal window if it is not already open

4. Continue with the deploying steps

Deploy in VS Code

VS Code Dev Containers

You can run this solution in VS Code Dev Containers, which will open the project in your local VS Code using the Dev Containers extension:

1. Start Docker Desktop (install it if not already installed)

2. Open the project:

   

3. In the VS Code window that opens, once the project files show up (this may take several minutes), open a terminal window.

4. Continue with the deploying steps

Deploy in your local environment

Local environment

If you're not using one of the above options for opening the project, then you'll need to:

1. Make sure the following tools are installed:

   • Azure Developer CLI (azd)
   • Python 3.9+
   • Docker Desktop
   • Git

2. Download the project code:

   azd init -t microsoft/Modernize-your-Code-Solution-Accelerator/

3. Open the project folder in your terminal or editor.

4. Continue with the deploying steps.

Deploy with Bicep/ARM template

Bicep

Click the following deployment button to create the required resources for this accelerator directly in your Azure Subscription.

Deploying

Once you've opened the project in Codespaces or in Dev Containers or locally, you can deploy it to Azure following the following steps.

To change the azd parameters from the default values, follow the steps here.

1. Login to Azure:

   azd auth login

   Note: To authenticate with Azure Developer CLI (azd) to a specific tenant, use the previous command with your Tenant ID:

   azd auth login --tenant-id <tenant-id>

2. Provision and deploy all the resources:

   azd up

3. Provide an azd environment name (like "cmsaapp")

4. Select a subscription from your Azure account, and select a location which has quota for all the resources.

   • This deployment will take 6-9 minutes to provision the resources in your account and set up the solution with sample data.
   • If you get an error or timeout with deployment, changing the location can help, as there may be availability constraints for the resources.

5. Once the deployment has completed successfully, open the Azure Portal, go to the deployed resource group, find the container app with "frontend" in the name, and get the app URL from Application URI.

6. You can now delete the resources by running azd down, when you have finished trying out the application.

Additional Steps

1. Deleting Resources After a Failed Deployment

   Follow steps in Delete Resource Group If your deployment fails and you need to clean up the resources.

2. Add App Authentication

   If you chose to enable authentication for the deployment, follow the steps in App Authentication

Running the application

To help you get started, sample Informix queries have been included in the data/informix/functions and data/informix/simple directories. You can choose to upload these files to test the application.

Responsible AI Transparency FAQ

Please refer to Transparency FAQ for responsible AI transparency details of this solution accelerator.

Supporting Documentation

Costs

Pricing varies per region and usage, so it isn't possible to predict exact costs for your usage. The majority of the Azure resources used in this infrastructure are on usage-based pricing tiers. However, Azure Container Registry has a fixed cost per registry per day.

You can try the Azure pricing calculator for the resources:

• Azure AI Foundry: Free tier. Pricing
• Azure Storage Account: Standard tier, LRS. Pricing is based on storage and operations. Pricing
• Azure Key Vault: Standard tier. Pricing is based on the number of operations. Pricing
• Azure AI Services: S0 tier, defaults to gpt-4o-mini. Pricing is based on token count. Pricing
• Azure Container App: Consumption tier with 0.5 CPU, 1GiB memory/storage. Pricing is based on resource allocation, and each month allows for a certain amount of free usage. Pricing
• Azure Container Registry: Basic tier. Pricing
• Log analytics: Pay-as-you-go tier. Costs based on data ingested. Pricing
• Azure Cosmos DB: Pricing

⚠️ To avoid unnecessary costs, remember to take down your app if it's no longer in use, either by deleting the resource group in the Portal or running azd down.

Security guidelines

This installs Azure Key Vault for use by AI Foundry.

This template uses Managed Identity for all Azure service communication.

To ensure continued best practices in your own repository, we recommend that anyone creating solutions based on our templates ensure that the Github secret scanning setting is enabled.

You may want to consider additional security measures, such as:

• Enabling Microsoft Defender for Cloud to secure your Azure resources.
• Protecting the Azure Container Apps instance with a firewall and/or Virtual Network.

Additional resources

• Azure AI Foundry documentation
• Semantic Kernel Agent Framework
• Azure Cosmos DB Documentation
• Azure OpenAI Service - Documentation, quickstarts, API reference - Azure AI services | Microsoft Learn
• Azure Container Apps documentation

Disclaimers

To the extent that the Software includes components or code used in or derived from Microsoft products or services, including without limitation Microsoft Azure Services (collectively, “Microsoft Products and Services”), you must also comply with the Product Terms applicable to such Microsoft Products and Services. You acknowledge and agree that the license governing the Software does not grant you a license or other right to use Microsoft Products and Services. Nothing in the license or this ReadMe file will serve to supersede, amend, terminate or modify any terms in the Product Terms for any Microsoft Products and Services.

You must also comply with all domestic and international export laws and regulations that apply to the Software, which include restrictions on destinations, end users, and end use. For further information on export restrictions, visit https://aka.ms/exporting.

You acknowledge that the Software and Microsoft Products and Services (1) are not designed, intended or made available as a medical device(s), and (2) are not designed or intended to be a substitute for professional medical advice, diagnosis, treatment, or judgment and should not be used to replace or as a substitute for professional medical advice, diagnosis, treatment, or judgment. Customer is solely responsible for displaying and/or obtaining appropriate consents, warnings, disclaimers, and acknowledgements to end users of Customer’s implementation of the Online Services.

You acknowledge the Software is not subject to SOC 1 and SOC 2 compliance audits. No Microsoft technology, nor any of its component technologies, including the Software, is intended or made available as a substitute for the professional advice, opinion, or judgement of a certified financial services professional. Do not use the Software to replace, substitute, or provide professional financial advice or judgment.

BY ACCESSING OR USING THE SOFTWARE, YOU ACKNOWLEDGE THAT THE SOFTWARE IS NOT DESIGNED OR INTENDED TO SUPPORT ANY USE IN WHICH A SERVICE INTERRUPTION, DEFECT, ERROR, OR OTHER FAILURE OF THE SOFTWARE COULD RESULT IN THE DEATH OR SERIOUS BODILY INJURY OF ANY PERSON OR IN PHYSICAL OR ENVIRONMENTAL DAMAGE (COLLECTIVELY, “HIGH-RISK USE”), AND THAT YOU WILL ENSURE THAT, IN THE EVENT OF ANY INTERRUPTION, DEFECT, ERROR, OR OTHER FAILURE OF THE SOFTWARE, THE SAFETY OF PEOPLE, PROPERTY, AND THE ENVIRONMENT ARE NOT REDUCED BELOW A LEVEL THAT IS REASONABLY, APPROPRIATE, AND LEGAL, WHETHER IN GENERAL OR IN A SPECIFIC INDUSTRY. BY ACCESSING THE SOFTWARE, YOU FURTHER ACKNOWLEDGE THAT YOUR HIGH-RISK USE OF THE SOFTWARE IS AT YOUR OWN RISK.