Microsoft Workloads on AWS

Fine-tune SQL Server to Aurora PostgreSQL conversions with AWS Transform and Kiro

Introduction

When customers modernize SQL Server databases with AWS Transform, most standard database objects convert automatically. A smaller set of complex objects needs human validation, and resolving them is often where modernization projects stall.

In this blog post, we show how to resolve the Microsoft SQL Server objects that AWS Transform flags for human review, such as stored procedures with proprietary Transact-SQL patterns. You fix them in your local integrated development environment (IDE) with Kiro and the AWS Transform Model Context Protocol (MCP) server, and then deploy the corrected schema to Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL).

This post builds on Modernize Microsoft SQL Server databases to Aurora PostgreSQL using AWS Transform, which walks through the full Offline Source workflow, from assessment to deployment.

Working in a local IDE can help you in two ways. First, you review the converted PostgreSQL code with full context. That context includes application and dependency code that you didn’t submit to AWS Transform, which can help you and your coding assistant make better-informed fixes. Second, you iterate locally while AWS Transform validates each round, which can help you reach a clean validation in fewer passes.

You can use the coding assistant that you prefer. Kiro uses the AWS Transform Kiro Power, and Claude Code, Codex, and Cursor use the AWS Transform agent plugin. Any MCP-compatible client can also connect through the AWS Transform MCP server. In this post, we use Kiro with the MCP server, and we wrote each step so that you can follow it manually, even if you haven’t used Kiro or MCP before.

Solution overview

After AWS Transform converts and validates your schema, it reports two kinds of results. Validation scores measure conversion completeness for each object group. AWS Transform also groups related issues that share a root cause into a single cluster and assigns each cluster a severity, as shown in Figure 1.

AWS Transform validation report showing scores per object group and issue clusters by severity

Figure 1: Scoring methodology

Severity Typical cause Impact
Critical Systemic or unfixable issue that requires re-migration Blocks production deployment
High Runtime failure, missing objects, or schema errors Must be resolved before production
Medium Behavioral difference Should be reviewed and addressed
Low Cosmetic or unlikely issue Advisory, low risk

AWS Transform then uses the cluster severities to grade the production readiness of the converted schema, as shown in the following table.

Grade Condition
Not Ready for Production One or more critical clusters
Conditional No critical clusters, and one or more high clusters
Ready for Production No critical or high clusters

Because the grade follows issue severity, a high validation score doesn’t override a critical or high issue. For Conditional and Not Ready for Production results, AWS Transform provides a Schema Conversion Report with specific next steps. You can address these objects in one of two ways:

  • Web console: Review and fix conversion issues directly in the browser.
  • Local IDE: Use a coding assistant, such as Kiro, with the AWS Transform MCP server to fetch the converted artifacts into your IDE and iterate there. This option requires a coding assistant on your workstation.

MCP is an open standard that connects AI assistants to external data sources, tools, and applications in a consistent way. Instead of building a custom integration for each tool, developers can use MCP so that any compatible AI assistant can read data and call functions. The AWS Transform MCP server runs on your workstation and exposes AWS Transform actions as tools that the Kiro agent can call. For example, the agent can fetch a converted artifact or upload an iterated one.

In this post, we follow the local IDE path. The IDE handoff is a built-in step of the AWS Transform workflow. AWS Transform carries the job context over and provides a ready-made prompt, so you don’t need to write new instructions. Kiro fetches the converted artifacts, along with that context, from your AWS Transform workspace, fixes the complex objects, and uploads the results. AWS Transform then revalidates the corrected schema and redeploys it to Aurora PostgreSQL. The following diagram shows how these pieces interact (Figure 2).

AWS Transform MCP server connecting Kiro to the AWS Transform workspace

Figure 2: AWS Transform MCP server connecting Kiro to the AWS Transform workspace

The walkthrough has six steps:

  1. Install and configure the AWS Transform MCP server in Kiro.
  2. Get the IDE prompt from AWS Transform.
  3. Start the IDE session and sign in.
  4. Review the handoff summary and choose how to fix.
  5. Approve the fixes and upload for revalidation.
  6. Deploy the corrected schema to Aurora PostgreSQL.

Prerequisites

