Google Cloud Certified - Professional Cloud Database Engineer (PR000256)

✅ <B>Option C is correct</B> because upgrading to the Cloud SQL Enterprise Plus edition provides "near zero downtime" for planned maintenance, which directly addresses the requirement to maximize database uptime during planned events without major application changes. This edition incorporates advanced features for high availability and maintenance. <BR><BR>❌ <B>Why the other choices are incorrect:</B><BR>

• <B>Option A is incorrect:</B> Automated backups are essential for data recovery and point-in-time restore, but they do not prevent downtime during planned maintenance events.
• <B>Option B is incorrect:</B> Creating a cross-region read replica primarily provides disaster recovery capabilities for regional outages and improves read scalability. While it can be part of a broader DR strategy, it does not inherently prevent downtime for the primary instance during its planned maintenance without potentially requiring significant application changes for write failover.
• <B>Option D is incorrect:</B> Setting up maintenance notifications helps in planning and preparing for maintenance windows, but it does not directly maximize actual uptime during the maintenance period itself.

✅ Option D (Correct) is correct because it describes the most secure, scalable, and centralized approach. For GKE and Compute Engine, using dedicated IAM service accounts with least-privilege roles and Workload Identity (for GKE) is the recommended best practice for application-level authentication. For developers, IAM authentication with the Cloud SQL Auth Proxy provides secure access using their Google identities, eliminating the need for database-native user/password management.❌ Why the other choices are incorrect: Option A is incorrect because while the Cloud SQL Auth Proxy is good for services, relying on local clients with database-native user/password authentication for developers is less secure and lacks centralized identity management via IAM. Option B is incorrect because enabling public IP addresses and restricting access via IP-based firewall rules is less secure, difficult to manage for dynamic IP addresses (like GKE pods), and not scalable for developers. Option C is incorrect because creating unique database-native user/password credentials directly on each instance for all services and developers is cumbersome, not scalable, and does not leverage Google Cloud IAM for centralized access management and auditing.

Option D is correct because Database Migration Service (DMS) is Google Cloud's native, serverless tool for migrating MySQL databases with minimal downtime through continuous replication. Cloud SQL Enterprise Plus for MySQL is the optimal it provides enhanced performance, higher availability, faster replication, and larger capacity, making it ideal for mission-critical, read-heavy workloads with several TBs of data, while requiring the fewest changes as it maintains MySQL compatibility.Option A is incorrect because AlloyDB is PostgreSQL-compatible. Migrating from Percona Server for MySQL to AlloyDB would require significant schema and application code changes, violating the 'fewest changes possible' requirement.Option B is incorrect because while Datastream can handle CDC, Database Migration Service (DMS) is a more comprehensive service for full database migrations with minimal downtime. Furthermore, Cloud SQL Enterprise Plus (Option D) offers superior performance and availability compared to Enterprise for mission-critical, large-scale, read-heavy workloads.Option C is incorrect because migrating to Spanner typically requires substantial schema redesign, query rewrites, and application code modifications to adapt to Spanner's distributed architecture and strong consistency model, which does not meet the 'fewest changes possible' criteria.

Option C is correct because migrating to AlloyDB for PostgreSQL provides a high-performance, fully managed solution for mixed workloads. The columnar engine specifically accelerates complex analytical queries involving large tables and JOIN operations, addressing the analytics team's needs. Utilizing read pools for query isolation ensures that these resource-intensive analytical queries run on separate instances, preventing them from impacting the primary instance's transactional workload. Read pools also offer elastic scaling of read capacity to handle sudden spikes in demand, fulfilling the requirement for efficient analytics without impacting transactional users and planning for future growth.Option A is incorrect because upgrading Cloud SQL for PostgreSQL Enterprise Edition and routing all queries to the primary instance will not resolve the resource contention between analytical and transactional workloads. It does not provide the necessary isolation or specialized analytical acceleration.Option B is incorrect because while migrating to AlloyDB is beneficial, simply upgrading the primary instance machine type and creating materialized views does not offer the same level of workload isolation and dedicated analytical processing capabilities as the columnar engine and read pools. Materialized views only help for predefined queries, not ad-hoc complex reports.Option D is incorrect because upgrading the Cloud SQL instance and implementing PgBouncer primarily addresses connection management and instance sizing within Cloud SQL. It does not provide the advanced analytical processing capabilities or the workload isolation and elastic read scaling that AlloyDB's columnar engine and read pools offer, failing to effectively separate and optimize the mixed workload.

✅ Option B is correct because Dataflow, a fully managed service for Apache Beam, is ideal for streaming data migrations. Using DynamoDBIO and BigtableIO connectors, a Dataflow pipeline can stream data from DynamoDB to Bigtable in near real-time, ensuring minimal downtime and data consistency. This is a cost-effective solution requiring minimal operational effort.❌ Why the other choices are incorrect:

