Cloud SQL Fundamentals: Provisioning and Configuring Managed Relational Databases

Cloud SQL is a fully managed database service offered by Google Cloud. It supports three popular database engines: MySQL, PostgreSQL, and SQL Server. 'Fully managed' means Google takes care of the underlying infrastructure for you. You do not need to install the database software, apply security patches, or monitor the health of the physical server. You simply define what you want, and Cloud SQL creates it.

To understand Cloud SQL, you first need to know what a relational database is. A relational database stores data in tables with rows and columns, similar to a spreadsheet. Each table has a fixed structure, like a contact list with columns for name, phone number, and email address. You use a language called SQL (Structured Query Language) to ask questions of your data, such as 'find all customers who live in London'. MySQL, PostgreSQL, and SQL Server are different brands of relational database software. Each has its own strengths, but they all speak SQL.

Now, what does provisioning a Cloud SQL instance actually mean? Provisioning is the process of creating a new database instance in the cloud. You choose the database engine (MySQL, PostgreSQL, or SQL Server), the version (for example, MySQL 8.0), the region where the data should be stored (like europe-west1 in Belgium), and the machine configuration. The machine configuration defines how much computing power and memory your database gets. Google Cloud offers different tiers: db-f1-micro and db-g1-small for small testing workloads, and standard and high-memory machine types for production.

Configuration goes beyond just picking a machine size. You also decide on storage type and capacity. Cloud SQL offers two storage options: SSD (solid-state drive) for fast performance and HDD (hard disk drive) for lower cost. You can set a storage capacity in gigabytes, and Cloud SQL automatically increases storage up to a limit you define. This is called automatic storage increase and is a key feature for avoiding unexpected out-of-space errors.

Another critical configuration choice is 'high availability' (HA). If the physical machine running your database fails, without HA your database becomes unavailable until someone manually restores it. With HA, Cloud SQL creates a standby instance in a different zone within the same region. If the primary zone fails, traffic automatically switches to the standby within seconds. You also configure backups. Cloud SQL can automatically take daily backups and store them for up to 365 days. You can also enable point-in-time recovery, which lets you restore your database to any moment within the last seven days.

Networking is another part of configuration. By default, your Cloud SQL instance is accessible only from within your Google Cloud project. You can allow specific IP addresses through authorised networks, or use a dedicated private connection via VPC (Virtual Private Cloud) peering. Authentication happens through Google Cloud IAM (Identity and Access Management) or database-native usernames and passwords. SSL/TLS encryption can be enforced to protect data in transit.

Finally, you must consider maintenance. Cloud SQL automatically applies security patches and minor version updates during a configurable maintenance window. You can define a one-hour window each week for these updates, minimising disruption to your application.

All these options (region, machine type, storage, HA, backups, networking, maintenance) are defined at creation time or can be modified later. The Google Cloud Console (web interface), gcloud command-line tool, or API (application programming interface) can be used to provision and configure instances. For the PCDE exam, you should understand how each configuration choice affects performance, availability, and cost.

Imagine you are a database engineer working for a growing e-commerce company called 'ShopFast'. ShopFast sells shoes online. The company has a monolithic application that stores all customer orders, product inventory, and user accounts in a single MySQL database running on a laptop under someone's desk. This has become unreliable. Orders are lost when the laptop overheats, and the company cannot afford downtime during the holiday sales season.

Your manager asks you to move the database to Google Cloud using Cloud SQL. Here is what you actually do as an IT professional, step by step:

First, you assess the current database. You identify that ShopFast uses MySQL 5.7 and the database size is 50 GB. You check that no features used by the application are incompatible with Cloud SQL (for example, any custom MySQL plugins not supported by Cloud SQL).

Next, you plan the Cloud SQL instance. You pick a region close to your users, for example us-central1 (Iowa). Since ShopFast is growing, you choose a standard machine type with 4 vCPUs and 15 GB of memory to start. You select SSD storage for fast reads and writes. You enable automatic storage increase up to 500 GB. For high availability, you enable HA zone redundancy because losing orders is not acceptable. You set daily backups at 2 AM UTC with a 30-day retention period, and you enable point-in-time recovery.

You then create the instance using the Google Cloud Console. You name it 'shopfast-prod-db'. You configure authorised networks: you add the IP addresses of the application server and the office VPN. You also create a separate user account for the application with limited privileges (only SELECT, INSERT, UPDATE, DELETE on the shop database) for security. You do not use the default root account for the app.

With the empty Cloud SQL instance running, you now need to migrate the existing data. For a 50 GB MySQL database, you use the mysqldump tool to export the data from the old laptop, then import it into Cloud SQL using the mysql command-line client. You schedule this migration during a low-traffic period, such as a Saturday morning. You test the import on a separate staging instance first to verify everything works.

After migration, you update the application configuration to point to the Cloud SQL instance's public IP address and new credentials. You test the application thoroughly. Finally, you set up monitoring using Cloud Monitoring and Cloud Logging to track query performance, CPU usage, and storage utilisation. You also create an alert if CPU usage exceeds 80% for more than 5 minutes, so you can scale up before users notice slowness.

Throughout this process, you document every configuration choice for the team. Six months later, when a hardware failure occurs in one of the zones in us-central1, the application does not even blink—Cloud SQL's HA failover switches to the standby instance, and users continue shopping. That is the real-world power of Cloud SQL.