PCDE Migrate data solutions Practice Test 4 — 15 Questions

Question 1

A company wants to migrate a self-managed PostgreSQL database to AlloyDB with minimal downtime. They plan to use Database Migration Service with continuous CDC. The source database is in a different region. Which THREE steps should they include in their cutover plan? (Choose 3 correct answers.)

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Quiesce writes to the source database.. Option B is correct because during a Database Migration Service (DMS) continuous CDC cutover, writes to the source PostgreSQL database must be quiesced (stopped) so that no new changes are generated that would not be captured before promotion, ensuring the destination is fully consistent. Option C is correct because the migration job's replication lag must be confirmed at zero (or effectively zero) before promotion; this verifies that all captured changes from the source have been applied to the AlloyDB destination, which is essential for minimal downtime and no data loss. Option E is correct because the final cutover step is to promote the AlloyDB destination, which stops the DMS migration job's replication and makes AlloyDB the new writable primary, allowing the application to reconnect to it. Option A is not part of the cutover plan itself; performance testing should be done earlier during validation, not as a cutover step, and it does not directly enable minimal-downtime promotion. Option D is incorrect because deleting the source database immediately after promotion is risky and unnecessary; the source should be retained until the migration is validated and rollback is no longer needed.

Answer

Run a full performance test on AlloyDB before cutover.

Answer

Delete the source database immediately after promotion.

Question 2

An engineer is migrating a MySQL database to Cloud SQL using mysqldump. The source database uses InnoDB tables. Which TWO mysqldump options should the engineer use to perform a consistent online backup without locking tables? (Choose 2 correct answers.)

Accepted Answer

--quick. Option B (--single-transaction) is correct because it issues a START TRANSACTION WITH CONSISTENT SNAPSHOT before dumping, giving a consistent snapshot of InnoDB tables without acquiring table-level locks, which is exactly what an online, lock-free backup requires. Option A (--quick) is correct because it streams rows from the server one at a time instead of buffering the entire result set in memory, which keeps the dump efficient and avoids memory pressure during a large online migration. Together these two options let mysqldump read InnoDB data consistently while other sessions continue writing. Option C (--routines) only includes stored procedures and functions and does not affect locking or consistency. Option D (--no-data) dumps only the schema and omits table data, which is not a full backup. Option E (--skip-lock-tables) merely disables explicit table locking but does not by itself provide a consistent snapshot, so it does not satisfy the requirement.

Answer

--routines

Answer

--no-data

Answer

--skip-lock-tables

Question 3

Your team is migrating a 4 TB on-premises PostgreSQL 13 database to Cloud SQL for PostgreSQL using Database Migration Service (DMS). The migration must be a continuous migration with minimal downtime. During the initial full dump, DMS fails with the error 'replication slot is not active'. You have verified that the source database has the required extensions and that the DMS connection profile has the necessary privileges. What should you do to resolve this issue?

Accepted Answer

Verify that the source database's wal_level parameter is set to logical and that the replication slot is not being used by another process.. DMS continuous migration from PostgreSQL requires logical replication, which depends on wal_level being set to logical and the replication slot being available. The error indicates the slot is not active, often due to misconfigured wal_level or contention. Verifying these settings ensures the slot can be used, enabling the migration to proceed without unnecessary changes to the destination or source restart.

Answer

Restart the source PostgreSQL database to clear any locks on the replication slot and then resume the migration.

Answer

Increase the Cloud SQL instance's storage capacity to accommodate the 4 TB of data and the replication slot metadata.

Answer

Set the Cloud SQL instance's max_replication_slots flag to a value greater than the current number of replication slots on the source.

Question 4

Your team is migrating a large on-premises PostgreSQL database to Cloud SQL for PostgreSQL using Database Migration Service (DMS). The migration must be continuous and have minimal downtime. During the initial full dump, you notice that the database contains tables without primary keys, and DMS reports that these tables cannot be replicated. What should you do to allow the migration to proceed while maintaining data integrity?

Accepted Answer

Add a primary key to each table that lacks one, then restart the migration.. Adding a primary key to each table without one enables DMS to uniquely identify rows during change data capture, which is essential for continuous replication. DMS requires a primary key or a unique identifier to apply updates and deletes correctly. Once the keys are added, the migration can proceed with minimal downtime, ensuring data integrity throughout the process.

Answer

Enable logical replication on the source and manually create a replication slot for each table without a primary key.

Answer

Configure DMS to use the REPLICA IDENTITY FULL setting for tables without primary keys.

Answer

Exclude the tables without primary keys from the migration and migrate them separately using a batch process.

Question 5

Your team is migrating an on-premises PostgreSQL 14 database to Cloud SQL for PostgreSQL using Database Migration Service (DMS). The database is 800 GB and must remain available for writes during the migration. After starting the migration job, you notice that the initial full dump is taking much longer than expected and the source is under heavy read load. You need to reduce the impact on the source and speed up the initial load without compromising data consistency. What should you do?

Accepted Answer

Set up a read replica on the source and point the DMS migration job to the replica for the initial dump.. Using a read replica as the source for the initial dump offloads read operations from the primary, reducing its load and potentially speeding up the dump. DMS can still capture ongoing changes from the primary via logical replication, ensuring consistency. This is a recommended practice for large migrations with minimal impact on production.

