SQL Server to Aurora PostgreSQL conversion with AI agents for AWS DMS

In this post, we show you how to use AI agents with AWS Database Migration Service (AWS DMS) Schema Conversion to orchestrate SQL Server to Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) schema conversion workflows through natural language. We cover how the conversion engine processes Transact-SQL (T-SQL) internally, how the agent drives the workflow interactively, and how you interpret and resolve CRITICAL action items using the decision frameworks for common incompatibilities.

To generate real examples, we used AdventureWorks2022 as a representative SQL Server database. It contains stored procedures, triggers, common language runtime (CLR) assemblies, XML schema collections, hierarchyid columns, and full-text search objects that exercise the full range of conversion scenarios.

Key components

To use AI agents for AWS DMS Schema Conversion, you work with two components:

AWS MCP Server: A managed remote Model Context Protocol (MCP) server that gives supported AI clients authenticated access to AWS services and AWS skills. Standard charges apply to AWS resources that the agent creates or uses.

The dms-schema-conversion skill: A curated package of DMS-specific instructions and reference material that the agent loads before starting. The skill supplies API patterns, operation sequencing, system-schema exclusions, and service best practices intended to reduce common errors and trial-and-error. The skill itself has no additional AWS charge.

For setup instructions, see Using AI agents with DMS Schema Conversion in the AWS DMS User Guide.

We assume you have familiarity with DMS Schema Conversion basics and SQL Server to PostgreSQL migration concepts. For background, see Accelerate database modernization with agentic AI in AWS DMS Schema Conversion.

Part 1: How the conversion engine processes T-SQL

When the agent runs a schema conversion, DMS Schema Conversion processes each object in the conversion scope through a four-step decision pipeline. The pipeline has two paths that produce output differently. The deterministic rule engine (Steps 1–3) applies extensively tested, version-locked transformation rules that produce functionally equivalent output for every supported pattern. These rules produce identical results on every run.

When generative AI-assisted conversion (Step 4) is enabled, DMS Schema Conversion can attempt additional code constructs that the rule-based converter cannot handle. The following four stages are a practical workflow model rather than a description of private service implementation details.

Step 1 – Parse. The SQL Parser builds an abstract syntax tree (AST) from the source T-SQL. If parsing fails, the engine generates action item 9998.

Step 2 – Resolve. The Reference Resolver links object references across schemas and databases. If references cannot be resolved, the engine generates action item 9997.

Step 3 – Transform. The Rule-Based Converter applies deterministic transformation rules. If no rule exists for a construct and the object is in scope for AI-assisted conversion, it passes to Step 4.

Step 4 – Generate. If you enabled generative AI, the engine attempts AI-assisted conversion and validates the output parses as valid PL/pgSQL. If syntax validation passes, the output is included and an INFO action item identifies that generative AI produced the code and that customer review is required. Syntax validation doesn’t establish semantic equivalence. If validation fails or the model can’t produce output, the output retains the unsupported source and the action item. That action item comes from the rule-based conversion phase.

For more information about action item codes and how to interpret them, see Understanding conversion assessment report metrics in the AWS DMS User Guide. The complete list of action item codes for your specific conversion is available in the exported CSV assessment report.

When the converter cannot transform a statement, it comments the original T-SQL with an action item code annotation (for example, [9996 – Severity CRITICAL]) and preserves the output structure with NULL placeholders or empty blocks. This allows the converted procedure to compile syntactically even though it produces no meaningful results for the unsupported section. Action item annotations follow the format /* [code – Severity LEVEL – description] … */, which distinguishes engine-generated comments from pre-existing source comments.

Validation boundary: The engine validates that AI-generated output is syntactically valid PL/pgSQL. It doesn’t validate semantic correctness, or whether the converted code produces the same results as the source. Because a language model produces AI-generated procedures, functionally test them against known inputs before production use. Deterministic rule-engine output is more predictable, but you should still validate any procedure your application depends on.

Action item severity and conversion complexity

The following severity levels apply to deterministic rule-engine output. If generative AI successfully converts a statement, DMS Schema Conversion assigns an INFO action item identifying AI-generated code and the need for customer review. In this context, INFO identifies provenance and review requirements. It doesn’t mean functional testing can be skipped.

LOW doesn’t mean “no action.” For example, the engine drops INCLUDE columns from non-clustered indexes because PostgreSQL doesn’t support them in the same form. This removes the covering index benefit. Queries that relied on index-only scans might now require additional heap fetches to retrieve non-indexed columns. Validate query performance for any LOW items that affect indexes. Query performance should be validated regardless of action item severity, especially for queries on the hot execution path.

