Highly Available RDS App

1. Situation

Start with a familiar highly available application tier:

flowchart LR
  User["User"] --> ALB["Application Load Balancer"]
  ALB --> AppA["Application<br/>AZ A"]
  ALB --> AppB["Application<br/>AZ B"]
  AppA --> DB["One RDS DB instance<br/>AZ A"]
  AppB --> DB

The load balancer can route around a failed application instance. The Auto Scaling group can replace unhealthy compute. But every request still depends on one database instance in one Availability Zone.

If that database becomes unavailable, the healthy application servers have nowhere to read or write business data. The web tier is highly available on paper, yet the application is down.

The business now gives four requirements:

1. Continue after a database instance or Availability Zone failure.
2. Recover from an accidental deletion or bad deployment.
3. Scale read traffic if reporting and product queries grow.
4. Keep the application usable while database connections move to a replacement writer.

Those requirements sound related because they all involve copies of data. They are not the same engineering problem.

This study uses the classic RDS Multi-AZ DB instance deployment as its main model: one primary DB instance and one synchronous standby in another Availability Zone.

2. Naive Design

Naive option 1: one database in one Availability Zone

application -> single-AZ RDS DB instance

This is simple and may be appropriate for development or a workload that can tolerate database downtime. It is not highly available.

If the instance or its Availability Zone has a problem, the application waits for recovery or a manual restore. Adding more application servers does not remove this database failure boundary.

Naive option 2: add a read replica and call it failover

flowchart LR
  App["Application"] -->|"reads and writes"| Primary["Primary DB instance"]
  Primary -.->|"asynchronous replication"| Replica["Read replica"]
  Reports["Read-only reporting"] --> Replica

A read replica is useful, but it normally has its own endpoint and receives changes asynchronously. In a classic RDS DB instance design, promoting it to a standalone writer and redirecting application traffic are separate recovery actions.

That is not the same experience as managed Multi-AZ failover.

Naive option 3: enable Multi-AZ and remove backups

The standby receives the database's current state. If the application drops a table, overwrites a balance, or runs a destructive migration, that bad change can reach the standby too.

A standby is a current replacement, not historical recovery.

Naive option 4: assume connections never break

Even managed failover involves a transition. Existing database sessions may fail. In-flight transactions can be interrupted. A connection pool may keep stale connections.

The database can recover before the application recovers if the application does not reconnect correctly.

3. What Breaks

Follow three failure stories.

Failure story 1: the primary instance stops responding

At 10:00, the application sends traffic to its usual RDS endpoint. At 10:01, the primary instance becomes unavailable.

In a Single-AZ design, there is no already-maintained standby ready for RDS to promote. Database downtime becomes an infrastructure recovery problem.

The architecture needs a synchronized standby and managed failover.

Failure story 2: an operator deletes valid data

At 14:00, a migration mistakenly deletes rows. The database remains healthy and continues accepting queries. Multi-AZ replication faithfully copies the new state.

Nothing has failed at the infrastructure level, so there is nothing to fail over from. The architecture needs a copy from before 14:00.

Failure story 3: the standby is promoted but users still see errors

RDS changes the database behind the application-facing endpoint. Old TCP sessions do not magically move to the new writer. Some transactions fail midway, and application instances continue trying stale connections from their pools.

The architecture needs bounded retries, fresh DNS resolution, connection-pool recovery, and safe handling of uncertain write outcomes.

These stories lead to a useful rule:

availability protects the service from infrastructure failure
recovery protects the data from unwanted change
application resilience protects users during the transition

4. AWS Architecture

Build the database architecture one responsibility at a time.

Step 1: place RDS in private database subnets

Create a DB subnet group containing database subnets in at least two Availability Zones. The subnet group tells RDS where database resources may be placed.

Do not make the database publicly accessible for a normal application architecture. Application instances connect privately from the application tier.

flowchart LR
  subgraph VPC["Application VPC"]
    subgraph AppTier["Private application subnets"]
      AppA["App<br/>AZ A"]
      AppB["App<br/>AZ B"]
    end

    subgraph DBTier["Private database subnets"]
      DBA["DB subnet<br/>AZ A"]
      DBB["DB subnet<br/>AZ B"]
    end
  end

  AppA --> DBA
  AppB --> DBA
  DBA -.-|"DB subnet group"| DBB

The subnet group provides placement options. It does not by itself create a standby or enable failover.

Step 2: enable a Multi-AZ DB instance deployment

For the central scenario, configure RDS with one primary DB instance and one synchronous standby in another Availability Zone.