Before you begin, verify you have the following:

  • An AWS Transform job for SQL Server modernization that has completed schema conversion, validation, and an initial schema deployment, with objects that still need fixes. These include objects in critical or high issue clusters and statements that failed to deploy. For the steps to create the job, refer to our earlier blog post.
  • Kiro or another MCP-compatible IDE installed and signed in on your workstation. For steps, refer to the Kiro installation guide. You can sign in with an AWS Builder ID or another supported provider.
  • Python 3.10 or later.
  • uv, which provides the uvx command that runs the MCP server. Step 1 shows how to check for and install both tools.
  • Outbound HTTPS (TCP port 443) access from your workstation to AWS Transform. The MCP server doesn’t open any inbound ports.

Walkthrough

In this walkthrough, we install the AWS Transform MCP server in Kiro, hand off our AWS Transform job from the console to Kiro, fix the complex objects, and redeploy the corrected schema. These steps are numbered for this post and don’t map to the step numbers in the overview post.

Step 1: Install and configure the AWS Transform MCP server

In this step, we check the tools that the MCP server needs and then add the server to Kiro. For all available settings, refer to the AWS Transform MCP server documentation.

  1. Open a terminal, and check your Python version:
    python3 --version # macOS or Linuxpython --version # Windows
    The output must show version 3.10 or later. If Python is missing or older, install it from python.org.
  2. Check that uvx is available:which uvx # macOS or Linux

    where uvx # WindowsIf the command isn’t found, install uv, and then open a new terminal so that the command is on your path:

    curl -LsSf https://astral.sh/uv/install.sh | sh # macOS or Linux

    powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/install.ps1 | iex" # Windows

  3. In Kiro, open the command palette (Cmd+Shift+P on macOS, or Ctrl+Shift+P on Windows and Linux), type and choose Kiro: Open user MCP config (JSON). This opens ~/.kiro/settings/mcp.json, which applies to all your Kiro workspaces (Figure 3). For more information, refer to MCP configuration in Kiro.

Kiro mcp.json file with the AWS Transform MCP server entry under mcpServers

Figure 3: Opening the MCP config file

Note: On Windows, if the command palette doesn’t open the file, press Windows+R and enter %USERPROFILE%\.kiro\settings\mcp.json to open it directly.

  1. Add the AWS Transform MCP server under mcpServers. On macOS or Linux, use the following configuration:
    {
      "mcpServers": {
        "awslabs.aws-transform-mcp-server": {
          "command": "uvx",
          "args": ["awslabs.aws-transform-mcp-server@latest"],
          "env": {
            "AWS_REGION": "us-east-1",
            "FASTMCP_LOG_LEVEL": "ERROR"
          }
        }
      }
    }

    On Windows, use the following configuration instead:

    {
      "mcpServers": {
        "awslabs.aws-transform-mcp-server": {
          "command": "uvx",
          "args": [
            "--from",
            "awslabs.aws-transform-mcp-server@latest",
            "awslabs.aws-transform-mcp-server.exe"
          ],
          "env": {
            "AWS_REGION": "us-east-1",
            "FASTMCP_LOG_LEVEL": "ERROR"
          },
          "disabled": false
        }
      }
    }

    If the file already lists other servers, add this entry alongside them. Don’t replace existing entries.

Save the file. Kiro applies MCP configuration changes when you save and reconnects the servers. If the server doesn’t connect, quit Kiro completely, and then open it again.

In the Kiro panel, find MCP servers, and verify that awslabs.aws-transform-mcp-server shows Connected (Figure 4).

Kiro MCP servers panel showing awslabs.aws-transform-mcp-server with a Connected status

Figure 4: The AWS Transform MCP server connected in Kiro

Note: If Kiro starts the server with the filesystem root as its working directory, the server refuses to download artifacts. To fix this, add AWS_TRANSFORM_MCP_WRITE_DIR to the env block, and set it to the folder where you want downloads saved.

Step 2: Get the IDE prompt from AWS Transform

In this step, we hand off the job from the AWS Transform console to Kiro. AWS Transform generates the prompt that Kiro needs, so you don’t have to look up any IDs.

  1. In the AWS Transform console, open your SQL Server modernization job. After the first schema deployment, the job chat lists the next steps that you can take (Figure 5).

AWS Transform job chat with options to convert remaining objects in the IDE or in chat

Figure 5: Next-step options in the AWS Transform job chat after schema deployment

  1. Choose Convert remaining objects in IDE. To fix the objects in the browser instead, choose Fix remaining objects in chat.
  2. AWS Transform confirms that the converted schema artifact is ready for IDE iteration and provides a prompt to continue in your IDE. The prompt already includes your database name, job ID, and workspace ID. Copy the prompt (Figure 6).

