Geo-replication and failover groups get lumped in with “general DR” a lot, but they deserve their own deep dive — the mechanics of how a secondary actually stays in sync, what a listener endpoint is really doing under the hood, and what genuinely happens during a forced failover are exactly the kind of specifics that separate someone who’s configured a failover group once from someone who actually understands it.
Split into Beginner (1–17), Intermediate (18–35), and Advanced (36–50), written the way you’d actually talk it through in a room — the reasoning behind each answer, grounded in the actual mechanism, not just the feature name.
One framing worth carrying through the whole list: geo-replication is asynchronous by nature once you cross regions, and almost every subtle, tricky question in this topic traces back to that one fact — something is always slightly behind, and the interesting questions are all about exactly how behind, and what that means when things go wrong.
Beginner Level
1. What is active geo-replication in Azure SQL Database? A feature that creates a readable secondary copy of your database in a different region (or the same region), continuously kept in near-sync with the primary — giving you both a disaster recovery option and a read-only replica you can offload reporting traffic to, at the same time.
2. What is a failover group, and how is it different from plain active geo-replication? A failover group is a construct built on top of active geo-replication that adds two things: an automatic (or manual) failover policy, and a stable, unchanging listener endpoint that automatically redirects to whichever database is currently primary — meaning the application’s connection string doesn’t need to change during a failover, which plain geo-replication alone doesn’t provide.
3. Can a database have more than one secondary via active geo-replication? Yes — a primary can have up to four readable secondaries, which can be in the same region or different regions, giving you flexibility for both broader DR coverage and distributing read-only workload across multiple replicas if needed.
4. What’s the difference between a “primary” and a “secondary” database in a geo-replication relationship? The primary accepts all read-write traffic and is the source of truth; secondaries are continuously updated copies that accept read-only queries but can’t be written to directly — the roles can switch (the secondary becomes primary) during a failover, but only one database in the relationship is ever primary at a time.
5. What does “readable secondary” mean, and how would an application actually query it? It means the secondary database can serve SELECT queries directly, not just sit idle as a DR copy — an application connects to it by specifying ApplicationIntent=ReadOnly in its connection string, which routes the connection to the secondary rather than the primary.
6. Is replication between primary and secondary synchronous or asynchronous? Asynchronous — the primary doesn’t wait for the secondary to confirm before considering a transaction committed, which is what allows geo-replication to work across regions without adding significant latency to every write on the primary, but it also means the secondary can lag slightly behind at any given moment.
7. What is replication lag, and why does it matter? The time delay between a change committing on the primary and that same change appearing on the secondary — it matters because it directly determines two things: how “stale” data on a readable secondary might be at any moment, and how much data you could potentially lose if you had to force a failover to that secondary during an outage.
8. What’s the difference between a planned and a forced failover? A planned failover performs a clean, fully synchronized switch — it waits for the secondary to catch up completely before switching roles, so there’s no data loss, appropriate for testing or a scheduled maintenance-driven switch. A forced failover is used when the primary is genuinely unavailable and you can’t wait for synchronization — it switches immediately, accepting the risk of losing any transactions that hadn’t yet replicated to the secondary.
9. What is the listener endpoint in a failover group, and why does it matter for applications? A single, stable DNS name the failover group provides, which always points to whichever database is currently primary (and a separate read-only listener endpoint that always points to a readable secondary) — meaning the application’s connection string, once pointed at the listener, doesn’t need to change during a failover, since the endpoint itself handles the redirection.
10. Can you manually control when a failover happens, or does it always happen automatically? You can choose — a failover group can be configured with an automatic failover policy (the platform detects a qualifying outage and initiates failover on its own) or a manual policy (a human explicitly triggers the failover), depending on how much control your organization wants over that decision.
11. What happens to an application’s existing connections during a failover? They’re dropped and need to reconnect — failover isn’t instantaneous or invisible at the connection level, though it’s typically fast. Applications need reasonable retry logic to handle the brief reconnect window gracefully rather than surfacing a hard error to users.
12. Can you use active geo-replication without a failover group, and when would you do that? Yes — you can set up active geo-replication directly, with manual failover control and without the stable listener endpoint, useful when you want a readable secondary for reporting purposes without necessarily needing the full failover group construct, or when you want more granular manual control than a failover group’s policies provide.
13. What’s the basic cost consideration of geo-replication — do you pay for the secondary database separately? Yes — the secondary is a genuine, running database consuming its own compute resources, so you’re paying for it similarly to how you’d pay for a standalone database at whatever tier you’ve provisioned it at, not a discounted “standby-only” rate, since it’s a fully functional, readable database, not a dormant placeholder.
14. Can geo-replication be set up between two databases in the same region, or does it have to cross regions? It can be same-region or cross-region — same-region geo-replication (sometimes combined with zone redundancy) protects against a datacenter-level issue without the latency or the “different region” DR guarantee that cross-region replication provides, while cross-region is what you’d use for genuine regional disaster protection.
15. What’s the difference between failing over and failing back? Failing over moves the primary role to the secondary, typically during an incident. Failing back moves the primary role back to the original region once it’s healthy again — and it involves the same kind of role switch and brief connection disruption as the original failover, which is why it deserves the same planning and care, not treated as an automatic, risk-free “return to normal.”
16. Is Azure SQL Managed Instance’s failover group functionality the same as Azure SQL Database’s? Broadly similar in concept — a stable listener endpoint, automatic or manual failover policy — but Managed Instance failover groups operate at the instance level (covering all databases on that instance together), whereas Azure SQL Database failover groups are configured per database or per group of specific databases, which is a meaningful structural difference worth knowing.
17. What’s a simple reason to test a failover before you actually need it in a real emergency? You genuinely don’t know how long it actually takes, whether the application reconnects correctly, or whether the whole process works as documented until you’ve seen it happen — a failover group that’s configured but never tested is, in practice, unproven, not validated.
Intermediate Level
18. Walk through what actually happens, mechanically, when a planned failover is triggered. The primary stops accepting new write transactions, waits for the secondary to fully catch up on any remaining replication lag (ensuring zero data loss), then the roles formally switch — the former secondary becomes primary and starts accepting writes, the former primary becomes a secondary, and the failover group’s listener endpoint updates to point at the new primary. Because it waits for full synchronization first, a planned failover is safe to use for genuine testing without data loss risk.
19. What’s mechanically different about a forced failover, and what determines how much data is actually lost? A forced failover doesn’t wait for the secondary to catch up — it immediately promotes the secondary to primary regardless of any outstanding replication lag, since a forced failover is used specifically when the primary is unavailable and waiting isn’t an option. The amount of data lost is directly determined by how much replication lag existed at the moment of the forced failover — meaning genuinely monitoring and understanding your typical replication lag under real load is what actually tells you your realistic worst-case RPO, not just an assumption that geo-replication guarantees near-zero loss regardless of circumstances.
20. How would you monitor replication lag between a primary and secondary, and what would you do if it’s consistently high? Replication lag is visible via metrics/DMVs specific to the geo-replication relationship — I’d set up alerting on sustained elevated lag rather than only checking it reactively, and if it’s consistently high, I’d investigate whether it’s driven by genuinely heavy write volume on the primary outpacing what the link can keep up with, network latency between the specific regions involved, or the secondary’s own compute tier being insufficient to apply changes as fast as they’re arriving — each of which points to a different fix (reducing write volume, choosing a closer region, or scaling the secondary).
21. What’s the significance of the secondary needing to be sized appropriately, not just “exist,” from an operational perspective? Two distinct concerns: first, if the secondary’s compute tier is too small, it may struggle to apply incoming replicated changes fast enough, causing chronic replication lag even under normal primary load. Second, and just as important, if the secondary ever needs to become primary during a real failover, it needs to be able to actually handle full production read-write load at that point — a secondary sized as a minimal placeholder “just to have a DR copy” can leave you with a technically-successful failover that immediately struggles under real traffic.
22. How would you configure a failover group to support both DR and read-scale-out reporting simultaneously? I’d set up the failover group with the secondary in a tier supporting read scale-out (Business Critical or Premium), and configure the application’s reporting workload to connect via ApplicationIntent=ReadOnly against the failover group’s separate read-only listener endpoint — meaning the same secondary that exists for DR purposes is also actively serving useful read traffic day-to-day, rather than sitting idle, and I’d make sure the reporting application gracefully handles the temporary loss of that read endpoint during an actual failover event, since roles do briefly swap.
23. What’s the difference between the failover group’s read-write listener and read-only listener, and how does an application choose which to use? The read-write listener always points to whichever database is currently primary, for standard read-write application traffic. The read-only listener always points to a readable secondary, intended specifically for reporting/analytical traffic using ApplicationIntent=ReadOnly — an application chooses which endpoint to use based on the nature of its workload, not by inspecting current primary/secondary roles itself, since the listener abstracts that away.
24. How would you decide between automatic and manual failover policy for a specific application, beyond just “automatic is faster”? I’d weigh how confident I am in the automatic detection correctly distinguishing a genuine sustained outage from a brief, self-resolving blip, against how costly an unnecessary failover would be for that specific application — a system where failing over unnecessarily causes real disruption (data reconciliation complexity, application state issues) might favor manual control despite the slower response, while a system where fast recovery clearly outweighs the small risk of an occasional unnecessary failover favors automatic.
25. What’s the “grace period” setting in an automatic failover policy, and how would you decide on its value? It’s a configurable delay before automatic failover actually triggers after an outage is detected, specifically to avoid failing over for a transient issue that resolves on its own within that window. I’d set it based on realistic data about how long genuine transient issues in that environment typically take to self-resolve versus how long a real outage tends to last — too short risks unnecessary failovers, too long directly eats into your actual achievable RTO, so it’s a genuine trade-off, not a “set and forget” default value.
26. How would you handle DNS considerations when an application connects via a failover group’s listener endpoint, particularly around caching? The listener endpoint’s DNS record needs to actually propagate its change to point at the new primary during a failover, and DNS caching (both at the client and at intermediate resolvers) can introduce a delay before an application genuinely sees the updated endpoint — I’d make sure the application’s connection handling doesn’t cache DNS resolution indefinitely, and understand that the listener endpoint’s effective failover time includes this DNS propagation factor, not just the database-side role switch itself.
27. What’s the operational difference between failover groups on Azure SQL Database versus Azure SQL Managed Instance in terms of what gets included in a single failover event? On Azure SQL Database, a failover group can include a specific, deliberately chosen set of databases (not necessarily every database on the server), meaning you can group related databases together for coordinated failover while leaving others independent. On Managed Instance, failover groups operate at the instance level — every database on that instance fails over together as a unit, which is a meaningful structural consideration if you have some databases that genuinely need independent failover behavior from others on the same instance.
28. How would you approach testing a failover without disrupting production traffic? A planned (not forced) failover is the safer test mechanism, since it performs a clean, zero-data-loss role switch you can then fail back from — I’d schedule it during a lower-traffic window regardless, communicate the brief connection disruption window to stakeholders in advance, and specifically validate the application’s reconnect behavior as part of the test, not just confirm the database-side role switch completed successfully.
29. What’s the relationship between geo-replication and Point-in-Time Restore — do they protect against the same failure modes? No, and this is a common point of confusion — geo-replication protects against infrastructure failure (a region going down) by maintaining a live, synchronized copy, but because it’s continuously replicating, it also faithfully replicates your own mistakes (a bad update, accidental data corruption) almost immediately. PITR, based on backups with a retention window, is what actually protects against that second category — human or application error — which geo-replication by design can’t undo.
30. How would you handle a scenario where you need to add a new database to an existing failover group without disrupting the databases already in it? Failover groups support adding databases to an existing group, and this operation is generally non-disruptive to the databases already present — I’d still validate the newly added database’s own initial seeding/sync completes successfully before considering it fully protected, since a freshly added database starts its own replication relationship and needs to reach a synchronized state just like an originally configured one would.
31. What’s the significance of “seeding time” when initially setting up geo-replication on an existing, already-large database? The very first synchronization — copying the full existing database to the new secondary before ongoing incremental replication can begin — takes time proportional to the database’s size, meaning a very large existing database can have a genuinely lengthy initial seeding period during which it’s not yet fully protected. I’d plan for and communicate this initial seeding window explicitly when setting up geo-replication on a large existing database, rather than assuming protection is instantaneous the moment you configure it.
32. How would you handle a situation where the secondary region itself is experiencing degraded performance, even though it’s not fully down? This complicates the failover decision meaningfully — failing over to a degraded secondary region might trade one problem for a different one, so I’d check Resource Health and Service Health for the secondary region specifically as part of the failover decision, not just assume failing over always improves the situation, and this is exactly the kind of ambiguous scenario where a manual failover policy’s human judgment earns its value over blind automatic failover.
33. What’s your approach to validating that Always Encrypted key access works correctly on a secondary after a failover, given the secondary is in a different region? I’d explicitly test that the column master key (typically in Azure Key Vault) is genuinely accessible from the secondary’s region — not just assume it is because it worked on the primary — since Key Vault itself needs appropriate redundancy or a replicated/paired setup in the secondary region, and a technically successful database failover that the application can’t actually decrypt data against afterward isn’t a genuinely successful recovery.
34. How would you approach failover group configuration for an application with components in multiple Azure regions that all need to reach the currently-primary database? I’d confirm that every region hosting an application component has appropriate network connectivity (via Private Endpoint, VPN, or ExpressRoute, depending on the architecture) to reach the failover group’s listener endpoint regardless of which region is currently primary — since a multi-region application architecture adds complexity to make sure every component can consistently resolve and reach the current primary, not just the application instances that happen to be co-located with the database’s original primary region.
35. What’s a realistic reason a failover group’s automatic failover might not trigger even though the primary genuinely seems unavailable from an application’s perspective? The automatic failover policy’s detection criteria are specifically about the platform’s own health signals for that database resource — an application-perceived unavailability caused by something outside the database itself (a network path issue between the application and Azure, a DNS problem, an application-side connection pool issue) wouldn’t trigger automatic failover, since the database itself might genuinely still be healthy and reachable by other means, which is exactly why confirming Resource Health directly is an important step before assuming “the failover should have happened by now.”
Advanced Level
36. Explain in detail how the underlying replication technology behind active geo-replication works, and why it’s asynchronous by design rather than synchronous. It’s built on a log-shipping-based mechanism — transaction log records generated on the primary are transmitted to the secondary and replayed there to apply the same changes, conceptually similar to (though more automated and managed than) traditional log shipping. It’s asynchronous by design specifically because synchronous replication across genuinely distant regions would impose the cross-region network round-trip latency on every single write transaction on the primary, which would be unacceptable for most production workloads — the asynchronous design is a deliberate trade-off favoring primary-side write performance over guaranteed zero-loss on the secondary, distinct from Business Critical’s synchronous in-region replica architecture, which doesn’t face the same cross-region latency problem.
37. How would you architect geo-replication and failover groups for a globally distributed application requiring low-latency reads in multiple regions simultaneously, not just DR? I’d use multiple readable secondaries positioned in the regions with genuine local read demand, each serving local read-only traffic via ApplicationIntent=ReadOnly against region-appropriate connection strings, while a failover group handles the DR/failover relationship for the primary role specifically — recognizing that having multiple geographically distributed secondaries serves two overlapping but distinct purposes (local read latency reduction, and DR), and I’d be explicit about which secondary is the “designated” failover target within the group versus which are purely read-scale secondaries not part of the automatic failover relationship.
38. Explain the data consistency implications for an application that reads from a geo-replicated secondary immediately after writing to the primary. Because replication is asynchronous, a read against the secondary immediately following a write to the primary can return stale data — the write may not have replicated yet. This is a classic eventual consistency problem, and an application that requires read-your-own-writes consistency needs to either read from the primary for that specific follow-up read, or be explicitly designed to tolerate and gracefully handle this staleness (a UI showing “your change is being processed” rather than assuming an immediate secondary read will reflect it) — this is an application design consideration, not something the platform resolves on your behalf.
39. How would you handle capacity and cost planning for a multi-secondary geo-replication topology (up to four secondaries) versus a single-secondary failover group setup? Each additional secondary is a genuinely running, separately billed database, so I’d justify each one against a specific need — DR, or local-read-latency reduction for a specific region — rather than defaulting to the maximum four secondaries without a clear purpose for each, since cost scales roughly linearly with secondary count, and an unused or under-justified secondary is exactly the kind of quiet, compounding cost creep worth catching in a cost review.
40. Explain how you’d design a monitoring and alerting strategy specifically for the health of a geo-replication relationship itself, separate from general database health monitoring. I’d specifically alert on sustained elevated replication lag (not just a momentary spike, since some lag variance under normal load is expected), on the geo-replication link’s actual state showing anything other than healthy/synchronized via the relevant metrics or DMVs, and on Resource Health/Service Health for the secondary region proactively — treating “is my DR relationship actually healthy right now” as its own distinct monitoring concern worth dedicated alerting, rather than assuming it’s fine simply because the primary database’s own health metrics look normal.
41. How would you approach a scenario where you need to change which region serves as the secondary in an existing failover group, without a full reconfiguration from scratch? Depending on the specific requirement, this might involve adding a new secondary in the desired region, allowing it to fully seed and synchronize, then removing the old secondary — effectively a controlled, gradual transition rather than a disruptive rip-and-replace — and I’d validate the new secondary’s full synchronization and read-scale-out functionality before removing the old one, ensuring you’re never left without DR coverage during the transition itself.
42. Explain the interaction between Automatic Tuning’s learned state (forced plans, created indexes) and a database that has just become primary via failover — does tuning history carry over correctly? Since Automatic Tuning’s state and Query Store data are part of the database itself, and a synchronized secondary shares that same underlying data (including Query Store’s internal tables), a forced plan or an automatically created index generally carries over correctly through a failover — but I’d specifically validate this for a genuinely critical database rather than assuming it transparently and identically applies, particularly worth confirming that a forced-plan decision made based on the original primary region’s historical performance characteristics still makes sense given the new primary’s underlying infrastructure, which could theoretically differ subtly.
43. How would you handle a scenario where a forced failover occurs, and afterward you need to determine exactly what data was lost, for incident reporting purposes? I’d compare the last committed transaction visible on the new primary (post-failover) against whatever evidence exists of the original primary’s state at the moment of the outage — Query Store data, application-side logs, transaction log evidence if the old primary later becomes accessible again — to quantify actual data loss as precisely as possible, rather than reporting only an assumed RPO based on typical replication lag, since the actual loss for a specific real incident deserves a specific, evidence-based answer, not a generic estimate.
44. Explain the trade-offs of using a failover group’s automatic failover policy in an environment with strict data residency or compliance requirements around which region processes writes. Automatic failover, by definition, could move write processing to a different region without a human decision point in the moment — which is a genuine conflict if compliance requires writes to only ever occur in a specific, approved geography under normal circumstances. I’d specifically evaluate whether automatic failover to a given secondary region is actually compliant with the relevant data residency requirement before enabling it, and if it’s not, manual failover policy (preserving a deliberate human compliance check at the moment of decision) is likely the more defensible configuration, even at the cost of a slower response during a genuine outage.
45. How would you diagnose a scenario where a planned failover test unexpectedly took much longer than the typical documented failover time? I’d check whether replication lag was unusually high at the moment the planned failover was initiated, since a planned failover specifically waits for full synchronization before switching — meaning an unusually long planned failover often just reflects genuinely elevated lag at that moment needing to fully catch up first, rather than a fundamental problem with the failover mechanism itself. I’d also check for any concurrent platform-level events (maintenance, an unrelated regional issue) that might have independently affected the timing.
46. Explain how you’d design a runbook specifically for a forced failover decision, given the genuine ambiguity and time pressure involved in a real incident. I’d define clear, objective criteria in advance for when a forced failover is justified — specific Resource Health/Service Health signals, a defined maximum acceptable wait time before defaulting to forced failover if the primary hasn’t recovered — so the decision isn’t being reasoned through for the first time under real incident pressure, explicitly document who has authority to make that call, and include a clear post-forced-failover checklist (validate application connectivity, quantify actual data loss, communicate to stakeholders) so the process doesn’t end the moment the failover technically completes.
47. How would you handle geo-replication and failover group design for a database subject to data residency requirements that restrict which regions are permitted to hold a copy of the data at all? This directly constrains which regions can even be considered as a secondary — I’d confirm the compliant region set explicitly with the compliance team before any technical configuration, and if no compliant secondary region exists within the required geographic boundary, geo-replication-based cross-region DR may simply not be available as an option at all for that database, in which case I’d document that explicitly as an accepted, compliance-driven residual risk rather than silently working around it with a non-compliant region.
48. Explain the operational risk of a secondary that’s been running as a read-scale-out replica for a long time without ever having actually served as primary, and how you’d mitigate it. There’s a real risk that a secondary which has genuinely never been promoted to primary (only tested via planned failovers that were promptly failed back, if tested at all) has some undiscovered gap — a configuration difference, an access or firewall rule that exists on the primary but was never replicated to apply equivalently to the secondary’s role as primary, or a connectivity path that assumes the original primary’s specific identity. I’d mitigate this by including a genuine, sustained period of running as primary (not just a brief test-and-immediately-failback) as part of periodic DR testing, specifically to surface any such gap while it’s still a test, not a real incident.
49. How would you approach explaining the actual, real-world RPO of a geo-replication-based DR strategy to a stakeholder who assumes “geo-replication” means zero data loss, always? I’d be specific and honest — geo-replication’s asynchronous nature means a planned failover achieves zero data loss because it waits for full sync, but a forced failover (the scenario that actually matters during a genuine primary-region outage) can lose whatever hadn’t yet replicated, typically a small but non-zero window measured in seconds during normal conditions, though potentially more under unusually high write load or lag. I’d present actual measured replication lag data from their specific environment rather than a generic industry claim, so the stakeholder’s understanding of RPO is grounded in their real system’s behavior, not an assumption the technology alone guarantees perfection.
50. Walk through a genuinely difficult geo-replication or failover group incident or design challenge you’ve handled or would expect to handle, end to end. Intentionally open-ended, because interviewers are evaluating judgment and depth of understanding, not a memorized script. A strong structure to demonstrate regardless of the specific scenario: (1) understand the actual mechanism at play — replication is asynchronous, lag is real and variable, forced failover trades consistency for speed — rather than treating the feature as a black-box guarantee, (2) design the failover policy (automatic versus manual) and secondary sizing based on genuine requirements and honest trade-offs, not defaults, (3) monitor the replication relationship’s actual health continuously, not just assume it’s fine, (4) test failover for real, including the less comfortable forced-failover and sustained-primary-role scenarios, not just a quick planned-and-immediately-failed-back test, and (5) after any real event, quantify what actually happened — real RTO, real RPO, real data loss if any — rather than reporting only the theoretical numbers the platform’s general documentation suggests. Interviewers consistently favor candidates who can explain precisely why a forced failover can lose data — grounded in the asynchronous replication mechanism — over candidates who only know that it “sometimes can,” without being able to say why.
A Closing Thought
The idea running through nearly every advanced answer here: geo-replication’s asynchronous nature isn’t a limitation to apologize for — it’s a deliberate, necessary trade-off, and understanding it precisely is what lets you design a genuinely honest DR strategy instead of one resting on an assumption the technology never actually promised.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