CRITICAL items require investigation and vary widely in effort. Architectural incompatibilities require a redesign on your side: for example, hierarchyid.GetAncestor() has no direct PostgreSQL equivalent. In other cases, syntax you expect to be supported is not recognized. This might be a product bug rather than an architectural problem, and you can open an AWS Support case so it can be investigated. Also investigate unresolved dependencies and newly introduced syntax.

Regardless of severity level, validate any procedure that your application depends on for correctness through functional testing on the target.

Verifying conversion correctness

A converted procedure that compiles is a strong starting point, but compilation confirms syntax rather than logic. As with any schema migration, verify behavior before you rely on it in production. For any converted code object, verify correctness through these approaches:

Automated regression testing:

• Run existing unit tests or integration tests against the converted procedures on the target database. If your SQL Server environment has a test suite, adapt and port it to PostgreSQL. Account for schema and object renaming, conversion settings and transformation rules, data type mappings, changed procedure or function signatures, output parameters, and result-set handling. Use the adapted suite as a first validation step.
• For procedures that return result sets, capture output from both source and target for the same input parameters and diff the results.

Action item severity is a starting point, not a guarantee:

• For rule-based conversion, INFO and LOW items are deterministic rule-based transformations with well-defined behavior. These carry lower risk but should still be included in regression testing, particularly for performance-sensitive queries.
• HIGH and CRITICAL items involve functional changes or manual remediation. Treat all AI-assisted conversions as requiring human review, regardless of whether they compile successfully.

What successful conversion means in practice:

• The agent and DMS Schema Conversion validate syntax, not semantics. A procedure marked as converted produces valid PostgreSQL that compiles. Confirm it matches the original SQL Server behavior for your edge cases before promoting it.
• For critical workloads, validate converted procedures against known input and output pairs before promoting to production.

Part 2: The agent workflow in practice

The agent handles multi-step orchestration (import metadata, convert, export, report) and presents results that support engineering decisions.

Interaction 1: Browsing source metadata

Prompt: “Show me the databases and schemas in my source database.”

The agent imports source metadata, waits for completion, and lists the discovered databases and schemas:

Agent response:

Import complete. I found the following databases:
- AdventureWorks2022
- DB1
- master (system)
- model (system)
- msdb (system)
- tempdb (system)

AdventureWorks2022 contains these schemas:
- dbo
- HumanResources
- Person
- Production
- Purchasing
- Sales

Would you like to drill into a specific schema, or proceed with converting a schema to Aurora PostgreSQL?

The agent manages the operation sequence and asynchronous status checks, then formats the results for review. Because the agent is flexible in how it presents data, you can ask for the output in whatever format is most convenient, such as a summary table, a bulleted list, or raw JSON for downstream tooling.

Interaction 2: Running conversion and interpreting results

Prompt: “Convert the AdventureWorks2022.dbo schema to Aurora PostgreSQL.”

The agent scopes the selected schema, starts conversion, monitors completion, exports the assessment report to Amazon Simple Storage Service (Amazon S3), and presents a summary:

Agent response:

Conversion complete for AdventureWorks2022.dbo.

Summary:
- Stored Procedures: 7 total (1 auto-converted, 6 with action items)
- Scalar Functions: 10 converted (0 action items)
- Table-valued Funcs: 1 converted (0 action items)
- Tables: 3 total (1 auto-converted, 2 with simple action items - DateTime defaults)
- Types/Domains: 6 converted (0 action items)

CRITICAL action items in stored procedures:
- uspGetEmployeeManagers: hierarchyid method not supported (7708, 9996)
- uspGetManagerEmployees: hierarchyid method not supported (7708, 9996)
- uspLogError: XACT_STATE() and CURRENT_USER() not supported (7811)
- uspSearchCandidateResumes: full-text search and EXECUTE AS not supported (7644, 7811, 9996)

Assessment reports exported to S3:
- PDF: s3://amzn-s3-demo-bucket/sqlserver-to-aurora-pg/2026-08-05-12-09-21.pdf
- CSV: s3://amzn-s3-demo-bucket/sqlserver-to-aurora-pg/2026-08-05-12-09-21.zip

What would you like to do next? I can help resolve these action items, export the converted scripts to S3, or apply them to your target database.

The agent orchestrated five sequential operations (build rules, start conversion, wait, export assessment, download and parse CSV) and distilled the results into a decision prompt.

Distinguishing rule-engine from AI-assisted conversions: After conversion, use the exported assessment report to identify objects marked as converted with generative AI and add those objects to your human-review and functional-testing plan. A candidate recommendation before conversion is not proof that generative AI converted the object.

