Design, migrate, and run scalable, highly available databases on Google Cloud across relational and NoSQL services, and track your readiness for the Professional Cloud Database Engineer exam.
Prepare and test your skills
Prepare and test your skills
Selection of tools for heterogeneous and analytical migrations is the process of matching the source system to the Google Cloud service that can move schema and data with the least friction. Heterogeneous migrations move between different database engines, while analytical migrations move data warehouses such as Amazon Redshift and Teradata into BigQuery (opens in a new tab). The choice depends on whether the schema can move unchanged, whether data must be transformed before or after loading, and how large the dataset is.
BigQuery Data Transfer Service (DTS) (opens in a new tab) is the fully managed service for automated batch transfer of schema and data from third-party data warehouses into BigQuery. For Amazon Redshift and Teradata, DTS copies tables directly from the source cluster into BigQuery, while still using Cloud Storage behind the scenes to stage data during the transfer. For any other warehouse, the pattern is to extract schema and data to files, upload those files to Cloud Storage, and then load them with DTS, the BigQuery Storage Write API, or batch load.
The recommended practice is to transfer the schema without changes during the first iteration. Keeping the schema identical means existing feeding pipelines do not need adjustment and automated tools can perform the transfer. Proofs of concept and exploration in BigQuery can then proceed in parallel while later iterations expand the data footprint and reroute upstream feeds.
Exam tip: Choose BigQuery Data Transfer Service when the goal is automated extract-and-load from Redshift or Teradata without transformation; choose Dataflow when data must be trimmed, enriched, or reshaped before it lands in BigQuery.
Dataflow (opens in a new tab) is the serverless, fully managed service for complex transformations and enrichment during migration, built on the Apache Beam model. Data flows from the source or staging bucket into a Dataflow pipeline, the pipeline applies Beam transforms, and the BigQuery I/O connector writes the result directly to BigQuery tables. Because Google manages capacity, the pipeline scales to very large processing volumes without provisioning clusters.
Dataflow provides Google-provided templates that speed heterogeneous extraction. The JDBC to BigQuery template reads from systems such as Amazon Redshift through a JDBC driver, and the Cloud Storage Text to BigQuery template reads staged files from Cloud Storage. To add transformation to a JDBC pipeline, the template code is cloned and Beam transforms are added before the write to BigQuery. Choose Dataflow when migration requires joining datasets, filtering sensitive fields, converting types, or enriching records in flight.
Other transformation frameworks handle the same staging flow when graphical development or Spark code is preferred. Cloud Data Fusion graphically builds fully managed ETL/ELT pipelines using preconfigured connectors and transformations, including JDBC drivers and CDAP plugins. Managed Service for Apache Spark runs Apache Spark and Hadoop clusters as a managed service and connects to Cloud Storage and BigQuery through dedicated connectors, with Sqoop commonly used to export relational data into Cloud Storage as Avro files.
Migration pipelines fall into three patterns that differ by where transformation occurs:
| Pattern | Where transformation happens | Use when |
|---|---|---|
| Extract and load (EL) | No transformation; data moves from source to Cloud Storage to BigQuery | Schema moves unchanged and speed matters most |
| Extract, transform, and load (ETL) | Transformation in Dataflow, Data Fusion, or Spark before BigQuery | Data must be cleaned or reshaped before loading |
| Extract, load, and transform (ELT) | Data lands in a BigQuery staging table, then SQL transforms it into production tables | BigQuery itself can do the reshaping with SQL |
A typical file-based EL pipeline follows an ordered sequence. First, data is exported from the source warehouse to object storage such as Amazon S3. Then, data is copied from Amazon S3 to Cloud Storage using Storage Transfer Service, the gcloud CLI, or Cloud Storage client libraries. Finally, data is loaded from Cloud Storage into BigQuery using DTS, the bq command-line tool, or BigQuery client libraries.
Schema conversion and file staging determine whether BigQuery can interpret migrated data correctly. Export to a vendor-neutral format such as Avro, ORC, or Parquet is recommended because schema information is embedded with the data and transfer complexity drops. If CSV or another delimited format is used, the schema must be specified separately at load time or auto-detected from file contents.
Files must reach a Cloud Storage bucket before loading, unless DTS is pulling directly from Redshift or Teradata. The upload choice depends on location and bandwidth:
Bucket location also matters for performance. Creating the bucket in the location closest to the data center minimizes latency, and placing the bucket in the same location as the BigQuery dataset avoids cross-location movement. After the initial load, analytical schemas are often evolved to fit BigQuery, including nested and repeated fields that reduce joins and clustering on frequently filtered columns to reduce scanned data.
Migrations to Cloud Spanner require additional schema conversion because data types, keys, and distribution differ from the source engine. Cloud Spanner is a globally distributed relational system, so tables must be redesigned for its keys and distribution. The Spanner migration tool (SMT) is the open-source tool that performs assessment, schema conversion, and data migration, with a graphical interface for configuring converted schemas. Database Migration Assessment (DMA) offers a basic early assessment for MySQL and PostgreSQL moves to Spanner.
Bulk and continuous movement depend on Datastream and Dataflow templates. Datastream is the Google Cloud service that reads bulk data and change data capture (CDC) events from the source database. Bulk data migration is a Dataflow template that moves large MySQL datasets directly to Spanner, while live data migration combines Datastream and Dataflow to migrate existing data plus the stream of changes made during migration for minimal downtime. For Cassandra sources, Spanner provides a Cassandra-compatible interface plus a dedicated schema conversion tool and the Sourcedb to Spanner template for bulk movement.
Sample size and validation create firm decision points. If a MySQL or PostgreSQL dump sample is less than 100 GB, SMT can load it in proof-of-concept mode; if it is more than 100 GB, the file is exported to Cloud SQL and moved with bulk or minimal-downtime migration. If the sample is in CSV, Avro, or Parquet format, it can be loaded to BigQuery and copied to Spanner using reverse ETL. After production migration, the Data Validation Tool (DVT) compares source and target with aggregations such as count, sum, and average to verify success.
Assessment of homogeneous migration tools compares paths that keep the same database engine when moving to Google Cloud. Homogeneous migration moves MySQL to MySQL, PostgreSQL to PostgreSQL, or SQL Server to SQL Server on Cloud SQL (opens in a new tab) and AlloyDB. The two main paths are Database Migration Service (DMS), which provides serverless continuous migration, and manual processes based on export and import or external replication.
Database Migration Service (DMS) (opens in a new tab) is the serverless service for homogeneous migrations from on-premises or other clouds to Cloud SQL and AlloyDB. DMS is built from two elements that work together. Connection profiles store connectivity information for a specific source that will be used in a migration, and migration jobs pair a source connection profile with a destination Cloud SQL instance plus migration-specific settings. Access to DMS is controlled by Identity and Access Management (IAM), which grants granular permissions to specific Google Cloud resources and prevents unwanted access to other resources.
DMS maintains consistency by combining an initial load with continuous replication from the source to the destination Cloud SQL instance. For SQL Server, DMS uses a backup-and-restore flow where data flows from the source database to a Cloud Storage bucket to the destination instance. You first upload the full database backup and transaction log files to a Cloud Storage bucket, then DMS actively monitors the bucket and continuously replicates the data to the destination Cloud SQL for SQL Server instance. This continuous flow keeps the destination in sync so cutover can happen with minimal downtime.
The destination must meet two consistency rules for DMS to start. The destination Cloud SQL instance must be empty, because DMS can only migrate to empty instances that contain no user-defined databases, tables, or functions. The destination database version must also be the same as the source or one major version above the source, which keeps schema behavior compatible during replication.
DMS also migrates PostgreSQL workloads to AlloyDB for PostgreSQL by creating the destination from a connection profile. The AlloyDB connection profile carries the cluster ID, initial user and password, VPC network, primary instance ID, and machine configuration such as CPU count. DMS uses the information in the profile to create a new AlloyDB cluster and instance in the specified VPC network. Because the network, user, and machine shape are defined up front, the migration job depends on the connection profile for both connectivity and destination provisioning.
Manual export and import moves data through files without DMS managing replication. For SQL Server, migrating entails exporting data from the source instance to a BAK file and importing the data into the target instance. The target instance must have sufficient storage for all data, the same authorized networks, and the same user accounts with the same privileges and passwords, and you must not export the sqlserver system database. Manual moves require the instance to become unavailable for a period of time, so they are best scheduled when database activity is low.
Another manual path is to replicate from an external server into Cloud SQL, where Cloud SQL supports various replication modes (opens in a new tab) to migrate data into the instance. After promotion to primary, Cloud SQL resets backup settings to their default values, so any customized backup settings such as a custom backup location must be configured again. The reverse direction uses the same replica idea in opposite order:
DMS, manual export and import, and external replication differ in how data moves and how changes are handled during cutover:
| Option | How data moves | Change handling |
|---|---|---|
| Database Migration Service (opens in a new tab) | Source to destination through a managed migration job; for SQL Server, source backups and logs flow through a Cloud Storage bucket monitored by DMS | Continuous replication keeps destination in sync for minimal-downtime cutover |
| Manual export and import | Source exports to a BAK file, then file imports into a prepared target with matching storage, networks, and users | One-time copy with outage during export, transfer, and import |
| External server replication | Source replicates to a replica instance that later changes roles through demotion and promotion | Replica stays synchronized until applications are redirected, then roles switch |
Choose DMS when the goal is to keep schema and data consistent with continuous sync and the destination can be empty with a compatible version. Choose manual export and import when a simple one-time copy is acceptable and downtime can be scheduled. Migration from Amazon RDS for SQL Server (opens in a new tab) often illustrates this tradeoff because instance-level logins and roles must be handled separately from database backups.
Exam tip: DMS requires an empty destination and a destination version that is the same or one major version above the source; promotion also resets Cloud SQL backup settings to defaults.
Evaluation of online versus offline migration strategies decides whether the source database stays usable while data moves to Google Cloud. An online migration, also called continuous migration, keeps the source operational and replicates changes until cutover. An offline migration, also called one-time or scheduled maintenance migration, copies data while the source is stopped or quiet and requires longer application downtime.
Database Migration Service (DMS) (opens in a new tab) performs online migrations using Change Data Capture (CDC), a process that captures only inserts, updates, and deletes made after the initial snapshot. Data flows from the source database to the DMS migration job and then to the destination Cloud SQL or AlloyDB instance, while the source continues to accept writes. Because only modifications are replicated after the first load, continuous replication is more efficient than copying the full database repeatedly and scales well as a serverless migration.
An online migration follows an ordered sequence:
DMS is cost-effective and straightforward when minimal downtime is acceptable and no data transformation or real-time synchronization is needed. The amount of downtime depends on transaction log backup frequency, and DMS does not support dual-write where both source and destination accept writes. For SQL Server from Amazon RDS (opens in a new tab), backups do not include instance-level logins, permissions, or server roles, so they must be scripted separately with tools like PowerShell or DBAtools.
Offline migrations copy a point-in-time image of the database and are best when significant downtime is acceptable and source databases are relatively static. The simplest offline path uses the database engine's built-in backup and restore, where a backup is taken to storage such as an Amazon S3 bucket in the same region as the source and then restored to Cloud SQL. This approach works for any database size and is often most effective for large instances, but it requires setup and synchronization effort when many databases are involved.
Database engine backups have general tradeoffs that matter for planning:
Other offline approaches use flat files or custom scripts to export and import data, including CSV or JSON files, the SQL Server Import and Export Wizard with SQL Server Integration Services (SSIS), and the Generate and Publish Scripts Wizard with the bcp utility. These methods are generally slower but allow filtering by table, age, or domain, performing validation or aggregation during migration, and consolidating multiple instances into one. Snapshot replication is another option for Amazon RDS Standard sources, where the backup is restored to a standalone SQL Server on Compute Engine and then replicated to Cloud SQL, though snapshot generation holds locks on source tables and adds load.
Recovery time objective (RTO) measures how long applications can stay down, while recovery point objective (RPO) measures how much data loss is tolerable. Online and offline migration types (opens in a new tab) differ in both measures because online migration replays CDC changes until promotion. Offline migrations have higher RTO and RPO because writes must stop before the final copy, and any changes after the backup are lost unless they are manually reconciled.
| Strategy | Downtime and data loss | How data moves | Choose when |
|---|---|---|---|
| Online with DMS and CDC | Minimal downtime equal to at least the replication delay; near-zero data loss if promotion waits for lag to drain | Initial snapshot plus continuous CDC from source to destination, then promote replica to primary | Production workloads need continuity, source stays operational, and no transformation or real-time sync is required |
| Offline with backup and restore | Significant scheduled downtime; changes after backup require re-sync | One-time backup to object storage, then restore to Cloud SQL; striped backup over 10 files for databases larger than 5 TB | Downtime is acceptable, databases are static, and simplicity for large instances matters most |
Creating and promoting the migration job (opens in a new tab) controls the final cutover, and monitoring migration progress (opens in a new tab) shows when replication delay is low enough to promote safely.
Exam tip: Choose continuous migration when the business cannot tolerate extended downtime, and remember that cutover downtime lasts at least the current replication delay — stop source writes before promoting.
Last updated