This page describes how to useadvanced disaster recovery (DR).Advanced DR provides two main capabilities:

• Replica failover lets you fail over your primary instance to the DRreplica immediately in the event of a region failure.For Cloud SQL for SQL Server, the DR replica is a cascadable replica.
• Switchover lets you reverse the roles of the primary instance anda DR replica with zero data loss. You can use switchover torestore a deployment to its original deployment state after replicafailover, or you can use switchover to test DR.

Advanced DR is supported only on Cloud SQL Enterprise Plus edition instances.

Before you begin

If you plan to use the Google Cloud SDK, then you must use version 502.0.0 orlater. To check the version of theGoogle Cloud SDK, rungcloud --version. To updatethe Google Cloud SDK, rungcloud components update.

To install the Google Cloud SDK, seeInstall thegcloud CLI.

Create a DR replica

Before you use advanced DR,create a cascadable replicaof the primary instance in a differentregion than the primary instance.

Perform a switchover

After you've created a DR replica, you can perform the switchover operation.However, as a best practice, avoid performing the switchover operation under thefollowing circumstances:

• The primary instance is being actively used.
• Admin operations are in progress, such as automated backup or theenablement or disablement of high availability (HA).

To avoid a timeout, consider performing switchover when the transaction volumeis low.

When switchover completes, the operation takes a backup of the new primaryinstance (the former DR replica) as soon as the new primary instance ispromoted.This backup can take between 5 and 15 minutes to complete depending on the disksize. After this backup is complete, if you want to use PITR on the promotedinstance, then you must manuallyenable PITR.For more information about the considerations of using PITR with advanced DR, seeUse PITR with advanced DR.

After the switchover operation is complete, you'll notice that the direction ofreplication is reversed.

After your old primary instance is reconfigured as a read replica,theDNS write endpoint,which previously resolved to the old primary instance, resolves to the newprimary instance.

Before you begin

Before you perform the switchover operation, do the following:

• If you haven't done so already,create a DR replica.
• Verify that the primary instance and the DR replica are online.
• If you're using aDNS write endpoint, then verify that the SSL configuration for the primary instance and the DR replica are the same. For example, if the DR replica is configured toenforce SSL encryption, but the primary instance allows unencrypted connections, then clients won't be able to connect to the new primary instance after the switchover operation completes.
• Take an on-demand backup of the primary instance. This backup is a precaution in case you need to recover from any unexpected failures.

Perform the switchover operation

gcloudsqlinstancesswitchoverREPLICA_NAME

resource "google_sql_database_instance" "original-primary" {  name             = "sqlserver-primary-instance-name"  region           = "us-east1"  database_version = "SQLSERVER_2022_ENTERPRISE"  instance_type    = "CLOUD_SQL_INSTANCE"  root_password    = "INSERT-PASSWORD-HERE"  replica_names    = ["sqlserver-replica-instance-name"]  settings {    tier    = "db-perf-optimized-N-2"    edition = "ENTERPRISE_PLUS"    backup_configuration {      enabled = "true"    }  }}resource "google_sql_database_instance" "dr_replica" {  name = "sqlserver-replica-instance-name"  # Remove or comment out the master_instance_name  # master_instance_name = google_sql_database_instance.original-primary.name  region           = "us-west2"  database_version = "SQLSERVER_2022_ENTERPRISE"  # Change the instance type from "READ_REPLICA_INSTANCE" to "CLOUD_SQL_INSTANCE".  instance_type = "CLOUD_SQL_INSTANCE"  root_password = "INSERT-PASSWORD-HERE"  # Add the original primary to the replica_names list  replica_names = ["sqlserver-primary-instance-name"]  # Remove or comment out the replica_configuration section  # replica_configuration {  #  cascadable_replica = true  # }  settings {    tier    = "db-perf-optimized-N-2"    edition = "ENTERPRISE_PLUS"  }}

resource "google_sql_database_instance" "original-primary" {  name = "sqlserver-primary-instance-name"  # Set master_instance_name to the new primary instance, the original DR replica.  master_instance_name = "sqlserver-replica-instance-name"  region               = "us-east1"  database_version     = "SQLSERVER_2022_ENTERPRISE"  # Change the instance type from "CLOUD_SQL_INSTANCE" to "READ_REPLICA_INSTANCE".  instance_type = "READ_REPLICA_INSTANCE"  root_password = "INSERT-PASSWORD-HERE"  # Remove  values from the replica_names field, but don't remove the field itself.  replica_names = []  # Add replica_configuration section and set cascadable_replica to true.  replica_configuration {    cascadable_replica = true  }  settings {    tier    = "db-perf-optimized-N-2"    edition = "ENTERPRISE_PLUS"    backup_configuration {      enabled = "true"    }  }}resource "google_sql_database_instance" "dr_replica" {  name             = "sqlserver-replica-instance-name"  region           = "us-west2"  database_version = "SQLSERVER_2022_ENTERPRISE"  # Change the instance type from "READ_REPLICA_INSTANCE" to "CLOUD_SQL_INSTANCE".  instance_type = "CLOUD_SQL_INSTANCE"  root_password = "INSERT-PASSWORD-HERE"  # Add the original primary to the replica_names list  replica_names = ["sqlserver-primary-instance-name"]  settings {    tier    = "db-perf-optimized-N-2"    edition = "ENTERPRISE_PLUS"  }}

POST https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME/switchover

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d "" \
     "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME/switchover"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME/switchover" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2024-04-01T22:43:37.981Z",  "operationType": "SWITCHOVER",  "name": "OPERATION_ID",  "targetId": "REPLICA_ID",  "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",  "targetProject": "PROJECT_ID"}

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/switchover

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d "" \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/switchover"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/switchover" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2024-04-01T22:43:37.981Z",  "operationType": "SWITCHOVER",  "name": "OPERATION_ID",  "targetId": "REPLICA_ID",  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID",  "targetProject": "PROJECT_ID"}

