Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Enable High Availability for Cloud SQL Database Instances

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: High (not acceptable risk)
Rule ID: CloudSQL-003

Ensure that all your production and mission-critical Google Cloud SQL database instances are configured for High Availability (HA) and automatic failover support.

This rule resolution is part of the Conformity Security & Compliance tool for GCP.

Reliability

When a High Availability (HA) configuration (also known as database cluster) is enabled, the Google Cloud SQL service provides data redundancy in the event of an outage or a planned maintenance session. A Cloud SQL database instance configured for high availability is also called a regional instance, being deployed to a primary and a secondary zone within the configured Google Cloud region. For a regional database instance, the redundant configuration consists of a primary instance (i.e. master) and a standby instance. Through synchronous replication to each zone's persistent disk, all writes made to the primary instance are also made to the standby instance. In the event of an instance, network, or zone failure, the HA configuration reduces downtime and facilitates the availability of your data to client applications.

Audit

To determine if your production Cloud SQL database instances are configured for high availability, perform the following operations:

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click on the name (ID) of the production database instance that you want to examine.

05 In the navigation panel, select Overview to access the configuration details of the selected instance.

06 In the Configuration section, under deployment zone, check the High Availability (HA) configuration status. If the HA status is set to Not highly available (zonal), the selected Google Cloud SQL database instance is not using the High Availability deployment configuration, therefore there is no failover support available for the instance in case of outage.

07 Repeat step no. 4 – 6 to check the HA configuration status for other Cloud SQL instances available within the selected project.

08 Repeat steps no. 2 – 7 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run projects list command (Windows/macOS/Linux) using custom query filters to list the IDs of all the Google Cloud Platform (GCP) projects available in your Google Cloud account: 

gcloud projects list
	--format="table(projectId)"

02 The command output should return the requested GCP project identifiers: 

PROJECT_ID
cc-mobile-project-123123
cc-neural-project-123123

03 Run sql instances list command (Windows/macOS/Linux) using custom filtering to describe the name of each Cloud SQL database instance provisioned for the selected Google Cloud project: 

gcloud sql instances list
	--project cc-mobile-project-123123
	--format="(NAME)"

04 The command output should return the requested database instance name(s): 

NAME
cc-production-instance
cc-staging-instance
cc-development-instance

05 Run sql instances describe command (Windows/macOS/Linux) using the name of the Cloud SQL database instance that you want to examine as identifier parameter and custom query filters to describe the availability type configured for the selected database instance: 

gcloud sql instances describe cc-production-instance
	--format="value(settings.availabilityType)"

06 The command output should return the level of availability set for the selected resource (REGIONAL for highly available and ZONAL for not highly available): 

ZONAL

If the sql instances describe command output returns ZONAL, as shown in the example above, the selected Google Cloud SQL database instance is not using the High Availability (REGIONAL) configuration, therefore there is no failover support available for the instance in case of outage.

07 Repeat step no. 5 and 6 to check the availability type for other Cloud SQL instances created for the selected project.

08 Repeat steps no. 3 – 7 for each project available within your Google Cloud account.

Remediation / Resolution

To configure your production and mission-critical Cloud SQL instances for High Availability (HA) and automatic failover support, perform the following operations:

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click on the ID of the database instance that you want to reconfigure.

05 In the navigation panel, select Overview to access the configuration details of the selected instance.

06 Click on the Edit button from the dashboard top menu to enter the instance edit mode.

07 In the Configuration options section, click on Backups, recovery, and high availability to expand the panel with the availability options made available for the selected instance.

08 Under Availability, make sure that Point-in-Time Recovery (PITR) feature is enabled, then choose High availability (regional) option to enable High Availability (HA) and automatic failover support for the selected Cloud SQL database instance. Click Close to close the panel.

09 Click Save to apply the configuration changes. IMPORTANT: Configuring an existing database instance for high availability produces a few minutes of downtime while the instance is being reconfigured.

10 For the Changes require restart confirmation box, select SAVE AND RESTART to restart the reconfigured database instance.

11 Repeat step no. 4 – 10 to configure High Availability (HA) for other Cloud SQL database instances available within the selected project.

12 Repeat steps no. 2 – 11 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run sql instances patch command (Windows/macOS/Linux) using the name of the Cloud SQL database instance that you want to reconfigure as identifier parameter, to enable High Availability (HA) and automatic failover support for the selected instance by setting the --availability-type parameter value to REGIONAL. Make sure that Point-in-Time Recovery (PITR) feature is enabled before you submit the command request: IMPORTANT: Configuring an existing database instance for high availability causes a few minutes of downtime while the instance is being reconfigured. 

gcloud sql instances patch cc-production-instance
	--availability-type REGIONAL

02 The output should return the sql instances patch command request status. Once HA is enabled, the selected instance automatically fails over to another zone within the region in case of an outage: 

The following message will be used for the patch API method.

{"name": "cc-production-instance", "project": "cc-mobile-project-123123", "settings": {"availabilityType": "REGIONAL"}}

Patching Cloud SQL instance...done.
Updated [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-mobile-project-123123/instances/cc-production-instance].

03 Repeat step no. 1 and 2 to configure High Availability (HA) for other Cloud SQL instances provisioned for the selected project.

04 Repeat steps no. 1 – 3 for each project created within your Google Cloud account.

References

Publication date Apr 21, 2021

Related CloudSQL rules