GCP Cloud SQL

• Cloud SQL provides a cloud-based alternative to local MySQL, PostgreSQL, and Microsoft SQL Server databases
• Cloud SQL is a managed solution that helps handles backups, replication, high availability and failover, data encryption, monitoring, and logging.
• Cloud SQL is ideal for lift and shift migration from existing on-premises relational databases

Cloud SQL High Availability

• Cloud SQL instance HA configuration provides data redundancy and failover capability with minimal downtime, when a zone or instance becomes unavailable due to a zonal outage, or an instance corruption
• HA configuration is also called a regional instance or cluster
• With HA, the data continues to be available to client applications.
• HA is made up of a primary and a standby instance and is located in a primary and secondary zone within the configured region
• If an HA-configured instance becomes unresponsive, Cloud SQL automatically switches to serving data from the standby instance.
• Data is synchronously replicated to each zone’s persistent disk, all writes made to the primary instance are replicated to disks in both zones before a transaction is reported as committed.
• In the event of an instance or zone failure, the persistent disk is attached to the standby instance, and it becomes the new primary instance.
• After a failover, the instance that received the failover continues to be the primary instance, even after the original instance comes back online.
• Once the zone or instance that experienced an outage becomes available again, the original primary instance is destroyed and recreated and It becomes the new standby instance.
• If a failover occurs in the future, the new primary will failover to the original instance in the original zone.
• Cloud SQL Standby instance does not increase scalability and cannot be used for read queries
• To see if failover has occurred, check the operation log’s failover history.

Cloud SQL Failover Process

• Each second, the primary instance writes to a system database as a heartbeat signal.
• Primary instance or zone fails.
• If multiple heartbeats aren’t detected, failover is initiated. This occurs if the primary instance is unresponsive for approximately 60 seconds or the zone containing the primary instance experiences an outage.
• Standby instance now serves data upon reconnection.
• Through a shared static IP address with the primary instance, the standby instance now serves data from the secondary zone.
• Users are then automatically rerouted to the new primary.

Cloud SQL Read Replica

• Read replicas help scale horizontally the use of data in a database without degrading performance
• 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 replica can be promoted if the original instance becomes corrupted.
• Primary instance and read replicas all reside in Cloud SQL
• Read replicas are read-only; you cannot write to them
• Read replicas do not provide failover capability
• Read replicas cannot be made highly available like primary instances.
• Cloud SQL currently supports 10 read replicas per primary instance
• During a zonal outage, traffic to read replicas in that zone stops.
• Once the zone becomes available again, any read replicas in the zone will resume replication from the primary instance.
• If read replicas are in a zone that is not in an outage, they are connected to the standby instance when it becomes the primary instance.
• GCP recommends putting read replicas in a different zone from the primary and standby instances. for e.g., if you have a primary instance in zone A and a standby instance in zone B, put the read replicas in zone C. This practice ensures that read replicas continue to operate even if the zone for the primary instance goes down.
• Client application needs to be configured to send reads to the primary instance when read replicas are unavailable.
• Cloud SQL supports Cross-region replication that lets you create a read replica in a different region from the primary instance.
• Cloud SQL supports External read replicas that are external MySQL instances which replicate from a Cloud SQL primary instance

Cloud SQL Point In Time Recovery

• Point-in-time recovery (PITR) uses binary logs or write-ahead logs
• PITR requires
  • Binary logging and backups enabled for the instance, with continuous binary logs since the last backup before the event you want to recover from
  • A binary log file name and the position of the event you want to recover from (that event and all events that came after it will not be reflected in the new instance)
• Point-in-time recovery is enabled by default when a new Cloud SQL instance is created

Cloud SQL Proxy

• Cloud SQL Proxy provides secure access to the instances without the need for Authorized networks or for configuring SSL.
  • Secure connections : Cloud SQL Proxy automatically encrypts traffic to and from the database using TLS 1.2 with a 128-bit AES cipher; SSL certificates are used to verify client and server identities.
  • Easier connection management : Cloud SQL Proxy handles authentication removing the need to provide static IP addresses.
• Cloud SQL Proxy does not provide a new connectivity path; it relies on existing IP connectivity. To connect to a Cloud SQL instance using private IP, the Cloud SQL Proxy must be on a resource with access to the same VPC network as the instance.
• Cloud SQL Proxy works by having a local client running in the local environment. The application communicates with the Cloud SQL Proxy with the standard database protocol used by the database.
• Cloud SQL Proxy uses a secure tunnel to communicate with its companion process running on the server.
• While the proxy can listen on any port, it only creates outgoing connections to the Cloud SQL instance on port 3307.

Cloud SQL Features Comparison

GCP Certification Exam Practice Questions

• Questions are collected from Internet and the answers are marked as per my knowledge and understanding (which might differ with yours).
• GCP services are updated everyday and both the answers and questions might be outdated soon, so research accordingly.
• GCP exam questions are not updated to keep up the pace with GCP updates, so even if the underlying feature has changed the question might not be updated
• Open to further feedback, discussion and correction.

1. You work for a mid-sized enterprise that needs to move its operational system transaction data from an on-premises database to GCP. The database is about 20 TB in size. Which database should you choose?
   1. Cloud SQL
   2. Cloud Bigtable
   3. Cloud Spanner
   4. Cloud Datastore
2. An application that relies on Cloud SQL to read infrequently changing data is predicted to grow dramatically. How can you increase capacity for more read-only clients?
   1. Configure high availability on the master node
   2. Establish an external replica in the customer’s data center
   3. Use backups so you can restore if there’s an outage
   4. Configure read replicas.
3. A Company is using Cloud SQL to host critical data. They want to enable high availability in case a complete zone goes down. How should you configure the same?
   1. Create a Read replica in the same region different zone
   2. Create a Read replica in the different region different zone
   3. Create a Failover replica in the same region different zone
   4. Create a Failover replica in the different region different zone
4. A Company is using Cloud SQL to host critical data. They want to enable Point In Time recovery (PIT) to be able to recover the instance to a specific point in. How should you configure the same?
   1. Create a Read replica for the instance
   2. Switch to Spanner 3 node cluster
   3. Create a Failover replica for the instance
   4. Enable Binary logging and backups for the instance

References

Google_Cloud_SQL