PCDOE Manage Database Solutions Practice Test 8 — 15 Questions

Question 1

A database administrator needs to restore a Cloud SQL for PostgreSQL instance to a specific point in time within the last 3 hours. Which configurations must be enabled to perform a point-in-time recovery (PITR)?

Accepted Answer

Automated backups and WAL archiving with a retention period. PITR in Cloud SQL for PostgreSQL requires automated backups enabled and write-ahead log (WAL) archiving with a specified transaction log retention period (1-7 days). Binary logging is for MySQL, not PostgreSQL.

Answer

Automated backups and binary logging

Answer

On-demand backups and binary logging

Answer

Automated backups and cross-region replicas

Question 2

An organization runs a Cloud SQL for MySQL instance for its e-commerce platform. During a load test, they notice the CPU utilization consistently exceeds 80% and queries are slowing down. The instance is using 2 vCPUs. The team needs to improve performance with minimal downtime. What should they do?

Accepted Answer

Increase the number of vCPUs and memory via vertical scaling. Increasing vCPUs and memory vertically (scaling up) is the quickest way to improve CPU-bound performance with minimal downtime. Cloud SQL supports online vertical scaling. Read replicas are for read offloading, not CPU-bound write performance. Auto-storage increase addresses storage, not CPU.

Answer

Create a cross-region read replica

Answer

Enable automatic storage increase

Answer

Shard the database using application-level sharding

Question 3

A company uses Cloud Spanner for a global inventory system. They need to add a new index on an existing table to support a new query pattern without downtime. What is the correct approach to create the index?

Accepted Answer

Use the gcloud CLI to create the index; Spanner handles it as an online, non-blocking operation. Option D is correct because Cloud Spanner supports online schema changes, including index creation, without requiring downtime. When you use the gcloud CLI or the Cloud Console to create an index, Spanner performs the operation as a non-blocking, asynchronous background process that allows continued reads and writes on the table.

Answer

Create a new table with the index and migrate data manually

Answer

Use the Cloud Console to create the index; it requires a maintenance window

Answer

Take the table offline, create the index, then bring it back online

Question 4

A financial services company uses Cloud Bigtable for real-time fraud detection. They have a cluster with 10 nodes using HDD storage and are experiencing high latency due to disk throughput bottlenecks. They need to improve performance with minimal downtime. What should they do?

Accepted Answer

Create a new cluster with SSD storage, replicate data, then update the application to point to the new cluster. Option B is correct because Cloud Bigtable does not support in-place conversion of storage from HDD to SSD. The only way to migrate to SSD storage is to create a new cluster with SSD, replicate data using Bigtable replication, and then update the application connection string to point to the new cluster. This approach minimizes downtime by allowing the old cluster to serve reads during replication.

Answer

Modify the existing cluster's storage type to SSD via the Cloud Console

Answer

Use Cloud Bigtable's hot tablet detection to rebalance the data

Answer

Increase the number of nodes in the existing cluster

Question 5

A startup uses Memorystore for Redis as a session store. They are concerned about losing session data if the instance fails. Which option provides the best durability for session data without significant application changes?

Accepted Answer

Configure a cross-region replica to provide high availability. Option D is correct because configuring a cross-region replica in Memorystore for Redis provides high availability and durability by replicating data asynchronously to a replica instance in a different region. If the primary instance fails, the replica can be promoted, minimizing session data loss without requiring significant application changes, as the application continues to connect to the same endpoint or a new primary endpoint.

Answer

Increase the maxmemory setting to store more data

Answer

Take regular snapshots of the instance using gcloud commands

Answer

Enable Redis persistence (RDB/AOF) on the Memorystore instance

Question 6

A Cloud Spanner instance is backing up a 2 TB database daily to Cloud Storage using the built-in backup feature. The compliance team requires the backup to be stored in a specific regional bucket with a retention policy of 14 days. How should the database administrator configure this?

Accepted Answer

Use the gcloud spanner databases export command to export the database to a Cloud Storage bucket in the desired region, then set a retention policy on the bucket. Spanner database backups are stored as managed backups within the Spanner service, not directly as files in Cloud Storage. However, they can be exported to Cloud Storage using the export API. The export can be configured to a specific bucket and region, and object lifecycle rules can enforce retention.

Answer

Schedule a cron job to copy the backup from Spanner's default location to the regional bucket

Answer

Use the CREATE BACKUP statement and specify a Cloud Storage bucket in the desired region

Answer

Use the backup retention period in Spanner's backup settings to keep backups for 14 days

Question 7

A gaming company uses Firestore in Native mode to store player profiles and game state. They need to query the data by both 'playerId' and 'lastLoginTimestamp' sorted descending. The current index configuration is automatic. How should they configure indexing to support this query efficiently?

Accepted Answer

Create a composite index on 'playerId' ascending and 'lastLoginTimestamp' descending. Firestore automatically creates single-field indexes for all fields. For queries with ordering on two fields, a composite index is required. The composite index must include both fields in the correct order (ascending or descending).

Answer

Create two separate single-field indexes: one on 'playerId' and one on 'lastLoginTimestamp'

Answer

Use an exemption to remove automatic indexing on 'playerId' and rely on single-field indexes

Answer

Use the automatic index configuration; Firestore will create the necessary composite index automatically

Question 8

