PCDE Plan and manage database infrastructure Practice Test 10 — 15 Questions

Question 1

Your team is migrating a 5 TB MySQL database from on-premises to Cloud SQL. The database receives 2,000 writes per second and the network link has 500 Mbps bandwidth. You need to minimize migration time with zero data loss. What should you do?

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

Accepted Answer

Configure Database Migration Service with a connectivity test and start continuous replication.. Database Migration Service (DMS) with continuous replication is the correct choice because it supports live migration with minimal downtime and zero data loss for large databases like 5 TB. DMS uses MySQL binlog-based replication to synchronize changes from the on-premises source to Cloud SQL while the source remains operational, then performs a cutover with only seconds of downtime. This approach handles the 2,000 writes/second load and 500 Mbps bandwidth efficiently by streaming incremental changes rather than transferring the entire 5 TB in one shot.

Answer

Export the database using mysqldump and import into Cloud SQL using the console.

Answer

Use gcloud sql import with a compressed CSV export from on-premises.

Answer

Set up a VPN, then use mysqldump with --master-data and pipe to mysql on Cloud SQL.

Question 2

Your organization requires that all database backups be stored in a different region for disaster recovery. You are using Cloud SQL for MySQL. What backup configuration should you use?

Accepted Answer

Enable automated backups and select a different region for the backup location.. Cloud SQL for MySQL allows you to specify a different region for automated backup storage, which satisfies the disaster recovery requirement of storing backups in a separate region. By selecting a different region for the backup location, you ensure that if the primary region fails, the backups remain accessible for recovery. This is the only built-in option that directly meets the cross-region backup requirement without additional manual steps.

Answer

Enable automated backups and select the same region as the instance.

Answer

Use on-demand exports to Cloud Storage in the same region.

Answer

Configure a multi-region Cloud Storage bucket and point automated backups there.

Question 3

You are designing a database architecture for a global e-commerce application. The application requires low-latency reads in multiple regions and must handle up to 100,000 writes per second globally. Which Google Cloud database solution should you use?

Accepted Answer

Cloud Spanner with multi-region configuration.. Cloud Spanner with a multi-region configuration is the correct choice because it provides globally distributed, strongly consistent relational database service with horizontal scalability, supporting over 100,000 writes per second across multiple regions while maintaining ACID transactions and low-latency reads via regional replicas. This meets the requirements of a global e-commerce application needing high write throughput and low read latency in multiple regions.

Answer

Firestore in multi-region mode.

Answer

Bigtable with multiple clusters.

Answer

Cloud SQL with cross-region replication.

Question 4

You run the above command to create a Spanner instance. Later, you need to increase the instance's compute capacity to handle higher traffic. What is the correct approach?

Accepted Answer

Run gcloud spanner instances update test-instance --nodes=5. Cloud Spanner allows you to increase the compute capacity of an existing instance by updating the node count using the `gcloud spanner instances update` command. This operation is performed online without downtime, as Spanner supports live resizing of nodes to handle increased traffic.

Answer

Use the console to change the instance configuration to a larger one.

Answer

Delete the instance and recreate with --nodes=5.

Answer

Create a new instance with a larger config and migrate data.

Question 5

A company is migrating an on-premises PostgreSQL database to Cloud SQL. The database is 2 TB and must have minimal downtime during migration. Which migration strategy should the Database Engineer recommend?

Accepted Answer

Use Database Migration Service to set up continuous replication from the on-premises database to Cloud SQL, then perform a minimal-downtime cutover.. The Database Migration Service (DMS) supports continuous replication from an on-premises PostgreSQL database to Cloud SQL using logical replication (pglogical or native PostgreSQL logical replication). This allows the source database to remain operational during the migration, and the cutover to Cloud SQL can be performed with minimal downtime, typically seconds to minutes, meeting the 2 TB size and low-downtime requirements.

Answer

Export the database using pg_dump and import into Cloud SQL using pg_restore during a maintenance window.

