Automate database instance provisioning

Automate Database Configuration and Performance Parameters is the practice of defining database instance settings in repeatable scripts instead of manual console steps. Cloud SQL (opens in a new tab) instance creation accepts compute sizing, database flags, high availability, backup, and private connectivity settings through gcloud commands and Terraform resources. Automation with infrastructure as code (IaC) reduces human error, keeps development, staging, and production consistent, and enables faster recovery from failures.

1.1Machine Type and Compute Sizing Through Automation

Machine type and compute sizing controls the vCPUs and memory available to a Cloud SQL instance for its workload. In gcloud, sizing is set with --cpu=NUMBER_OF_CORES and --memory=AMOUNT_OF_MEMORY, where memory requires a unit such as 3072MiB or 9GiB, plus --database-version, --zone, and --root-password. In Terraform, the same sizing is set in the google_sql_database_instance resource with settings { tier = "db-custom-2-7680" }, where the tier encodes CPU and memory as db-custom-[CPUS]-[MEMORY_MBS] or uses a preset such as db-f1-micro. Instance names are externally visible, so the name must not contain sensitive or personally identifiable information, and the project ID must not be included in the name because Cloud SQL adds it automatically where needed.

1.2Database Flags and Instance Parameters

Database flags are key-value instance parameters applied at creation time to change database behavior. A gcloud example sets --database-flags=cloudsql.iam_authentication=on to enable Identity and Access Management (IAM) database authentication for PostgreSQL, while the MySQL variant uses cloudsql_iam_authentication=on. In Terraform, the flag is set inside settings with a database_flags { name = "cloudsql.iam_authentication" value = "on" } block alongside name, region, database_version, and tier. The automation flow runs from the defined flag to the Cloud SQL control plane, which then applies the flag to the provisioned instance.

1.3High Availability Configuration

High availability (HA) (opens in a new tab) configuration determines whether a Cloud SQL instance can fail over to another zone in the same region. The availability_type parameter controls this behavior, and automated scripts set it to enable a regional standby that uses synchronous replication between zones for zero or near-zero recovery time objective (RTO) and recovery point objective (RPO). When primary and secondary zones are both specified, the primary zone defaults to Any and the secondary zone defaults to Any (different from primary), and the two zones must be distinct. A Terraform HA example combines availability_type = "REGIONAL" with tier = "db-custom-2-7680" and an enabled backup_configuration block, because automated backups support recovery alongside HA.

1.4Automated Backup Configuration

Automated backup configuration defines whether backups run on a schedule and when the backup window starts. In Terraform, backups are enabled inside settings with a backup_configuration { enabled = true start_time = "20:55" binary_log_enabled = true } block, where binary_log_enabled is used for MySQL point-in-time recovery. In gcloud, an existing instance is patched with gcloud sql instances patch INSTANCE_NAME --backup-start-time=HH:MM, where the time uses 24-hour format in UTC and defines the start of a 4-hour window during which the backup can begin. The change is verified with gcloud sql instances describe INSTANCE_NAME, which must show enabled: true and the specified time in the backupConfiguration section.

1.5Private Connectivity Configuration

Private connectivity configuration controls how Virtual Private Cloud (VPC) clients reach the Cloud SQL instance without using public IP. In Terraform, the google_sql_database_instance resource sets ip_configuration { ipv4_enabled = false } with a nested psc_config { psc_enabled = true allowed_consumer_projects = [] } block to enable Private Service Connect (PSC). Traffic flows from allowed consumer projects listed in allowed_consumer_projects to the PSC endpoint attached to the Cloud SQL instance, while the trust boundary sits between the consumer VPC network and the Google-managed database network. The same Terraform project can deploy both the private networking setup and Compute Engine VMs as clients alongside the instance for consistent end-to-end networking.

1.6Terraform Provisioning Workflow

The Terraform provisioning workflow applies version-controlled configuration files to a Google Cloud project through an ordered lifecycle. Each configuration file must live in its own directory, called a root module, with a .tf extension such as main.tf, and samples assume required APIs are already enabled in the project. Automation extends beyond initial provisioning because IaC minimizes human errors, Eventarc or Workflows can trigger remedial actions from audit logs, autoscaling can increase capacity during failover, and platform engineering can apply policy guardrails during deployment.

