Cloud SQL

In the last lesson, we looked at the architecture and working of BQ. we have also looked at the use-cases where BQ is the right choice and some exam tips about BQ. In this lesson, we will look at the alternative of MySQL or any SQL-based traditional database and also, the reason to choose the GCP’s Cloud SQL over self-hosted MySQL or PostgreSQL databases. So, let’s start.

Introduction

Cloud SQL is a fully-managed database service that helps you set up, maintain, manage, and administer your relational databases on the Google Cloud Platform. The main advantage of Cloud SQL is, it takes care of the security, reliability, and maintenance of our SQL servers at a lower level. Normally, CloudSQL is used for CMS backends but we can use it as a normal MySQL database also.

CloudSQL provides read replicas with just a couple of clicks. It also helps in having a high availability database with a failover mechanism. These are some of the offerings of CloudSQL. Let’s see the advantages in detail.

Advantages

CloudSQL has a lot of advantages over traditional SQL servers. Some of them are:

• CloudSQL reduces maintenance costs with fully managed relational databases in the cloud.

• It ensures business continuity with reliable and secure services backed by 24/7 Google’s SRE team

• Automates database provisioning, storage capacity management, and other time-consuming tasks for us. CloudSQL automatically increases storage for the database.

• Provides easy integration with existing apps and Google Cloud services like GKE and BigQuery.

• Provides point-in-time recovery. Because of regular backups, we can restore the DB to its state at an earlier point in time. (Make sure binary logging is enabled.)

• Secure access via an inbuilt firewall.

• Easy migration from on-premises or from any other cloud platform with minimum downtime.

When to choose?

There are multiple ways you can use CloudSQL. The most common are using CloudSQL for CMS backends for platforms like Drupal or WordPress. Google Cloud has a one-click launch mechanism for these CMS software and behind the scene, it uses CloudSQL as a backend DB.

Apart from this, you can use CloudSQL for:

1. Lift and shift: When you want to lift and shift a SQL database from on-premises to the cloud.

2. Data analysis: Import data from a CSV text file into Cloud SQL and then analyze it with basic queries. CloudSQL is good for analyzing a medium size of data <100GB.

3. Compliant and secure database: CloudSQL provides data encryption at rest and in transit. Private connectivity with Virtual Private Cloud and user-controlled network access with firewall protection. CloudSQL is compliant with SSAE 16, ISO 27001, PCI DSS, and HIPAA.

These are some of the points you should keep in mind while choosing the RDBMS in the cloud. CloudSQL’s most frequent use-case is “Lift and Shift” and whenever you come across the words saying “Lift and shift existing database”, CloudSQL is the way to go.

Let’s create our first MySQL instance in the cloud. (You can choose PostgreSQL as well.)

Demo

In this demo, we will create a SQL instance using UI as well as using gcloud SDK. Let’s first try creating the instance using UI.

Creating a MySQL instance using UI

To open CloudSQL,

1. Go to the Main menu > Databases > SQL

2. Click on the Create Instance button.