Planning a database migration is the art of moving a structured set of information from one place to another without losing, corrupting, or exposing it. It matters for the PCDE exam because, as a Professional Cloud Database Engineer, you will be the person responsible for designing and executing these moves, and a poorly planned migration can cause huge business outages and data loss.
Jump to a section
A simple way to picture Planning a Database Migration: Assessment, Connectivity, and Tools
First, you decide to move to a new house, which leads to a chain of tasks: you need to assess everything you own, figure out how to connect your old life to the new one, and choose the right tools for the move.
Planning a database migration is exactly like orchestrating a house move. You wouldn't just rent a van and start throwing boxes in. First, you do the 'assessment': you walk through every room, decide which furniture is worth taking (your data), which needs to be thrown away (old logs), and which is too fragile for the journey (sensitive customer records). You check the size of your new house – is it bigger or smaller? This is like checking if the target database has enough capacity.
Then comes 'connectivity'. This is the problem of getting your boxes from the old house to the new one without them getting lost or broken. You need a clear path – a moving truck on a road, not a bicycle on a dirt track. In IT, this means setting up a reliable network connection between your old data centre and Google Cloud, maybe through a private link (like a dedicated truck route) instead of using the public internet (like a crowded highway).
Finally, 'tools'. You wouldn't use a toothbrush to move a grand piano. You choose the right tools: a big truck for heavy furniture (a bulk data transfer service), padded blankets for fragile items (encryption for security), and a detailed inventory checklist (a database migration service). The analogy holds because each step is interdependent – a poor assessment leads to wrong tools, which leads to broken connections and a failed move.
A database migration, at its simplest, is the process of moving a database from one environment to another. This could be from a physical server in your office (on-premises) to Google Cloud, from one cloud to another, or even from an old database type to a newer one in the same cloud. The chapter you are studying focuses on the 'planning' phase, which is broken into three major parts: Assessment, Connectivity, and Tool Selection.
Part 1: Assessment
Assessment is the discovery and evaluation phase. Before you move anything, you must know what you have. This involves:
Inventory: Listing every database you own. This includes the type (e.g., Oracle, MySQL, PostgreSQL), the version, its size (gigabytes or terabytes), and its configuration.
Compatibility Check: Not all databases are the same. An older version of MySQL might use features that the newer version Google Cloud offers does not support. You need to check for 'compatibility issues' – things like stored procedures, triggers, or specific data types that might break after the move.
Dependency Mapping: A database does not live in a vacuum. It connects to applications, reporting tools, and other databases. You must map out which applications talk to it and when, to avoid breaking those connections.
Performance Baseline: How fast is your current database? You need to measure its current read and write speeds (throughput and latency) to ensure your new cloud database can match or beat that performance.
Compliance and Security: Does your data contain personally identifiable information (PII) or credit card numbers? You must assess which data needs to be encrypted during the move and at rest in the new location.
Google Cloud provides a tool called the 'Database Migration Assessment' or you can use third-party tools (like AWS Schema Conversion Tool for migrations that cross clouds) to automate much of this inventory and compatibility checking.
Part 2: Connectivity
Connectivity is the 'pipe' through which your data will travel. If the pipe is too small or unstable, your migration will fail or take too long. The options available are:
Public Internet: The simplest and cheapest option. You use a VPN (Virtual Private Network) to create an encrypted tunnel over the public internet. This is like driving a regular delivery van on a public road. It works, but it is subject to traffic jams (latency spikes) and the road could be closed (interruptions).
Cloud Interconnect: This is a dedicated private physical connection directly from your on-premises network to Google Cloud. It bypasses the public internet entirely. This is like building a private tunnel between your two houses. It is faster, more reliable, and more secure, but costs more money and takes longer to set up (it requires physical cabling).
Carrier Peering: You use a third-party telecom provider to connect your network to Google Cloud. It is a middle-ground option – more reliable than public internet, but less expensive than Dedicated Interconnect.
The choice depends on how sensitive your data is and how much data you are moving. If you are moving 50 terabytes of public data, public internet with a VPN might be fine. If you are moving 500 terabytes of customer health records, you will want a dedicated interconnect.
Part 3: Tool Selection
You do not move a database by copying files one by one. Google Cloud offers several tools, and you must choose the right one for your specific situation. The primary tool is 'Database Migration Service (DMS)'. DMS is a managed service that can move databases from on-premises to Google Cloud with minimal downtime. It works by continuously replicating changes from the source to the target, so the application can keep running during the move.
However, DMS only supports certain database types (like MySQL, PostgreSQL, and SQL Server). If you are moving an Oracle database, you might need a different tool called 'Database Migration Service – Oracle to Bare Metal', or you might use a third-party tool like 'Striim' or 'Qlik'. For very large datasets, you might use a physical appliance called 'Transfer Appliance', where you mail your data on a portable hard drive to Google.
The key is to match the tool to the database type, the size of the data, and the acceptable downtime. If your application can be offline for a weekend, a simple dump-and-load tool is fine. If it must stay online 24/7, you need a continuous replication tool like DMS.
Discovery and Inventory
Identify all databases in your current environment. You use automated tools or scripts to list every database instance, its type (Oracle, MySQL, etc.), version, and size. This step is critical because you cannot plan a migration for databases you do not know exist.
Compatibility Assessment
Check whether the source database features are supported by the target Google Cloud database. For example, if your source uses Oracle's 'SYS_REFCURSOR', you must verify that Cloud SQL for PostgreSQL supports it (it does not). This step reveals the 'gaps' that need manual fixing.
Dependency and Security Mapping
List every application, service, and user that connects to the source database. Map out which data contains sensitive information (like PII or payment data). This step determines downtime tolerance (some apps can be offline, others cannot) and encryption requirements.
Connectivity Planning
Choose and set up the network path between your on-premises data centre and Google Cloud. Options include a VPN over public internet, Cloud Interconnect (dedicated physical link), or Carrier Peering. This step ensures the data has a fast, secure, and reliable road to travel on.
Tool and Strategy Selection
Based on the assessment results (size, downtime tolerance, database type), choose the appropriate Google Cloud migration tool. For small databases with high tolerance, use a dump and load. For large, critical databases, use Database Migration Service (DMS) or a third-party replication tool. For huge databases over slow networks, choose the Transfer Appliance.
Testing and Validation
Perform at least one dry-run migration to a non-production Google Cloud environment. Validate that the data arrived intact, that performance meets baselines, and that all applications can connect to the new database. This step catches problems before the real cutover.
Cutover and Switch
Execute the final migration during a planned outage window. Stop all writes to the source database, sync any final changes, and then redirect all application traffic to the new database. Verify everything is working, then decommission the old database.
Imagine you are a database engineer at a mid-sized e-commerce company called 'ShopNest'. ShopNest has been running its customer database on an old Oracle server in a rented data centre for ten years. The server is running out of space, the licensing costs are skyrocketing, and the CIO has decided to move it to Google Cloud to use Cloud SQL (PostgreSQL). Your job is to plan this migration.
Step 1: The Assessment
You start by doing an inventory. You run a script that discovers all Oracle databases in the data centre. You find one primary database (300 gigabytes) and two smaller replica databases. You then run a compatibility assessment using Google's native tools or a third-party tool like the 'Schema Conversion Tool (SCT)' to check if all of the Oracle-specific features (like PL/SQL packages) have equivalents in PostgreSQL. You discover that three stored procedures use a date format that PostgreSQL does not support. You note this as a 'schema conversion issue' to be fixed later.
You also map dependencies. You find that the checkout application connects to this database, as does the customer support team's reporting dashboard. You learn that the checkout app can tolerate 15 minutes of downtime, but the dashboard cannot. This influences your choice of migration tool.
Step 2: Setting Up Connectivity
Because the checkout app is critical and the data contains customer payment information, management decides against using the public internet. Instead, you set up a 'Cloud Interconnect' connection. This involves working with a telecom provider and Google to physically connect your data centre to Google's network. It takes three weeks to install, but once it is up, you have a 10 gigabit per second private link. This is the high-speed tunnel for your 300 GB data move.
Step 3: Choosing the Tool
Because DMS (Database Migration Service) does not support Oracle to PostgreSQL migration directly, you evaluate two options: using a third-party tool like Striim for continuous replication, or doing a manual export/import. You choose a manual approach: you use Oracle's own export tool to create a dump file. You then use Google's Schema Conversion Tool to convert the Oracle schema to PostgreSQL syntax, fixing the incompatible stored procedures manually. You then load the data using Google's Dataflow service. This approach means the database will be offline for about 2 hours while you do the final sync, which is acceptable because the checkout app can handle that.
Step 4: Testing and Cutover
You do a dry run migration to a test environment in Google Cloud. You run performance tests to make sure the new database is faster than the old one. You then schedule the cutover for Sunday at 2 AM. During the cutover, you put the old database in read-only mode, copy the final changes, and then point the checkout application to the new Google Cloud database. It works. The assessment discovered the issues, the connectivity provided a safe path, and the tool selection made the move possible without buying expensive new Oracle licenses.
The PCDE exam tests your ability to plan a migration, not necessarily to execute every step from memory. The questions are scenario-based. Expect to be presented with a business requirement (e.g., 'Company X wants to move their 5 TB MySQL database to Cloud SQL with less than 1 hour of downtime. They have a slow network connection. Which approach should they use?').
Concepts the exam loves to test:
Compatibility Matrix: You must know which source databases are supported by which Google tools. For example, DMS supports MySQL, PostgreSQL, and SQL Server. It does not support Oracle. If a question mentions Oracle, the answer will not be DMS – it will be a third-party tool or a manual process.
Downtime Thresholds: The exam tests the trade-off between downtime and tool complexity. If the question says 'zero downtime', you need a continuous replication tool. If it says 'batch window of 8 hours', you can use a simple export/import.
Connectivity Options: You must know the difference between VPN (cheap, standard reliability), Cloud Interconnect (expensive, high reliability, dedicated), and Carrier Peering (middle option). The exam will ask you to choose based on cost, speed, and security requirements.
Transfer Appliance: For datasets larger than 10 TB with a slow network, the correct answer is often the physical Transfer Appliance. This is a common trap – beginners think 'cloud' means 'all over the internet', but the fastest way to send 100 TB of data is often to mail a hard drive.
Schema Conversion: The exam will present cases where the database engine changes (e.g., Oracle to Cloud SQL Postgres). You need to recognise that schema conversion tools exist (like Google's Schema Conversion Tool or AWS SCT for cross-cloud), and that manual code changes may be required for stored procedures and triggers.
Trap patterns:
The 'Fastest' Trap: A question asks for the 'fastest' way to migrate. If the network is slow, the fastest way is not the fastest transfer method (which would be Cloud Interconnect) – it is a physical transfer (Transfer Appliance). Do not pick the internet-based answer if the pipe is small.
The 'Always Use DMS' Trap: DMS is great, but it only supports specific sources. If the source is Oracle or DB2, DMS is not the answer. Look for the trap where the source database is not in the DMS compatibility list.
The 'Security' Trap: If the data is highly sensitive (PII, healthcare), the exam expects you to choose the most secure connectivity option, even if it is more expensive. Public internet + VPN is secure enough for many cases, but a dedicated interconnect is the 'most' secure and the expected answer for high-security scenarios.
Key definitions to memorise:
Migration: Moving a database from one environment to another.
Replication: Copying data changes from one database to another continuously.
Cutover: The moment when you switch the application from the old database to the new one.
Schema: The structure of the database (tables, columns, data types).
Dump and Load: Exporting data to a file, then importing it.
Continuous Replication: A method where changes are synced in real-time, enabling near-zero downtime.
A database migration starts with a thorough assessment of your current environment, including the database type, version, size, dependencies, and security requirements.
Google's Database Migration Service (DMS) only supports specific source databases (MySQL, PostgreSQL, SQL Server) and is not a universal tool.
For large datasets (over 10 TB) with slow internet, the fastest migration method is the physical Transfer Appliance, not an online transfer.
Connectivity choices affect cost and reliability: public VPN is cheapest, Cloud Interconnect is most reliable, and Carrier Peering is a middle ground.
Schema conversion is required when moving between different database engines (e.g., Oracle to PostgreSQL), and it often involves manual code fixes for stored procedures.
Zero downtime migrations require continuous replication tools; merely dumping and loading a database will always require some application downtime.
A compatibility assessment must be performed before any migration to catch unsupported data types, functions, or configurations that would break the target database.
The cutover step is the final and most critical part: you must stop writes to the old database, sync the last changes, and then point the application to the new one.
These come up on the exam all the time. Here's how to tell them apart.
Database Migration Service (DMS)
Managed service – Google handles replication setup and monitoring for you.
Supports continuous replication, enabling near-zero downtime migrations.
Only supports specific source databases (MySQL, PostgreSQL, SQL Server).
Manual Dump and Load
You manually run export (dump) and import (load) scripts.
Requires application downtime during export and import.
Works with any database that can be exported to a standard format (SQL dump, CSV).
VPN over Public Internet
Low cost – only pay for VPN gateway usage and internet bandwidth.
Speed and reliability depend on your internet service provider; subject to congestion.
Setup is fast – can be configured in minutes or hours.
Cloud Interconnect (Dedicated)
High cost – includes monthly fees for the physical port and cross-connect.
Provides dedicated bandwidth (up to 100 Gbps) with a service level agreement (SLA) for uptime.
Setup takes weeks – requires physical cabling and telecom provider coordination.
Schema Conversion Tool (SCT)
Only converts the schema (structure) of the database, not the actual data.
Used for heterogeneous migrations where the engine changes (e.g., Oracle to PostgreSQL).
Reports compatibility issues and generates conversion scripts for you to review.
Database Migration Service (DMS)
Migrates both the schema and the data automatically.
Used for homogeneous migrations where the engine stays the same (e.g., MySQL on-prem to Cloud SQL MySQL).
Does not report on schema issues – it assumes the schema is compatible if it supports the source.
Online Migration (DMS/Replication)
Application can stay running during most of the migration process.
Requires a stable network connection with low latency to keep data syncing.
More complex to set up and monitor, but minimal business disruption.
Offline Migration (Dump and Load)
Application must be taken offline (scheduled downtime) for the entire migration.
No network required during the migration itself – you can do it over a local file transfer.
Simpler to execute and has fewer potential points of failure during the move.
Mistake
You can just copy the database file using the internet, and it always works.
Correct
Databases are not flat files. They have internal structures, locks, and transaction logs that require specialised tools to export and import without corruption.
Beginners are familiar with copying documents or photos. They assume a database is just a big file, not a live, complex system that cannot be grabbed mid-operation.
Mistake
The cloud is always faster than my on-premises hardware.
Correct
The cloud's speed depends on the network latency between your location and the cloud, and on the instance type you choose. A poorly configured cloud database can be slower.
Marketing material often emphasises the cloud's power, but beginners forget that the 'last mile' of their own internet connection is a bottleneck.
Mistake
Database Migration Service (DMS) can migrate any database type.
Correct
DMS only supports a limited set of sources: MySQL, PostgreSQL, and SQL Server (with specific versions). It does not support Oracle, MongoDB, or DB2.
The name 'Database Migration Service' sounds universal. Beginners assume it covers everything, so they select it for a non-supported database and fail the question.
Mistake
If I use a VPN, my migration is completely secure and unaffected by internet problems.
Correct
A VPN encrypts the data but does not guarantee speed or reliability. Public internet still faces congestion (slow speeds) and outages (interruptions). A private interconnect is the only way to get guaranteed performance.
People often equate 'encryption' with 'reliability'. Securing the data is only half the battle; the other half is ensuring the data actually arrives on time.
Mistake
Assessment is just about knowing how much data you have (the size).
Correct
Assessment includes the data size, the schema, the database version, dependencies, security requirements, compliance needs, and the structure of stored procedures. Size is just one of many factors.
Beginners think 'more data = longer migration', but forget about structural incompatibilities that can stop a migration entirely, even with small amounts of data.
Mistake
Zero downtime migrations are always possible and standard.
Correct
Zero downtime migrations are complex and require special tools (like continuous replication) and careful cutover planning. Many migrations accept some downtime because it is cheaper and simpler.
The term 'zero downtime' sounds like a requirement everyone has, but in reality, the cost and complexity often make a few minutes of downtime acceptable for most applications.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
The easiest way is to use Google's Database Migration Service (DMS) which automates the setup and continuous replication. If you prefer a manual method, you can export the data using 'mysqldump' and import it into Cloud SQL using the cloud-sql-proxy.
No. DMS does not support Oracle as a source. You would need to use a third-party tool like Striim or Datastream for continuous replication, or manually export from Oracle, convert the schema using Google's Schema Conversion Tool, and then import the data.
It is safe if you use a VPN to encrypt the data. However, it is not reliable for very large datasets or for applications that require minimal downtime, because public internet speeds fluctuate and connections can drop.
A Transfer Appliance is a physical server that Google sends to your data centre. You copy your data onto it and ship it back to Google, who then uploads it into your cloud project. Use it when you have more than 10 TB of data and a slow or expensive internet connection.
Dump and load creates a snapshot of the database at one point, requiring the application to be offline during the export and import. Continuous replication mirrors changes in real-time, allowing the application to run during the full migration except for a final brief cutover.
You run a compatibility assessment using Google's Schema Conversion Tool (SCT) or the native migration assessment features. It scans your source database for unsupported data types, functions, and syntax and reports what needs to be changed.
Cutover is the final step where you stop writes to the old database, sync any remaining changes, and then redirect the application to the new database. It is the moment when the migration is considered complete and the old database is turned off.
You've finished Planning a Database Migration: Assessment, Connectivity, and Tools. Continue through the PCDE study guide to build a complete picture of the exam.
Done with this chapter?