A DevOps engineer needs to monitor Cloud SQL for MySQL replication lag on a read replica to ensure data freshness. Which Cloud Monitoring metric should they create an alert on?

Accepted Answer

cloudsql.googleapis.com/database/replication/lag. The metric 'replication_lag' in Cloud SQL measures the seconds behind the primary. Cloud SQL for MySQL uses 'seconds_behind_master' internally, but the exposed metric is 'replication_lag'.

Answer

cloudsql.googleapis.com/database/replica/state

Answer

cloudsql.googleapis.com/database/postgresql/replication/lag

Answer

cloudsql.googleapis.com/database/mysql/replication/seconds_behind_master

Question 9

A company runs a Cloud SQL for PostgreSQL instance that is nearing its storage limit of 500 GB. The database administrator needs to increase storage capacity online without any downtime. What should they do?

Accepted Answer

Use the gcloud sql instances patch command with --storage-size flag. Option A is correct because the `gcloud sql instances patch` command with the `--storage-size` flag allows you to increase the storage capacity of a Cloud SQL for PostgreSQL instance online without any downtime. Cloud SQL supports live storage increases by automatically resizing the underlying persistent disk while the instance remains available, so no restart or maintenance window is required.

Answer

Enable automatic storage increase and wait for it to trigger

Answer

Delete and recreate the instance with a larger disk

Answer

Shut down the instance, increase the disk size, and restart

Question 10

A large enterprise uses Cloud Bigtable for analytics. They notice that some nodes are handling significantly more traffic than others, causing hot spots and performance degradation. Which tool should they use to identify the specific row keys causing the issue?

Accepted Answer

Bigtable Key Visualiser. Key Visualiser is a Bigtable tool that provides a heatmap of row key access patterns, helping to identify hot spots by showing which row key ranges are heavily accessed.

Answer

Stackdriver Error Reporting

Answer

Bigtable cbt tool with read rows command

Answer

Cloud Monitoring dashboard for Bigtable

Question 11

A developer is setting up a Memorystore for Redis instance and needs to restrict access to only a specific Compute Engine VM in the same VPC network. Which configuration should they use?

Accepted Answer

Configure the Memorystore instance with a firewall rule that allows only the VM's internal IP. Memorystore for Redis uses Private Service Access with VPC peering. Access control is via authorized networks (VPC networks) and optionally by using Cloud Armor or firewall rules at the VM level. The simplest is to authorize the VM's VPC network and then use a firewall rule to allow traffic only from the specific VM's IP.

Answer

Use Cloud Armor to whitelist the VM's external IP

Answer

Use the AUTH password and share it only with the VM

Answer

Place the Redis instance in a separate VPC and peer only with the VM's VPC

Question 12

A company has a Cloud SQL for MySQL instance with a cross-region read replica for disaster recovery. During a regional outage, they need to promote the read replica to a standalone instance as quickly as possible. What is the correct procedure?

Accepted Answer

Use the gcloud sql instances promote-replica command on the replica. Promoting a read replica makes it a standalone instance. It can be done via the Cloud Console or gcloud command. The promotion is immediate, but the original primary may still be active if not stopped.

Answer

Create a backup of the replica and restore it as a new instance

Answer

Delete the read replica and restore a backup from the primary as a new instance

Answer

Stop replication on the replica by issuing STOP SLAVE on the instance

Question 13

A company uses Cloud Spanner for a global application. They need to capture real-time changes to certain tables for downstream processing. Which two services or features can be used together to achieve this? (Choose TWO.)

Accepted Answer

Spanner change streams. Spanner change streams capture transactional changes in near real-time. Dataflow can read from change streams and stream the changes to downstream systems like Pub/Sub, BigQuery, or Cloud Storage.

Answer

Cloud Pub/Sub Lite

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Functions

Question 14

A company runs a Memorystore for Redis cluster with standard tier (for replication). They need to ensure high durability of session data and be able to recover from a complete zone failure. Which three actions should they take? (Choose THREE.)

Accepted Answer

Schedule regular exports of the instance data to Cloud Storage using gcloud. For durability, cross-region replication provides HA across zones. Enabling persistence (RDB/AOF) is not supported in Memorystore. Regular exports to Cloud Storage can be used for backup. Scaling horizontally with Redis Cluster can improve performance but not durability. AUTH is for security, not durability.

Answer

Use a Redis Cluster with multiple shards for horizontal scaling

Answer

Enable RDB persistence on the primary instance

Question 15

A company wants to migrate from an on-premises MySQL database to Cloud SQL. They need to minimize downtime and ensure data consistency. Which two steps should they include in the migration plan? (Choose TWO.)

Accepted Answer

Set up continuous replication from the on-premises database to the Cloud SQL instance using Database Migration Service. Option C is correct because Database Migration Service (DMS) supports continuous replication using MySQL binary logs, enabling a near-zero-downtime migration by keeping the Cloud SQL instance synchronized with the on-premises database until cutover. Option D is correct because taking a consistent snapshot (e.g., using mysqldump with --single-transaction or a storage-level snapshot) at cutover ensures data consistency and provides a fallback point if the migration fails.

Answer

Create a read replica in the same region for validation before cutover

Answer

Use Cloud SQL's import functionality to load a mysqldump file

Answer

Disable binary logging on the on-premises database to reduce overhead