Answer

Increase the Cloud SQL destination instance's vCPU count to speed up the initial load.

Answer

Pause the migration job and restart it during a maintenance window to reduce contention.

Answer

Configure the migration job to use a single-threaded dump and increase the source's max_connections.

Question 6

A media company runs a 4 TB PostgreSQL 13 database on-premises with a steady write load of about 2,000 transactions per second. They must move to Cloud SQL for PostgreSQL with less than five minutes of downtime. Network bandwidth between the data center and Google Cloud is 1 Gbps and the migration window is a single weekend. Which migration approach should they use?

Accepted Answer

Database Migration Service for PostgreSQL with a continuous migration job, followed by a promotion of the destination replica.. DMS for PostgreSQL is purpose-built for this pattern: it takes a consistent snapshot and then applies continuously streamed changes, so the final cutover only has to wait for replication lag to reach zero. That keeps the application outage to minutes regardless of database size, and the 1 Gbps link is adequate for the initial load within a weekend window.

Answer

Set up a read replica in Cloud SQL, promote it, and repoint the application connection string at the moment of cutover.

Answer

Base backup with pg_basebackup, ship WAL segments to a Cloud Storage bucket, and replay them on Cloud SQL until cutover.

Answer

pg_dump with the --format=directory option, copied over the WAN link with gsutil, then restored with pg_restore into Cloud SQL.

Question 7

A retail company is migrating an on-premises MongoDB 4.4 replica set to MongoDB Atlas on Google Cloud. They need to perform a minimal-downtime migration with continuous replication. Which TWO actions should they take? (Choose two.)

Accepted Answer

Use the mongomirror tool to perform an initial sync and then continuously replicate oplog entries.. mongomirror is the tool for live migrations to MongoDB Atlas. It performs an initial sync and then tails the oplog to keep the destination updated. For this to work, the source replica set must have oplog access enabled and an oplog large enough to cover the migration duration. These two actions are essential for a successful minimal-downtime migration.

Answer

Configure a VPN or Cloud Interconnect between the on-premises network and the Google Cloud VPC to secure data transfer.

Answer

Use mongodump and mongorestore with the --oplogReplay option to apply changes during cutover.

Answer

Enable MongoDB Atlas Data Lake to automatically sync data from the on-premises replica set.

Question 8

A financial services company is migrating a 5 TB on-premises SQL Server 2019 database to Cloud SQL for SQL Server. The database contains tables with foreign key constraints and triggers. They plan to use Database Migration Service (DMS) for a continuous migration with minimal downtime. During testing, they find that the initial dump fails due to foreign key violations. What should they do to ensure a successful migration?

Accepted Answer

Ensure that the migration job has the necessary permissions and that the source database is configured for CDC; DMS automatically handles foreign key constraints during the initial dump.. Database Migration Service for SQL Server is designed to handle foreign key constraints automatically during the initial dump. The failure is likely due to missing permissions or incorrect CDC setup. Verifying that the migration job has the required privileges and that the source is properly configured for change data capture will allow DMS to manage constraints and complete the migration.

Answer

Use the DMS 'skip foreign key checks' option in the migration job settings.

Answer

Configure DMS to perform the initial dump in a single transaction with SET FOREIGN_KEY_CHECKS=0 on the destination.

Answer

Disable foreign key constraints on the source before starting the migration.

Question 9

An organization is migrating a large on-premises MySQL database to Cloud SQL for MySQL using Database Migration Service (DMS). They need to ensure that the migration is secure and that the data is encrypted both in transit and at rest. Which TWO configurations should they implement to meet these requirements? (Choose two.)

Accepted Answer

Enable encryption at rest on the Cloud SQL destination instance using Google-managed encryption keys.. To secure the migration, you must encrypt data in transit and at rest. Configuring SSL/TLS on the source MySQL instance ensures that data transferred to DMS is encrypted, while enabling encryption at rest on the Cloud SQL destination instance protects stored data. These two configurations together provide end-to-end encryption, meeting the organization's security requirements.

Answer

Enable binary logging on the source MySQL instance with SSL/TLS required for replication.

Answer

Use a VPN tunnel between the on-premises network and Google Cloud to encrypt all traffic.

Answer

Configure Cloud SQL to use customer-managed encryption keys (CMEK) stored in Cloud KMS.

Question 10

A healthcare analytics team is migrating a 5 TB on-premises Microsoft SQL Server 2019 database to Cloud SQL for SQL Server. The database has tables with foreign key relationships and uses the bulk-logged recovery model. They need to perform a one-time migration with minimal administrative overhead. Which approach should they use?

Accepted Answer

Perform a backup and restore using the Cloud SQL import feature from a BAK file stored in Cloud Storage.. For a one-time migration of a SQL Server database to Cloud SQL, using a BAK file import is the simplest and most direct method. It supports databases in bulk-logged recovery model and handles foreign keys automatically. This approach minimizes administrative overhead because it leverages native backup and restore capabilities without requiring continuous replication or complex ETL processes.

