×
Study Notes — Certification Prep

Google Cloud Database
Cloud SQL

Fully-managed relational database service supporting MySQL, PostgreSQL, and MS SQL Server. Designed for structured, relational data.

Author: Michaël Bettan
Compatibility: 100% SQL compatible
01

Overview & Use Cases

Cloud SQL is a fully-managed relational database service. It is designed for relational, record-based data (tables, rows, and columns) and super structured data. It scales vertically and allows field updates.

99.95%
SLA
64TB
Max Storage Capacity
60k
Max IOPS (read/write)
96
Max Cores (624 GB RAM)
Supported DB Engines
MySQL, PostgreSQL, and MS SQL Server.
Availability Options
Multiple zones (Highly Available) or Single zone.
Billing Components
Instance, CPU, Memory, Storage, Network Cost, and Licensing Cost (SQL Server only).

Primary Use Cases

  • OLTP Workloads: OnLine Transaction Processing
  • Migration: Lift & Shift of legacy SQL RDBMS
  • Applications: Web Frameworks, ERP, CRM, SaaS
  • Websites: Back-ends DB applications, Ecommerce, blogs

Compute & Scaling

SQL databases generally scale vertically for structured data. Note that no autoscaling for compute nodes is supported natively in Cloud SQL, though auto-scaling for storage exists.

02

Key Capabilities

Replication & Disaster Recovery

  • Read Replica: Read-only copy to offload work. It is an exact copy of the primary instance, updated in almost real time. Limited to the same region as the master.
  • Cascading Replication: Create a read replica under another read replica in the same or a different region.
  • Disaster Recovery: Use a cascading hierarchy to simulate primary topology. During an outage, a replica is promoted to primary; replicas under the new primary continue to replicate.

Performance & Cost

  • Performance Improvements: Reduce burden on the primary instance by offloading replication work to multiple read replicas.
  • Scale Reads: Have more replicas to share the read load.
  • Cost Reduction: Reduce networking costs using a single cascading replica with cross-region replication.
  • Automated Management: Automatic encryption, Auto-scale (vertically for storage) and auto-backup.
03

Operational Model & Migration

Google's Responsibilities

As a fully-managed service, Google owns:

  • IaaS Compute (hardware, virtualization)
  • OS install, upgrade, patches
  • DB install, upgrade, patches
  • Database Backups
  • High-Availability (failover)
  • Scaling (disk space)
  • Monitoring & Network connectivity

Supported Versions

  • MySQL: 8.4, 8.0, 5.7, 5.6
  • PostgreSQL: 16, 15, 14, 13, 12, 11, 10, 9.6
  • SQL Server: 2022, 2019, 2017

Standard Migration Steps

STEP 1

Configure Source

Provide details on your existing data source

STEP 2

Create Replica

Create a read replica in Cloud SQL

STEP 3

Sync Data

Sync the read replica with the source database

STEP 4

Promote

Promote the read replica to the primary instance

04

Architecture & Considerations

Cloud SQL vs BigQuery

Cloud SQL is optimized for transactions (high throughput WRITES to RECORDS). It is record-based, meaning an entire record must be opened on disk even if you SELECT a single column. BigQuery, in contrast, is column-based and optimized for reporting workloads (mostly reads).

Technical Considerations

05

Best Practices & Data Movement

Design Best Practices

Loading & Exporting Data

06

Security, Auth Proxy & Monitoring

Cloud SQL Auth Proxy

The Cloud SQL Auth proxy offers enhanced security, especially for apps with dynamic IP addresses.

  • Creates a secure tunnel leveraging Google IAM for authentication.
  • Eliminates the need to manage Authorized Networks, allowlisting IPs, or SSL certs manually.
  • Best Practice: Enforce exclusive use by leaving Authorized Networks empty, preventing any direct IP-based connection.

Monitoring & Security

  • Cloud SQL Insights: Offers 7-day retention to detect, diagnose, and identify the root cause of performance problems.
  • Stackdriver: MySQL connector is required for Stackdriver (Cloud Monitoring) tracking.
  • GKE Access: Use Secrets & Service accounts to securely connect to a SQL Instance from Kubernetes.
RPO
Recovery Point Objective (Acceptable amount of data loss measured in time).
RTO
Recovery Time Objective (Acceptable amount of downtime).
Database Migration Service
Creates Migration jobs to move data between source and destination. You define the source by creating a new job or via a connection profile.

Self-Assessment Questions

Q1. You have an application running in GKE with dynamic IP addresses that needs to connect to Cloud SQL securely. What is the best way to handle this?

Use the Cloud SQL Auth Proxy. It handles secure tunnels using IAM and removes the need to allowlist dynamic IPs.

Q2. Your analytics team needs to run heavy reporting queries, but doing so on Cloud SQL degrades performance for the web application. How can you solve this?

Create a Read Replica and offload the reporting workload to it, or export the data to BigQuery for OLAP reporting.

Q3. True or False: Cloud SQL automatically scales its compute nodes when CPU utilization hits 90%.

False. Cloud SQL does not support autoscaling for compute nodes (only for storage). Vertical scaling of compute requires a restart.

Q4. What is the fundamental difference between how Cloud SQL and BigQuery store data?

Cloud SQL is record-based (row-oriented, good for OLTP), whereas BigQuery is column-based (good for OLAP).
Study Progress — Cloud SQL Readiness 100%