Study guide

7.2 Amazon RDS & Aurora Operations, Read Replicas & Multi-AZ

AWS Certified Data Engineer - Associate DEA-C01

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Streaming Data Ingestion

1.1 Kinesis Data Streams vs. Firehose Architecture & Partitioning 1.2 Amazon MSK (Managed Streaming for Apache Kafka) & Serverless Patterns 1.3 Event-Driven Streaming Ingestion with DynamoDB Streams & Lambda 1.4 Streaming Distribution, Replay & Processing State

2 Chapter 2: Batch Data Ingestion & Migration

2.1 AWS DMS (Database Migration Service) & Change Data Capture (CDC) 2.2 S3 Batch Operations & High-Throughput Bulk Data Ingestion 2.3 Hybrid Data Transfer with Snow Family & AWS DataSync 2.4 Application, API & Managed File Ingestion

3 Chapter 3: ETL Transformation with AWS Glue

3.1 AWS Glue Jobs, DynamicFrames & PySpark Transformations 3.2 Glue Crawlers, Data Catalog & Schema Evolution 3.3 Data Preparation & Quality Rules with AWS Glue DataBrew & Data Quality 3.4 Incremental Processing with AWS Glue Job Bookmarks & Partition Processing 3.5 Technical and Business Catalogs, Connections & Lineage

4 Chapter 4: Big Data Processing & Workflow Orchestration

4.1 Amazon EMR Cluster Architecture, Spark & Hadoop Ecosystem 4.2 EMR Serverless & Instance Selection (On-Demand vs. Spot Optimization) 4.3 Serverless Orchestration with AWS Step Functions 4.4 Airflow Pipeline Orchestration with Amazon MWAA 4.5 Containers, Lambda & Distributed Processing Performance 4.6 Data Engineering Code, IaC & CI/CD

5 Chapter 5: Object Storage & Data Lake Architecture

5.1 Amazon S3 Storage Classes, Lifecycle Policies & Cost Optimization 5.2 S3 Data Lake Partitioning, Bucketing & Layout Strategies 5.3 Efficient Columnar Formats: Parquet, ORC, and Avro Schema Design 5.4 S3 Performance Optimization, Multipart Uploads & Transfer Acceleration 5.5 Apache Iceberg, S3 Tables & Open Table Operations

6 Chapter 6: Data Warehousing with Amazon Redshift

6.1 Amazon Redshift Architecture, RA3 Nodes & Managed Storage 6.2 Redshift Data Modeling: Distribution Styles (KEY, EVEN, ALL) & Sort Keys 6.3 Loading & Unloading Data with COPY/UNLOAD & Redshift Spectrum

7 Chapter 7: NoSQL, Relational & Specialty Data Stores

7.1 Amazon DynamoDB Partition Keys, Global Secondary Indexes & Data Modeling 7.2 Amazon RDS & Aurora Operations, Read Replicas & Multi-AZ 7.3 Graph & Analytical Querying with Amazon Neptune, OpenSearch & Athena 7.4 Vector Stores, Indexes & LLM Data Processing

8 Chapter 8: Pipeline Operations, Monitoring & Observability

8.1 Monitoring Pipeline Health with Amazon CloudWatch Metrics & Logs 8.2 Event-Driven Alerting & Automation with EventBridge & CloudWatch Alarms 8.3 Centralized Audit Logging with AWS CloudTrail

9 Chapter 9: Troubleshooting, Data Quality & Performance Tuning

9.1 Diagnosing & Resolving AWS Glue, EMR & Lambda Execution Failures 9.2 Redshift & Athena Query Performance Tuning & Memory Management 9.3 Implementing Data Quality Frameworks & Automated Validation Checks 9.4 Visualization, Notebooks, SQL Analytics, Sampling & Skew

10 Chapter 10: High Availability, Backup & Cost Optimization

10.1 Disaster Recovery, Cross-Region Replication & Backup Strategies 10.2 Designing High Availability Architectures Across AWS Data Services 10.3 Cost Optimization & Resource Utilization across Analytics Workloads

11 Chapter 11: Data Security, Encryption & Identity Management