AWS Transform job chat with a ready-made prompt to continue the conversion in the IDE

Figure 6: Prompt that AWS Transform provides to continue in your IDE

The prompt has the following form:

Continue converting remaining database objects for <database-name>
from AWS Transform job <job-ID>
in workspace <workspace-ID>.

Step 3: Start the IDE session and sign in

In this step, we paste the prompt into Kiro. If the MCP server isn’t signed in to AWS Transform yet, Kiro asks you to sign in first. From here, Kiro relays messages between you and the AWS Transform agent.

  1. In Kiro, choose File, and then choose Open Folder. Open or create a folder to use as the working directory for the conversion files.
  2. Start a new chat session, and paste the prompt from Step 2 (Figure 7).

Kiro chat session with the AWS Transform handoff prompt pasted in

Figure 7: Prompt pasted into a new Kiro chat session

  1. When Kiro asks to run an MCP tool or a command, review the request. Choose Run to approve that call, or Trust to let Kiro run it in the future without asking. You can manage trusted commands in the Kiro Trusted Commands setting.
  2. If you aren’t signed in, Kiro offers two ways to authenticate: single sign-on through IAM Identity Center, or a session cookie from the AWS Transform web app. In this post, we use IAM Identity Center (Figure 8).

Kiro offering IAM Identity Center or a session cookie to sign in to AWS Transform

Figure 8: Kiro asking how to authenticate to AWS Transform

  1. Reply with your AWS access portal URL and Region:
    Start URL: <your-AWS-access-portal-URL>
    Region: us-east-1
  2. A browser window opens. Sign in with your IAM Identity Center user. When the page asks whether to allow aws-transform-mcp to access your data, choose Allow access (Figure 9).

AWS access portal page asking to allow aws-transform-mcp to access AWS Transform

Figure 9: Allowing the AWS Transform MCP server to access AWS Transform

  1. Return to Kiro. Kiro confirms the connection and continues with the job. The server saves your sign-in to ~/.aws-transform-mcp/config.json and reloads it when Kiro restarts. When your sign-in expires, repeat this step.

Step 4: Review the handoff summary and choose how to fix

In this step, Kiro downloads the converted artifact, summarizes where the job stands, and asks how you want to approve fixes.

  1. Kiro connects to the job, downloads the converted schema artifact, and creates a working copy in your working directory. It then presents an IDE handoff summary with the validation report (Figure 10).

Kiro IDE handoff summary with the validation report for the converted schema

Figure 10: IDE handoff summary and validation report in Kiro

  1. Kiro groups the issues into clusters. Critical and high issues are in scope for fixes. Advisory items, such as type-mapping warnings, aren’t fixed unless you ask. Before it applies any fixes, Kiro asks how to approve them and which issues to fix (Figure 11).

Kiro listing issue clusters by severity and asking for autonomy mode and fix scope

Figure 11: Issue clusters and approval questions in Kiro

  1. In this post, we chose autonomous mode (Figure 12). For production code, we recommend per-fix or per-cluster mode so that you review each change before Kiro applies it.

Kiro chat with autonomous mode selected to apply all critical and high fixes

Figure 12: Choosing autonomous mode for fixes

Step 5: Approve the fixes and upload for revalidation

In this step, we review the proposed fixes and then send the corrected schema back to AWS Transform for validation.

Note: While Kiro works through the fixes, the chat might pause with Waiting on your input and show Reject, Trust, and Run for a command that Kiro wants to run. Choose Run to continue. This applies to every command and MCP tool call in this step and the remaining steps, so check the chat if progress seems to stop. To avoid repeated prompts for the same command, choose Trust.

  1. When all in-scope clusters are fixed, Kiro checks whether the custom rules need updates and removes non-ASCII characters that could corrupt the package. It then asks whether to upload the working copy for revalidation. Reply Yes.
  2. Kiro packages the corrected SQL, the custom rules, and a manifest into a ZIP file. It uploads the ZIP file and the source SQL to AWS Transform and asks the job to run validation again (Figure 13).

AWS Transform confirming that validation started for the uploaded schema artifacts

Figure 13: Validation started in AWS Transform after the upload

  1. Validation takes several minutes. Kiro polls the job and reports the results when validation finishes. In our example, overall compatibility increased from 97.9 percent to 100 percent (Figure 14).

Kiro showing revalidation results and the options to deploy or do another round of fixes

