PCDE Migrate data solutions Practice Test 5 — 15 Questions

Question 1

A team is migrating an on-premises MySQL database to Cloud SQL for MySQL using Database Migration Service (DMS). The source database has a table with a composite primary key consisting of three columns. During the migration, DMS fails to replicate changes to this table. What is the most likely cause of this failure?

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

Accepted Answer

The composite primary key includes a column with a data type that DMS cannot replicate.. DMS relies on the primary key to uniquely identify rows for replication. If the composite primary key contains a column with a data type that DMS cannot process, such as certain binary or large object types, replication for that table will fail. Ensuring all columns in the primary key are of supported data types is necessary for successful migration.

Answer

DMS requires a unique index on the table in addition to the primary key.

Answer

The source MySQL instance is not configured with binary logging enabled.

Answer

DMS does not support tables with composite primary keys.

Question 2

A startup is migrating a small MongoDB 4.4 database from a single on-premises server to MongoDB Atlas on Google Cloud. The database is about 10 GB and has a low write rate. They want to minimize downtime and avoid managing servers. Which migration approach is most appropriate?

Accepted Answer

Use MongoDB Atlas Live Migration Service to perform a continuous migration with minimal downtime.. MongoDB Atlas Live Migration Service provides a managed, continuous migration path that minimizes downtime by syncing ongoing changes. It is the recommended approach for migrating to Atlas, especially when avoiding server management is a priority. The other options either cause downtime, are unsupported, or use the wrong service.

Answer

Set up a replica set between on-premises and Atlas, then promote Atlas as primary.

Answer

Use mongodump and mongorestore to export and import the database, then switch the connection string.

Answer

Use Google Cloud Database Migration Service for MongoDB to migrate to Atlas.

Question 3

A media company is migrating a 10 TB on-premises Apache Cassandra cluster to Google Cloud. They require a fully managed, scalable database with minimal operational overhead and want to use a lift-and-shift approach with minimal schema changes. Which Google Cloud service should they choose?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is the recommended Google Cloud service for migrating Apache Cassandra workloads because it provides a fully managed, scalable wide-column store that can be accessed using the Cassandra Query Language (CQL) via a proxy. This allows for a lift-and-shift migration with minimal schema changes, reducing operational overhead and leveraging Bigtable's high performance for large datasets.

Answer

Cloud Spanner

Answer

MongoDB Atlas

Answer

Firestore

Question 4

A healthcare company is migrating an on-premises Oracle 19c database to Cloud SQL for PostgreSQL using Database Migration Service (DMS). The database contains many tables with CLOB and BLOB columns. During the migration, they encounter errors related to large object (LOB) handling. They need to ensure that LOB data is migrated correctly. Which TWO actions should they take? (Choose two.)

Accepted Answer

Grant the Oracle source user the SELECT privilege on the tables containing LOB columns and ensure it has access to the DBMS_LOB package.. Migrating LOB data with DMS requires proper source permissions and sufficient temporary tablespace. The source user must have SELECT on the tables and access to DBMS_LOB to read LOBs. Also, LOB operations can consume significant temporary space, so ensuring adequate temporary tablespace prevents failures during the initial dump.

Answer

Increase the Cloud SQL destination instance's max_connections to allow more concurrent LOB writes.

Answer

Ensure that the Oracle source user has the SELECT privilege on the DBA_LOBS and ALL_LOBS views.

Answer

Configure the DMS migration job to use the 'LOB mode' setting to 'compact' or 'full' as appropriate.

Question 5

Your team is migrating a sharded on-premises MongoDB 5.0 cluster to Firestore in Native mode. Some collections exceed 1 million documents, and the application performs frequent multi-document ACID transactions across two collections. You plan to use the Datastore mode bulk loader approach. Which TWO limitations must you account for in the target design? (Choose two.)

Accepted Answer

Firestore transactions have a limit on the number of documents and the total size of the transaction, so large cross-collection operations must be redesigned.. Firestore's atomicity unit is bounded by transaction document and size limits, and its query engine depends on composite indexes for multi-field filters and sorts. These two properties directly shape how a MongoDB sharded workload with large cross-collection transactions must be refactored before bulk loading data.

Answer

Firestore in Native mode does not support multi-document ACID transactions across more than one collection.

Answer

Firestore in Native mode cannot store documents larger than 100 KB, so large MongoDB documents must be split.

Answer

Firestore requires all collections to be created with an explicit schema definition before any data can be written.

Question 6

A team is migrating a 1 TB MySQL 8.0 database from on-premises to Cloud SQL for MySQL using Database Migration Service (DMS). They want to minimize downtime and ensure data consistency. The source database has a mix of InnoDB and MyISAM tables. Which TWO actions should they take before starting the migration? (Choose two.)