flowchart LR
  App["Application"] --> Endpoint["RDS endpoint<br/>stable application-facing name"]
  Endpoint --> Primary["Primary DB instance<br/>AZ A"]
  Primary ==>|"synchronous replication"| Standby["Standby DB instance<br/>AZ B"]
  Standby -.->|"not used for application reads"| Note["Failover capacity"]

The primary serves database traffic. The standby is maintained for high availability. In this deployment type, the application cannot send reporting queries to the standby.

If RDS detects a supported failure condition, it can promote the standby and update the DNS record behind the same RDS endpoint name.

Step 3: add automated backups for recovery

Enable automated backups with a retention period that matches the acceptable recovery window. Automated backups support point-in-time recovery, or PITR, within that window.

PITR creates a new DB instance at the selected time. It does not rewind the damaged production database in place.

flowchart LR
  Production["Production RDS<br/>bad change at 14:00"] --> Backups["Automated backups<br/>and transaction logs"]
  Backups -->|"restore to 13:55"| Restored["New restored DB instance"]
  Restored --> Validate["Validate data<br/>then switch or reconcile"]

Use a manual snapshot before a risky migration when a named pre-change recovery point is useful. Test restore procedures; an untested backup is only a promise.

Step 4: make the application failover-aware

The application connects to the RDS endpoint through a bounded connection pool. It must discard broken connections and establish new ones after failover.

Use retries with backoff for transient connection failures, but retry database writes carefully. If the database committed a transaction and the response was lost, blindly repeating the operation can duplicate business work.

Use idempotency keys, unique constraints, transaction identifiers, or operation-specific reconciliation where repeated writes could be harmful.

RDS Proxy is an optional managed connection layer for supported engines and workloads. It can pool connections and make some failovers more transparent, but it does not replace correct transactions, retries, or database recovery planning.

Step 5: add read capacity only when reads are the bottleneck

If metrics show that read-heavy queries are exhausting the writer, create read replicas and route suitable read-only traffic to their endpoints.

flowchart LR
  App["Application"] -->|"writes and current reads"| Writer["Multi-AZ writer endpoint"]
  Writer -.->|"asynchronous replication"| ReadReplica["Read replica<br/>separate endpoint"]
  Reports["Reports and stale-tolerant reads"] --> ReadReplica

Replication lag means a read replica may not immediately contain the latest committed write. Do not route a read-after-write workflow to a replica unless the product can tolerate stale data.

5. Request Or Data Flow

Learn three lifecycles instead of one long request path.

Lifecycle 1: normal request

1. A user request reaches an application instance through the load balancer.
2. The application obtains database credentials from a secure store such as Secrets Manager.
3. The application connects to the RDS endpoint.
4. DNS directs that connection to the current primary DB instance.
5. The primary commits the transaction while the Multi-AZ deployment maintains its standby according to the deployment's replication behavior.
6. The application returns the result to the user.

Lifecycle 2: database failover

flowchart TD
  Failure["Primary becomes unavailable"] --> Detect["RDS detects a failover condition"]
  Detect --> Promote["RDS promotes standby"]
  Promote --> DNS["RDS endpoint DNS points to new primary"]
  DNS --> Broken["Old application connections may fail"]
  Broken --> Reconnect["Application resolves and reconnects"]
  Reconnect --> Retry["Retry safe operations with backoff"]

The RDS service handles the database-side promotion. The application handles the client-side interruption.

Long transactions may roll back. Requests in flight may return errors. Monitoring should distinguish a brief failover window from a persistent database or application fault.

Lifecycle 3: bad-data recovery

1. Operators identify the last known good time.
2. RDS restores to that point as a new DB instance.
3. The team validates the restored data and application compatibility.
4. The team chooses a recovery method: switch the application, export selected data, or reconcile changes made after the restore point.
5. DNS, secrets, connection strings, and security groups are updated deliberately if the application moves to the restored instance.

Failover is automatic infrastructure recovery. Point-in-time restore is a data-recovery workflow that requires validation and cutover planning.

6. Security Controls

Protect the network boundary

Keep RDS in private database subnets and set public accessibility to false for the normal architecture.

Use security-group referencing:

application security group
  -> allowed on the database port
  -> database security group

The database security group should not allow its port from 0.0.0.0/0, an office IP range used only for convenience, or the whole VPC when the application tier can be named more precisely.

Protect the credentials and database identity

Store database credentials in AWS Secrets Manager or another approved secret store. Rotate them according to engine and application requirements.

IAM policies govern who may create, modify, snapshot, restore, or delete RDS resources through AWS APIs. Database users and roles govern what the application may do inside the relational engine. These are separate permission systems.

