PCD Practice Test 21 — 15 Questions

Question 1

A company is migrating an on-premises MySQL 5.7 database to Cloud SQL for MySQL 8.0. The database includes MyISAM tables. What is the required action for these tables?

Accepted Answer

Convert MyISAM tables to InnoDB. Cloud SQL for MySQL 8.0 does not support MyISAM as the default storage engine; MyISAM tables must be converted to InnoDB. InnoDB provides ACID compliance, row-level locking, and crash recovery.

Answer

Use a MySQL 5.7 compatible Cloud SQL instance

Answer

Keep MyISAM tables as is

Answer

Migrate to Cloud Spanner instead

Question 2

An organization is migrating a 2 TB Oracle database to Cloud SQL for PostgreSQL. They need to minimize downtime and have a limited maintenance window of 30 minutes. Which migration strategy should they use?

Accepted Answer

Use DMS with continuous migration (CDC) and promote the replica for cutover.. Database Migration Service (DMS) with continuous migration provides an online migration approach with minimal downtime. The full dump and continuous CDC replication allow the source to stay operational until cutover, which can be performed in a short window.

Answer

Export the database as a SQL dump and import into Cloud SQL.

Answer

Use BigQuery Data Transfer Service to migrate the data.

Answer

Use a one-time migration job with DMS.

Question 3

A company is migrating their on-premises SQL Server database to Cloud SQL for SQL Server. They need to ensure that schema changes are version-controlled and repeatable across environments. Which tool should they use?

Accepted Answer

Flyway. Flyway is a database migration tool that supports version-controlled schema migrations and integrates well with CI/CD pipelines. It is commonly used for SQL Server and Cloud SQL.

Answer

Cloud Deployment Manager

Answer

Cloud Build

Answer

Database Migration Service

Question 4

An engineer is deploying AlloyDB for PostgreSQL and needs to support read scaling with automatic scaling of read replicas based on load. They also want to ensure minimal operational overhead. What should they use?

Accepted Answer

Create a read pool instance with autoscaling enabled.. AlloyDB read pool instances provide a set of read replicas that can be configured with autoscaling. The pool manages the number of replicas based on load (e.g., CPU utilization). 'Read pool autoscaling' is the feature. Cross-region replication is for disaster recovery, not read scaling. AlloyDB Omni is for on-premises. Manual read replicas require operational overhead.

Answer

Manually create additional read replicas and set up a load balancer.

Answer

Create multiple cross-region replicas.

Answer

Use AlloyDB Omni to deploy replicas on-premises.

Question 5

An organization is migrating from Oracle to Cloud SQL for PostgreSQL using Ora2Pg. After conversion, several stored procedures fail to compile. Which step should they take to identify and fix the issues?

Accepted Answer

Re-run Ora2Pg with the --debug flag to get detailed error output.. Ora2Pg provides a log file with conversion details and error messages. Reviewing this log helps identify syntax differences and unsupported features that need manual adjustment.

Answer

Manually rewrite all procedures from PL/SQL to PL/pgSQL.

Answer

Use Database Migration Service continuous migration to replicate the procedures.

Answer

Convert the Oracle database to PostgreSQL using pgloader instead.

Question 6

A data engineering team needs to migrate a 10 TB Teradata data warehouse to BigQuery. They want to automate the migration of historical data and ongoing changes. Which Google Cloud service should they use?

Accepted Answer

BigQuery Data Transfer Service. BigQuery Data Transfer Service can automate data transfers from Teradata (and other sources) into BigQuery. It handles both historical and incremental loads.

Answer

Dataproc

Answer

Cloud Data Fusion

Answer

Cloud SQL for PostgreSQL

Question 7

A company is evaluating Cloud Bigtable for a high-throughput write workload. They need to monitor disk usage and request latency. Which metrics should they monitor? (Select the best combination.)

Accepted Answer

disk/bytes_used and request_latency. Bigtable provides 'disk/bytes_used' for disk usage and 'request_latency' for latency. 'cpu/utilisation' is not a Bigtable metric; Bigtable uses 'cpu_load' and 'server_latency' but request latency is the key metric. 'database/queries' is a Cloud SQL metric. 'rows_deleted' is a Spanner metric.

Answer

cpu/utilisation and database/queries

Answer

disk/bytes_used and rows_deleted

Answer

request_latency and database/memory/utilisation

Question 8

A company has a Cloud Spanner instance with 1000 processing units and wants to enable auto-scaling. Which three parameters must be configured? (Choose THREE.)

Accepted Answer

Maximum processing units. When enabling auto-scaling for Cloud Spanner, you must set the minimum and maximum processing units (or nodes) and the target high-priority CPU utilization. Scaling increments and scaling limits are not direct parameters. The instance type is fixed.

Answer

Scaling increment (e.g., 100 units)

Answer

Instance type (regional or multi-region)

Question 9

