DMS Automates SQL Server to PostgreSQL Multi-Result Set Migrations

Migrating from SQL Server to PostgreSQL can feel like navigating a swamp with a broken paddle—especially when your stored procedures return multiple result sets. But Google Cloud’s Database Migration Service (DMS) is here to be your trusty capybara guide, automating the trickiest parts of the translation.

In a recent deep dive, Google Cloud engineers broke down how DMS handles the architectural gap between SQL Server’s native multi-stream output and PostgreSQL’s cursor-based approach. If you’re facing hundreds of procedures, manual rewriting is a non-starter. DMS steps in with a smart decision matrix that keeps your logic intact while adapting to PostgreSQL’s deterministic style.

The Core Conversion Strategy

DMS looks at two key things: how many result sets your procedure returns, and whether it uses a scalar RETURN value. Based on that, it decides whether to translate to a PostgreSQL PROCEDURE or FUNCTION.

• Scenario A: One result set or just a scalar return → Stored Procedure with an INOUT refcursor.
• Scenario B: Multiple result sets or a mix of result sets and scalar return → Function returning SETOF refcursor, with the scalar value appended as a separate cursor.

This approach mirrors the natural flow of a swamp channel—each result set gets its own clear path, no muddled currents.

Inside the Automation: A Healthcare Example

Consider a master procedure sp_GetPatientSummary that calls child procedures for lab results and doctor visits. Depending on conditions, it can return up to four result sets plus a status integer. DMS translates this into a PL/pgSQL function that opens cursors sequentially, and even creates a dedicated return_value cursor to carry the scalar status.

The translated code is clean and deterministic, with each cursor explicitly managed. For the simpler child procedure that returns one result set, DMS generates a standard procedure with an INOUT refcursor—simple and effective.

How DMS Counts Result Sets

DMS uses a sophisticated pre-processing engine to analyze your procedures. It scans for direct SELECT statements, flags dynamic constructs like loops or conditionals, and builds a call graph to propagate result set counts across the entire procedure network. A Depth First Search (DFS) algorithm then ensures accurate counts even with nested calls and recursion.

This graph-based approach is like mapping the entire swamp before you start paddling—no surprises, no dead ends.

Testing Your Migrated Code

Once migrated, testing requires wrapping your calls in a transaction block because PostgreSQL cursors are transaction-bound. You’ll execute the function, fetch from each portal sequentially, and finally grab the return_value cursor. It’s a bit different from SQL Server, but once your team gets the hang of it, it’s smooth sailing.

For more on PostgreSQL and cloud database options, check out our Google Cloud review and Neon Database review.

Summary

Google Cloud’s DMS takes the guesswork out of structural transformations, preserving your business logic while adapting to PostgreSQL’s execution model. It changes how you interact with results, but with a little preparation, your migration can be as smooth as a capybara gliding through calm waters.

Ready to start? Google Cloud offers $300 in free credits to get you going. Start building for free.

Original announcement published on Google Cloud.