All jobs / Database administrator
High Availability interview questions
High Availability questions a Database administrator can be asked, from first job to senior. Open one to see the strongest answer and why it works.
What is a read replica for, and what is it not?
The strongest answer: It offloads read traffic and gives you a warm copy for failover; it is not a backup, because a bad DELETE replicates in seconds
Why it works: Exactly — replicas are for reads and availability, never a substitute for backups.
- Replicas serve reads and failover.
- A replica is not a backup.
- Mistakes replicate too.
- Writes still go to one place.
Synchronous versus asynchronous replication — how do you choose?
The strongest answer: Synchronous when losing a committed write is unacceptable and the standby is close enough that the extra commit latency is tolerable; asynchronous when latency or distance matters more than the last few transactions
Why it works: Exactly — it is a trade between durability guarantee and write latency, decided per system.
- Sync: no lost commits, higher latency.
- Async: fast, may lose recent writes.
- A slow sync standby slows the primary.
- Decide per system, not by fashion.
Finance says the end-of-day report, which reads from a replica, is missing the last few minutes of orders.
How do you respond?
The strongest answer: Measure the lag and its cause — long transactions, heavy reporting queries, or network — then either route that report to the primary, add lag-aware routing, or fix the workload that is holding replay back
Why it works: Exactly — lag has a cause; find it and decide where that particular read belongs.
- Measure lag and find the cause.
- Route lag-sensitive reads deliberately.
- Fix the workload blocking replay.
- Restarts are not diagnosis.
With replication and clustering available, when is log shipping still the right answer?
The strongest answer: For a cheap standby at distance, or a deliberately delayed copy that has not yet applied a bad change — asynchronous with manual failover, fine for recovery but not for seconds of downtime
Why it works: Right — simplicity and the delayed copy are the real reasons, and the price is manual failover and lag.
- Cheap, simple, works at distance.
- Delay is a feature, not a fault.
- Failover is manual.
- A DR tool, not an HA tool.
The standby was set up two years ago by someone who has since left.
There is a standby but nobody has ever failed over to it. What do you propose?
The strongest answer: A planned failover in a low-traffic window with the application team on hand, checking connection strings, replication back to the old primary, and timing it — then repeat regularly
Why it works: Exactly — an untested standby is a hope; a rehearsed failover is a plan.
- Rehearse failover deliberately.
- Involve the application team.
- Check the way back too.
- Repeat on a schedule.
A product needs high availability for its main relational database. How do you pick the topology?
The strongest answer: Start from RPO, RTO and the failure modes we must survive — node, zone, region — then choose the simplest topology that meets them, often a synchronous standby with automated failover, and rehearse it
Why it works: Exactly — requirements first, then the least complex design that satisfies them, proven by drills.
- Targets and failure modes first.
- Simplest topology that meets them.
- Automate and rehearse failover.
- Multi-primary is not a default.
What can go wrong with automatic failover, and how do you guard against it?
The strongest answer: Split brain when the old primary keeps taking writes, flapping on network blips, and data loss if the standby was behind — so I need quorum or a witness, fencing, and sensible detection thresholds
Why it works: Exactly — split brain, flapping and lag are the three, and fencing plus quorum are the defence.
- Fence the old primary.
- Quorum or a witness decides.
- Tune detection against flapping.
- Know the standby’s lag at failover.
Application latency spikes every weekday at nine when the analytics team starts work.
Analysts run heavy ad-hoc queries against the production primary and slow the application. What do you set up?
The strongest answer: A dedicated replica or a warehouse feed for analytics, with their own resource limits and statement timeouts, and route analysts there — and if they need fresher data, agree what lag is acceptable
Why it works: Exactly — isolate the workload, bound it, and negotiate freshness.
- Separate the workload.
- Replica or warehouse feed.
- Timeouts and resource limits.
- Agree acceptable lag.
The data platform wants a change feed from the database. Physical or logical replication, and why?
The strongest answer: Logical — it emits row changes the platform can consume selectively and across versions, whereas physical replication is a byte-level copy for standbys; I watch log retention if the consumer falls behind
Why it works: Exactly — logical for consumers, physical for standbys, and mind the retention trap.
- Logical for change feeds.
- Physical for standbys.
- Lagging consumers retain logs.
- Exports are not a feed.
You are designing the vote layout for a cluster spread across two data centres.
How do you set up quorum and a witness, and why does the vote count matter?
The strongest answer: An odd number of votes so one side can always hold a majority, with the witness in a third location that fails independently — otherwise whichever site holds it wins every partition
Why it works: Exactly — the vote count decides who may continue, and putting the witness inside one site quietly hands that site every decision.
- Keep the vote count odd.
- A majority continues; a minority stops.
- Put the witness in a third failure domain.
- Forcing quorum is an incident action.
For SQL Server, when do you choose an availability group over a failover cluster instance, and what does each actually protect?
The strongest answer: A cluster instance fails over to another server on the same storage — one copy; an availability group keeps a copy per replica, so it survives storage loss, but logins and jobs stay behind
Why it works: Exactly — shared storage against replicated copies, and remembering that instance-level objects live outside the databases.
- Cluster instance: shared storage, one copy.
- Availability group: a copy per replica.
- Only the group survives storage loss.
- Sync logins and jobs yourself.
The failover tool promoted the standby, but the old primary never lost its clients and kept committing.
After a network partition, two nodes both accepted writes for several minutes. What now?
The strongest answer: Freeze both, capture the divergent transactions from the logs, involve the product owners in reconciling conflicting rows, rebuild the losing node from the winner, and then fix the fencing that allowed two primaries
Why it works: Exactly — reconciliation is a business decision informed by the logs, and the root cause is missing fencing.
- Freeze and capture both histories.
- Reconcile with the owners.
- Rebuild the loser.
- Fix the fencing.
Several teams each want their own change-data-capture connector on the same production databases. How do you organise it?
The strongest answer: One managed CDC feed per database into a shared stream that consumers subscribe to, with ownership, schema-change contracts and monitoring of slot or log retention — not a connector per team eating the primary
Why it works: Exactly — capture once, fan out many, and own the retention risk centrally.
- Capture once, fan out.
- Own retention centrally.
- Schema-change contracts.
- Connector sprawl fills disks.