Answer

Set up PostgreSQL on Compute Engine and replicate data, then switch over to Cloud SQL.

Answer

Use Cloud SQL's built-in replication feature to connect directly to the on-premises database.

Question 6

A Database Engineer is responsible for managing a Cloud SQL for MySQL instance. The engineer needs to ensure that automated backups are retained for 14 days and that point-in-time recovery (PITR) is enabled. Which configuration should the engineer set?

Accepted Answer

Enable automated backups, set backup retention to 14 days, and enable binary logging.. Enabling automated backups with a 14-day retention ensures backup files are kept for the required duration, and enabling binary logging (which is required for PITR) allows point-in-time recovery by replaying transaction logs against a base backup. In Cloud SQL for MySQL, PITR is not automatically enabled with automated backups; binary logging must be explicitly enabled.

Answer

Create scheduled manual backups each day and retain them for 14 days; PITR is not needed because backups are daily.

Answer

Enable automated backups with a retention of 14 days; PITR is automatically enabled.

Answer

Enable automated backups with 14-day retention and set up a cross-region replica for disaster recovery.

Question 7

A company runs a critical application on Cloud SQL for PostgreSQL. The database engineer needs to ensure that if the primary instance fails, a standby instance in a different region can take over with minimal data loss. Which configuration should the Database Engineer implement?

Accepted Answer

Create a cross-region replica with asynchronous replication and manually promote it during a disaster.. Cloud SQL for PostgreSQL supports cross-region replicas with asynchronous replication, which allows a standby instance in a different region to be promoted manually during a disaster. This minimizes data loss by replicating changes asynchronously, though some transactions may be lost if the primary fails before replication completes. Automatic failover is not supported for cross-region replicas in Cloud SQL, so manual promotion is required.

Answer

Set up a second Cloud SQL instance and configure application-level dual-writes to both instances.

Answer

Configure high availability (HA) within the same region using a regional persistent disk.

Answer

Create a cross-region replica with synchronous replication and enable automatic failover.

Question 8

A Database Engineer is deploying a Cloud SQL for PostgreSQL instance for a financial services application that requires high availability and automatic failover. The engineer also needs to ensure that backups are taken daily and retained for 30 days. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Enable automated backups and set backup retention to 30 days.. Cloud SQL automated backups can be configured with a retention period of up to 365 days, and setting it to 30 days meets the requirement for daily backups with 30-day retention. Option C is correct because enabling high availability (HA) on a Cloud SQL instance with a regional persistent disk provides automatic failover to a standby instance in a different zone within the same region, ensuring high availability for the financial services application.

Answer

Create a cross-region replica and configure automatic failover.

Answer

Schedule a Cloud Scheduler job to export the database to Cloud Storage daily.

Answer

Enable point-in-time recovery (PITR) with a 7-day retention.

Question 9

A company's Cloud Spanner database currently uses a regional configuration in us-central1. Due to growth, the database must support global reads with low latency and maintain strong consistency. The database engineer is evaluating options. Which THREE considerations should the engineer include in the design? (Choose three.)

Accepted Answer

Select a configuration that places the leader region close to the majority of write traffic.. In a multi-region Cloud Spanner configuration, the leader region handles all writes and must be placed close to the majority of write traffic to minimize write latency. This ensures strong consistency, as all reads are served from the same leader region by default, and global reads with low latency require careful leader placement to avoid cross-region round trips.

Answer

Use interleaved tables to reduce the number of reads required for hierarchical data.

Answer

Add read replicas in remote regions to serve reads with eventual consistency.

Question 10

Your company is deploying a new application on Google Cloud and needs to choose a database solution. The application requires strong transactional consistency, complex SQL queries, and the ability to scale horizontally for read-heavy workloads. Which database service should you recommend?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it provides strong transactional consistency (ACID) across globally distributed nodes, supports complex SQL queries with standard SQL syntax, and offers horizontal scaling for read-heavy workloads through automatic sharding and read replicas. Unlike other options, Spanner uniquely combines these three requirements—strong consistency, SQL, and horizontal scaling—in a single managed service.