Accepted Answer

Convert all MyISAM tables to InnoDB before migration.. DMS for MySQL requires that all tables use the InnoDB storage engine and that the binary log format be set to ROW. MyISAM tables do not support transactional replication, and ROW-based logging ensures that all changes are captured accurately for continuous replication. These two actions are critical for a successful, consistent migration with minimal downtime.

Answer

Enable GTID mode on the source MySQL server.

Answer

Increase the max_allowed_packet size on the source MySQL server.

Answer

Disable foreign key checks on the source MySQL server during the migration.

Question 7

A retail company is migrating a 1.5 TB on-premises MongoDB 4.4 replica set to MongoDB Atlas on Google Cloud. Their security policy forbids granting the migration tool any write access to the source cluster, and they require an initial full copy plus continuous change stream capture with minimal source impact. Which migration approach should they use?

Accepted Answer

Use MongoDB Atlas Live Migration Service, which connects to the source as a read-only client, performs an initial sync, and then tails the oplog to apply continuous changes until cutover.. Atlas Live Migration Service is the only option that natively performs a full initial sync followed by continuous oplog tailing while connecting to the source with read-only credentials. The other approaches either require manual oplog management, do not support MongoDB Atlas as a destination, or are not valid Google Cloud migration tools for a homogeneous MongoDB migration.

Answer

Use gcloud database-migration jobs create with a MongoDB source connection profile and a MongoDB Atlas destination, enabling continuous replication and change data capture.

Answer

Use mongodump with --oplog on a secondary node, restore to Atlas, then manually replay the oplog using mongorestore --oplogReplay after the cutover window.

Answer

Use Google Cloud Database Migration Service (DMS) for MongoDB, which supports homogeneous MongoDB-to-Atlas migrations with continuous replication and requires only read access on the source.

Question 8

A team is migrating an on-premises Microsoft SQL Server database to Cloud SQL for SQL Server using Database Migration Service (DMS). They need to ensure that the migration captures all schema changes and data modifications during the migration. Which TWO steps must they perform to enable continuous replication? (Choose two.)

Accepted Answer

Enable change data capture (CDC) on the source SQL Server database.. For DMS continuous replication from SQL Server, change data capture (CDC) must be enabled on the source database and tables, and the database must use the full recovery model to prevent log truncation before changes are captured. These two steps ensure that all data modifications are tracked and replicated. Other options describe unrelated replication methods or excessive privileges, and are not required for DMS.

Answer

Grant the DMS service account the sysadmin role on the source SQL Server.

Answer

Enable SQL Server Agent and create a job to periodically copy transaction log backups to Cloud Storage.

Answer

Create a replication distribution database on the source SQL Server.

Question 9

A retail company is migrating a 1 TB MySQL 8.0 database from on-premises to Cloud SQL for MySQL using Database Migration Service (DMS). They need to ensure that the migration captures all schema changes, including new tables and columns added during the migration. Which DMS feature should they enable?

Accepted Answer

Enable continuous replication to capture ongoing data changes and schema changes.. Continuous replication in DMS captures both data and schema changes by reading the source's binary log. This includes DDL statements like CREATE TABLE and ALTER TABLE, ensuring that new tables and columns are replicated. The other options either describe non-existent features or incorrect configurations. Thus, enabling continuous replication is the correct choice.

Answer

Enable the 'schema drift' option in DMS to automatically apply DDL changes.

Answer

Configure the source to use STATEMENT-based binary logging to capture DDL.

Answer

Use the 'online migration' mode in DMS, which automatically includes schema changes.

Question 10

You are migrating a 6 TB PostgreSQL 13 database to Cloud SQL for PostgreSQL 15 using Database Migration Service. The source has a table with a column of type json and several columns using the money type. The initial dump completed, but the CDC phase repeatedly fails on changes to that table. What is the most likely cause?

Accepted Answer

The source uses logical decoding with a plugin that does not emit values for unchanged TOASTed columns, causing the destination apply to fail.. Logical decoding with the default pgoutput plugin can omit the value of an unchanged TOASTed column, so updates to rows with large json values arrive without that column's data and fail on apply. Configuring replica identity to FULL makes the source emit complete old and new row images, resolving the CDC failures.

Answer

Cloud SQL for PostgreSQL 15 rejects the money type because it was deprecated after PostgreSQL 13.

Answer

The destination instance uses a smaller machine tier than the source, so applying large json changes exceeds memory during replay.

Answer

Database Migration Service does not support the json and money data types, so changes to tables containing them cannot be replicated.

Question 11

An organization is planning to migrate a small on-premises PostgreSQL database to Cloud SQL for PostgreSQL. They want to use the simplest possible method that requires minimal downtime and does not involve continuous replication. Which tool should they use to perform the migration?

Accepted Answer

