Google Professional Cloud Database Engineer Exam Questions Instant Access

Question 1

You manage a production MySQL database running on Cloud SQL at a retail company. You perform routine maintenance on Sunday at midnight when traffic is slow, but you want to skip routine maintenance during the year-end holiday shopping season. You need to ensure that your production system is available 24/7 during the holidays. What should you do?

ADefine a maintenance window on Sundays between 12 AM and 1 AM, and deny maintenance periods between November 1 and January 15.

BDefine a maintenance window on Sundays between 12 AM and 5 AM, and deny maintenance periods between November 1 and February 15.

CBuild a Cloud Composer job to start a maintenance window on Sundays between 12 AM and 1AM, and deny maintenance periods between November 1 and January 15.

DCreate a Cloud Scheduler job to start maintenance at 12 AM on Sundays. Pause the Cloud Scheduler job between November 1 and January 15.

Answer : A

'Deny maintenance period. A block of days in which Cloud SQL does not schedule maintenance. Deny maintenance periods can be up to 90 days long. ' https://cloud.google.com/sql/docs/mysql/maintenance

Question 2

Your company is migrating their MySQL database to Cloud SQL and cannot afford any planned downtime during the month of December. The company is also concerned with cost, so you need the most cost-effective solution. What should you do?

AOpen a support ticket in Google Cloud to prevent any maintenance in that MySQL instance during the month of December.

BUse Cloud SQL maintenance settings to prevent any maintenance during the month of December.

CCreate MySQL read replicas in different zones so that, if any downtime occurs, the read replicas will act as the primary instance during the month of December.

DCreate a MySQL regional instance so that, if any downtime occurs, the standby instance will act as the primary instance during the month of December.

Answer : B

https://cloud.google.com/sql/docs/mysql/maintenance?hl=fr

Question 3

Your application uses Cloud SQL for MySQL. Your users run reports on data that relies on near-real time; however, the additional analytics caused excessive load on the primary database. You created a read replica for the analytics workloads, but now your users are complaining about the lag in data changes and that their reports are still slow. You need to improve the report performance and shorten the lag in data replication without making changes to the current reports. Which two approaches should you implement? (Choose two.)

ACreate secondary indexes on the replica.

BCreate additional read replicas, and partition your analytics users to use different read replicas.

CDisable replication on the read replica, and set the flag for parallel replication on the read replica. Re-enable replication and optimize performance by setting flags on the primary instance.

DDisable replication on the primary instance, and set the flag for parallel replication on the primary instance. Re-enable replication and optimize performance by setting flags on the read replica.

EMove your analytics workloads to BigQuery, and set up a streaming pipeline to move data and update BigQuery.

Answer : B, C

Replication lag and slow report performance. E is eliminated because using BigQuery would mean changes to the current reports. Report slowness could be the result of poor indexing or just too much read load (or both!). Since excessive load is mentioned in the question, creating additional read replicas and spreading the analytics workload around makes B correct and eliminates A as a way to speed up reporting. That leaves the replication problem. Cloud SQL enables single threaded replication by default, so it stands to reason enabling parallel replication would help the lag. To do that you disable replication on the replica (not the primary), set flags on the replica and optionally set flags on the primary instance to optimize performance for parallel replication. That makes C correct and D incorrect. https://cloud.google.com/sql/docs/mysql/replication/manage-replicas#configuring-parallel-replication

Question 4

Your organization has hundreds of Cloud SQL for MySQL instances. You want to follow Google-recommended practices to optimize platform costs. What should you do?

AUse Query Insights to identify idle instances.

BRemove inactive user accounts.

CRun the Recommender API to identify overprovisioned instances.

DBuild indexes on heavily accessed tables.

Answer : C

The Cloud SQL overprovisioned instance recommender helps you detect instances that are unnecessarily large for a given workload. It then provides recommendations on how to resize such instances and reduce cost. This page describes how this recommender works and how to use it. https://cloud.google.com/sql/docs/mysql/recommender-sql-overprovisioned#:~:text=The%20Cloud%20SQL%20overprovisioned%20instance%20recommender%20helps%20you%20detect%20instances%20that%20are%20unnecessarily%20large%20for%20a%20given%20workload.%20It%20then%20provides%20recommendations%20on%20how%20to%20resize%20such%20instances%20and%20reduce%20cost.%20This%20page%20describes%20how%20this%20recommender%20works%20and%20how%20to%20use%20it.

Question 5

Your company is using Cloud SQL for MySQL with an internal (private) IP address and wants to replicate some tables into BigQuery in near-real time for analytics and machine learning. You need to ensure that replication is fast and reliable and uses Google-managed services. What should you do?

ADevelop a custom data replication service to send data into BigQuery.

BUse Cloud SQL federated queries.

CUse Database Migration Service to replicate tables into BigQuery.

DUse Datastream to capture changes, and use Dataflow to write those changes to BigQuery.

Answer : D

''Datastream is a serverless and easy-to-use Change Data Capture (CDC) and replication service that allows you to synchronize data across heterogeneous databases, storage systems, and applications reliably and with minimal latency. Datastream supports change data streaming from Oracle and MySQL databases to Google Cloud Storage (GCS). The service offers streamlined integration with Dataflow templates to power up to date materialized views in BigQuery for analytics, replicate their databases into Cloud SQL or Cloud Spanner for database synchronization, or leverage the event stream directly from GCS to realize event-driven architectures.''

Question 6

You are running a mission-critical application on a Cloud SQL for PostgreSQL database with a multi-zonal setup. The primary and read replica instances are in the same region but in different zones. You need to ensure that you split the application load between both instances. What should you do?

AUse Cloud Load Balancing for load balancing between the Cloud SQL primary and read replica instances.

BUse PgBouncer to set up database connection pooling between the Cloud SQL primary and read replica instances.

CUse HTTP(S) Load Balancing for database connection pooling between the Cloud SQL primary and read replica instances.

DUse the Cloud SQL Auth proxy for database connection pooling between the Cloud SQL primary and read replica instances.

Answer : B

https://severalnines.com/blog/how-achieve-postgresql-high-availability-pgbouncer/

https://cloud.google.com/blog/products/databases/using-haproxy-to-scale-read-only-workloads-on-cloud-sql-for-postgresql

This answer is correct because PgBouncer is a lightweight connection pooler for PostgreSQL that can help you distribute read requests between the Cloud SQL primary and read replica instances1.PgBouncer can also improve performance and scalability by reducing the overhead of creating new connections and reusing existing ones1.You can install PgBouncer on a Compute Engine instance and configure it to connect to the Cloud SQL instances using private IP addresses or the Cloud SQL Auth proxy2.

Question 7

You want to migrate an on-premises mission-critical PostgreSQL database to Cloud SQL. The database must be able to withstand a zonal failure with less than five minutes of downtime and still not lose any transactions. You want to follow Google-recommended practices for the migration. What should you do?

ATake nightly snapshots of the primary database instance, and restore them in a secondary zone.

BBuild a change data capture (CDC) pipeline to read transactions from the primary instance, and replicate them to a secondary instance.

CCreate a read replica in another region, and promote the read replica if a failure occurs.

DEnable high availability (HA) for the database to make it regional.

Answer : D