Figure 14: Revalidation results and next options in Kiro

Note: In a long session, Kiro might summarize the conversation and continue in a new chat tab. The job context carries over, so you can keep working in the new tab.

Step 6: Deploy the corrected schema to Aurora PostgreSQL

In this step, we apply the corrected schema to the target Aurora PostgreSQL cluster. You can complete this step from the Kiro chat.

  1. After revalidation, Kiro offers two options: Deploy the updated schema, or Do another round of fixes in IDE. To address advisory items before you deploy, choose another round. In our example, we replied Deploy.
  2. AWS Transform asks how to handle the objects that the earlier deployment created in the target, and Kiro relays the question (Figure 15).

Kiro relaying the AWS Transform deployment decision for the corrected schema

Figure 15: Choosing how to apply the corrected schema to the target

  1. Reply with one of the following options:
    • Yes, clean and redeploy: Drops all existing objects in the target database and redeploys the schema from scratch.
    • No, deploy over existing: Applies the updated schema on top of the existing objects. This keeps existing data but can produce errors for objects that already exist.

Warning: Yes, clean and redeploy permanently deletes all data in the target database, including any synthetic test data. Use it only on a new or empty target. If the target has data that you want to keep, take a backup first.

In our example, the target held only the partial schema from the first deployment, so we chose to clean and redeploy.

  1. Kiro reports the deployment result and the artifacts that AWS Transform produced, including the deployment report. If statements fail, ask Kiro for another round of fixes, or review the deployment report in the AWS Transform console. In our example as shown in Figure 16, schema deployed successfully to Aurora PostgreSQL cluster.

Kiro confirming the schema deployed to Aurora PostgreSQL and listing next options

Figure 16: Deployment complete in Kiro, with the next steps

  1. Choose the next step. You can reply in Kiro or in the AWS Transform job chat:
    • Generate synthetic test data: Populates the Aurora PostgreSQL cluster with test data for validation.
    • Connect source code: Starts the .NET code assessment and transformation.
    • Finish workflow – skip code track: Completes the job without code assessment or transformation.
  2. In our example, we chose Finish workflow – skip code track in the AWS Transform console, and the job status changed to Completed (Figure 17).

Completed job in the AWS Transform console

Figure 17: Completed job in the AWS Transform console

Cleaning up

To avoid ongoing charges and remove saved credentials from your workstation, clean up the following resources:

  1. If you created an Aurora PostgreSQL cluster for testing, delete it in the Amazon Relational Database Service (Amazon RDS) console. Select the cluster, choose Actions, choose Delete, and confirm. For details, refer to Deleting Aurora DB clusters and DB instances.
  2. To remove the MCP server from Kiro, delete the awslabs.aws-transform-mcp-server entry from ~/.kiro/settings/mcp.json, and save the file.
  3. To remove your saved sign-in, delete the ~/.aws-transform-mcp/config.json file.

You can keep your AWS Transform workspace for future reference.

Conclusion

In this blog post, we showed how to resolve the SQL Server objects that need human review in a SQL Server to Aurora PostgreSQL modernization from your IDE. With this approach, you can fix the objects that AWS Transform can’t convert automatically in your own IDE. AWS Transform revalidates each round against the same checks that it applies to automatically converted objects.

To get started, open the AWS Transform console, and start a SQL Server modernization job. For the steps, refer to SQL Server modernization in the AWS Transform User Guide. When the first schema deployment finishes, choose Convert remaining objects in IDE in the job chat, and follow the steps in this post.

Vikas Babu Gali

Vikas Babu Gali

Vikas Babu Gali is a Senior Specialist Solutions Architect, at Amazon Web Services, specializing in SQL Server migration, modernization and cloud database transformation. Vikas has guided many of Fortune 500 customers through mission-critical cloud transformations at scale.

Amit Kachroo

Amit Kachroo

Dr. Amit Kachroo is a Senior Applied Scientist at Amazon Agentic AI Science team, where he builds generative AI and LLM systems for code transformation. He leads the science team building transformation capabilities across different programming languages and databases. Learn more at: https://amitkac.github.io/

Nits Jeganathan

Nits Jeganathan

Nits Jeganathan is a product leader for AWS Transform, with 15 years of IT industry experience, 12 patents, and 2 publications in edge computing, system development, and application modernization. Nits is passionate about solving complex challenges, and improving customer experiences. Now he is focused on accelerating modernization of legacy applications using generative AI.