INFRASTRUCTURE LEARNINGINFRASTRUCTURE LEARNING
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
Infrastructure learning guide
Find a lesson or lab/

Esc to close · Search terms stay on your device

LEARNING日本語

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.

What you will learn

Choose a deployment by readable targets, change application and post-transition destinations rather than its name alone.

Prerequisite

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

Single standby, read replica and DB cluster
DeploymentCan the copy serve reads?Main purpose
Multi-AZ DB instance, single standbyThe standby does not accept user read trafficAvailability through a standby in another AZ
Read replicaA read destination; account for apply lagRead scaling and other engine-specific supported uses
Multi-AZ DB clusterOne writer and two readable readersThree AZs; MySQL/PostgreSQL, not a SQL Server deployment

For SQL Server, separate the standby from the read target

Different destinations from a SQL Server primary
Different destinations from a SQL Server primary1: Primary writer — Synchronous standby, subject to support — Multi-AZ standby. 2: Primary writer — Read copy; account for apply lag — Read replica12Primary writerMulti-AZ standbyRead replicaDifferent destinations from a SQL Server primary1: Primary writer — Synchronous standby, subject to support — Multi-AZ standby. 2: Primary writer — Read copy; account for apply lag — Read replica12Primary writerMulti-AZstandbyRead replica
  1. Primary writer → Multi-AZ standbySynchronous standby, subject to support
  2. 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.

Keep SQL Server conditions explicit
CheckWhat to verify
Multi-AZCheck engine version, edition and build; do not reuse an obsolete Enterprise-only blanket rule
Read replicasCheck supported Enterprise Edition versions, classes and other prerequisites; not every SQL Server edition qualifies
Operational scopeCheck 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

Route reads according to required freshness
Observation or requirementDecision
Writer completed 87; replica applied through 86The observation does not establish that the replica can return 87
A result including change 87 is requiredUse a consistency-preserving path, such as an appropriate writer read
A report tolerates some stalenessDefine acceptable lag and consider a read replica
A single Multi-AZ standby existsThat 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

Correlate SQL Server transitions and connections
TransitionRelationship changeApplication concern
Multi-AZ failoverA standby takes over within the deploymentThe endpoint can remain the same while connections break; plan reconnection and retry behavior
Read replica promotionBreak replication and create a standalone databaseThe old primary does not automatically become its replica; verify data and the intended writer
SQL Server switchoverReverse primary and replica roles in a supported configurationEach DB retains its endpoint; reconnect applications to the new primary

Choose checks from the failure you need to handle

  1. Separate DB/AZ failure, read load, accidental updates and Region failure.
  2. Specify recovery-time (RTO) and recovery-point/data-loss (RPO) objectives; a deployment name is not proof they are met.
  3. Include freshness, the writer, reconnection and application results in acceptance.
  4. Plan point-in-time recovery separately for mistakes that replication can propagate.
  5. 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.

Can a SQL Server single Multi-AZ standby serve reporting queries?
Read the answer and reason

Answer: No; this standby is not a user read destination. · Evaluate a supported read replica or another read-serving design.

In A1, the primary is at 87 and replica at 86. Is change 87 guaranteed on that replica?
Read the answer and reason

Answer: No; application of 87 has not been established. · Choose a read path that satisfies the freshness requirement.

Choose the one-writer/two-reader Multi-AZ DB cluster as a SQL Server deployment?
Read the answer and reason

Answer: No; this DB cluster is for MySQL/PostgreSQL. · Distinguish the single-standby deployment from a DB cluster.

After a supported SQL Server switchover, keep sending writes to the old primary endpoint?
Read the answer and reason

Answer: No; reconnect to the new primary and verify results. · The old primary becomes a read replica and its endpoint is read-only.

Check, then explain in your own words

  1. Identify deployment type and engine.
  2. Relate read destinations to required change application.
  3. Explain the transition and post-transition writer.
  4. 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.