Setting Up a Fully Managed RDBMS

In the previous chapter, we discussed the considerations and best practices when designing a database for storing structured data, and some real-world SQL examples. In this chapter, we will take the structured database design to do some hands-on learning with a fully managed cloud relational database. You will learn how to set up and configureyour instance, and create databases and objects in the database and how to programmatically connect to the database and access data.

In this chapter, we’ll cover the following topics:

• Fully managed databases
• Fully managed RDBMS
• Cloud SQL as an example
• Setting up and configuring a fully managed RDBMS
• Creating an application with the cloud database
• Operational aspects of cloud relational databases

Fully managed databases

Fully managed databases reduce the need for manual effort and lower the costs involved in administering your databases. They also allow your teams to focus more on productive areas of work, contributing to your business. In a fully managed database, the provider takes care of infrastructure provisioning, maintenance, routine updates, scalability, backup, recovery, replication, latency, availability, security, privacy, regulatory settings, and administration in a very self-sufficient and cost-effective way, as opposed to self-managed databases, where all of these need to be done by the database administration teams.

Some examples of self-managed databases are the non-cloud version of MySQL, PostgreSQL, SQL Server, Maria DB, Oracle, and IBM DB2. Some of these databases have equivalent managed cloud services..

Fully managed RDBMS

A fully managed Relational Database Management System (RDBMS) is a provider-managed database system that does the following:

• Structures data in tables made of rows and columns
• Has the ability to join tables to derive information between related data
• Adheres to the atomicity, consistency, isolation, and durability (ACID) properties of your applications’ transactions
• Supports integrity constraints to maintain data consistency across multiple tables
• Supportive of rows having a unique identifier, columns have unique names, and values are atomic
• Has values that all have the same datatype in a given column
• Has a fixed schema definition for the relational data model, and the data definition language (DDL) changes for any further schema-level modifications

Let’s dive into the setting up, configuration, and application of the most preferred fully managed cloud relational database service for MySQL, PostgreSQL, and SQL...

Cloud SQL

Cloud SQL is a fully managed database service that makes it easy to set up, maintain, manage, and administer your relational databases on the Google Cloud Platform. It supports up to 99.99% availability and supports up to 64 TB of storage, with the ability to automatically increase storage size as needed. You can use Cloud SQL with MySQL, PostgreSQL, and SQL Server.

As we already discussed, Cloud SQL is a fully managed database and Google will take care of all operational activities including minor version upgrades, backup, recovery, scaling (if user configures for it), failover, monitoring, authorization, networking, security, observability, and so on. The following are some of its key features:

• Cloud SQL provides integration to other Google Cloud services such as Cloud Run, GKE, Cloud Functions, BigQuery, and Compute Engine
• It has secure access
• It has high availability with its zonal/regional database replication feature
• It can scale up by adding...

Setting up and configuring a fully managed RDBMS

If you are new to Google Cloud, go to the Google Cloud console (https://console.cloud.google.com/), select your organization, and create a new Google Cloud project with billing enabled.

You can follow the instructions here:

https://cloud.google.com/resource-manager/docs/creating-managing-projects.

All the steps mentioned in the following subsections can be done with Cloud Shell commands or via the Google Cloud console.

Creating a Cloud SQL instance for MySQL

Go to the Google Cloud console and, in the search bar, search for Cloud SQL. Then, follow these steps:

1. Go to the Cloud SQL instance page at https://console.cloud.google.com/sql. The following figure shows the Cloud SQL instance page:

Figure 4.1 – The Cloud SQL instance page

2. Click on the CREATE INSTANCE button. This will take you to the Create an instance page, as shown here:

Figure...

Creating an application with the Cloud database

Let us create a small one-page application to see how to access Cloud SQL for MySQL programmatically. You can try it in any programming language of your choice. I am going to use Java as my preferred language for code samples.

Before stepping into the application, make sure you have the Cloud Functions service account configured for Cloud SQL connections.

Configuring the Cloud Functions service account

Perform the following steps to configure your Cloud Functions service account:

1. Go to the Google Cloud console’s IAM page using this link: https://console.cloud.google.com/iam-admin/iam.
2. Edit the service account – Compute Engine default service account – to add a role.
3. Click on Add another role.
4. Add the Cloud SQL Client role.
5. Click Save.

Now that the service account is configured, let’s create the functions.

Creating a Cloud Function

Cloud Functions let you create...

Operational aspects of cloud relational databases

Apart from the key features and setup details we discussed so far, there are some key operational considerations that every organization or startup needs to assess before starting their database design journey. We will look at only a few key operational considerations and how Cloud SQL supports these aspects.

Migration

Cloud SQL’s Database Migration Service comes in handy from the Google Cloud perspective; it simplifies migrations on-premise or from other cloud providers to Google Cloud SQL. Its key features are that it is very simple, has minimal downtime migration, and is fully serverless to set up without any operational burden.

Monitoring

We can monitor Cloud SQL instances from the Cloud Monitoring dashboard to cover metrics such as CPU utilization, active connections, storage usage, memory usage, read and write operations, ingress and egress bytes, queries, questions, and so much more.

Query Insights

Beyond...

Summary

In this chapter, we have designed a structured database via hands-on learning by creating and configuring a fully managed cloud relational database. We discussed the essential features of a fully managed RDBMS: Google Cloud’s Cloud SQL. We covered the setup and configuration of Cloud SQL, created a database and objects, performed database operations on it programmatically, and assessed design considerations and operational highlights such as migration, monitoring, query insights, and security when working with Cloud SQL. We also created a one-page application to access Cloud SQL with a Java Cloud Function as an example.

In the upcoming chapters, we will move on to other formats of data, hands-on applications, and their operational aspects.