1. Prepare Cloud Shell: Launch Cloud Shell and set the default project once per project with export GOOGLE_CLOUD_PROJECT=PROJECT_ID, unless explicit values in the configuration override the environment variable.
2. Prepare the directory: Create and enter the root module directory, create main.tf, copy in the sample code, review and modify parameters for the environment, then run terraform init once per directory, with -upgrade to use the latest Google provider version.
3. Review the plan: Run terraform plan to preview resources that Terraform will create or update, and correct the configuration when the preview does not match expectations.
4. Apply the configuration: Run terraform apply, enter yes at the prompt, wait for the Apply complete! message, then verify the resources in the Google Cloud console.
5. Delete when needed: Set deletion_protection = "false" in the configuration, run terraform apply to apply that change, then run terraform destroy and enter yes to remove previously applied resources.

Infrastructure as Code (IaC) (opens in a new tab) is the process of provisioning and managing infrastructure using code instead of graphical interfaces or manual command-line scripts. IaC configurations declare the desired state of resources such as virtual machines, database connections, and storage, and the tooling creates that state in a safe and repeatable way. The same configuration can be deployed multiple times to create reproducible development, test, and production environments for databases including Cloud SQL, Spanner, and Bigtable.

2.1Benefits of IaC for Database Provisioning

The benefits of IaC for database provisioning come from treating infrastructure changes like application code with review, history, and reuse. A single versioned configuration becomes the source of truth that teams share across environments, which reduces manual errors at scale. IaC supports consistent database deployment, scalability, and high availability because every environment is built from the same declared settings.

• Infrastructure is defined from requirements and the same configuration is reused to create multiple environments consistently.
• Creation and management of cloud resources is automated, including deployment and test environments.
• Infrastructure changes are reviewed and automatically validated through change-controlled processes, as with application changes.
• History of all configuration changes is kept, so changes can be audited and reverted.
• A single source of truth is maintained for cloud infrastructure.

2.2IaC Tools for Google Cloud

IaC tools for Google Cloud divide the work between defining infrastructure and automating its deployment and state. HashiCorp Terraform defines resources in human-readable configuration files, while Infrastructure Manager (Infra Manager) and Terraform Cloud handle automated deployment, remote execution, and state. Choose the tool based on whether the need is authoring configurations, deploying them natively on Google Cloud, or managing change across an organization.

2.3Defining Database Resources with Terraform

Defining database resources with Terraform means declaring Cloud SQL instances, databases, and users as resources that depend on each other. The google_sql_database_instance resource defines the instance name, region, database version such as POSTGRES_14 or MYSQL_8_0, machine tier such as db-custom-2-7680, database flags, high availability, and backups. The google_sql_database resource depends on the instance through instance = google_sql_database_instance.instance.name, and the google_sql_user resource attaches CLOUD_IAM_USER or CLOUD_IAM_SERVICE_ACCOUNT identities to the instance named by google_sql_database_instance.default.name. High availability and protection (opens in a new tab) are also declared in code, where setting availability_type = "REGIONAL" creates a regional Cloud SQL instance that spans multiple zones, while a backup_configuration block with enabled = true and start_time = "20:55" schedules automated backups. The deletion_protection argument controls whether Terraform is allowed to delete the instance, which separates day-to-day updates from destructive removal.

2.4Standard Terraform Provisioning Workflow

The standard Terraform provisioning workflow moves configuration from source files to running infrastructure in a fixed order. Each configuration lives in its own directory, called a root module, and Terraform tracks the mapping between files and real resources as state. The workflow depends on Cloud Shell for authentication and project context, on the root module for the declared resources, and on plan review before any change is applied.

1. Set the project: Launch Cloud Shell and export GOOGLE_CLOUD_PROJECT once per project, unless explicit values in the configuration override it.
2. Prepare the root module: Create a directory with a .tf file such as main.tf, copy and edit parameters for the environment, then run terraform init once per directory.
3. Review the plan: Run terraform plan first and correct the configuration so created or updated resources match expectations.
4. Apply the change: Run terraform apply, confirm with yes, wait for the Apply complete! message, then verify the resources in the Google Cloud console.
5. Remove when needed: First set deletion_protection = "false" and run terraform apply, and only after that run terraform destroy with yes to remove previously applied resources.

2.5Version Control, State Management, and CI/CD

Version control, state management, and CI/CD turn Terraform files into a reliable database delivery pipeline. Provisioning logic is stored in source control, modular templates are reused across projects, and configurations become artifacts with versioning for dev, staging, and production promotion. Terraform Cloud securely stores state and secrets remotely, while Infra Manager and Cloud Build run automated validate and plan checks and trigger updates on code commits, so environment differences are handled by templating rather than manual edits.

