Cloud SQL regional migration
Nancy Goyal
Strategic Cloud Engineer, Google
Are you expanding your business to a new region using Cloud SQL? Do you need to move your applications that are running on Cloud SQL to a region that has specific data compliance or geographic locality requirements? Maybe you have experienced a regional outage, and you need to move your Cloud SQL instance into another region. Or perhaps you are an enterprise customer looking to migrate to the newly announced Cloud SQL Enterprise Plus edition for the 99.99% availability, near-zero downtime planned maintenance and 3x improved performance. In all these cases, you would first move your data first to a new region and operationalize applications to quickly start serving customers. This article will focus on reducing migration complexity, application downtime, and simplifying the migration process for Cloud SQL.
This blog post entails steps for planning a regional migration with Cloud SQL:
Assess migration requirements
Migration approach
Application enablement
Assess migration requirements
Whenever a migration plan is considered, you will need to define application-specific integration requirements. These applications interact with backend databases and help define regional migration decisions, for example:
What is the size of the database?
The size of the database determines how long it takes to migrate it. You can control the time to a degree by choosing between an online approach (e.g., replicas) or an offline approach (e.g., export/import or backups) approach. Online approaches are ideal for applications that cannot afford an extended downtime period, whereas offline approaches are simpler to plan and execute as long as you can afford the downtime.
Is there downtime tolerance?
Applications having Change Request schedules in the production environment make it easy to decide migration complexity. In cases, where downtime is acceptable for change deployment, consider a simple one-time migration approach rather than continuous migration.
How is the Database connectivity configured in the application?
Cloud SQL regional migration may require updates to the database connection configuration within your applications. If your applications are already using Cloud NAT gateway, Cloud DNS, or a Load balancer etc. for connecting to Cloud SQL, then updating their configuration will be easy without impacting applications itself.
What is the hourly size of updates to your database?
Regional migration approach decisions are highly impacted by frequency of changes or transactions hitting your database every hour. Choosing a migration window with the lowest transaction frequency can help you avoid the risk of losing transactional data, high number of user connection failures, application timeouts, high error rates, etc. This makes the transaction frequency during the hour of migration a critical factor to consider. Identifying the time when your instance handles the lowest amount of traffic to the database can help you plan your migration more easily.
Do you have client side database encryption?
Cloud SQL offers capability to encrypt data that can be decrypted by applications. Encryption keys used for data encryption and decryption are stored in Cloud key management service (KMS). The usage of regional or global KMS keys for data encryption impacts the migration decision for Cloud SQL. In case of a single region KMS key, data might need to be re-encrypted depending on the migration approach used. For more information, refer Cloud SQL interaction with encryption keys.
Answering above questions helps you plan the best migration path possible and to migrate Client applications to the new instance of Cloud SQL. This ensures that the client applications do not experience a split-brain state.
Region migration approach
For cross-region migration of Cloud SQL databases, there are various approaches available. We will discuss most popular approaches in the following section:
Cross-region read replicas for region migration:
A read-only replica contains an entire copy of the database and can serve reads without requiring a round trip back to the leader region. In doing so, read-only replicas deliver low latency stale reads to nearby clients and help increase a node’s overall read scalability to clients everywhere. Cross-region replication makes it easy to create a fully managed read replica in a different region than that of the primary instance.
The cross-region read replica is set up to asynchronously replicate the primary instance. This means that the read replica can lag behind the primary instance. If the read replica is not in sync with the primary instance during migration, then new data in the primary instance will be lost. To ensure replica is not lagging behind primary instance Monitoring metrics must be defined and tracked on the replica instance during migration hour.
When the whole region needs to be migrated due to region expansion or business requirement, this cross-region read replica can be promoted to become a standalone instance. During replica promotion scenarios, the Cloud SQL instance IP address may stay the same or change.
With zonal read replicas, the Cloud SQL replica instance IP address will be the same as the primary instance.
However for regional read replicas, the Cloud SQL replica instance IP address will be different from the primary instance.
So, when you promote a Cloud SQL regional read replica, its IP address becomes the IP Address of the newly promoted primary instance. Since applications are pointing to IP Address of Cloud SQL before promotion (which was primary instance IP), a config update will be required to point to the IP address of newly promoted Cloud SQL instance (which was regional read replica IP).
The complexity of repointing applications to the new Cloud SQL instances depends on how your application configuration is defined. Refer Cloud SQL lab to understand how to use Cloud DNS for config update.
Data migration service for new regional Cloud SQL instance:
Database Migration Service (DMS) can be used to migrate databases from an existing CloudSQL instance to a new one with the same or different Cloud SQL edition in a different region, and then perform an ongoing replication from the source database into the target. DMS supports CDC-style migrations for all sources (both homogenous and heterogenous migrations) which continually move changes from the source to the destination. See the PostgreSQL DMS known limitations.
Backup restore for new regional Cloud SQL instance:
Automated or on-demand backups stored on multi-regional buckets can be used to migrate Cloud SQL to another region. Setting up Cloud SQL instances from backup consumes some time as it involves creating a new instance and then restoring from the backup.
Export and import database in new region:
Database exports can also be used for database migration to a different region. Critical considerations while choosing export/import databases are mostly around efforts, time, and data loss tolerance levels for an organization. For regional migration, one time export and import can be used in multi-regional Cloud storage buckets which can be accessed in the target region.
To plan disaster recovery using export/import, the process should be automated to export databases regularly based on business requirements. For example, what are your SLA requirements? Are there any compliance requirements such as GDPR to be considered while exporting data? What should be the frequency of data export?
Below is a quick comparison between backup/restore and export/import:
Application enablement
Applications consuming data from Cloud SQL have a dedicated configuration for the database. The complexity of moving away from one configuration to another depends how the configuration is defined. Database connection endpoints are defined in the application runtime configuration.
It is recommended to use aliases for database endpoints instead of using database IP addresses in the connection strings. This allows for database migration without impacting applications, as there is no need to change the connection string. You will only need to update the alias details of the migrated Cloud SQL database.
Enterprise customers mostly use a private Cloud SQL instance unless there is any specific need for a public instance. For example, Cloud DNS can be used to resolve DNS records (FQDN) to the public or private IP address of Cloud SQL. The corresponding Cloud DNS name can be used in application configuration for pointing to the SQL host record. After migration, it will only require an update to the Cloud DNS record for the migrated Cloud SQL instance.
Learn more
Cloud SQL migration from one region to another region involves the analysis of database components impacted, application enablement and reconfiguration, network configuration, impact on user interfacing applications, migration of database monitoring, and many more activities. We discussed different approaches for Cloud SQL regional database migration using backups, read replicas, and offline exports. Using cross-regional read replicas is the most convenient approach among all, however the decision should be made based on business, cost and latency requirements. We hope that this blog has helped you better understand the differences and choose the configuration that is right for you.
To get started, create a Cloud SQL instance in your GCP account or in a SQL regional migration qwiklab to try the regional migration experience.
Want to learn more? Check out the resources below to dive in:
Cross-region replica for Cloud SQL
