ReleaseBytes
gcp Google Cloud Blog ·

Google Cloud DMS automates SQL Server multi-result set stored procedure migration

blogdatagcpengineerhealthcare
feature

Google Cloud's Database Migration Service (DMS) now automates the translation of SQL Server stored procedures that return multiple result sets to PostgreSQL. This feature addresses a significant challenge for large-scale database migrations, allowing engineers to avoid manual rewriting of complex procedures. The tool maps SQL Server's native streaming to PostgreSQL's explicit cursor management, supporting both single and multiple result set scenarios. This enhancement is particularly relevant for applications with intricate data retrieval logic, such as in healthcare reporting, and requires developers to adapt their data access patterns to handle sequential cursor fetching.

  • →DMS conversion strategy for multi-result sets
  • →Stored procedure translation using INOUT refcursor
  • →Function translation for multi-set scenarios with SETOF refcursor
  • →Introduction to migrating multi-result set stored procedures
  • →Automated translation of SQL Server to PostgreSQL cursor logic
Features (3) ›
  • DMS conversion strategy for multi-result sets

    DMS analyzes stored procedures for the number of result sets and the presence of scalar RETURN values to determine the translation strategy. Procedures with a single result set or only a scalar RETURN value are converted to PostgreSQL STORED PROCEDURES using INOUT refcursor parameters. Procedures with multiple result sets or a combination of result sets and scalar RETURN values are converted to PostgreSQL FUNCTIONS returning SETOF refcursor.

  • Stored procedure translation using INOUT refcursor

    For child procedures that yield a single result set, DMS generates standard PostgreSQL PROCEDURES. These procedures utilize an explicit INOUT refcursor parameter to safely pass the result set pointer back to the caller.

  • Function translation for multi-set scenarios with SETOF refcursor

    For master routines and complex child routines producing varying arrays of output, DMS transforms them into PL/pgSQL Functions returning a SETOF refcursor. These functions build cursors sequentially and pass the scalar integer back via a dedicated 'return_value' cursor.

Enhancements (2) ›
  • Automated translation of SQL Server to PostgreSQL cursor logic

    DMS automates the translation from SQL Server's tabular data stream protocol to PostgreSQL's distinct execution protocol using cursors. A healthcare reporting scenario is presented, demonstrating the conversion of a master procedure calling child procedures, which can collectively return multiple result sets and a status integer.

  • Programmatic handling of multi-cursor output

    Applications integrating migrated routines must adapt to fetch data sequentially from an array of cursor references received from PostgreSQL. DMS isolates scalar return values into a 'return_value' cursor at the end of the execution stack, which aids in accurate parsing of multi-layered response arrays.

Notes (1) ›
  • Introduction to migrating multi-result set stored procedures

    This article details how Google Cloud's Database Migration Service (DMS) automates the conversion of SQL Server stored procedures with multiple result sets to PostgreSQL, a task that is often manual and time-consuming. The migration to PostgreSQL is motivated by its performance, availability, and AI capabilities.

Read the original announcement →

https://cloud.google.com/blog/products/databases/automating-postgres-translations-with-database-migration-service/

Related releases