PCDBE practice questions 51–60

1. Question 51 · 170 in bank

   Your organization deployed a new version of a critical application that uses Cloud SQL for MySQL with high availability (HA) and binary logging enabled to store transactional information. The latest release of the application had an error that caused massive data corruption in your Cloud SQL for MySQL database. You need to minimize data loss. What should you do?

   • AOpen the Google Cloud Console, navigate to SQL > Backups, and select the last version of the automated backup before the corruption.
   • BReload the Cloud SQL for MySQL database using the LOAD DATA command to load data from CSV files that were used to initialize the instance.
   • CPerform a point-in-time recovery of your Cloud SQL for MySQL database, selecting a date and time before the data was corrupted.
   • DFail over to the Cloud SQL for MySQL HA instance. Use that instance to recover the transactions that occurred before the corruption.

   Show answer

   Answer: C

   Explanation

   C. The question specifically mentions binary logging and the binary logs are used by point-in-time recovery. D doesn’t buy you anything since the corrupt data would also be on the HA replica you fail over to. B looks like a lot of work and if the Cloud SQL instance were instantiated a while ago, option B could take a long time. A would work but the backup could have been taken a while before the corruption began. In which case restoring using that backup would wipe all the good data up to the point of corruption. The question asks for minimal data loss and the only way to ensure that is to restore to a point-in-time just before the corruption began.

   Source: Adapted from community discussion — dynamic_dba

   References

   • Google Cloud documentation

   Next question

2. Question 52 · 170 in bank

   Select 2

   You plan to use Database Migration Service to migrate data from a PostgreSQL on-premises instance to Cloud SQL. You need to identify the prerequisites for creating and automating the task. What should you do? (Choose two.)

   • ADrop or disable all users except database administration users.
   • BDisable all foreign key constraints on the source PostgreSQL database.
   • CEnsure that all PostgreSQL tables have a primary key.
   • DShut down the database before the Data Migration Service task is started.
   • EEnsure that pglogical is installed on the source PostgreSQL database.

   Show answer

   Answer: CE

   Explanation

   C, E. A is wrong because you want user accounts migrated as well as the data. B is nonsense. D is also nonsense since the DMS is an online migration. That leave C and E, both of which are mentioned in the Google doc: https://cloud.google.com/database-migration/docs/postgres/configure-source-database

   Source: Adapted from community discussion — dynamic_dba

   References

   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation

   Next question

3. Question 53 · 170 in bank

   You are using Compute Engine on Google Cloud and your data center to manage a set of MySQL databases in a hybrid configuration. You need to create replicas to scale reads and to offload part of the management operation. What should you do?

   • AUse external server replication.
   • BUse Data Migration Service.
   • CUse Cloud SQL for MySQL external replica.
   • DUse the mysqldump utility and binary logs.

   Show answer

   Answer: A

   Explanation

   C. D is nonsense, so can be eliminated. The question tells us we’re already managing BOTH sets of MySQL instances, one on prem and the other in GCE. The question also says an objective is to offload part of the management. That can only mean leverage a managed service. The Data(base) Migration Service is a managed service used to instantiate a new migrated DB in Cloud SQL (or AlloyDB for PostgreSQL but that’s not in scope here). The question isn’t asking about database migration, so we can eliminate B. A could be used to create replicas, but doesn’t help with offloading management operations. That leaves C which does use a managed service which could be leveraged to create replicas.

   Source: Adapted from community discussion — dynamic_dba

   References

   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation

   Next question