11.1 Fine-Grained Access Control with AWS IAM Policies, Roles & Conditions 11.2 Data Encryption at Rest & in Transit with AWS KMS & S3 Bucket Policies 11.3 Secure Credentials & Secrets Management with AWS Secrets Manager & Parameter Store 11.4 Private Network Connectivity & Isolation with AWS PrivateLink & VPC Endpoints

12 Chapter 12: Data Governance, Privacy & Compliance

12.1 Centralized Data Lake Governance & Access Control with AWS Lake Formation 12.2 Automated Sensitive Data Discovery & PII Protection with Amazon Macie 12.3 Configuration Auditing & Compliance Enforcement with AWS Config 12.4 Data Privacy, Sovereignty, Sharing & Retention Guardrails 12.5 SageMaker Unified Studio Domains, Projects & Catalog Governance

7.2 Amazon RDS & Aurora Operations, Read Replicas & Multi-AZ

Key Takeaways Explain This Topic AI

Amazon RDS Multi-AZ deployments synchronously replicate to a standby in another Availability Zone and fail over automatically; AWS documents that failovers commonly take 60–120 seconds, while actual RPO and RTO depend on engine and failure conditions.

RDS read replicas use asynchronous engine-native replication to offload reads and can usually be promoted; the exact replication mechanism and promotion behavior depend on the database engine.

Amazon Aurora features a decoupled storage engine where data volumes auto-expand up to 128 TiB and are replicated 6 ways across 3 AZs using a 4-of-6 write quorum and 3-of-6 read quorum.

Aurora Global Database uses storage-based asynchronous replication to as many as 10 secondary Regions, subject to engine-version and Region support; recovery-point loss depends on replication lag.

Aurora Serverless v2 scales in fractional Aurora Capacity Units; supported minimum and maximum capacity depend on the engine version and Region, so use current orderable ranges rather than memorizing 128 ACUs.

Last updated: August 2026

  • 7.2 Amazon RDS & Aurora Operations, Read Replicas & Multi-AZ
  • Relational Database Management in AWS (RDS Engine Overview)

Amazon Relational Database Service (RDS) is a fully managed database service that automates administrative tasks such as hardware provisioning, database setup, OS/database patching, automated back-ups, and storage scaling. RDS supports Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle, Microsoft SQL Server, and IBM Db2. Feature availability differs by engine, version, and Region.

Storage Architecture & Performance Tiers

Amazon RDS volumes run on AWS Elastic Block Store (EBS). Choosing the appropriate EBS storage tier is essential for data engineering pipelines requiring predictable IOPS:

General Purpose SSD (gp2 / gp3) : Baseline performance of 3,000 IOPS and 125 MB/s throughput for gp3. Volume size scales storage independently of IOPS.

Provisioned IOPS SSD (io1 / io2 Block Express) : Designed for high-throughput, low-latency OLTP workloads. Allows provisioning up to 64,000 IOPS per RDS instance (or 256,000 IOPS on io2 Block Express).

High Availability (Multi-AZ) vs. Read Scaling (Read Replicas)

A core topic on the AWS Data Engineer exam is distinguishing between Multi-AZ Deployments (for High Availability and Fault Tolerance) and Read Replicas (for Read Scalability and Analytical Offloading).

  • Feature RDS Multi-AZ Deployment RDS Read Replicas
  • Primary Objective High Availability (HA) & Disaster Recovery Read Scalability & Performance Offloading

Replication Type Synchronous replication to a standby or standbys Usually asynchronous engine-native replication

Availability Zones Primary and standby capacity in separate AZs Same AZ, Cross-AZ, or Cross-Region where supported

Database Access Standby is generally not a read-scaling endpoint Replicas are accessible for read traffic

RPO (Data Loss) Designed for near-zero data loss, but do not treat this as an unconditional zero-loss guarantee Replication lag can cause data loss on promotion

RTO (Failover Time) Automatic endpoint/DNS failover; time varies by topology and workload Promotion is a separate recovery action

Backup Source Backup behavior depends on the Multi-AZ topology and engine Backup support varies by engine