Use the pg_dump and pg_restore utilities with the --single-transaction option for a consistent snapshot.. For a small PostgreSQL database, the simplest migration method with minimal downtime and no continuous replication is to use pg_dump with the --single-transaction option to create a consistent snapshot, then restore it using pg_restore. This approach avoids the complexity of DMS and the heavy locking of a plain pg_dump, making it ideal for small databases where a brief downtime is acceptable.

Answer

Use pg_dump and pg_restore with the --clean and --if-exists options.

Answer

Use Database Migration Service (DMS) in continuous migration mode.

Answer

Use the Cloud SQL Migration Utility (gcloud sql instances import) to import a dump file.

Question 12

An organization is migrating a 10 TB on-premises Oracle database to Cloud SQL for PostgreSQL. They have converted the schema using Ora2Pg and are now planning the data migration. The database has large tables with LOB columns. They want to minimize downtime and ensure data integrity. Which method should they use to migrate the data?

Accepted Answer

Use Database Migration Service (DMS) with an Oracle source and Cloud SQL for PostgreSQL destination, configured for continuous replication.. Database Migration Service (DMS) natively supports Oracle to Cloud SQL for PostgreSQL migrations, including continuous replication for minimal downtime. It handles schema conversion and data migration, including LOB columns, and is a fully managed service. Other methods either use incompatible formats, require third-party tools, or are unsuitable for LOBs and large scale. DMS is the recommended approach for this scenario.

Answer

Export the Oracle data to CSV files, transfer to Cloud Storage, and use the Cloud SQL import feature to load into PostgreSQL.

Answer

Use Striim or a similar third-party tool to replicate data from Oracle to PostgreSQL.

Answer

Use Oracle Data Pump to export the data to a file, transfer it to Cloud Storage, and import into Cloud SQL for PostgreSQL using pg_restore.

Question 13

A media company is migrating a large on-premises Cassandra cluster to Google Cloud Bigtable. They need to migrate data efficiently and ensure that the schema is correctly mapped. Which TWO steps should they take? (Choose two.)

Accepted Answer

Run a Spark job using the Bigtable Spark connector to read from Cassandra and write to Bigtable, mapping the schema appropriately.. Migrating from Cassandra to Bigtable requires a scalable data movement method and a careful schema design. Using a Spark job with the Bigtable Spark connector allows efficient reading from Cassandra and writing to Bigtable, while designing row keys based on Cassandra's primary key structure preserves query performance. These two steps address both data migration and schema mapping, which are essential for a successful migration.

Answer

Use Google Cloud Database Migration Service (DMS) to migrate Cassandra to Bigtable, as DMS supports NoSQL databases.

Answer

Use the Cassandra to Bigtable proxy to allow existing Cassandra clients to connect to Bigtable without code changes.

Answer

Export Cassandra data to CSV files using cqlsh COPY, then import into Bigtable using the HBase Java API.

Question 14

A team is migrating a 4 TB on-premises PostgreSQL 12 database to Cloud SQL for PostgreSQL 14 using Database Migration Service (DMS). The source has a high volume of write activity, and the team needs to minimize downtime. They have configured the source connection profile and the destination connection profile. What should they do next to start the migration with continuous replication?

Accepted Answer

Create a migration job in DMS, select 'Continuous' as the migration type, and start the job.. To migrate with minimal downtime using DMS, you create a migration job with continuous replication. This performs an initial snapshot and then replicates ongoing changes from the source to the destination using PostgreSQL logical replication. After creating the necessary connection profiles, the next logical step is to create and start the migration job with the continuous migration type. This ensures that data changes during the migration are captured, allowing for a short cutover at the end.

Answer

Configure the destination Cloud SQL instance with the same PostgreSQL version as the source, then use DMS with 'Continuous' migration type.

Answer

Set up a VPN tunnel between the on-premises network and Google Cloud, then use pg_dump and pg_restore to migrate the data.

Answer

Create a migration job in DMS, select 'One-time' as the migration type, and start the job.

Question 15

A team is migrating an on-premises Microsoft SQL Server database to Cloud SQL for SQL Server using Database Migration Service (DMS). The source database is 1 TB and has a high transaction rate. They want to minimize downtime and have configured the source and destination connection profiles. Which migration type should they choose in DMS to achieve minimal downtime?

Accepted Answer

Continuous. To minimize downtime for a high-transaction SQL Server database, continuous migration is required. It captures ongoing changes via CDC after the initial snapshot, allowing the source to remain operational. The final cutover requires only a brief downtime to stop writes and apply remaining changes. One-time migration would necessitate downtime throughout the migration, which is unacceptable for this scenario.

Answer

One-time with change data capture (CDC) enabled

Answer

One-time

Answer

Continuous with low-downtime mode