Instances Should Be Multi AZ

More Info:

Ensures that SQL instances have a failover replica to be cross-AZ for high availability. Creating SQL instances in with a single AZ creates a single point of failure for all systems relying on that database. All SQL instances should be created in multiple AZs to ensure proper failover.

Risk Level

Medium

Address

Operational Maturity, Reliability, Security

Compliance Standards

• APRA CPS 234 (Australia)
• BSI C5 (Germany)
• Brazil LGPD
• CCPA / CPRA (California)
• CIS Critical Security Controls v8
• CMMC 2.0
• CSA Cloud Controls Matrix v4
• DPDPA
• Digital Operational Resilience Act (EU)
• Essential 8
• HITRUST CSF
• ISO/IEC 27017
• ISO/IEC 27018
• ISO/IEC 27701
• KSA PDPL
• MAS Technology Risk Management (Singapore)
• MITRE ATT&CK (Cloud)
• NIS2 Directive
• NIST CSF
• NIST SP 800-171
• NYDFS 23 NYCRR 500
• PCI
• Reserve Bank of India (RBI) Master Direction – Information Technology Framework
• SOC2
• SWIFT Customer Security Controls Framework
• Sarbanes-Oxley IT General Controls
• UK NCSC Cyber Assessment Framework

Triage and Remediation

Remediation

Remediation

Using Console

In GCP, the equivalent of Multi-AZ in AWS is called “Instance Group”. Here are the step-by-step instructions to remediate the misconfiguration:

1. Open the GCP console and navigate to the Compute Engine section.
2. Select the instance that needs to be remediated.
3. Click on the “Edit” button at the top of the page.
4. Scroll down to the “Availability Policy” section.
5. Check the box next to “Create a new instance group” and select the region where the group will be created.
6. Choose the “Regional” option for the instance group type.
7. In the “Size” field, enter the number of instances you want to create in the group.
8. Click on “Save” to create the instance group.

Once the instance group is created, the instance will automatically be replicated across multiple zones within the selected region, providing Multi-AZ functionality.

Using CLI

In GCP, the equivalent of Multi AZ is called Regional Managed Instance Groups. Here are the step-by-step instructions to remediate the misconfiguration:

1. Open the Cloud Shell in the GCP Console.
2. Run the following command to create a new regional managed instance group:

gcloud compute instance-groups managed create [INSTANCE_GROUP_NAME] --region [REGION] --template [INSTANCE_TEMPLATE_NAME] --size [SIZE]

Replace [INSTANCE_GROUP_NAME] with the name you want to give your instance group, [REGION] with the region where you want to create the group, [INSTANCE_TEMPLATE_NAME] with the name of the instance template you want to use, and [SIZE] with the desired number of instances in the group.

3. Verify that the instance group has been created successfully by running the following command:

gcloud compute instance-groups list

This command will display a list of all the instance groups in your project. Verify that the new instance group is listed.

4. Once the instance group is created, you can add instances to it by running the following command:

gcloud compute instance-groups managed set-autoscaling [INSTANCE_GROUP_NAME] --region [REGION] --max-num-replicas [MAX_REPLICAS] --target-cpu-utilization [CPU_UTILIZATION]

Replace [INSTANCE_GROUP_NAME] with the name of your instance group, [REGION] with the region where the group is located, [MAX_REPLICAS] with the maximum number of instances you want in the group, and [CPU_UTILIZATION] with the target CPU utilization percentage that triggers the autoscaling.

5. Verify that the autoscaling has been set up successfully by running the following command:

gcloud compute instance-groups managed describe-autoscaler [INSTANCE_GROUP_NAME] --region [REGION]

This command will display the details of the autoscaler for the specified instance group.

With these steps, you have successfully created a regional managed instance group with autoscaling enabled. This will ensure that your instances are highly available and can handle increased traffic or workload.

Using Python

In GCP, instances can be made highly available by using instance groups and regional managed instance groups. The regional managed instance groups provide high availability by automatically distributing instances across multiple zones within a region. Follow the below steps to remediate the misconfiguration:

1. First, create an instance template that specifies the configuration for the instances that you want to create.

from googleapiclient import discovery

compute = discovery.build('compute', 'v1')