Answer

BigQuery

Answer

Cloud SQL

Answer

Cloud Firestore

Question 11

Your company runs a global application on Cloud Spanner. You notice that recent schema changes have caused a significant increase in latency for cross-node transactions. The previous schema used interleaved tables for parent-child relationships, but the new schema uses separate tables with foreign keys. What is the most likely cause of the increased latency?

Accepted Answer

The new schema uses foreign keys that require cross-node transactions.. The new schema uses separate tables with foreign keys instead of interleaved tables. In Cloud Spanner, interleaved tables guarantee that parent and child rows are co-located on the same split, allowing local joins without cross-node communication. Foreign keys between non-interleaved tables can reference rows stored on different splits, forcing distributed transactions that require two-phase commit across nodes, which significantly increases latency.

Answer

The new schema does not use commit timestamps for versioning.

Answer

The new schema lacks secondary indexes on foreign key columns.

Answer

The Spanner instance was not resized after the schema change.

Question 12

Arrange the steps to configure high availability for a Cloud SQL for MySQL instance.

Accepted Answer

Step 1: Create a Cloud SQL for MySQL instance. Step 2: Enable high availability and configure the standby zone. Step 3: Verify high availability and test failover.. Create instance first, then enable HA and set standby zone, then verify and test.

Answer

Step 1: Enable high availability and configure the standby zone. Step 2: Create a Cloud SQL for MySQL instance. Step 3: Verify high availability and test failover.

Answer

Step 1: Create a Cloud SQL for MySQL instance. Step 2: Verify high availability and test failover. Step 3: Enable high availability and configure the standby zone.

Answer

Step 1: Enable high availability and configure the standby zone. Step 2: Verify high availability and test failover. Step 3: Create a Cloud SQL for MySQL instance.

Question 13

Arrange the steps to set up database encryption with Cloud KMS for Cloud SQL.

Accepted Answer

1. Create KMS key ring and key. 2. Grant Cloud SQL service account access to the KMS key. 3. Create Cloud SQL instance with CMEK. 4. Verify encryption and test.. First create KMS key, grant access, then create instance with CMEK, verify, test.

Answer

1. Grant Cloud SQL service account access to the KMS key. 2. Create KMS key ring and key. 3. Create Cloud SQL instance with CMEK. 4. Verify encryption and test.

Answer

1. Create Cloud SQL instance with CMEK. 2. Create KMS key ring and key. 3. Grant Cloud SQL service account access to the KMS key. 4. Verify encryption and test.

Answer

1. Create KMS key ring and key. 2. Create Cloud SQL instance with CMEK. 3. Grant Cloud SQL service account access to the KMS key. 4. Verify encryption and test.

Question 14

Match each Google Cloud database service to its primary use case.

Accepted Answer

Cloud SQL: Relational database for standard workloads. The correct matches are Cloud SQL for standard relational workloads, Cloud Spanner for globally distributed transactions, and Bigtable for analytical workloads. Firestore is for mobile/web apps, and Memorystore is for caching. Common mistakes include confusing the use cases of these services.

Answer

Firestore: Horizontally scalable relational database for global transactions

Answer

Memorystore: Relational database for standard workloads

Question 15

Match each database migration term to its description.

Accepted Answer

Homogeneous migration: Migrating a database from one source engine to the same target engine.. The correct matches are: Homogeneous migration = same engine, Heterogeneous migration = different engine, Database Migration Service = Google Cloud's database migration tool. Common confusions involve swapping the definitions of homogeneous and heterogeneous, or misattributing DMS to application migration.

Answer

Homogeneous migration: Migrating a database to a different database engine.

Answer

Heterogeneous migration: Migrating a database to the same database engine.

Answer

Database Migration Service (DMS): A tool for migrating applications to compute engines.