Migrating to Cloud SQL: Homogeneous and Heterogeneous Migrations

A database is like a giant digital filing cabinet that stores all the information your application needs — user accounts, orders, inventory, etc. Cloud SQL is Google’s managed database service that runs MySQL, PostgreSQL, and SQL Server for you, handling backups, patching, and scaling automatically. Migration is the process of moving that filing cabinet from its old location (on-premises, meaning in your own office or data centre) to Google Cloud without losing any files or breaking your application.

There are two main types of migration tested in the PCDE exam: homogeneous and heterogeneous.

Homogeneous migration means you are moving the same type of database engine. For example, you have a MySQL database running on a server in your office, and you want to move it to Cloud SQL (which also uses MySQL). The database engine — the software that reads, writes, and organises data — is the same, so the data itself does not need to change. Think of it like copying a Word document from your old laptop to a new laptop. You open the same Word file; it works perfectly because both laptops have Word installed. In a homogeneous migration, the schema (the structure of your tables, like column names and data types) stays identical. The main challenge is transferring the large amount of data without downtime — meaning your application keeps working while the move happens. Google provides a tool called Database Migration Service (DMS) that handles this for MySQL and PostgreSQL. It uses a method called continuous replication, where it copies all existing data first, then keeps copying any new changes while your application is live, and finally switches over in a few seconds so users barely notice.

Heterogeneous migration means you are moving from one database engine to a different one. Common examples include moving from Oracle or Microsoft SQL Server to Cloud SQL for MySQL or PostgreSQL. Here, the database engines are not identical — they speak different languages (SQL dialects) and organise data differently. For instance, Oracle uses PL/SQL for stored procedures (pre-written code blocks that run inside the database), while Cloud SQL for MySQL uses a different procedural language. The data types may also differ: Oracle has a 'NUMBER' type that can hold decimals, while MySQL uses 'DECIMAL' or 'FLOAT'. You cannot just copy the data; you must transform it. This is like moving your Word document from a PC to an iPad: the document itself might open, but the formatting, fonts, and macros may not work, so you need to convert it to a compatible format.

For heterogeneous migrations, the process is more complex:

Why does this matter for the PCDE exam? The exam will present scenarios where you must choose the correct migration strategy, tool, and steps. They love testing whether you understand that DMS can handle homogeneous migrations with minimal downtime but that heterogeneous migrations require schema conversion and more planning. They also test terms like downtime (the period when your app is unavailable), replication (copying data continuously), and schema (the structure definition). You need to know that for homogeneous, you can use DMS with continuous replication for near-zero downtime. For heterogeneous, you typically use a one-time dump and load method with a maintenance window, or tools like Cloud SQL Proxy for secure connections during transfer.

Imagine you work for a mid-sized retail company called "ShopEase" that runs an e-commerce website. For years, their product catalogue and customer orders have been stored in an on-premises Microsoft SQL Server database. The company decides to move to Google Cloud to reduce hardware costs and improve scalability. Your task: migrate this SQL Server database to Cloud SQL for PostgreSQL. This is a heterogeneous migration because SQL Server and PostgreSQL are completely different database engines.

Here is exactly what you do as an IT professional:

First, you gather information about the source database. You run Google’s Database Migration Assessment tool against the SQL Server instance. This tool scans all the tables, stored procedures, functions, triggers, and data types. It produces a report that flags issues: for example, SQL Server uses a data type called 'MONEY' which does not exist in PostgreSQL. The report suggests replacing it with 'NUMERIC(19,4)'. It also notes that SQL Server stored procedures use T-SQL syntax, which must be rewritten as PL/pgSQL for PostgreSQL. You share this report with the development team so they can start rewriting the stored procedures.

Next, you set up the target Cloud SQL for PostgreSQL instance. You choose a machine size (say, 8 vCPUs and 32 GB RAM) to match the expected workload, enable automated backups, and configure a private IP address so that it connects securely over Google’s internal network (not over the public internet). You also set up a Cloud SQL Proxy to allow your local development machines to connect for testing.

Then comes schema conversion. You use the Database Migration Service’s conversion feature (or a third-party tool) to translate the SQL Server schema into PostgreSQL-compatible DDL (Data Definition Language — the commands that create tables and indexes). You review the converted schema, manually adjust any remaining issues like index names or default values, and then run the converted SQL commands against the Cloud SQL instance to create the empty tables.

Now for data migration. Because the source is SQL Server and the target is PostgreSQL, continuous replication via DMS is not natively supported for this pair (as of the PCDE exam scope). You must use a batch approach. You use a tool like 'pgloader' or a custom script that connects to both databases, reads rows from SQL Server in chunks, transforms data types on the fly (e.g., converting 'MONEY' to 'NUMERIC'), and inserts them into Cloud SQL. You schedule this during a maintenance window — say, 2 AM on a Sunday when traffic is lowest. Before the migration, you put the application in maintenance mode (a simple webpage saying “We’ll be right back”) to prevent new orders from being written to the old database.

After the data is copied, you validate. You run a script that counts rows in each table on both databases to ensure they match. You spot-check a few orders to confirm that dates, prices, and customer names are correct. Your QA team tests the application against the new PostgreSQL database in a staging environment first.

Finally, you cut over. You update the application’s connection string (the configuration that tells the app where the database is) from the old SQL Server address to the Cloud SQL private IP. You restart the application, take the maintenance page down, and monitor logs for errors. You keep the old database running for a week as a fallback in case something goes wrong. This real-world scenario shows that heterogeneous migration is a multi-step, manual, and careful process — not a simple button click.