A business analyst needs to create reports based on live production data in Cloud SQL. Running large analytical queries directly on the transactional…

Creating a read replica is the standard and most efficient solution for this scenario. A read replica is a separate Cloud SQL instance that asynchronously replicates data from the primary instance. By directing the business analyst's large, resource-intensive analytical queries to the replica, the primary production instance is isolated from this workload. This prevents performance degradation for the main application, which handles live transactions, while still providing the analyst with near real-time data for reporting. This approach effectively separates Online Transactional Processing (OLTP) from Online Analytical Processing (OLAP) workloads.

A. Migrate all data to BigQuery: This is incorrect because BigQuery is an analytical data warehouse (OLAP), not a transactional database (OLTP). Migrating the live production database would break the application that relies on transactional capabilities.

B. Enable Point-in-Time Recovery on the primary instance: This is incorrect as Point-in-Time Recovery is a data protection feature for restoring a database to a specific moment. It does not provide a mechanism for offloading read queries.

D. Switch to a multi-region Cloud Spanner configuration: This is an unnecessarily complex and costly solution. Migrating from Cloud SQL to Cloud Spanner is a major architectural change, whereas creating a read replica is a simple, built-in feature for this exact use case.

1. Google Cloud Documentation - Cloud SQL - Replication overview: "You can use read replicas to offload work from a primary instance. For example, you could have clients that are only reading connect to the replica. For analytics traffic, you can also send it to a read replica." This directly supports using a read replica for analytical queries to avoid impacting the primary instance.

(Source: Google Cloud, "Replication overview", Cloud SQL for MySQL documentation)

2. Google Cloud Documentation - Cloud SQL - Read Replicas: "The primary use case for read replicas is to scale the use of data in a database without degrading the performance of the primary instance." This confirms the purpose of read replicas is to handle read-heavy workloads like reporting without impacting the main database.

(Source: Google Cloud, "Creating read replicas", Cloud SQL for MySQL documentation)

3. Google Cloud Documentation - What is BigQuery?: "BigQuery is a serverless, highly scalable, and cost-effective cloud data warehouse designed for business agility." This establishes BigQuery as an OLAP system, making it unsuitable as a direct replacement for a transactional Cloud SQL database.

(Source: Google Cloud, "What is BigQuery?", BigQuery documentation)

4. Google Cloud Documentation - Overview of backups: "Point-in-time recovery helps you recover an instance to a specific point in time." This reference clarifies that PITR is a recovery tool, not a query offloading mechanism.

(Source: Google Cloud, "Overview of backups", Cloud SQL for MySQL documentation)