GCP – Supercharge SQL Server to PostgreSQL migrations with AI-powered code conversion in Database Migration Service

As organizations continue to prioritize cloud-first strategies to accelerate innovation and gain competitive advantage, legacy databases  remain a bottleneck by hindering modernization and stifling growth with unfriendly licensing, complex agreements, and rigid infrastructure. 

That’s why this week at Google Cloud Next, we’re announcing that Database Migration Service (DMS) is extending its comprehensive database modernization offering to support SQL Server to PostgreSQL migrations, enabling you to unlock the potential of open-source databases in the cloud and build modern, scalable, and cost-effective applications.

While holding great benefits, migrating from SQL Server to a modern, managed PostgreSQL offering like AlloyDB or Cloud SQL can be a highly complex task. Even though SQL Server and PostgreSQL both adhere to SQL standards, they still have fundamental differences in their architectures, data types, and procedural languages which require deep expertise in both technologies for a successful migration.

For example, SQL Server’s T-SQL syntax and built-in functions often require manual translation to PostgreSQL’s PL/pgSQL. Data type mappings can be intricate, as SQL Server’s DATETIME precision and NVARCHAR handling differ from PostgreSQL’s equivalents. 

Furthermore, features like SQL Server’s stored procedures, triggers, and functions often necessitate significant refactoring to align with PostgreSQL’s implementation. This requires deep knowledge in both database systems, as well as specific migration expertise that developers typically don’t possess, and it requires hours of painstaking work, even with the benefit of an automated conversion tool.

Simplifying database modernization with Database Migration Service 

DMS is a fully-managed, serverless cloud service that offers a complete set of capabilities to simplify database “lift and shift” migrations and database modernization journeys.

For modernization efforts, DMS offers an interactive experience that includes data migration, as well as schema and resident code conversion, all in the same powerful user interface. For data migration, it offers high-throughput database initial loads followed by low-latency change data capture to reduce downtime and minimize the impact on business critical applications.

Announcing SQL Server to PostgreSQL migration

The new SQL Server to PostgreSQL.migration experience supports the migration of both self-managed and cloud-managed SQL Server offerings to Cloud SQL for PostgreSQL and AlloyDB to accelerate your database modernization journey. Similar to the existing database modernization offerings, this new experience features a high-throughput initial load of the database followed by seamless change data capture (CDC) replication to synchronize the SQL Server source and PostgreSQL destination, all while the production application is up and running to ensure minimal business interruption.

For SQL Server schema and code conversion, DMS offers a fast, customizable algorithmic code conversion engine that automates the conversion of most of the database schema and code to the appropriate PostgreSQL dialect, leaving minimal manual conversion work for the user to complete.  

The algorithmic conversion engine maps the source database data types and SQL commands to the most suitable PostgreSQL ones, and even refactors complex source features which have no direct PostgreSQL equivalents to achieve the same functionality using available PostgreSQL capabilities. Algorithmic engines are extremely accurate, by nature, for the scenarios they are programmed for. However, they’re limited to just those scenarios, and in real-life usage some of the database code will consist of scenarios that can’t be anticipated.

For these situations, we’re pushing the boundaries of automated database modernization with the introduction of the Gemini automatic conversion engine. This new engine automatically augments the output of the algorithmic conversion, further automating the conversion tasks and reducing the amount of remaining manual work. It also provides a comprehensive conversion report, highlighting which parts of the code were enhanced, why they were changed, and how they were converted.

Instead of spending time researching suitable PostgreSQL features and fixing conversion issues, you can simply review the Gemini recommendations in the conversion report and mark the conversion as verified. Reviewing the completed conversions instead of having to research and fix issues can significantly reduce the manual migration effort and speed up the conversion process. 

To further empower SQL Server DBAs, DMS offers a Gemini conversion assist with targeted yet comprehensive SQL Server to PostgreSQL conversion training. Gemini analyzes both the source and the converted code and explains the conversion rationale, highlighting the chosen PostgreSQL features, why they were used, and how they compare to the SQL Server ones. It can then optimize the migrated code for better performance and automatically generate comprehensive comments, for better long-term maintainability. 

At Google Cloud, we’ve been working closely with customers looking to modernize their database estate. One of them is Wayfair LLC, an American online home store for furniture and decor.

“Google Cloud’s Database Migration Service simplifies the process of modernizing databases. Features like Change Data Capture to reduce downtime and AI-assisted code conversion help evolve our database usage more efficiently. This makes the migration process less manual and time-consuming, allowing teams to spend more time on development and less on infrastructure,” said Shashank Srivastava, software engineering manager, Data Foundations, Wayfair.

How to get started

To start your Gemini-powered SQL Server migration, navigate to the Database Migration page in the Google Cloud console, and follow these simple steps:

1. Create your source and destination connection profiles, which contain information about the source and destination databases. These connection profiles can later be used for additional migrations.

2. Create a conversion workspace that automatically converts your source schema and the code to a PostgreSQL schema and compatible SQL. Make sure you choose to enable the new Gemini-powered conversion workspace capabilities.

3. Review the converted schema objects and SQL code, and apply them to your destination Cloud SQL for PostgreSQL or AlloyDB for PostgreSQL instance.

4. Create a migration job and choose the conversion workspace and connection profiles previously created. 

5. Test your migration job and get started whenever you’re ready.

To learn more about how Database Migration Service can help you modernize your SQL Server databases, please review our DMS documentation and start your migration journey today.