Engine Support Verify the current Multi-AZ options for the selected engine Verify replica and cross-Region support for the selected engine Multi-AZ: App ---> [ Primary DB (AZ-A) ] ===(Synchronous Storage Dup)===> [ Standby DB (AZ-B) ]

Read Replica: App ---> [ Primary DB (AZ-A) ] ---(Asynchronous Log Stream)---> [ Read Replica (AZ-C) ] (Readable)

How RDS Multi-AZ Failover Operates

When a primary database instance fails due to hardware loss, network degradation, or AZ outage:

The storage layer detects heartbeat loss.

RDS flips the canonical domain name record ( CNAME ) of the database endpoint to point to the IP address of the Standby instance.

Applications automatically reconnect to the new Primary instance using the existing endpoint string without code modifications.

Exam Note : Standard RDS Multi-AZ Standby instances cannot serve read queries. However, RDS Multi-AZ with Two Readable Standbys (available for PostgreSQL and MySQL) introduces readable standby instances backed by local NVMe caching, supporting both HA and read offloading.

Amazon Aurora Architecture: Decoupled Distributed Storage

Amazon Aurora is AWS's cloud-native, MySQL- and PostgreSQL-compatible relational database engine. Aurora re-architects relational database storage specifically for the cloud, separating compute processing from persistent storage. [ Aurora Compute Layer (Writer / Reader Instances) ]

  • | (10 Gbps Net)
  • ----------------------------------------------------------------------------------------

[ Storage Node 1 ] [ Storage Node 2 ] [ Storage Node 3 ] [ Storage Node 4 ] [ Storage Node 5 ] [ Storage Node 6 ]

  • (AZ-A Copy 1) (AZ-A Copy 2) (AZ-B Copy 1) (AZ-B Copy 2) (AZ-C Copy 1) (AZ-C Copy 2)
  • The 6-Way Storage Replication & Quorum Model

Aurora does not write database pages directly to local disks. Instead, an Aurora Cluster Volume is a single virtual storage volume spanning 3 Availability Zones :

6 Copies across 3 AZs : Data is automatically replicated in 10 GB chunks (protection units) six times across 3 AZs (2 copies per AZ).

Write Quorum (4 of 6) : A write operation is acknowledged as durable as soon as 4 out of 6 storage nodes confirm log arrival.

Read Quorum (3 of 6) : Reading data requires confirmation from 3 of 6 nodes, ensuring read consistency even if an entire AZ (2 storage nodes) fails.

Self-Healing Storage : Peer-to-peer gossip protocols automatically detect missing or corrupt storage blocks and repair them asynchronously in the background without affecting database latency.

Log-Is-The-Network Paradigm

Traditional RDBMS engines write both database pages and WAL/redo logs over the network, causing heavy I/O bottlenecks. Aurora streams only log records ( redo log records ) directly to storage nodes. Storage nodes apply log records to database pages asynchronously in parallel. This eliminates 84% of network traffic compared to standard PostgreSQL/MySQL engines.

Aurora Endpoints & Read Replica Auto-Scaling

An Aurora cluster separates connection management using dedicated endpoints:

Cluster Endpoint (Writer Endpoint) : Connects to the current primary database instance. Accepts INSERT , UPDATE , DELETE , and SELECT statements.

Reader Endpoint : Load-balances incoming read-only traffic ( SELECT ) across all active Aurora Read Replicas in the cluster.

Custom Endpoints : Groups specific subset instances for targeted workloads (e.g., routing heavy batch reporting queries to a high-memory instance while keeping low-latency web app queries on smaller replicas).

Read Replica Scaling

Aurora supports up to 15 Aurora Replicas per cluster with sub-10 millisecond replication lag (since all replicas share the exact same underlying virtual storage volume). Aurora Auto Scaling can dynamically add or remove read replicas based on CPU utilization or average connection metrics.

  • Aurora Global Databases & Aurora Serverless v2
  • Aurora Global Databases

Designed for globally distributed applications and cross-region disaster recovery:

Architecture : A primary database cluster in one AWS region and read-only secondary clusters in up to 10 AWS regions .

Storage-Level Physical Replication : Replication occurs at the storage layer without consuming compute resources on database nodes.

Performance : Typical cross-region replication latency is under 1 second .