4. Question 54 · 170 in bank

   Your company is migrating the existing infrastructure for a highly transactional application to Google Cloud. You have several databases in a MySQL database instance and need to decide how to transfer the data to Cloud SQL. You need to minimize the downtime for the migration of your 500 GB instance. What should you do?

   • A1. Create a Cloud SQL for MySQL instance for your databases, and configure Datastream to stream your database changes to Cloud SQL. 2. Select the Backfill historical data check box on your stream configuration to initiate Datastream to backfill any data that is out of sync between the source and destination. 3. Delete your stream when all changes are moved to Cloud SQL for MySQL, and update your application to use the new instance.
   • B1. Create migration job using Database Migration Service. 2. Set the migration job type to Continuous, and allow the databases to complete the full dump phase and start sending data in change data capture (CDC) mode. 3. Wait for the replication delay to minimize, initiate a promotion of the new Cloud SQL instance, and wait for the migration job to complete. 4. Update your application connections to the new instance.
   • C1. Create migration job using Database Migration Service. 2. Set the migration job type to One-time, and perform this migration during a maintenance window. 3. Stop all write workloads to the source database and initiate the dump. Wait for the dump to be loaded into the Cloud SQL destination database and the destination database to be promoted to the primary database. 4. Update your application connections to the new instance.
   • D1. Use the mysqldump utility to manually initiate a backup of MySQL during the application maintenance window. 2. Move the files to Cloud Storage, and import each database into your Cloud SQL instance. 3. Continue to dump each database until all the databases are migrated. 4. Update your application connections to the new instance.

   Show answer

   Answer: B

   Explanation

   B. A is wrong because Datastream is a CDC and replication service for data synchronization across heterogeneous databases. It’s reasonable to assume you’ll be using Cloud SQL for MySQL, so you’ll be performing a homogeneous migration. Plus, while the a Datastream source can be MySQL, a Datastream target is either BigQuery or Cloud Storage and not Cloud SQL. See https://cloud.google.com/datastream/docs/overview. C is wrong because a one time migration wouldn’t capture all the data changes once the maintenance window ended and the apps were fired back up. Furthermore, stopping all writes during the dump would constitute downtime which the question wants minimized. D would take forever in a rapidly changing source system. B is the cleanest and simplest solution especially since the question puts no time constraint on making the migration happen.

   Source: Adapted from community discussion — dynamic_dba

   References

   • Google Cloud documentation

   Next question

5. Question 55 · 170 in bank

   Your company wants to move to Google Cloud. Your current data center is closing in six months. You are running a large, highly transactional Oracle application footprint on VMWare. You need to design a solution with minimal disruption to the current architecture and provide ease of migration to Google Cloud. What should you do?

   • AMigrate applications and Oracle databases to Google Cloud VMware Engine (VMware Engine).
   • BMigrate applications and Oracle databases to Compute Engine.
   • CMigrate applications to Cloud SQL.
   • DMigrate applications and Oracle databases to Google Kubernetes Engine (GKE).

   Show answer

   Answer: A

   Explanation

   A. The key here is the current architecture and minimal disruption to it. The simplest way to keep the current architecture is a live migrate using VMware. That can only mean one thing, use Oracle running in GCVE. You could do B. There's nothing stopping you creating a VM in GCE, copying the Oracle binaries to it and spinning up an Oracle database or several. However, the licensing costs would not be attractive (if even supported), plus the migration would likely be disruptive. C is wrong because Cloud SQL doesn't support Oracle. D is wrong because that represents an architecture change.

   Source: Adapted from community discussion — dynamic_dba

   References

   • Google Cloud documentation

   Next question

6. Question 56 · 170 in bank

   Your company uses the Cloud SQL out-of-disk recommender to analyze the storage utilization trends of production databases over the last 30 days. Your database operations team uses these recommendations to proactively monitor storage utilization and implement corrective actions. You receive a recommendation that the instance is likely to run out of disk space. What should you do to address this storage alert?

   • ANormalize the database to the third normal form.
   • BCompress the data using a different compression algorithm.
   • CManually or automatically increase the storage capacity.
   • DCreate another schema to load older data.

   Show answer

   Answer: C

   Explanation

   C. A is wrong since modifying the schemas to 3NF would use more disk. D is nonsense. B sounds vague at best and probably not supported at the database level. C is the best answer. The link provided by Kloudgeek is spot on.

   Source: Adapted from community discussion — dynamic_dba

   References

   • Google Cloud documentation
   • Google Cloud documentation

   Next question