Protect data and administrative actions

Use encryption at rest with AWS KMS where required and TLS for application-to-database connections. KMS permissions matter when copying, sharing, or restoring encrypted snapshots.

Protect destructive control-plane actions such as deleting a DB instance, changing backup retention, disabling deletion protection, or modifying network exposure.

Enable the engine logs, CloudWatch metrics, events, audit capabilities, and performance tooling that match the database and compliance needs.

7. Resilience Controls

A highly available relational application needs all of these layers:

Multi-AZ is a same-Region availability control. It is not automatically a multi-Region disaster-recovery architecture.

Monitor database events and signals such as availability, connections, CPU, free memory, storage, I/O latency, replica lag, deadlocks, and slow queries. The correct metric set depends on the engine and workload.

Test failover deliberately in a non-production environment and through controlled production exercises. Measure how long the application—not only the database—takes to recover.

Test restore separately. A successful failover test proves that the standby can take over; it does not prove that the team can recover a table deleted yesterday.

8. Performance Controls

Start with the database work itself:

1. Find slow and frequent queries.
2. Review indexes and execution plans.
3. Remove unnecessary database round trips.
4. Bound connection pools across all application instances.
5. Choose instance, storage, and IOPS capacity from measurements.

Adding application instances can increase database pressure. Ten app instances with pools of 100 connections can attempt 1,000 database connections. Horizontal compute scaling does not automatically scale a relational writer.

Use read replicas when read traffic is the bottleneck and those reads can tolerate asynchronous replication lag. Reporting, analytics, and some product browsing often fit better than immediate read-after-write workflows.

Use Amazon ElastiCache for repeated hot reads when staleness and invalidation are understood. A cache reduces database pressure; it is not the durable source of truth.

For this study's Multi-AZ DB instance deployment, the standby is failover capacity, not read capacity. If the requirement needs readable standbys, inspect RDS Multi-AZ DB clusters or Aurora rather than applying the classic instance model blindly.

9. Cost Controls

Each reliability or scaling feature buys a different outcome:

Do not disable Multi-AZ merely because Single-AZ is cheaper when the requirement explicitly demands high availability.

Do not add read replicas before identifying read pressure. A replica that receives no appropriate traffic adds cost without relieving the writer.

Right-size from metrics, but remember that database cost is not only instance size. Poor indexes, unbounded connections, excessive storage, long snapshot retention, and cross-Region copies can all drive cost.

Aurora may offer a better architecture for some MySQL- or PostgreSQL-compatible workloads, but it is not automatically cheaper or necessary. Start with compatibility, availability, scaling, and operational requirements before choosing the engine family.

10. Exam Variants

Translate the wording into the failure the architecture must survive.

Watch for requirements that need more than one answer

Suppose an exam question says:

A production MySQL database must fail over automatically after an Availability Zone failure and must recover to a specific time after an accidental deletion.

The question describes two failures:

infrastructure failure -> Multi-AZ
bad data -> automated backups and PITR

Neither feature replaces the other.

11. Common Traps

Final Mental Model: One-Minute Review

Final Architecture Map

The color and line styles separate three copy purposes: availability, read scaling, and historical recovery.

flowchart LR
  App["Application"] --> Endpoint["RDS endpoint<br/>stable name"]
  Endpoint --> Primary["Primary DB<br/>reads and writes"]
  Primary ==>|"Synchronous copy"| Standby["Multi-AZ standby<br/>failover capacity"]
  Primary -.->|"Asynchronous copy"| Replica["Read replica<br/>read scaling"]
  Primary -.->|"Backups and transaction logs"| Backups
  Backups["Automated backups<br/>and transaction logs"] -->|"Point-in-time restore"| Restored["New restored DB instance"]

Every copy has a different purpose: the standby protects availability, the read replica adds read capacity, and backups protect historical recoverability. The application still needs to reconnect and retry safely during failover.

12. Related Topics

Use Amazon RDS for engines, subnet groups, storage, backups, security, and the overall managed database model.

Use RDS Multi-AZ vs Read Replicas when availability and read scaling still feel interchangeable.

Use RDS And Aurora Recovery Choices for snapshots, point-in-time recovery, backup replication, and disaster-recovery comparisons.

Use Amazon Aurora when the workload needs Aurora's cluster storage, reader endpoints, replica promotion, or global database options.

Use AWS Secrets Manager for database credential storage and rotation patterns.

Official AWS references:

• Configuring and managing RDS Multi-AZ deployments
• Multi-AZ DB instance deployments
• Working with RDS read replicas
• Restoring an RDS DB instance to a specified time
• Amazon RDS Proxy