Surviving a Zone Outage With SQL Server on GCP

PCA By islever Editorial Last updated

Designing network, storage, and compute resources. Configuring compute systems. Ensuring the reliability of solutions in production (e.g., chaos engineering, penetration testing, and load testing)

Short answer

D Deploy SQL Server Always On Availability Groups on Windows Failover Clustering with nodes placed in different zones to survive any zone outage in the region.

Question

You need to set up Microsoft SQL Server on GCP. Management requires that there's no downtime in case of a data center outage in any of the zones within a
GCP region. What should you do?
  1. Configure a Cloud SQL instance with high availability enabled.
  2. Configure a Cloud Spanner instance with a regional instance configuration.
  3. Set up SQL Server on Compute Engine, using Always On Availability Groups using Windows Failover Clustering. Place nodes in different subnets.
  4. Set up SQL Server Always On Availability Groups using Windows Failover Clustering. Place nodes in different zones. Correct Answer

Key Takeaways

It tests whether you can match a stated failure domain (a zone data-center outage) to a topology option, while recognising that active-passive failover reduces but does not remove downtime.

This PCA question tests zero-downtime SQL Server availability across GCP zones, where Always On Availability Groups with nodes in different zones (D) is the correct design over Cloud SQL HA (A).

Common mistake: Option A is chosen because Cloud SQL HA places a standby in another zone, but its active-passive switchover interrupts writes during failover, so it only reduces downtime instead of eliminating it.

Expert Analysis

The trap: Cloud SQL high availability (A)

Option A is the seductive one. Cloud SQL's regional HA configuration keeps a standby in a second zone of the same region and fails over automatically when the primary zone goes dark, so it appears to answer "a data center outage in any of the zones." The community discussion calls it "the simplest and most cost-effective option." The single point where it breaks is the failure model: Cloud SQL HA is active-passive. The standby never serves traffic, and the switchover stops writes for the duration of the failover — the discussion itself concedes that "this configuration reduces downtime" rather than removing it. Management demanded no downtime, so A satisfies zone placement but fails the zero-interruption condition.

Why B and C are out

Option B returns the wrong engine entirely: Cloud Spanner is Google's horizontally scalable SQL database, not Microsoft SQL Server, so it discards the stated requirement. Option C specifies multi-subnet placement. In GCP a subnet is a regional resource spanning every zone, so two nodes in different subnets can still sit in the same zone and be hit by one data-center failure; Google's single-zone WSFC guidance even states that "all WSFC nodes are deployed in the same zone and use a common subnet." Subnet separation alone guarantees nothing.

Why D is correct

Option D pairs the correct engine with the correct failure domain: Always On Availability Groups on Windows Failover Clustering with replicas placed in different zones, behind a multi-subnet listener so clients reconnect to the surviving replica. Google's multi-subnet SQL Server deployment does exactly this, keeping SQL Server serving when any one zone in the region fails.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

Read the failure domain first and reject any option that names a different resource (Cloud Spanner) or a weaker one (subnets, which are regional in GCP and do not imply zone separation). Then judge each surviving option's failover model: active-passive managed services with a switchover pause cannot satisfy a literal "no downtime" requirement, so the cross-zone cluster topology wins. Apply the same filter to any HA wording question.

Frequently Asked Questions

Does Cloud SQL SQL Server high availability protect against a zone outage?

Yes, Cloud SQL HA keeps a standby in another zone of the same region and fails over automatically when the primary zone fails. However, the active-passive switchover pauses writes for the duration of the failover, so it reduces downtime rather than eliminating it.

Why can't Cloud Spanner be used for this SQL Server requirement?

Cloud Spanner is Google's own horizontally scalable SQL database and is not Microsoft SQL Server. It fails the explicit "set up Microsoft SQL Server" requirement even though a regional Spanner instance is highly available.

Why do the cluster nodes need different zones instead of just different subnets?

In GCP, subnets are regional resources that can span every zone, so nodes in separate subnets may still share one zone and fail together. Placing replicas in different zones is what guarantees survival of a single data-center failure.

More PCA FAQ →

Community Votes

D
53%
A
45%
B
1%
C
1%