Integrate Security and CI/CD into Provisioning Workflows means defining database infrastructure, access, and encryption in version-controlled code and deploying that code through an automated pipeline. Terraform (opens in a new tab) defines the desired state of resources such as Cloud SQL instances in human-readable files, while Cloud Build executes the build and Infrastructure Manager (Infra Manager) automates the deployment of the Terraform configuration to Google Cloud. The same pipeline that creates development, test, and production environments also applies Identity and Access Management (IAM) roles, service accounts, and Customer-Managed Encryption Keys (CMEK) so security is consistent on every deployment.

3.1Integrate IaC with Cloud Build Pipelines

Cloud Build is the service that runs database provisioning builds on Google Cloud by importing Terraform code, executing the specified steps, and producing versioned artifacts. Code flows from source control into Cloud Build, then from Cloud Build to Infra Manager or Terraform, and finally from the deployment tool to the target Google Cloud project and database instance. Cloud Build can import source from Cloud Storage buckets for data and artifacts, Cloud Source Repositories for Google-hosted code, GitHub for external version control, and Bitbucket for external version control. A database provisioning pipeline moves code through an ordered lifecycle where each stage depends on the previous stage:

1. Commit trigger: A code commit to a configured branch automatically triggers Cloud Build.
2. Validate and plan: The pipeline runs automated checks such as Terraform validate and plan to preview creates, updates, or deletes.
3. Version artifact: The pipeline stores the tested configuration as a versioned artifact in Artifact Registry or source control.
4. Promote environments: The same configuration is promoted through distinct dev, staging, and prod definitions with environment-specific variables and templating.
5. Apply deployment: Infra Manager or Terraform applies the approved configuration to create or update the database instance in the target environment.

3.2Automate IAM Roles and Service Accounts for Databases

Automating IAM for databases means enabling IAM database authentication on the instance and granting database access through IAM policy bindings in code. Cloud SQL supports IAM database authentication through the cloudsql.iam_authentication database flag, and access is granted with the cloudsql.instanceUser role that permits principals to log in to the instance. IAM policies and service accounts are defined in Terraform or in Config Connector YAML manifests that are deployed to a Google Kubernetes Engine (GKE) cluster, so application and infrastructure access change together through the pipeline. Roles can be granted on the project that contains the Cloud SQL instances by using the Google Cloud console IAM page, the gcloud CLI, Terraform, or the Cloud SQL Admin API.

The cloudsql.instanceUser role applies to three types of principals, where an individual user or individual service account requires its own role binding while a group requires only one binding for all current members:

• IAM users who log in with individual Google identities
• Service accounts that allow applications and Cloud Build jobs to authenticate without passwords
• Groups where every member automatically inherits the permissions granted to the group

Best practice is to create a dedicated service account for each service that requires database access and assign least-privilege roles through the pipeline rather than manually in the console.

3.3Automate Encryption and Guardrails in Provisioning Code

Automating encryption and guardrails means declaring network, encryption, and lifecycle protections in the same Terraform templates that create the database. Customer-Managed Encryption Keys (CMEK) let the team control the encryption keys used for data, and compliance controls such as Require CMEK on Dataproc Clusters show the pattern of enforcing CMEK through code rather than manual selection. Related guardrails include restricting public IP addresses on database and compute resources, defining VPC Service Controls perimeters, and verifying Cloud SQL failover replication for high availability. Terraform enforces this desired state through a consistent workflow where Cloud Shell prepares the project, the root module holds the .tf files, and commands move state forward in order. First, terraform init prepares the directory, then terraform plan previews the changes against expectations, and only after review does terraform apply create or update the resources. To allow deletion, the configuration must first set deletion_protection to false before terraform destroy removes the managed resources.

Key Facts

• Cloud SQL machine sizing is automated with gcloud --cpu and --memory flags and with Terraform tier = "db-custom-2-7680" in the google_sql_database_instance resource.
• availability_type = "REGIONAL" creates a Cloud SQL standby in a different zone in the same region, while ZONAL is the default with no failover capability.
• Terraform automated backups use backup_configuration { enabled = true start_time = "20:55" }, while gcloud patching uses --backup-start-time=HH:MM in UTC for the start of a 4-hour window.
• Enabling Cloud SQL IAM database authentication requires setting cloudsql.iam_authentication to on and granting the cloudsql.instanceUser role.
• Terraform cannot destroy a protected Cloud SQL instance until deletion_protection is set to false and applied before running terraform destroy.