Professional Cloud Database Engineer Part 2
Question #: 31
Topic #: 1
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?
A. Check the cloudsql.googleapis.com/postgres.log instance log.
B. Perform the gcloud sql operations list command.
C. Use Cloud Audit Logs to verify the status.
D. Use the Google Cloud Console.
Selected Answer: B
Question #: 32
Topic #: 1
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?
A. Increase the number of processing units.
B. Modify the database schema, and add additional indexes.
C. Shard data required by the application into multiple instances.
D. Decrease the number of processing units.
Selected Answer: A
Question #: 33
Topic #: 1
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?
A. Recommend solid-state drives (SSD).
B. Recommend splitting the Bigtable instance into two instances in order to load balance the concurrent reads.
C. Recommend hard disk drives (HDD).
D. Recommend mixed storage types.
Selected Answer: A
Question #: 34
Topic #: 1
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?
A. 1. 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.
B. 1. 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.
C. 1. 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.
D. 1. 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.
Selected Answer: B
Question #: 35
Topic #: 1
You are building an Android game that needs to store data on a Google Cloud serverless database. The database will log user activity, store user preferences, and receive in-game updates. The target audience resides in developing countries that have intermittent internet connectivity. You need to ensure that the game can synchronize game data to the backend database whenever an internet network is available. What should you do?
A. Use Firestore.
B. Use Cloud SQL with an external (public) IP address.
C. Use an in-app embedded database.
D. Use Cloud Spanner.
Selected Answer: A
Question #: 36
Topic #: 1
You released a popular mobile game and are using a 50 TB Cloud Spanner instance to store game data in a PITR-enabled production environment. When you analyzed the game statistics, you realized that some players are exploiting a loophole to gather more points to get on the leaderboard. Another DBA accidentally ran an emergency bugfix script that corrupted some of the data in the production environment. You need to determine the extent of the data corruption and restore the production environment. What should you do? (Choose two.)
A. If the corruption is significant, use backup and restore, and specify a recovery timestamp.
B. If the corruption is significant, perform a stale read and specify a recovery timestamp. Write the results back.
C. If the corruption is significant, use import and export.
D. If the corruption is insignificant, use backup and restore, and specify a recovery timestamp.
E. If the corruption is insignificant, perform a stale read and specify a recovery timestamp. Write the results back.
Selected Answer: AE
Question #: 37
Topic #: 1
You are starting a large CSV import into a Cloud SQL for MySQL instance that has many open connections. You checked memory and CPU usage, and sufficient resources are available. You want to follow Google-recommended practices to ensure that the import will not time out. What should you do?
A. Close idle connections or restart the instance before beginning the import operation.
B. Increase the amount of memory allocated to your instance.
C. Ensure that the service account has the Storage Admin role.
D. Increase the number of CPUs for the instance to ensure that it can handle the additional import operation.
Selected Answer: A
Question #: 38
Topic #: 1
You are migrating your data center to Google Cloud. You plan to migrate your applications to Compute Engine and your Oracle databases to Bare Metal Solution for Oracle. You must ensure that the applications in different projects can communicate securely and efficiently with the Oracle databases. What should you do?
A. Set up a Shared VPC, configure multiple service projects, and create firewall rules.
B. Set up Serverless VPC Access.
C. Set up Private Service Connect.
D. Set up Traffic Director.
Selected Answer: A
Question #: 39
Topic #: 1
You are running an instance of Cloud Spanner as the backend of your ecommerce website. You learn that the quality assurance (QA) team has doubled the number of their test cases. You need to create a copy of your Cloud Spanner database in a new test environment to accommodate the additional test cases. You want to follow Google-recommended practices. What should you do?
A. Use Cloud Functions to run the export in Avro format.
B. Use Cloud Functions to run the export in text format.
C. Use Dataflow to run the export in Avro format.
D. Use Dataflow to run the export in text format.
Selected Answer: C
Question #: 40
Topic #: 1
You need to redesign the architecture of an application that currently uses Cloud SQL for PostgreSQL. The users of the application complain about slow query response times. You want to enhance your application architecture to offer sub-millisecond query latency. What should you do?
A. Configure Firestore, and modify your application to offload queries.
B. Configure Bigtable, and modify your application to offload queries.
C. Configure Cloud SQL for PostgreSQL read replicas to offload queries.
D. Configure Memorystore, and modify your application to offload queries.
Selected Answer: D
Question #: 41
Topic #: 1
You need to migrate existing databases from Microsoft SQL Server 2016 Standard Edition on a single Windows Server 2019 Datacenter Edition to a single Cloud SQL for SQL Server instance. During the discovery phase of your project, you notice that your on-premises server peaks at around 25,000 read IOPS. You need to ensure that your Cloud SQL instance is sized appropriately to maximize read performance. What should you do?
A. Create a SQL Server 2019 Standard on Standard machine type with 4 vCPUs, 15 GB of RAM, and 800 GB of solid-state drive (SSD).
B. Create a SQL Server 2019 Standard on High Memory machine type with at least 16 vCPUs, 104 GB of RAM, and 200 GB of SSD.
C. Create a SQL Server 2019 Standard on High Memory machine type with 16 vCPUs, 104 GB of RAM, and 4 TB of SSD.
D. Create a SQL Server 2019 Enterprise on High Memory machine type with 16 vCPUs, 104 GB of RAM, and 500 GB of SSD.
Selected Answer: C
Question #: 42
Topic #: 1
You are managing a small Cloud SQL instance for developers to do testing. The instance is not critical and has a recovery point objective (RPO) of several days. You want to minimize ongoing costs for this instance. What should you do?
A. Take no backups, and turn off transaction log retention.
B. Take one manual backup per day, and turn off transaction log retention.
C. Turn on automated backup, and turn off transaction log retention.
D. Turn on automated backup, and turn on transaction log retention.
Selected Answer: C
Question #: 43
Topic #: 1
You manage a meeting booking application that uses Cloud SQL. During an important launch, the Cloud SQL instance went through a maintenance event that resulted in a downtime of more than 5 minutes and adversely affected your production application. You need to immediately address the maintenance issue to prevent any unplanned events in the future. What should you do?
A. Set your production instance’s maintenance window to non-business hours.
B. Migrate the Cloud SQL instance to Cloud Spanner to avoid any future disruptions due to maintenance.
C. Contact Support to understand why your Cloud SQL instance had a downtime of more than 5 minutes.
D. Use Cloud Scheduler to schedule a maintenance window of no longer than 5 minutes.
Selected Answer: A
Question #: 44
Topic #: 1
You are designing a highly available (HA) Cloud SQL for PostgreSQL instance that will be used by 100 databases. Each database contains 80 tables that were migrated from your on-premises environment to Google Cloud. The applications that use these databases are located in multiple regions in the US, and you need to ensure that read and write operations have low latency. What should you do?
A. Deploy 2 Cloud SQL instances in the us-central1 region with HA enabled, and create read replicas in us-east1 and us-west1.
B. Deploy 2 Cloud SQL instances in the us-central1 region, and create read replicas in us-east1 and us-west1.
C. Deploy 4 Cloud SQL instances in the us-central1 region with HA enabled, and create read replicas in us-central1, us-east1, and us-west1.
D. Deploy 4 Cloud SQL instances in the us-central1 region, and create read replicas in us-central1, us-east1 and us-west1.
Selected Answer: C
Question #: 45
Topic #: 1
You work in the logistics department. Your data analysis team needs daily extracts from Cloud SQL for MySQL to train a machine learning model. The model will be used to optimize next-day routes. You need to export the data in CSV format. You want to follow Google-recommended practices. What should you do?
A. Use Cloud Scheduler to trigger a Cloud Function that will run a select * from table(s) query to call the cloudsql.instances.export API.
B. Use Cloud Scheduler to trigger a Cloud Function through Pub/Sub to call the cloudsql.instances.export API.
C. Use Cloud Composer to orchestrate an export by calling the cloudsql.instances.export API.
D. Use Cloud Composer to execute a select * from table(s) query and export results.
Selected Answer: B
Question #: 46
Topic #: 1
You are choosing a database backend for a new application. The application will ingest data points from IoT sensors. You need to ensure that the application can scale up to millions of requests per second with sub-10ms latency and store up to 100 TB of history. What should you do?
A. Use Cloud SQL with read replicas for throughput.
B. Use Firestore, and rely on automatic serverless scaling.
C. Use Memorystore for Memcached, and add nodes as necessary to achieve the required throughput.
D. Use Bigtable, and add nodes as necessary to achieve the required throughput.
Selected Answer: D
Question #: 47
Topic #: 1
You are designing a payments processing application on Google Cloud. The application must continue to serve requests and avoid any user disruption if a regional failure occurs. You need to use AES-256 to encrypt data in the database, and you want to control where you store the encryption key. What should you do?
A. Use Cloud Spanner with a customer-managed encryption key (CMEK).
B. Use Cloud Spanner with default encryption.
C. Use Cloud SQL with a customer-managed encryption key (CMEK).
D. Use Bigtable with default encryption.
Selected Answer: A
Question #: 48
Topic #: 1
You are managing a Cloud SQL for MySQL environment in Google Cloud. You have deployed a primary instance in Zone A and a read replica instance in Zone B, both in the same region. You are notified that the replica instance in Zone B was unavailable for 10 minutes. You need to ensure that the read replica instance is still working. What should you do?
A. Use the Google Cloud Console or gcloud CLI to manually create a new clone database.
B. Use the Google Cloud Console or gcloud CLI to manually create a new failover replica from backup.
C. Verify that the new replica is created automatically.
D. Start the original primary instance and resume replication.
Selected Answer: C
Question #: 49
Topic #: 1
You are migrating an on-premises application to Google Cloud. The application requires a high availability (HA) PostgreSQL database to support business-critical functions. Your company’s disaster recovery strategy requires a recovery time objective (RTO) and recovery point objective (RPO) within 30 minutes of failure. You plan to use a Google Cloud managed service. What should you do to maximize uptime for your application?
A. Deploy Cloud SQL for PostgreSQL in a regional configuration. Create a read replica in a different zone in the same region and a read replica in another region for disaster recovery.
B. Deploy Cloud SQL for PostgreSQL in a regional configuration with HA enabled. Take periodic backups, and use this backup to restore to a new Cloud SQL for PostgreSQL instance in another region during a disaster recovery event.
C. Deploy Cloud SQL for PostgreSQL in a regional configuration with HA enabled. Create a cross-region read replica, and promote the read replica as the primary node for disaster recovery.
D. Migrate the PostgreSQL database to multi-regional Cloud Spanner so that a single region outage will not affect your application. Update the schema to support Cloud Spanner data types, and refactor the application.
Selected Answer: C
Question #: 50
Topic #: 1
Your team is running a Cloud SQL for MySQL instance with a 5 TB database that must be available 24/7. You need to save database backups on object storage with minimal operational overhead or risk to your production workloads. What should you do?
A. Use Cloud SQL serverless exports.
B. Create a read replica, and then use the mysqldump utility to export each table.
C. Clone the Cloud SQL instance, and then use the mysqldump utlity to export the data.
D. Use the mysqldump utility on the primary database instance to export the backup.
Selected Answer: A
Question #: 51
Topic #: 1
You are deploying a new Cloud SQL instance on Google Cloud using the Cloud SQL Auth proxy. You have identified snippets of application code that need to access the new Cloud SQL instance. The snippets reside and execute on an application server running on a Compute Engine machine. You want to follow Google-recommended practices to set up Identity and Access Management (IAM) as quickly and securely as possible. What should you do?
A. For each application code, set up a common shared user account.
B. For each application code, set up a dedicated user account.
C. For the application server, set up a service account.
D. For the application server, set up a common shared user account.
Selected Answer: C
Question #: 52
Topic #: 1
Your organization is running a low-latency reporting application on Microsoft SQL Server. In addition to the database engine, you are using SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), and SQL Server Integration Services (SSIS) in your on-premises environment. You want to migrate your Microsoft SQL Server database instances to Google Cloud. You need to ensure minimal disruption to the existing architecture during migration. What should you do?
A. Migrate to Cloud SQL for SQL Server.
B. Migrate to Cloud SQL for PostgreSQL.
C. Migrate to Compute Engine.
D. Migrate to Google Kubernetes Engine (GKE).
Selected Answer: C
Question #: 53
Topic #: 1
An analytics team needs to read data out of Cloud SQL for SQL Server and update a table in Cloud Spanner. You need to create a service account and grant least privilege access using predefined roles. What roles should you assign to the service account?
A. roles/cloudsql.viewer and roles/spanner.databaseUser
B. roles/cloudsql.editor and roles/spanner.admin
C. roles/cloudsql.client and roles/spanner.databaseReader
D. roles/cloudsql.instanceUser and roles/spanner.databaseUser
Selected Answer: D
Question #: 54
Topic #: 1
You are responsible for designing a new database for an airline ticketing application in Google Cloud. This application must be able to:
Work with transactions and offer strong consistency.
Work with structured and semi-structured (JSON) data.
Scale transparently to multiple regions globally as the operation grows.
You need a Google Cloud database that meets all the requirements of the application. What should you do?
A. Use Cloud SQL for PostgreSQL with both cross-region read replicas.
B. Use Cloud Spanner in a multi-region configuration.
C. Use Firestore in Datastore mode.
D. Use a Bigtable instance with clusters in multiple regions.
Selected Answer: B
Question #: 55
Topic #: 1
You are writing an application that will run on Cloud Run and require a database running in the Cloud SQL managed service. You want to secure this instance so that it only receives connections from applications running in your VPC environment in Google Cloud. What should you do?
A. 1. Create your instance with a specified external (public) IP address.
2. Choose the VPC and create firewall rules to allow only connections from Cloud Run into your instance.
3. Use Cloud SQL Auth proxy to connect to the instance.
B. 1. Create your instance with a specified external (public) IP address.
2. Choose the VPC and create firewall rules to allow only connections from Cloud Run into your instance.
3. Connect to the instance using a connection pool to best manage connections to the instance.
C. 1. Create your instance with a specified internal (private) IP address.
2. Choose the VPC with private service connection configured.
3. Configure the Serverless VPC Access connector in the same VPC network as your Cloud SQL instance.
4. Use Cloud SQL Auth proxy to connect to the instance.
D. 1. Create your instance with a specified internal (private) IP address.
2. Choose the VPC with private service connection configured.
3. Configure the Serverless VPC Access connector in the same VPC network as your Cloud SQL instance.
4. Connect to the instance using a connection pool to best manage connections to the instance.
Selected Answer: D
Question #: 56
Topic #: 1
You are troubleshooting a connection issue with a newly deployed Cloud SQL instance on Google Cloud. While investigating the Cloud SQL Proxy logs, you see the message Error 403: Access Not Configured. What should you do?
A. Check the app.yaml value cloud_sql_instances for a misspelled or incorrect instance connection name.
B. Check whether your service account has cloudsql.instances.connect permission.
C. Enable the Cloud SQL Admin API.
D. Ensure that you are using an external (public) IP address interface.
Selected Answer: C
Question #: 57
Topic #: 1
You are working on a new centralized inventory management system to track items available in 200 stores, which each have 500 GB of data. You are planning a gradual rollout of the system to a few stores each week. You need to design an SQL database architecture that minimizes costs and user disruption during each regional rollout and can scale up or down on nights and holidays. What should you do?
A. Use Oracle Real Application Cluster (RAC) databases on Bare Metal Solution for Oracle.
B. Use sharded Cloud SQL instances with one or more stores per database instance.
C. Use a Biglable cluster with autoscaling.
D. Use Cloud Spanner with a custom autoscaling solution.
Selected Answer: D
Question #: 58
Topic #: 1
Your organization has strict policies on tracking rollouts to production and periodically shares this information with external auditors to meet compliance requirements. You need to enable auditing on several Cloud Spanner databases. What should you do?
A. Use replication to roll out changes to higher environments.
B. Use backup and restore to roll out changes to higher environments.
C. Use Liquibase to roll out changes to higher environments.
D. Manually capture detailed DBA audit logs when changes are rolled out to higher environments.
Selected Answer: C
Question #: 59
Topic #: 1
Your organization has a production Cloud SQL for MySQL instance. Your instance is configured with 16 vCPUs and 104 GB of RAM that is running between 90% and 100% CPU utilization for most of the day. You need to scale up the database and add vCPUs with minimal interruption and effort. What should you do?
A. Issue a gcloud sql instances patch command to increase the number of vCPUs.
B. Update a MySQL database flag to increase the number of vCPUs.
C. Issue a gcloud compute instances update command to increase the number of vCPUs.
D. Back up the database, create an instance with additional vCPUs, and restore the database.
Selected Answer: A
Question #: 60
Topic #: 1
You are configuring a brand new Cloud SQL for PostgreSQL database instance in Google Cloud. Your application team wants you to deploy one primary instance, one standby instance, and one read replica instance. You need to ensure that you are following Google-recommended practices for high availability. What should you do?
A. Configure the primary instance in zone A, the standby instance in zone C, and the read replica in zone B, all in the same region.
B. Configure the primary and standby instances in zone A and the read replica in zone B, all in the same region.
C. Configure the primary instance in one region, the standby instance in a second region, and the read replica in a third region.
D. Configure the primary, standby, and read replica instances in zone A, all in the same region.
Selected Answer: A