53% of anonymous learners picked answer D. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 269 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 30 of 269

  1. learningpv 👍 66
    A seems correct. "... high availability (HA) configuration for Cloud SQL instances... A Cloud SQL instance configured for HA is also called a regional instance and is located in a primary and secondary zone within the configured region. In the event of an instance or zone failure, this configuration reduces downtime, and your data continues to be available to client applications."
  2. SMS 👍 32
    Answer is A. Cloud SQL supports SQL Server and selecting high availability provides automatic failover within a region.
  3. diluviouniv 👍 12
    but it says: you need to setup SQL Server
  4. cetanx 👍 11
    It is available, please see; https://cloud.google.com/sql/docs/sqlserver/high-availability?_ga=2.30855355.-503483612.1582800507 Also a video from Google; https://youtu.be/vMUpNoukwnM
  5. tartar 👍 10
    D is ok. a couple of good paragraphs in the upper part of the page explains well. https://cloud.google.com/solutions/disaster-recovery-for-microsoft-sql-server
Show 25 more comments
  1. tycho 👍 10
    and what is Cloud SQL -> a managed service for MySQL, Posrgers, and MS SQL server
  2. mrealtor 👍 9
    You need to set up a Microsoft SQL server. Why are we talking about Cloud SQL
  3. taer 👍 8 Answered D
    Cloud SQL's high availability configuration relies on a primary instance and a standby instance that resides in a different zone within the same region. In the event of an outage, Cloud SQL will automatically failover to the standby instance, but the failover process takes some time, usually around 1-2 minutes, which might result in a brief downtime. On the other hand, using SQL Server Always On Availability Groups with Windows Failover Clustering allows you to have multiple replicas of your data distributed across different zones. The failover process between these replicas is faster and more seamless, reducing the risk of downtime even further.
  4. asure 👍 8
    Answer as of 10-May-2020 is changes to A, Microsoft SQL server is now offered as a managed service from GCP, much easier to create a highly available instance. https://cloud.google.com/sql-server
  5. Dannyygcp 👍 7
    Option is A gcloud sql instances create [REGIONAL_INSTANCE_NAME] \ --availability-type=REGIONAL \ --database-version=SQLSERVER_2017_STANDARD \ --cpu=[NUMBER_CPUS] --memory=[MEMORY_SIZE]
  6. DevOpsifier 👍 6
    you said it yourself,"this configuration reduces downtime", it doesn't remove it completely as required, so the correct answer is D. See @taer answer: "...Cloud SQL's high availability configuration relies on a primary instance and a standby instance that resides in a different zone within the same region. In the event of an outage, Cloud SQL will automatically failover to the standby instance, but the failover process takes some time, usually around 1-2 minutes, which might result in a brief downtime. On the other hand, using SQL Server Always On Availability Groups with Windows Failover Clustering allows you to have multiple replicas of your data distributed across different zones. The failover process between these replicas is faster and more seamless, reducing the risk of downtime even further..."
  7. gfhbox0083 👍 6
    A, for sure. Google Cloud SQL is a fully-managed database service that makes it easy to set-up, maintain, manage and administer your relational MySQL and PostgreSQL databases in the cloud. A Cloud SQL instance configured for HA is also called a regional instance and is located in a primary and secondary zone within the configured region. Within a regional instance, the configuration is made up of a primary instance (master) and a standby instance. Through synchronous replication to each zone's persistent disk, all writes made to the primary instance are also made to the standby instance. In the event of an instance or zone failure, this configuration reduces downtime, and your data continues to be available to client applications.
  8. Ric350 👍 6
    Actually, although Cloud SQL offers high availability configurations, it currently doesn't support Microsoft SQL Server as one of its managed database engines. It primarily focuses on MySQL, PostgreSQL, and SQL Server (but not the full Microsoft SQL Server). And the question clearly states "you need to set up Microsoft SQL." Very tricky question. The answer is D
  9. Il_Tera 👍 6 Answered A
    Now MSSQL is provided as managed service by GCP so setting HA automatically configure everything for you and use different zones to ensure HA
  10. zaki_b 👍 6
    I believe D captures the concept more correctly since it specifically mentions different zone
  11. jlambdan 👍 5 Answered A
    A it's the doc https://cloud.google.com/sql/docs/mysql/high-availability see also https://cloud.google.com/compute/docs/instances/sql-server/disaster-recovery-for-microsoft-sql-server#high_availability_and_disaster_recovery B spanner is not sql server C is not possible, the doc says all nodes are in one subnets. "all WSFC nodes are deployed in the same zone and use a common subnet" => https://cloud.google.com/compute/docs/instances/sql-server/configure-failover-cluster-instance-pd-multi-writer D is not possible. Nodes needs to share the same persistent disk but it is a zonal resources. So you can't put them in different zones. "Because persistent disks in multi-writer mode are a zonal resource, all WSFC nodes are deployed in the same zon" => https://cloud.google.com/compute/docs/instances/sql-server/configure-failover-cluster-instance-pd-multi-writer
  12. hasib125 👍 5
    As the requirement is Microsoft SQL Server on GCP and management requires - no downtime a data center outage in any of the zones within a GCP region : the solution must use 1. windows based clustering - which is "Always On Availability Groups using Windows Failover Clustering". 2. For no downtime in any of the zones within a GCP region - the clustering nodes should be on different zones. So the answer is ""D"".
  13. YuriP 👍 5
    I think answer is D. Depsite A working for MS SQL now, the downtime is several minutes before sessions get re-established. Option C puts nodes in different subnets (not Zones) therefore doesn't meet the requirement. Therefore it's D. When posting comments, avoid confirmation bias. Think through, instead of finding facts to back your hypothesis.
  14. OrangeTiger 👍 5
    In official recommend A.It released in 2021. https://events.withgoogle.com/solution-design-pattern-infra-rdb-network/high-availability/ But cleally said 'You need to set up Microsoft SQL Server on GCP'. It's a really confusing issue. The harder you study, the more wrong you will be.
  15. RitwickKumar 👍 5 Answered A
    As per question MS SQL Server "needs" to be setup and doesn't exist yet on GCP. Creating SQL Server in HA mode is as simple as selecting a multi zonal instance in Cloud SQL. Source: https://cloud.google.com/sql/docs/sqlserver/configure-ha#console
  16. jaxclain 👍 5 Answered A
    I believe most of those who choose the option D were submitted 1 or 2 years ago because Cloud SQL allows you to setup MS SQL, why would you do extra work when Cloud SQL allows you to do the required on this question?
  17. kalschi 👍 5
    should be "D", it says configure "Always On Failover cluster" , not "Always On availability group" https://cloud.google.com/compute/docs/instances/sql-server/configure-failover-cluster-instance
  18. minmin2020 👍 5 Answered D
    D - The question is for Microsoft SQL Server and not cloud sql, therefore Cloud SQL and Spanner are not relevant. The server must be in different zones within GCP region and not subnets.
  19. JasonL_GCP 👍 5
    It looks like the HA wasn't supported SQL server before, so in the past, the correct answer is D, but now is A
  20. sunny031982 👍 5
    D is correct as question is about availability during zone failure so nodes should be on different zones..
  21. SerGCP 👍 5 Answered D
    A - can be ok if you can accept a minimal downtime. D - is ok becouse the managament required no downtime
  22. learningpv 👍 5
    It applies for MySQL and HA is not available for MS SQL
  23. daurib 👍 5
    Selected D in the exam because of zone
  24. siumk 👍 4
    By referring to the following URLs, creating nodes in different/same zones in different subnets are both possible. Different zones + different subnets + multiple subnets: https://cloud.google.com/solutions/deploy-multi-subnet-sql-server Same zones + different subnets + multiple subnets: https://cloud.google.com/compute/docs/instances/sql-server/configure-availability In the second link, it says "You can use an existing network for this task if necessary, but it is a best practice to isolate systems into different networks and subnets". My interpretation is that the purpose of putting systems in different subnets is isolation but not for High availability. So, I guess answer D is addressing the concern of "no downtime in case of a data center outage in any of the zones within a GCP region"
  25. omermahgoub 👍 4
    The correct answer is option D: Set up SQL Server Always On Availability Groups using Windows Failover Clustering. Place nodes in different zones. To ensure high availability and protect against data center outages, you can set up SQL Server Always On Availability Groups using Windows Failover Clustering. This allows you to replicate the data to multiple servers and automatically failover to a secondary server in case of an outage or failure. To further protect against outages within a region, you should place the nodes in different zones within the region. This will ensure that the secondary server is available in a different zone in case of an outage in one of the zones.

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Multi-Regional vs Regional Cloud Storage for Global Software Downloads

S-grade

Fixing BigQuery connection errors from a Compute Engine VM

S-grade

Moving 75 TB into Cloud Storage with Google-recommended practices

S-grade

Optimizing Cost and Persistence for Interruptible Batch AI Workloads on Google Cloud

S-grade

Practice All PCA Questions

Access 81 questions with complete answers and detailed explanations.

View Full PCA Practice Test →

← Back to PCA Study Guide