Note: When the client-side agent (Kiro, Claude Code, Cursor) helps resolve remaining action items after export, those modifications happen outside DMS service control and aren’t tracked by the assessment report.

Interaction 3: Resolving an action item with agent assistance

Prompt: “Help me fix the uspLogError CRITICAL action items.”

The agent retrieves both source and target definitions, identifies the commented-out sections, and explains the issue:

Agent response:

The assessment report lists 2 complex action-item occurrences for uspLogError:

1. XACT_STATE() [code 7811]
PostgreSQL has no direct equivalent to SQL Server XACT_STATE(). An unhandled PostgreSQL error aborts the current transaction, while a PL/pgSQL EXCEPTION block runs as a subtransaction and rolls back changes made within that block before entering the handler.
Recommendation: redesign this branch for PostgreSQL transaction semantics. Preserve any warning, logging, or early-return behavior that the application requires; do not delete the surrounding block without review.

2. CURRENT_USER() [code 7811]
PostgreSQL exposes current_user as a special SQL value without parentheses. Replace CURRENT_USER() with current_user, or use session_user if the requirement is to record the original session identity rather than the active role. Validate the intended security semantics.

The source INSERT also relies on SQL Server ERROR_* context. If you rewrite the error path manually, capture diagnostics inside the PL/pgSQL EXCEPTION handler with GET STACKED DIAGNOSTICS, then pass the required values to the logging procedure or log them directly. PostgreSQL SQLSTATE values do not map one-to-one to SQL Server error number, severity, state, procedure, and line fields.

Would you like me to show a reviewed PostgreSQL implementation and a source-to-target test plan?

This is the human-in-the-loop decision flow: the agent identifies the issue, explains why it cannot be auto-converted, provides the PostgreSQL equivalent concept, and asks whether to proceed. The customer’s designated migration engineer, database engineer, senior software engineer, application owner, or technical reviewer makes the final engineering decision.

A note on the XACT_STATE recommendation: The agent suggests removing the XACT_STATE check but removing only the unsupported function call isn’t sufficient. SQL Server and PostgreSQL expose different transaction and exception semantics, so the surrounding branch must be reviewed and intentionally redesigned.

In this example, the XACT_STATE branch prints a warning and returns early. In other procedures, similar branches can write audit records, send alerts, or conditionally roll back. In PostgreSQL, a PL/pgSQL block with an EXCEPTION clause rolls back database changes made inside that block before the handler runs. An eventual rollback of the outer transaction can still roll back ordinary log-table inserts.

Preserve required warning, logging, and return behavior intentionally. Capture error details inside the applicable PL/pgSQL EXCEPTION handler, decide whether current_user or session_user matches the original identity requirement, and pass diagnostic values to a logging routine when appropriate. Add tests for both handled and unhandled error paths.

For repeatable guidance, create an agent steering file (a project-specific instruction file that customizes the agent’s behavior). For example, add a rule such as “always preserve error logging when removing XACT_STATE checks.” See the MCP Server documentation for configuration details.

Note: When the agent proposes code changes to resolve action items (as in Interaction 3), this is client-side AI operating outside DMS Schema Conversion. Service-side generative AI-assisted conversions are marked in the post-conversion assessment report. Agent-suggested rewrites are not recorded there as service-side conversions. Treat both as generated code that requires human review and functional testing.

Part 3: Decision frameworks for complex action items

When the agent presents complex action items from the assessment report, ask: “What are my options for resolving this action item?” The following frameworks cover common patterns.

Note: Resolving a complex action item is rarely local. Before adopting any target representation, run a database-wide dependency review: inventory every dependent table, view, procedure, function, trigger, index, ETL transformation, and application query. Apply the choice consistently, and validate database-level integrity. When rewriting recursive logic, use path-based cycle detection or the PostgreSQL CYCLE clause to prevent cycles. Add a depth limit only when it represents a tested business rule. Don’t use an arbitrary WHERE level < N condition as the primary cycle safeguard.

Full-text search (FREETEXT, CONTAINS)

Relevant codes depend on the exact construct. In this sample, the converter reports internal converter errors (code 9996) in the full-text join sections, and it flags the CONTAINSTABLE pseudo-columns RANK and KEY as unresolved objects (code 9997). Use the exported CSV as the source of truth for the codes attached to each occurrence, because the specific codes vary by predicate and converter result.

Decision: Treat these mappings as candidate approaches and choose a target based on required semantics, not only syntax. PostgreSQL full-text search can support tokenization, ranking, dictionaries, and Boolean queries, but SQL Server ranking and thesaurus behavior are not one-to-one. Build representative semantic and relevance tests. Consider Amazon OpenSearch Service when the application requires advanced relevance tuning, analytics, or search-service scale.