project = 'my-project' # Replace with your project ID
zone = 'us-central1-a' # Replace with your desired zone
name = 'my-instance-template' # Replace with the desired name for your instance template

config = {
    'name': name,
    'properties': {
        'machineType': f'zones/{zone}/machineTypes/n1-standard-1',
        'disks': [
            {
                'boot': True,
                'autoDelete': True,
                'initializeParams': {
                    'sourceImage': 'projects/debian-cloud/global/images/family/debian-10'
                }
            }
        ],
        'networkInterfaces': [
            {
                'network': 'global/networks/default',
                'accessConfigs': [
                    {
                        'type': 'ONE_TO_ONE_NAT',
                        'name': 'External NAT'
                    }
                ]
            }
        ],
        'metadata': {
            'items': [
                {
                    'key': 'startup-script',
                    'value': '#!/bin/bash\napt-get update\napt-get install apache2 -y\n'
                }
            ]
        },
        'serviceAccounts': [
            {
                'email': 'default',
                'scopes': [
                    'https://www.googleapis.com/auth/devstorage.read_write',
                    'https://www.googleapis.com/auth/logging.write'
                ]
            }
        ]
    }
}

request = compute.instanceTemplates().insert(project=project, body=config)
response = request.execute()

print(f'Instance template {name} created.')

2. Next, create a regional managed instance group using the instance template. This will automatically distribute instances across multiple zones within the region.

from googleapiclient import discovery

compute = discovery.build('compute', 'v1')

project = 'my-project' # Replace with your project ID
zone = 'us-central1' # Replace with your desired zone
name = 'my-instance-group' # Replace with the desired name for your instance group
template = 'projects/my-project/global/instanceTemplates/my-instance-template' # Replace with the name of your instance template

config = {
    'name': name,
    'region': f'{zone}-b',
    'baseInstanceName': 'my-instance',
    'instanceTemplate': template,
    'targetSize': 2,
    'autoHealingPolicies': [
        {
            'initialDelaySec': 300,
            'healthCheck': 'projects/my-project/global/httpHealthChecks/my-http-health-check'
        }
    ]
}

request = compute.regionInstanceGroups().insert(project=project, body=config)
response = request.execute()

print(f'Regional managed instance group {name} created.')

3. Finally, verify that the instances are running in multiple zones within the region.

from googleapiclient import discovery

compute = discovery.build('compute', 'v1')

project = 'my-project' # Replace with your project ID
zone = 'us-central1' # Replace with your desired zone
name = 'my-instance-group' # Replace with the name of your instance group

request = compute.regionInstanceGroups().listInstances(project=project, region=f'{zone}-b', instanceGroup=name)
response = request.execute()

for instance in response['items']:
    print(f'{instance["name"]} is running in zone {instance["zone"]}.')

This will ensure that the instances are running in multiple zones within the region, providing high availability and ensuring that your application remains available even if one zone experiences an outage.

Using Terraform

resource "google_sql_database_instance" "PRIMARY_INSTANCE" {
  name             = "PRIMARY_INSTANCE_NAME"          # replace with your instance name
  database_version = "MYSQL_8_0"                      # or POSTGRES_x, SQLSERVER_x
  region           = "PRIMARY_REGION"                 # e.g. "us-central1"

  settings {
    tier = "db-custom-2-7680"                         # replace with your machine type

    availability_type = "REGIONAL"                    # enables HA with a failover replica

    location_preference {
      zone           = "PRIMARY_ZONE"                 # e.g. "us-central1-a"
      secondary_zone = "FAILOVER_ZONE"                # e.g. "us-central1-b"
    }

    backup_configuration {
      enabled = true
    }

    ip_configuration {
      ipv4_enabled = true
    }
  }
}

Changing an existing single-zone (availability_type = "ZONAL" or unset) instance to availability_type = "REGIONAL" forces replacement of the Cloud SQL instance, causing downtime and data loss unless you migrate data (export/point-in-time restore) to the new instance first.

Verification: terraform plan should show the google_sql_database_instance either being created with availability_type = "REGIONAL" and a secondary_zone, or, for existing instances, being replaced with those attributes added/changed.

Additional Reading:

• https://cloud.google.com/sql/docs/mysql/instance-settings