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

Important: Before using this feature,review thelimitations.

Introduction

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

Note: Although this page is primarily concerned with specific read replicascenarios, you can set up replication from external databases (external toCloud SQL) to Cloud SQL. For example, seeMigrating data from SQL Server 2017 to Cloud SQL for SQL Server using snapshot replication.

Other reasons for replication include migrating data between regions.

Additionally, if an original instance is corrupted, a replica could be promotedto a standalone instance (in which case, existing replicas wouldn't considerthat instance as primary).

Note: This page applies to replicas of a Cloud SQL instance.To set up a Cloud SQL instance to act as a publisher to an externalsubscriber, seeConfigure external replicas.

When referring to a Cloud SQL instance, the instance that is replicated iscalled theprimary instance and the copies are calledread replicas. The primary instance and read replicas all reside inCloud SQL.

Note: SQL Server replication is implemented usingSQL Server Read Scale Availability Groups.

When the first replica is created:

• The primary instance is set to the fullrecovery model for all databases on the primary instance.

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

  Note: Normal storage billing rates apply to the temporary disk.

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

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

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

Cloud SQL supports the following types of replicas:

• Read replicas
• Cross-region read replicas
• Cascading read replicas

By usingconnector enforcement, you can enforce using only the Cloud SQL Auth Proxy or Cloud SQL Language Connectors to connect to Cloud SQL instances. With connector enforcement, Cloud SQL rejects direct connections to the database. You can't create read replicas for an instance that has connector enforcement enabled. Similarly, if an instance has read replicas, then you can't enable connector enforcement for the instance.

This limitation doesn't apply to the instances that haveprivate service access configured.

Cloud SQL doesn't support replication between two external servers.

Read replicas

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

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

Note: Active long-running transactions might affect the creation of read replicas.We recommend creating a read replica under the following conditions:

• When the instance is under low traffic demands.
• After long-running transactions complete.

Youconnect to a replicadirectly using its connection name and IP address. If you're connecting to areplica using a private IP address, you don't need to create an additional VPCprivate connection for the replica because the connection is inherited from theprimary 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.

Note: Read replicas don't provide failover capability. To provide failovercapability for an instance, seeConfiguring an instance for highavailability.

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

Selecting an appropriate machine type

Read replicas can have a different number of vCPUs and memory from that of theprimary. You shouldmonitor metrics on yourinstance such as CPU and memory usageto ensure that the replica instance is sized correctly for its workload,especially if it is smaller than the primary instance. A replica instance thatis undersized is more prone to poor performance, such as frequent out-of-memory(OOM) events.

Storage capacity on read replicas

When a primary instance is resized, all of its read replicas are resized, ifneeded, so that they have at least as much storage capacity as the updatedprimary instance.

Cross-region read replicas

Cross-region replication lets you create a read replica in a different regionfrom the primary instance. You create a cross-region read replica the same wayas youcreate anin-region replica.

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

Cross-region replicas:

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

SeePromotingreplicas for regional migration or disaster recovery for more informationabout cross-region replicas.

Cascading read replicas

Note: This feature is available with Cloud SQL Enterprise Plus editiononly. For more information about Cloud SQL editions, seeIntroduction to Cloud SQL editions.

Cascading replication lets you create a read replica under another read replica in the sameor a different region. Cascading replicas are implemented usingdistributed availability groups.Some use cases for cascadingreplicas are as follows:

• Disaster recovery: you can use a cascading hierarchy of read replicas tosimulate the topology of your primary instance and its read replicas.During an outage, your selected read replica is promoted to primary and the readreplicas under the new primary continue to replicate and are ready for use.The old primary becomes a secondary of the new primary when it becomes available,and you can switch back to the old primary once it recovers using a switchover.For more information about using cascading replicas for disaster recovery, seeAbout disaster recovery.
• Performance improvement: Reduce the burden on the primary instance by offloading replication work to multiple read replicas.
• Scale reads: You can have more replicas to share the read load.
• Cost reduction: You can reduce networking costs by using a single cascading replica with cross-region replication in other regions.

Terminology

• Cascadable replica: A cross-region read replica that can be used for switchover and replica failover operations inadvanced disaster recovery (DR) with Cloud SQL for SQL Server.
• Cascading replica: A read replica that can have its own replica.
• Levels: You can create levels of replicas in a cascading replica hierarchy. For example, if you add four replicas to an instance, those four replicas are at the same level.
• Sibling instances: Multiple replicas that replicate from the same primary instance. Siblings are at the same level in the replica hierarchy. A replica can officially have up to eight siblings.
• Leaf replica: A read replica that does not have any replicas of its own. In a multi-level replication hierarchy, the leaf replica is the last level.
• Promote An action that converts a replica, at any level in the hierarchy, into a primary instance. When promoted, the replica's cascading replica hierarchy is retained.

Configure cascading replicas

Cascading replicas let you add read replicas to any existing replicas. You can add up to four levels of replicas, including the primary instance. Whenyou promote the replica at the top of a cascading replica hierarchy, it becomes a primary instance and its cascading replicas continue to replicate.

For more information on configuring distributed availability groups,seeConfigure an Always On distributed availability group.

Restrictions

• You can't delete a replica that has replicas under it. To delete the replica, youmust start with the leaf replicas and work your way upward through thehierarchy.
• Circular region dependency isn't supported. To have the replica of acascading replica in the same region as the primary instance,the cascading replica must also be in the same region.

• You must create cascadable replicas in a differentregion than the region of the primary instance. Then you can createcascading replicas in the same region as the cascadable replica.

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	Creating read replicas Managing read replicas Replication metrics
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	Creating read replicas Managing read replicas Replication metrics
SQL Server replication	Instance external to Cloud SQL	Cloud SQL for SQL Server instance	Migration path to Cloud SQL Replication from an external server to Cloud SQL Replication from Cloud SQL to an external server Replication between Cloud SQL instances Data replication to Google Cloud Analytics target	Migrating data from SQL Server 2017 to Cloud SQL for SQL Server using snapshot replication

Billing

• A read replica is charged at the same rate as a standard Cloud SQLinstance. There is no charge for the data replication.
• Pricing for a cross-region read replica is the same as for creating a newCloud SQL instance in the region. Refer toCloud SQL instance pricing and select theappropriate region. In addition to the regular cost associated with theinstance, a cross-region replica incurs cross-region data transfer chargesfor replication logs sent from the primary instance to the replica instance,as described inNetwork Egress Pricing.

Quick reference for Cloud SQL read replicas

Topic	Discussion
Backups	You cannot configure backups on the replica.
Cores and memory	Read replicas can use a different number of cores and amount of memory from those of the primary instance.
Deleting the primary instance	Before you can delete a primary instance, you must promote all of its read replicas to standalone instances or delete the read replicas.
Deleting the replica	When you delete a replica, there is no impact on the status of the primary instance.
Deleting a replicated database	You can delete a replicated SQL Server database using the Google Cloud console or thegcloud command, and the deletion automatically propagates to the replicas. You cannot delete a replicated SQL Server database using T-SQL commands.
Failover	A primary instance can failover to a replica only if the replica is a DR replica. Read replicas are unable to failover in any way during an outage.
Load balancing	Cloud SQL does not provide load balancing between replicas.
Maintenance windows	Read replicas share maintenance windows with the primary instance. The replicas follow the maintenance settings for the primary instance, including the maintenance window, rescheduling, and the deny maintenance period. During maintenance, Cloud SQL updates all read replicas first before updating the primary instance.
Multiple read replicas	You can create up to 8 read replicas for a single 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.
Restoring the primary instance	You cannot restore the primary instance of a replica while the replica exists. Before restoring an instance from a backup, or performing a point-in-time recovery on it, you must promote or delete all of its 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.
Stopping a replica	You cannotstop a replica. You canrestart ordelete it, but you cannot stop it as you can a primary instance.
Upgrading a replica	Read replicas can experience a disruptive upgrade at any time.
User tables	You cannot make changes on the replica. All user changes must be done on the primary instance.

Limitations

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

  • SQL Server 2017 Enterprise
  • SQL Server 2019 Enterprise
  • SQL Server 2022 Enterprise

• Cloud SQL doesn't supportadvanced disaster recovery (DR)replicas in the same region. If you want to create a read replica in the sameregion as the source, then you can only promote the read replica to a newprimary instance.

• Cascading replicas are available with Cloud SQL Enterprise Plus edition only.

• You can't create a cascading replica from the Google Cloud console.

• 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 insingle-user mode. Otherwise, replica creation fails.

• To create a replica, the primary instance must meet the followingrequirements:

  • Must not contain databases in single-user mode.
  • Must not contain databases in anOFFLINE state.
  • Must not contain databases in read-only mode.
  • Must not contain databases with auto-close enabled.
  • Must not be used as adistribution database.
  • Must not have push transactional replication enabled.
  • Must not be adatabase snapshot.

  If the primary instance doesn't meet the necessary requirements, then replicacreation fails and the following error message is returned:

  Instance is not eligible for replica creation...

• Replication can't be configured on an instance configured withexternal replication.

What's next

• Learn how tocreate a read replica.
• Learn how toconfigure an instance for high availability.
• Learn aboutAdvanced disaster recovery (DR)