Disaster Recovery : A secondary Region can be promoted after a regional failure, but recovery time and possible data loss depend on topology and measured replication lag. Planned switchover and unplanned failover have different guarantees.

Aurora Serverless v2

Aurora Serverless v2 scales database compute capacity up and down instantly in response to application traffic:

Aurora Capacity Units (ACUs) : Compute capacity is measured in ACUs. 1 ACU provides approximately 2 GiB of RAM, corresponding CPU, and networking.

Granular Scaling : Scales in fractional ACUs. Supported ranges vary by engine version; current versions can support scale-to-zero and maxima above older 128-ACU limits.

Managed scaling : Adjusts capacity without replacing the cluster, while applications should still use resilient connections and test scaling behavior for their engine and workload.

RDS Proxy for Serverless Architectures

When serverless applications (such as AWS Lambda functions) connect directly to relational databases, short-lived function invocations can rapidly exhaust database connection limits. Amazon RDS Proxy is a managed database proxy that maintains a pool of persistent database connections, sharing connections across thousands of Lambda invocations and helping applications reuse connections and recover them more predictably during database failover.

Transactions, MVCC, and Lock Management

Relational loads should keep transactions short and deliberate. In RDS engines that use multi-version concurrency control (MVCC) , readers can often see a consistent snapshot while writers create new row versions, but MVCC does not eliminate locks. Updates can take row or table locks; DDL commonly takes stronger locks; two transactions that acquire resources in different orders can deadlock. Commit or roll back promptly, update keys in a consistent order, index predicates so updates touch fewer rows, monitor engine lock/wait views or Performance Insights, and retry only the transaction the engine aborts. Exact isolation and lock behavior are engine-specific.

Amazon Redshift also manages transaction isolation and table locks for warehouse operations. Long DDL/DML transactions, overlapping loads, or sessions left open can block later work. Use system views such as SVV_TRANSACTIONS and STV_LOCKS to identify the waiting and holding sessions, then resolve the application transaction rather than indiscriminately terminating queries. Schedule conflicting maintenance and loads, stage data before a short merge, and make retries idempotent.

Code Example: Managing RDS Multi-AZ Failover & Aurora Serverless Endpoint via AWS CLI & Boto3 import boto3

  • # Initialize RDS client
  • rds_client = boto3.client('rds', region_name='us-east-1')

def trigger_multi_az_reboot_failover(db_instance_identifier: str):

"""

Forces a Multi-AZ failover reboot on an RDS primary instance for testing DR procedures.

"""

try:

  • response = rds_client.reboot_db_instance(
  • DBInstanceIdentifier=db_instance_identifier,
  • ForceFailover=True # Forces RDS to swap CNAME to Standby instance
  • )
  • status = response['DBInstance']['DBInstanceStatus']
  • print(f"Forced failover initiated for '{db_instance_identifier}'. Status: {status}")
  • return status

except Exception as e:

  • print(f"Error initiating failover reboot: {str(e)}")
  • raise e

def create_aurora_serverless_v2_cluster(cluster_id: str, db_name: str, admin_user: str):

"""Create the cluster and the required db.serverless writer instance."""

try:

  • response = rds_client.create_db_cluster(
  • DBClusterIdentifier=cluster_id,
  • Engine='aurora-postgresql',
  • DatabaseName=db_name,
  • MasterUsername=admin_user,
  • ManageMasterUserPassword=True,
  • ServerlessV2ScalingConfiguration={
  • 'MinCapacity': 0.5,
  • 'MaxCapacity': 16.0
  • },
  • EnableHttpEndpoint=True
  • )
  • rds_client.create_db_instance(
  • DBInstanceIdentifier=f"{cluster_id}-writer",
  • DBClusterIdentifier=cluster_id,
  • Engine='aurora-postgresql',
  • DBInstanceClass='db.serverless'
  • )
  • print(f"Aurora Serverless v2 cluster '{cluster_id}' and writer creation started.")
  • return response['DBCluster']['DBClusterArn']

except Exception as e:

  • print(f"Error creating Aurora cluster: {str(e)}")
  • raise e

Loading diagram... Amazon Aurora Distributed Storage & Connection Proxy Architecture