7. Question 57 · 170 in bank

   You are managing a mission-critical Cloud SQL for PostgreSQL instance. Your application team is running important transactions on the database when another DBA starts an on-demand backup. You want to verify the status of the backup. What should you do?

   • ACheck the cloudsql.googleapis.com/postgres.log instance log.
   • BPerform the gcloud sql operations list command.
   • CUse Cloud Audit Logs to verify the status.
   • DUse the Google Cloud Console.

   Show answer

   Answer: B

   Explanation

   Should be B Perform the gcloud sql operations list command https://cloud.google.com/sql/docs/postgres/backup-recovery/backups#troubleshooting-backups Under Troubleshooting: Issue: "You can't see the current operation's status." The Google Cloud console reports only success or failure when the operation is done. It isn't designed to show warnings or other updates. Run the gcloud sql operations list command to list all operations for the given Cloud SQL instance. A and C iis wrong because there are used for WHO issued the Backup, but not the current status of the backup D is wrong only shows success or failure in the Cloud Console but not the current status of the backup

   Source: Adapted from community discussion — Sekierer

   References

   • Google Cloud documentation

   Next question

8. Question 58 · 170 in bank

   You support a consumer inventory application that runs on a multi-region instance of Cloud Spanner. A customer opened a support ticket to complain about slow response times. You notice a Cloud Monitoring alert about high CPU utilization. You want to follow Google-recommended practices to address the CPU performance issue. What should you do first?

   • AIncrease the number of processing units.
   • BModify the database schema, and add additional indexes.
   • CShard data required by the application into multiple instances.
   • DDecrease the number of processing units.

   Show answer

   Answer: A

   Explanation

   In case of high CPU utilization like, mentioned in question, refer: https://cloud.google.com/spanner/docs/identify-latency-point#:~:text=Check%20the%20CPU%20utilization%20of%20the%20instance.%20If%20the%20CPU%20utilization%20of%20the%20instance%20is%20above%20the%20recommended%20level%2C%20you%20should%20manually%20add%20more%20nodes%2C%20or%20set%20up%20auto%20scaling. "Check the CPU utilization of the instance. If the CPU utilization of the instance is above the recommended level, you should manually add more nodes, or set up auto scaling." Indexes and schema are reviewed post identifying query with slow performance. Refer : https://cloud.google.com/spanner/docs/troubleshooting-performance-regressions#review-schema

   Source: Adapted from community discussion — njda

   References

   • Google Cloud documentation
   • Google Cloud documentation

   Next question

9. Question 59 · 170 in bank

   Your company uses Bigtable for a user-facing application that displays a low-latency real-time dashboard. You need to recommend the optimal storage type for this read-intensive database. What should you do?

   • ARecommend solid-state drives (SSD).
   • BRecommend splitting the Bigtable instance into two instances in order to load balance the concurrent reads.
   • CRecommend hard disk drives (HDD).
   • DRecommend mixed storage types.

   Show answer

   Answer: A

   Explanation

   A. When you create a Bigtable instance you have to choose either SSD or HDD. The SSD options says, “Lower latency and more rows read per second. Typically used for real-time serving use cases, such as ad serving and mobile app recommendations”. User facing plus low latency plus read intensive equals SSD.

   Source: Adapted from community discussion — dynamic_dba

   References

   • Google Cloud documentation

   Next question

10. Question 60 · 170 in bank

    Your organization has a critical business app that is running with a Cloud SQL for MySQL backend database. Your company wants to build the most fault-tolerant and highly available solution possible. You need to ensure that the application database can survive a zonal and regional failure with a primary region of us-central1 and the backup region of us-east1. What should you do?

    • A1. Provision a Cloud SQL for MySQL instance in us-central1-a. 2. Create a multiple-zone instance in us-west1-b. 3. Create a read replica in us-east1-c.
    • B1. Provision a Cloud SQL for MySQL instance in us-central1-a. 2. Create a multiple-zone instance in us-central1-b. 3. Create a read replica in us-east1-b.
    • C1. Provision a Cloud SQL for MySQL instance in us-central1-a. 2. Create a multiple-zone instance in us-east-b. 3. Create a read replica in us-east1-c.
    • D1. Provision a Cloud SQL for MySQL instance in us-central1-a. 2. Create a multiple-zone instance in us-east1-b. 3. Create a read replica in us-central1-b.

    Show answer

    Answer: B

    Explanation

    B. Cloud SQL is a regional service with read replicas allowed in other regions. So the answer must reference 2 different zones in the us-central1 region, one for the primary and one for the HA replica. A read replica needs to be in a zone within us-east1. The only options which provides that is B.

    Source: Adapted from community discussion — dynamic_dba

    References

    • Google Cloud documentation

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

PCDBE practice, question 51. 170 questions available.