• Option A is incorrect because exporting to CSV and importing via cbt CLI is a batch process, leading to significant downtime and lacking real-time consistency.
• Option C is incorrect because developing a custom script requires substantial development effort, testing, and maintenance, conflicting with the "minimal effort" goal.
• Option D is incorrect because Dataproc, primarily a batch processing service, is less efficient for real-time, minimal-effort migrations with continuous consistency between these NoSQL databases compared to Dataflow's specialized streaming capabilities.

Option C is correct because configuring Private Services Access is a mandatory prerequisite for enabling private IP connectivity for Cloud SQL instances. This process involves allocating an IP range for private services and establishing a VPC Network Peering connection between your VPC network (or shared VPC host project's network) and the Google-managed service network. If this foundational setup is not completed, attempting to assign a private IP to a Cloud SQL instance will result in a “Network association failed” error, as the necessary network infrastructure for private communication does not exist. Option A is incorrect because using the Google Cloud console is a method for assigning the private IP, not a solution to the underlying configuration failure. The console operation would still fail if Private Services Access is not configured. Option B is incorrect because while enabling the Service Networking API is a component of Private Services Access setup, it's not the complete solution. The

✅ Option A (Correct)
Reasoning: Option A directly addresses all identified problems. Implementing row key salting with a hash prefix is crucial for eliminating hotspotting by evenly distributing writes across all Bigtable nodes, preventing specific device ranges from concentrating traffic. Configuring automated scaling based on CPU utilization optimizes performance and cost by dynamically adjusting cluster size to handle 2 million writes/second peak and 500,000 writes/second off-peak. Migrating data older than 6 months (given reads only access the last 24 hours) to a cheaper storage solution like Cloud Storage significantly reduces the escalating storage costs for 500TB of historical data.

❌ Why the other choices are incorrect:
* Option B is incorrect: Splitting tables by device type or using separate instances for geographic regions does not directly solve the existing hotspotting or storage cost issues. Read replicas are for high availability/read distribution, not write hotspotting or cost reduction.
* Option C is incorrect: The problem states the current row key is deviceId#timestamp. Changing it to the same format is not a solution for hotspotting. The other suggestions (scaling, data migration) are good, but this critical flaw makes the option incorrect.
* Option D is incorrect: Simply increasing cluster size (to 20 nodes) without fixing the row key distribution will not eliminate hotspotting; it might just spread the concentrated load over more nodes but still leave others underutilized. Bigtable automatically compresses data, so "enabling compression" is not a configurable action for users.

✅ Option B (Correct)
Reasoning: Option B describes a robust and fully managed solution using Google Cloud services. Cloud Scheduler is a managed cron service that can trigger events on a schedule (like daily at 6:00 AM). It can publish messages to a Pub/Sub topic. A Cloud Run service can then be configured to subscribe to this Pub/Sub topic and execute custom code, such as a script containing the gcloud sql export bak command, effectively automating the export process without managing servers.

❌ Why the other choices are incorrect:

• Option A is incorrect: Enabling retained backups refers to Cloud SQL's native automated backups, which are not the same as exporting native BAK files to Cloud Storage via the gcloud sql export bak command.
• Option C is incorrect: Implementing automated backups using the console also refers to Cloud SQL's built-in managed backups, not the specific user-initiated export of native BAK files required by the question.
• Option D is incorrect: While cron is used for scheduling, "Create a cron job to export" is vague. If it implies a cron job on a virtual machine, it's not a fully managed Google Cloud component approach. Cloud Scheduler is the managed equivalent of cron, but it still requires an execution Cloud Run to run the export command.

✅ Option B (Correct)
Reasoning: Option B is correct because AlloyDB for PostgreSQL is fully compatible with PostgreSQL, supporting existing stored procedures. Deploying an AlloyDB cluster in the primary region with a high-availability configuration meets the strict RTO (5 minutes) and RPO (30 seconds) requirements, and ensures local transactional integrity with primary data residing in a single region for regulatory compliance. Establishing cross-region read replicas effectively optimizes for global read access for the distributed customer base, leveraging AlloyDB's superior performance and availability.
❌ Why the other choices are incorrect:

• Option A is incorrect: Spanner is not directly compatible with PostgreSQL stored procedures, requiring significant application changes. While highly available, a "single-instance" Spanner is misrepresentative, and its default multi-regional nature might conflict with single-region data residency for primary data.
• Option C is incorrect: A single-region AlloyDB cluster with a primary and read replica in the same not optimize for global read access, which is a key requirement for a global customer base with peak traffic across continents. It lacks cross-region read scaling.
• Option D is incorrect: While Cloud SQL for PostgreSQL is compatible and supports read replicas, a "multi-regional Cloud SQL for PostgreSQL deployment" for primary transactional data contradicts the requirement for primary data to reside in a single geographic region due to regulatory compliance. AlloyDB also offers superior RTO/RPO for critical workloads compared to Cloud SQL.