Perform DR by invoking a replica failover

In the event of region failure or a disaster, you can perform DR byinvoking a replica failover operation to your designated DR replica. To performa replica failover, you promote the DR replica. In contrast with switchover, the promotion of the DR replica isimmediate.

Since the DR replica assumes the role of the primary instance immediately,it's possible that the replica doesn't have all of the data from theold primary instance due to replication lag. For this reason, a replica failovercan incur data loss.

As part of the promotion process, replica failover takes a backup of the newprimary instance (the former DR replica) right after the DR replica becomes thenew primary instance. After this backup is complete, point-in-time-recovery(PITR) is fully enabled on the new primary instance. This backup can takebetween 5 and 15 minutes to complete depending on the disk size of the new (andold) primary instance. During this backup period, PITR isn't available.

When the old primary instance comes back online, the replica failover processtakes a backup. After this backup is taken, the old primary instance isrecreated as a read replica of the new primary instance.

For more information about the considerations of using PITR with advanced DR,seeUse PITR with advanced DR.

After you invoke the replica failoveroperation, theDNS write endpoint,which previously resolved to the old primary instance,resolves to the new primary instance.

Before you begin

Before you can perform a replica failover, do the following:

• If you haven't done so already, thencreate a DR replica.
• Make sure the DR replica is online and healthy.

Perform the replica failover operation

gcloudsqlinstancespromote-replica\REPLICA_NAME--failover

POST https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d "" \
     "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2020-01-21T22:43:37.981Z",  "operationType": "PROMOTE_REPLICA",  "name": "OPERATION_ID",  "targetId": "REPLICA_NAME",  "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",  "targetProject": "PROJECT_ID"}

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d "" \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2024-04-01T22:43:37.981Z",  "operationType": "PROMOTE_REPLICA",  "name": "OPERATION_ID",  "targetId": "REPLICA_NAME",  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID",  "targetProject": "PROJECT_ID"}

Check the status of a replica failover

Replica failover occurs in two phases.The first phase is the promotion of the DR replica.The second phase is the recreation ofthe old primary instance as a read replica.

To check the status of replica failover, check the status of eachphase.

1. Check the status of the first phase.

   Console

   To check if the DR replica has been promoted to a standalone instance, do the following:

   1. In the Google Cloud console, go to theCloud SQL Instances page.

      Go to Cloud SQL Instances

   2. Find the name of the DR replica that you promoted.
   3. Verify thatSQL ServerVERSION appears in theType column for the new primary instance.

   gcloud

   You can check the status by running the following command:

   gcloudsqlinstancesdescribeDR_REPLICA_NAME

   Replace the following variable:

   • DR_REPLICA_NAME: the name of the promoted DR replica

   In the output, check that the following field appears and thereplica has become a standalone Cloud SQL primary instance:

   instanceType: CLOUD_SQL_INSTANCE

2. To verify the completion of the second phase, check theoperations log on the instance for the messageRECONFIGURE_OLD_PRIMARY.

   The appearance of this message depends on when the oldprimary instance returns online, which can take minutes ordays in the event of a disaster.

   For more information on how to check the operations logs on an instance, seeView instance logs.

Use PITR with advanced DR

Whether a switchover or replica failover, if the DR replica is promoted to aprimary instance, and you want to use PITR on the promoted instance, you mustmanually enable PITR.

After PITR is enabled, the backup configuration and transaction log retentionpolicies apply. If you don't specify values for these settings, the default valueof 14 days applies.

For more information, seeUse PITR.

After PITR is enabled on the new primary instance, you can restore the instanceto any point in time during which it is an active primary instance.

Split-brain during replica failover

It's possible that split-brain occurs when the primary instance continues toaccept writes while a replica is promoted using replica failover. After thereplica is promoted, when the old primary instance is available again, it isrebuilt as a replica of the promoted instance and a final backup is made. Thisbackup can be used to recover any split-brain data that was not written to thepromoted replica.

If you need to delete backups sooner, then you can remove backups manually. Formore information, seeDelete a backup.

• Keep KMS key project in same perimeter as instance: as a best practice, include the project containing the KMS key in the same VPC Service Controls perimeter as your Cloud SQL instances.
• Use a perimeter bridge: as a secondary (less recommended) option, you can use aperimeter bridge to connect projects in different perimeters.
• Test: use VPC Service Controls dry run mode to test your DR procedures (like switchover) and identify potential VPC Service Controls violations without enforcement.

For more information, seeConfigureVPC Service Controls.

Limitations

• You can't designate a Cloud SQL Enterprise Plus edition read replicainstance as DR replica if the primary instance stores its transaction logsfor point-in-time recovery (PITR) on disk. To check where an instancestores its logs for PITR, seeCheck the storage location of transaction logs used for PITR.

• You can't designate an external replica as a DR replica.

• Terraform isn't supported for replica failover operations.

• You can't use the Google Cloud console to perform replica failover or switchover operations.

Troubleshoot

What's next

• View all theGoogle Cloud services available in locations worldwide.
• Read aboutdatabase observability
• Monitor Cloud SQL instances