A team is deploying a Cloud SQL for PostgreSQL instance and needs to enforce that all connections use SSL/TLS. Which flag must they enable on the Cloud SQL instance?

Accepted Answer

require_ssl. The 'require_ssl' flag, when set to 'on', ensures that only SSL-encrypted connections are allowed. This is a database flag specific to Cloud SQL for PostgreSQL and MySQL.

Answer

ssl_enforcement

Answer

enforce_tls

Answer

cloudsql_ssl_only

Question 10

A company is running a Cloud Spanner instance with 1000 processing units. They notice high-priority CPU utilization exceeding 90% during peak hours, causing increased latency. They want to automatically scale capacity to handle the load while staying cost-effective. Which TWO actions should they take? (Choose 2.)

Accepted Answer

Set the high-priority CPU target to 70% to trigger scaling earlier.. Spanner autoscaling requires setting min and max processing units and a high-priority CPU target. Autoscaling adjusts capacity based on the target. Manual scaling is not automatic. Nodes are a different capacity unit; mixing units is not recommended. Proactive scaling is for scheduled changes, not automatic.

Answer

Enable proactive scaling based on a schedule.

Answer

Manually increase processing units to 2000 during peak hours.

Answer

Convert the instance to use nodes instead of processing units.

Question 11

A company is deploying AlloyDB for PostgreSQL in multiple regions to support disaster recovery. They need the secondary region to be able to serve reads and automatically scale read capacity. Which configuration should they use?

Accepted Answer

Create a primary cluster in the primary region and a read pool cluster in the secondary region with autoscaling enabled on the read pool. AlloyDB supports cross-region replication using read pool instances in secondary regions. These read pools can have autoscaling enabled to automatically adjust the number of nodes based on load. Primary instances are for writes only. AlloyDB Omni is for on-premises, not cross-region replication. A single cluster with multiple read pools in the same region does not provide cross-region DR.

Answer

Create a primary cluster in the primary region and a primary cluster in the secondary region, then set up bidirectional replication

Answer

Use AlloyDB Omni in the secondary region to replicate data from the primary region

Answer

Create a primary cluster with multiple read pools in the same region and distribute read traffic

Question 12

An engineer is deploying a Cloud Spanner instance for a global application that requires strong consistency across continents. The workload is moderate, and they want to minimize costs while meeting latency requirements. Which instance configuration should they choose?

Accepted Answer

Dual-region configuration with 500 processing units. Multi-region configurations provide strong global consistency and high availability across continents, but are expensive. Dual-region offers synchronous replication across two regions, a cost-effective option for moderate workloads needing strong consistency across two geographic areas. Regional is single-region, not global. Multi-region with 1000 nodes is overkill.

Answer

Multi-region configuration with 1000 nodes

Answer

Regional configuration with 100 processing units

Answer

Multi-region configuration with 500 processing units

Question 13

An organization needs to secure their Cloud SQL for MySQL instance. They require that all connections use IAM for authentication and that the database is not accessible from the public internet. Which THREE actions should they take? (Choose 3.)

Accepted Answer

Enable IAM database authentication on the Cloud SQL instance.. IAM database authentication links IAM users to database logins. Private IP and no public IP ensure no internet access. Cloud SQL Auth Proxy uses IAM for authentication but does not directly enforce IAM DB auth; it provides a secure tunnel. SSL encryption is separate from IAM authentication. Authorized networks are not needed with private IP.

Answer

Configure the Cloud SQL Auth Proxy for all connections.

Answer

Assign a public IP and configure authorized networks.

Question 14

An engineer is monitoring Cloud Bigtable performance and notices high read latency. They suspect a hot spot issue. Which tool should they use to identify the hot spot?

Accepted Answer

Key Visualiser. Cloud Bigtable's Key Visualiser is a tool specifically designed to detect and visualize hot spots (uneven distribution of read/write load across row keys). It helps identify row key patterns causing performance issues. Stackdriver is the legacy name for Cloud Monitoring; Cloud Monitoring can show overall metrics but not hot spot analysis. Bigtable Studio does not exist. cbt is the CLI tool.

Answer

The cbt command-line tool

Answer

Bigtable Studio

Answer

Cloud Monitoring (formerly Stackdriver)

Question 15

A company is migrating a PostgreSQL application to Cloud SQL. They require automatic failover in under 60 seconds if the primary instance fails, and they want to minimize write latency. Which configuration should they choose?

Accepted Answer

Create a Cloud SQL for PostgreSQL instance with high availability (HA) configuration.. Cloud SQL HA uses a synchronous standby in a different zone within the same region, providing automatic failover in under 60 seconds and no data loss. Cross-region replicas are asynchronous and do not meet the failover time requirement. Zonal instances lack HA. Regional clusters are for Spanner.

Answer

Create a zonal Cloud SQL instance and set up a read replica in a different zone.

Answer

Create a Cloud SQL for PostgreSQL instance with cross-region replication.

Answer

Configure a Cloud Spanner regional instance with multi-zone nodes.