After a load balancer was placed in front of a primary database and two read-only replicas, the application intermittently fails with errors stating that the database is read-only. The balancer distributes all connections round-robin across the three servers. What is the correct fix?
Select an answer to reveal the explanation.
Short Explanation
The replicas are reference copies; you can read them, but you can't write in them. Round-robin sends your writes there two times out of three. Split the traffic so writes always land on the primary and reads go wherever there's room.
Full Explanation
In primary-replica topologies, only the primary accepts writes; replicas apply changes received from it and are read-only. A balancer that distributes every connection round-robin sends write statements to replicas about two-thirds of the time, producing read-only errors. The fix is read/write splitting: writes and transactions that write are routed to the primary, often through a dedicated write endpoint or listener, while read-only traffic is distributed across replicas, sometimes including the primary. Applications or drivers may use separate connection strings, or a database-aware proxy may route by statement type. Making every replica writable converts the design to multi-primary replication, which introduces write conflicts and conflict resolution complexity and is a major architectural change rather than a fix for misrouting. Adding more read-only replicas increases the share of writes sent to nodes that cannot accept them, making errors more frequent. Lengthening the health-check interval is unrelated, since the replicas are healthy; they are simply receiving statements they are not allowed to execute. Exam caveat: after a failover, the role of primary moves, so routing must follow the role rather than a fixed server name. Operational check: run a test write through the write endpoint and a read through the read endpoint and confirm each reaches the intended server.