Error handling (XACT_STATE, ERROR_*)

Action item code in this sample: 7811

Decision: Capture diagnostics in the PL/pgSQL EXCEPTION handler that catches the error. If you retain a separate logging procedure, pass the captured values into it. Don’t assume SQL Server error number, severity, state, procedure, and line values have direct PostgreSQL equivalents, and don’t remove an XACT_STATE branch without preserving required business behavior.

CLR assemblies

Action item codes for CLR-related objects depend on the exact construct and converter result. Use the exported assessment CSV for the affected object. Don’t treat a generic converter code as CLR-specific.

Decision: Place pure relational work in PostgreSQL when appropriate and move external side effects to an application or event-driven service. Aurora PostgreSQL can invoke Lambda through the aws_lambda extension using synchronous or asynchronous invocation, but review AWS Identity and Access Management (IAM), networking, timeout, retry, and transaction implications before adopting that pattern. See Invoking an AWS Lambda function from an Aurora PostgreSQL DB cluster.

Part 4: Operating at scale

For portfolios with many schemas or databases, an agent can reduce operational overhead by sequencing asynchronous requests, polling status, exporting reports, and presenting consistent summaries. Plan concurrency around migration-project isolation and AWS DMS quotas rather than assuming unlimited parallelism.

Service constraints that affect planning

• Serialize dependent operations within a migration project. Metadata import, assessment, conversion, and export operations are asynchronous. Poll each request to a terminal state before starting an operation that depends on its output, and avoid launching conflicting changes on the same project.
• AWS DMS has a default quota of 50 migration projects per AWS account per AWS Region. Confirm current quotas before planning a portfolio and request an increase where the quota is adjustable. See Quotas for AWS Database Migration Service.
• The first launch of DMS Schema Conversion for a project can take up to five minutes while AWS DMS starts a schema conversion instance and reads metadata. Later, access is typically faster. See Managing migration projects in DMS Schema Conversion.

Maximizing throughput

To increase throughput safely:

• Use separate migration projects for independent source databases when that separation matches your network, credentials, ownership, and artifact-retention model.
• Run parallel agent sessions only against independent projects, and make sure that each session uses the intended AWS account, Region, and least-privilege credentials.
• Use bounded concurrency with exponential backoff and jitter for throttled AWS DMS API requests. The documented API quota uses a burst capacity of 200 requests and a refresh rate of 8 requests per second. Don’t rely on a fixed delay or session-count threshold.

Monitor request rates, operation status, and project ownership centrally. Parallel sessions don’t remove service quotas or the need to coordinate changes to shared data providers, secrets, IAM roles, S3 locations, and target databases.

Cross-database consistency: The rule-based converter is designed to produce consistent, repeatable output for supported patterns. Generative AI-assisted output and client-side agent recommendations still require review. Establish an approved target pattern for shared objects and apply it consistently across databases.

Cost model

DMS Schema Conversion is available at no additional charge, but the surrounding workflow can use billable services. Review the current AWS DMS pricing and pricing for every supporting service in your design.

• AI coding agent: Pricing depends on the selected tool, plan, model, and usage. Consult the provider’s current pricing and enterprise terms.
• AWS Secrets Manager: Charges are based on secrets stored and API calls. Secret reuse and rotation affect the estimate. Don’t assume every project owns exactly two secrets. See AWS Secrets Manager pricing.
• Amazon S3: Standard storage and request charges apply to assessment reports, exported SQL scripts, and retained versions according to the selected storage class and lifecycle policy.
• Amazon CloudWatch Logs: Standard ingestion, storage, analysis, and data-transfer charges can apply to schema conversion logs according to your configuration and retention period.

Estimate cost with current regional pricing and your expected secret count, API-call volume, log retention, and S3 lifecycle. During cleanup, delete only resources created exclusively for the migration and only after explicit approval. Verify that secrets, IAM roles, data providers, instance profiles, S3 objects, and target schemas are not shared or production dependencies before changing or deleting them.

Conclusion

The agent handles orchestration and explains conversion gaps, but resolving CRITICAL action items requires PostgreSQL architectural expertise. Validate all converted code objects, whether produced by the deterministic rule engine or AI-assisted conversion, through review and regression testing before production deployment. A procedure that compiles successfully is not guaranteed to produce semantically identical results to the original.

To get started, connect your IDE to the AWS MCP Server and load the AWS DMS Schema Conversion skill. For setup instructions, see Using AI agents with DMS Schema Conversion in the AWS DMS User Guide.

About the author