Answer

Configure transactional replication from the on-premises SQL Server to Cloud SQL.

Answer

Use Database Migration Service (DMS) for SQL Server to perform a continuous migration with a promotion phase.

Answer

Use the SQL Server Integration Services (SSIS) to extract and load data into Cloud SQL.

Question 11

A financial services firm is moving a 6 TB Microsoft SQL Server 2019 database to Cloud SQL for SQL Server. The application cannot tolerate more than a ten-minute outage, and the source uses a full recovery model with transaction log backups every fifteen minutes. Which combination of steps should the engineer perform to guarantee the smallest possible cutover window?

Accepted Answer

Restore a full backup and subsequent log backups to Cloud SQL, take a final tail-log backup after quiescing the application, restore it, and then recover the database.. Cloud SQL for SQL Server supports restoring native backups from Cloud Storage, including full, differential, and transaction log backups. Restoring a full backup plus the chain of log backups leaves only the tail log to apply at cutover. With the application quiesced briefly for the tail-log backup, the final restore and recovery complete in minutes, keeping the outage within the ten-minute objective.

Answer

Configure transactional replication from the on-premises publisher to the Cloud SQL instance as a subscriber, then stop replication at cutover.

Answer

Use Database Migration Service for SQL Server in continuous mode, then promote the destination after verifying replication lag is near zero.

Answer

Detach the database files from the source, upload the .mdf and .ldf to Cloud Storage, and attach them to the Cloud SQL instance at cutover.

Question 12

A healthcare company is migrating an on-premises Oracle 19c database to Cloud SQL for PostgreSQL. They have a large number of PL/SQL stored procedures and want to minimize manual rewriting. They plan to use the Google Cloud Oracle to PostgreSQL migration tooling. Which tool should they use to automatically convert the PL/SQL code to PL/pgSQL?

Accepted Answer

Database Migration Service (DMS) schema conversion. Database Migration Service (DMS) schema conversion is the Google Cloud tool designed to automatically convert Oracle schemas and PL/SQL code to PostgreSQL-compatible DDL and PL/pgSQL. It is part of the DMS suite and provides a managed, integrated experience for heterogeneous migrations, reducing manual rewriting effort. The other options either require manual work or are not conversion tools.

Answer

Cloud SQL Migration Assessment

Answer

Ora2Pg

Answer

Google Cloud Dataflow with Apache Beam

Question 13

A team is migrating an on-premises PostgreSQL 13 database to Cloud SQL for PostgreSQL using Database Migration Service (DMS). The source database is 500 GB and has a high write workload. They want to minimize downtime and ensure data consistency. During the migration, they notice that the replication lag is increasing. Which action should they take to reduce replication lag while maintaining consistency?

Accepted Answer

Increase the number of vCPUs on the Cloud SQL destination and ensure the source has sufficient I/O capacity to read WAL.. Replication lag in DMS for PostgreSQL can stem from either the source's WAL read performance or the destination's apply rate. Increasing destination vCPUs enhances apply parallelism, while ensuring source I/O capacity prevents WAL read delays. Other options either misidentify tunable parameters or only partially address the issue. The correct action is to scale both source I/O and destination compute to achieve consistent, low-lag replication.

Answer

Increase the number of vCPUs on the Cloud SQL destination instance.

Answer

Configure the migration job to use a larger replication slot and increase the wal_sender_timeout on the source.

Answer

Increase the max_replication_slots and max_wal_senders on the source, and ensure the network bandwidth between source and destination is sufficient.

Question 14

A retail company is migrating a 1 TB on-premises MongoDB 4.4 replica set to MongoDB Atlas on Google Cloud. They want to minimize downtime and ensure that the destination is in sync before cutover. Which Google Cloud service should they use?

Accepted Answer

Database Migration Service (DMS) for MongoDB. Database Migration Service (DMS) for MongoDB is a fully managed service that supports continuous replication from on-premises MongoDB to MongoDB Atlas on Google Cloud. It handles the initial snapshot and ongoing changes, ensuring the destination is in sync before cutover, thus minimizing downtime. This is the native Google Cloud solution for homogeneous MongoDB migrations.

Answer

BigQuery Data Transfer Service

Answer

Migrate for Compute Engine

Answer

Cloud Data Fusion

Question 15

An e-commerce company is migrating its on-premises MySQL 5.7 database to Cloud SQL for MySQL 8.0 using Database Migration Service (DMS). They want to minimize downtime and ensure that the destination is continuously updated during the migration. After the initial dump, they notice that the replication lag is increasing steadily. The source database has a high volume of write transactions. What is the most likely cause of the increasing replication lag?

Accepted Answer

The destination Cloud SQL instance is undersized for the write workload.. Increasing replication lag during CDC typically indicates that the destination cannot apply changes as fast as they are generated. An undersized Cloud SQL instance may lack the CPU, memory, or IOPS to keep up with the write workload. Scaling up the destination instance is the most direct solution to reduce lag.

Answer

The source database has too many indexes, causing slow reads during the initial dump.

Answer

The source database's binary log format is set to ROW, which is not supported by DMS.

Answer

The migration job is using a single-threaded apply process on the destination.