Topic lessonTopic: RDS and SQL Server: management, connections and deployment
RDS Multi-AZ vs read replicas: availability, reads and failover
Multi-AZ provides availability, while read replicas primarily distribute read workloads. Readable targets and transitions depend on the deployment type and database engine. Use SQL Server examples to distinguish a standby, a read destination and recovery.
Choose a deployment by readable targets, change application and post-transition destinations rather than its name alone.
For readers familiar with RDS responsibilities and database reads/writes. Read configurations and fictional change numbers; no AWS deployment or transition is performed.
First distinguish three similarly named deployments
| Deployment | Can the copy serve reads? | Main purpose |
|---|---|---|
| Multi-AZ DB instance, single standby | The standby does not accept user read traffic | Availability through a standby in another AZ |
| Read replica | A read destination; account for apply lag | Read scaling and other engine-specific supported uses |
| Multi-AZ DB cluster | One writer and two readable readers | Three AZs; MySQL/PostgreSQL, not a SQL Server deployment |
For SQL Server, separate the standby from the read target
- Primary writer → Multi-AZ standbySynchronous standby, subject to support
- Primary writer → Read replicaRead copy; account for apply lag
- Primary writer
- Accepts application changes.
- Multi-AZ standby
- Availability copy, not a user read target.
- Read replica
- Read destination; verify required changes are applied.
The diagram compares relationships and purposes; boxes do not represent different Regions. A Multi-AZ primary can have read replicas under the supported conditions. That does not make the SQL Server read replica itself Multi-AZ.
| Check | What to verify |
|---|---|
| Multi-AZ | Check engine version, edition and build; do not reuse an obsolete Enterprise-only blanket rule |
| Read replicas | Check supported Enterprise Edition versions, classes and other prerequisites; not every SQL Server edition qualifies |
| Operational scope | Check login synchronization, restore and cross-Region differences separately; database replication is not an identical server environment |
Example A1: change 87 was written, but the reader is at 86
| Observation or requirement | Decision |
|---|---|
| Writer completed 87; replica applied through 86 | The observation does not establish that the replica can return 87 |
| A result including change 87 is required | Use a consistency-preserving path, such as an appropriate writer read |
| A report tolerates some staleness | Define acceptable lag and consider a read replica |
| A single Multi-AZ standby exists | That standby is not a reporting destination |
86 and 87 are fictional change numbers, not measured lag or log positions. Low load or an active replication status does not prove a required change is readable. Even a MySQL/PostgreSQL Multi-AZ DB cluster does not remove the need to consider reader apply state.
Distinguish failover, promotion and switchover
| Transition | Relationship change | Application concern |
|---|---|---|
| Multi-AZ failover | A standby takes over within the deployment | The endpoint can remain the same while connections break; plan reconnection and retry behavior |
| Read replica promotion | Break replication and create a standalone database | The old primary does not automatically become its replica; verify data and the intended writer |
| SQL Server switchover | Reverse primary and replica roles in a supported configuration | Each DB retains its endpoint; reconnect applications to the new primary |
Choose checks from the failure you need to handle
- Separate DB/AZ failure, read load, accidental updates and Region failure.
- Specify recovery-time (RTO) and recovery-point/data-loss (RPO) objectives; a deployment name is not proof they are met.
- Include freshness, the writer, reconnection and application results in acceptance.
- Plan point-in-time recovery separately for mistakes that replication can propagate.
- Compare versions, editions, Regions, costs and operational work on equivalent terms.
Check deployment and transition decisions
Choose an option to see the result and explanation.
Check, then explain in your own words
- Identify deployment type and engine.
- Relate read destinations to required change application.
- Explain the transition and post-transition writer.
- Separate availability from recovery to an earlier point.
Check your understanding: Does Multi-AZ automatically undo an accidental update?
No. The unwanted change can replicate too.
Availability during failure and recovery to an earlier point are separate requirements.
Sources and conditions checked
The official references used in this lesson are collected here with their scope, conditions and corresponding sections. Independent expert review and live AWS comparison have not been performed.
Scope and conditionsSynchronous single-standby instance deployment; standby does not serve reads; distinguish DB clusters.
Used in this lessonFirst distinguish three similarly named deployments For SQL Server, separate the standby from the read target
Scope and conditionsThree-AZ writer/two-readable-replica deployment with possible apply lag; distinct from single standby and Aurora; check supported engines separately.
Used in this lessonFirst distinguish three similarly named deployments Example A1: change 87 was written, but the reader is at 86
Scope and conditionsMySQL/PostgreSQL support; unavailable for SQL Server and other listed engines; also verify Region and version.
Used in this lessonFirst distinguish three similarly named deployments
- AWS: SQL Server read replicasChecked:
Scope and conditionsCurrent EE/version/class conditions; no Multi-AZ replica, no login synchronization/PITR from replica; Region restrictions.
Used in this lessonFor SQL Server, separate the standby from the read target
- AWS: SQL Server Multi-AZChecked:
Scope and conditionsVersion/edition-specific DBM, AG and block-level support; SQL 2022 Web has build and instance-history conditions.
Used in this lessonFor SQL Server, separate the standby from the read target Distinguish failover, promotion and switchover
- AWS: Read replica promotionChecked:
Scope and conditionsPromotion breaks the replication relationship and needs traffic checks; distinguish switchover.
Used in this lessonDistinguish failover, promotion and switchover
- AWS: SQL Server switchoverChecked:
Scope and conditions2022–2025 EE role reversal; endpoints remain instance-specific and old primary becomes read-only; prerequisites and application stop/reconnect apply.
Used in this lessonDistinguish failover, promotion and switchover
Scope and conditionsBusiness-defined RTO/RPO; a deployment name alone does not prove recovery objectives are met.
Used in this lessonChoose checks from the failure you need to handle