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: SQL Server recovery and analytics data

SQL Server backups, replicas and failover: choose a recovery mechanism

Decide whether the incident calls for historical data or a different connection target. Evaluate host failure, an incorrect update and separated analytics reads against different outcomes.

What you will learn

Evaluate host failure, an incorrect update and separated analytics reads against different outcomes.

Prerequisite

A fictional comparison using RDS for SQL Server.

Classify what needs recovery

RDS automated backups and snapshots are recovery material. A standby in a Multi-AZ DB instance can support availability after a primary failure.

Match the failure to the evidence
Match the failure to the evidence1: Business impact — Separate requirements — Recovery needs. 2: Recovery needs — Choose a mechanism — Mechanism. 3: Mechanism — Verify business results — Verify123Business impactRecovery needsMechanismVerifyMatch the failure to the evidence1: Business impact — Separate requirements — Recovery needs. 2: Recovery needs — Choose a mechanism — Mechanism. 3: Mechanism — Verify business results — Verify123Business impactRecovery needsMechanismVerify
  1. Business impact → Recovery needsSeparate requirements
  2. Recovery needs → MechanismChoose a mechanism
  3. Mechanism → VerifyVerify business results
Business impact
Writes unavailable or incorrect
Recovery needs
Continuity or past data
Mechanism
Failover or point-in-time restore
Verify
Connections and data outcome

Match mechanisms to three different goals

A fictional orders database serves three different operational needs.
ScenarioCandidateCheck
Primary host failureFail over to standbyReconnect and verify an order write
Incorrect update at 11:20Restore a point before the updateVerify recovery point and affected rows
Isolate analytics readsA supported read replicaCheck readable targets and freshness
Reopen after failoverRead back the business resultA change of primary alone is insufficient

Restoration material and failover solve separate problems. After an incorrect update, identify pre-error data and decide how to reconcile legitimate changes made later.

Design a test for each mechanism

Verify backup usability. Check retention, recovery point, target and business data scope before using the recovered data.

Read the failover configuration. Distinguish the standby, read replicas and engine-specific options. Verify connection targets and responsibilities.

Keep each outcome separate. Record independent results for host failure, incorrect changes and analytics isolation.

Check the worked example

Choose an option to see the result and explanation.

You need to inspect data before an incorrect update. Which mechanism fits?
Read the answer and reason

Answer: Restore and validate the earlier point separately · Restore earlier data and reconcile it.

What is the goal when a primary host fails?
Read the answer and reason

Answer: Test the business transaction after standby failover · Resume the required transaction after reconnecting.

What do you verify when separating analytics reads?
Read the answer and reason

Answer: Engine and configuration support, plus data freshness · Verify read support and the freshness of the data.

Check, then explain in your own words

  1. Classify failures and purposes.
  2. Verify support for each mechanism.
  3. Measure business outcomes separately.
Check your understanding: Does successful failover establish recovery from an incorrect update?

Test restoration to a consistent point before the error separately.

Availability and historical recovery have different success conditions.

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.