About replication in Cloud SQL

Replication is the ability to create copies of a Cloud SQL instance and offload work to the copies.

Introduction

The primary reason for using replication is to scale the use of data in a database without degrading performance.

Other reasons include migrating data between regions.

Additionally, if an original instance is corrupted, a replica could be promoted to a standalone instance (in which case, existing replicas would not consider that instance as primary).

When referring to a Cloud SQL instance, the instance that is replicated is called the primary instance and the copies are called read replicas. The primary instance and read replicas all reside in Cloud SQL.

When the first replica is created:

  • The primary instance is set to the full recovery model for all databases currently on the primary instance.

  • A temporary disk is created, and a full backup is taken and stored on the temporary disk. The temporary disk is deleted after the replica creation process is complete.

If, in the time period of the first replica creation, the user switches to the simple recovery model, the replica creation fails.

The following apply to databases added to the primary instance after the replicas are created:

  • The databases automatically are added to the availability groups and are populated in the replicas using auto-seeding.
  • Each replica creation invokes a full (full recovery model) backup of databases on the primary instance. Logins and server objects created after the replica aren't replicated.

Cloud SQL supports the following types of replicas:

Cloud SQL does not support replication between two external servers.

Read replicas

You use a read replica to offload work from a Cloud SQL instance. The read replica is an exact copy of the primary instance. Data and other changes on the primary instance are updated in almost real time on the read replica.

Read replicas are read-only; you cannot write to them. The read replica processes queries, read requests, and analytics traffic, thus reducing the load on the primary instance. You can have up to 8 read replicas per primary instance.

You connect to a replica directly using its connection name and IP address. If you're connecting to a replica using a private IP address, you don't need to create an additional VPC private connection for the replica as it is inherited from the primary instance.

For information about how to create a read replica, see Creating read replicas. For information about managing a read replica, see Managing read replicas.

As a best practice, put read replicas in a different zone than the primary instance when you use HA on your primary instance. This practice ensures that read replicas continue to operate when the zone that contains the primary instance has an outage. See the Overview of high availability for more information.

Cross-region read replicas

Cross-region replication lets you create a read replica in a different region from the primary instance. You create a cross-region read replica the same way as you create an in-region replica.

In the case of SQL Server read replicas, it is assumed that the replica is in the same virtual network as the primary replica, or that they communicate via Public IP.

Cross-region replicas:

  • Improve read performance by making replicas available closer to your application's region.
  • Provide additional disaster recovery capability to guard against a regional failure.
  • Let you migrate data from one region to another.

See Promoting replicas for regional migration or disaster recovery for more information about cross-region replicas.

Replication use cases

The following use cases apply for each type of replication.

Name Primary Replica Benefits and use cases More information
Read replica Cloud SQL instance Cloud SQL instance
  • Additional read capacity
  • Analytics target
  • Improve read performance
  • Export from replicas
Cross-region read replica Cloud SQL instance Cloud SQL instance
  • Additional read capacity
  • Analytics target
  • Additional disaster recovery capability
  • Improve read performance
  • Migrate data between regions

Billing

  • A read replica is charged at the same rate as a standard Cloud SQL instance. There is no charge for the data replication.
  • Because a replica always maintains a connection to its primary, the primary instance is never deactivated. This scenario could result in a billing increase for the primary instance. Learn more.
  • In addition to the regular cost associated with any Cloud SQL instances, a cross-region replica incurs cross-region network egress charges for replication logs sent from the primary to the replica, as described in Network Egress Pricing.
  • Pricing for a cross-region read replica is the same as for creating a new instance in the region. Refer to Cloud SQL instance pricing and select the appropriate region.

Quick reference for Cloud SQL read replicas

Topic Discussion
High availability Read replicas neither provide high availability nor offer it.
Failover A primary instance cannot failover to a read replica, and read replicas are unable to failover in any way during an outage.
Maintenance windows Maintenance windows cannot be set on read replicas and they do not share maintenance windows with the primary instance. Maintenance can occur at any time on the read replica. Maintenance occurs on read replicas at a different time than on the primary instance.
Disruptive upgrades Read replicas can experience a disruptive upgrade at any time.
Multiple read replicas You can create up to 8 read replicas for a single primary instance.
Load balancing Cloud SQL does not provide load balancing between replicas.
Settings The settings of the primary instance are propagated to the replica, including changes to the data about users who can access the instance.
Cores and memory Read replicas can use a different number of cores and amount of memory from those of the primary instance.
User tables You cannot make changes on the replica. All user changes must be done on the primary instance.
Backups You cannot configure backups on the replica.
Restoring the primary instance You cannot restore the primary of a replica while the replica exists. Before restoring an instance from a backup, you must promote or delete all of its replicas.
Deleting the primary instance Before you can delete a primary instance, you must promote all of its read replicas to stand-alone instances or delete the read replicas.
Deleting a replicated database You can delete a replicated SQL Server database using the Google Cloud Console or the gcloud command, and the deletion automatically propagates to the replicas. You cannot delete a replicated SQL Server database using T-SQL commands.
Creating a replica of a replica You cannot create a replica of a replica.
Stopping a replica You cannot stop a replica. You can restart or delete it, but you cannot stop it as you can a primary instance.
Private IP If you're connecting to a replica using a private IP address, you don't need to create an additional VPC private connection for the replica, as it is inherited from the primary instance.

Limitations

  • This feature only applies to the following versions of Cloud SQL for SQL Server:

    • SQL Server 2017 Enterprise
    • SQL Server 2019 Enterprise

  • Logins aren't propagated to a replica.

  • You must monitor your replica using T-SQL and/or SQL Server Management Studio.

  • Before deleting a database, you must close your database connections.

  • When you create a replica, the primary instance can contain no databases in single-user mode. Otherwise, replica creation fails.

What's next