Create and Configure Amazon RDS Databases in the AWS Console – SAP-C02 Study Guide

Purpose of This Lesson

This hands-on lesson demonstrates how to create and configure an Amazon Relational Database Service (Amazon RDS) database. The workflow covers engine selection, instance sizing, storage, VPC networking, public accessibility, security groups, backups, monitoring, and read replicas.

For the SAP-C02 exam, the important skill is not memorizing console screens. It is understanding how each configuration choice affects availability, scalability, security, performance, and cost.

Key Concepts

Amazon RDS engine selection

Amazon RDS is a managed relational database service. AWS manages much of the underlying infrastructure and operational work, including provisioning, automated backups, patching options, monitoring integrations, and certain high-availability features.

Supported engines include:

• Amazon Aurora MySQL-Compatible Edition
• Amazon Aurora PostgreSQL-Compatible Edition
• MySQL
• PostgreSQL
• MariaDB
• Oracle
• Microsoft SQL Server

The engine must match the application’s compatibility, licensing, feature, and migration requirements. Aurora is not automatically the correct answer simply because it is an AWS-native engine.

Creation templates

The RDS console provides templates such as:

• Production: prioritizes availability and production-oriented settings.
• Dev/Test: balances capability and cost for nonproduction environments.
• Free tier: restricts choices to configurations eligible for applicable Free Tier benefits.

These templates can limit instance classes, deployment modes, and other options. A low-cost single-instance configuration is not equivalent to a production highly available deployment.

DB instance versus DB cluster

A standard RDS deployment generally provisions a DB instance. Aurora commonly uses a DB cluster containing a writer and one or more readers. Some RDS engines also offer Multi-AZ DB clusters with a writer and readable standby instances.

Do not confuse these designs:

• Multi-AZ deployment: primarily a high-availability and failover mechanism. The standby is generally not used for application read scaling in the traditional Multi-AZ standby model.
• Read replica: primarily a read-scaling and reporting mechanism using asynchronous replication. It can also support disaster recovery or migration patterns, but it is not the same as synchronous Multi-AZ failover.

Instance class and storage

The DB instance class determines compute capacity, memory, and network performance. The free-tier-compatible class is suitable for a small lab, but it is rarely appropriate for a production workload.

RDS storage options vary by engine and configuration. Common choices include:

• General Purpose SSD (gp2 or gp3): suitable for many general workloads. gp3 can provide more independently configurable performance than gp2 where supported.
• Provisioned IOPS SSD (io1 or supported newer options): designed for workloads requiring consistently high I/O performance.
• Magnetic storage: legacy and generally not the preferred option for modern deployments.

Storage autoscaling can increase allocated storage when configured thresholds are reached. It does not reduce storage automatically, and scaling events should still be planned around workload growth.

VPC networking and subnet groups

An RDS DB instance is deployed into a VPC through a DB subnet group. The subnet group should contain subnets in at least two Availability Zones for Multi-AZ designs and failover flexibility.

A typical secure architecture uses:

1. Application servers in private application subnets.
2. RDS in private database subnets.
3. No direct public access to the database.
4. Security group rules that allow database traffic only from the application tier’s security group.

The database endpoint and port are provided after provisioning. For MySQL, the default port is 3306; however, security groups must explicitly permit the selected port from the correct source.

Public accessibility

For most application architectures, set Public access to No. This gives the DB instance a private networking posture and avoids exposing it directly to the internet.

A private database is not secured by the subnet setting alone. Security groups, network ACLs, routing, credentials, encryption, and database authorization must also be configured correctly.

Security groups

RDS uses VPC security groups to control network access. A common least-privilege rule is:

• Inbound: TCP 3306 from the application server security group, not from 0.0.0.0/0.
• Outbound: governed by the security group’s egress policy and surrounding network controls.

Referencing a security group as the source is generally more resilient than allowing a changing subnet CIDR or individual instance IP addresses.

Credentials and authentication

RDS supports password authentication and, depending on the engine and configuration, alternatives such as IAM database authentication or Kerberos.

AWS Secrets Manager can store database credentials and support credential rotation. Applications should retrieve secrets securely rather than hard-code passwords in source code or configuration files.

A secret does not automatically make an application follow every RDS failover design. Applications should use the appropriate stable RDS or Aurora endpoint, connection retry logic, and connection management strategy.

RDS Proxy

Amazon RDS Proxy provides a managed connection pool and proxy endpoint for supported RDS databases. It is particularly useful when many short-lived clients, such as Lambda functions, create database connections rapidly.

RDS Proxy can:

• Pool and reuse database connections.
• Reduce connection storms.
• Improve failover behavior for supported configurations.
• Integrate with IAM and Secrets Manager for authentication and credential handling.

It does not replace database scaling, query optimization, or a read replica. It also adds cost and must be evaluated for engine and feature compatibility.

Backups and snapshots

RDS provides two distinct backup concepts:

• Automated backups: retained for a configured period and used for point-in-time recovery within that retention window.
• Manual DB snapshots: user-created backups retained until explicitly deleted, independent of the automated backup retention period.

A newly created database may have an initial snapshot or backup-related activity. Automated backups, maintenance, and snapshots can incur storage or other charges depending on the configuration and retention period.

Read replicas

A read replica receives changes asynchronously from the source database. It has its own endpoint and can serve read-only traffic.

Read replicas are useful for:

• Scaling read-heavy workloads.
• Offloading reporting or analytics queries.
• Providing a source for certain migration or disaster recovery workflows.
• Creating replicas in another Availability Zone or Region, where supported.

Because replication is asynchronous, replica lag is possible. A read replica should not be used for reads that require immediate visibility of a just-completed write unless the application handles consistency appropriately.

A replica does not automatically distribute application traffic. The application, a proxy, or a routing layer must direct eligible reads to the replica endpoint.

Monitoring and maintenance

Basic monitoring is available through Amazon CloudWatch. Enhanced Monitoring provides more detailed operating-system-level metrics and uses an IAM role to publish monitoring data.

RDS also supports maintenance windows and automatic minor engine version upgrades, depending on the engine and settings. Production environments should control maintenance timing and test engine upgrades before broad rollout.

Exam-Relevant Takeaways

• Choose the database engine based on compatibility and requirements, not on the console’s default selection.
• A single-AZ DB instance is not highly available. Use Multi-AZ for managed failover across Availability Zones.
• Multi-AZ and read replicas solve different problems: availability versus read scaling.
• Read replica replication is asynchronous, so replica lag and stale reads are possible.
• Keep RDS private whenever possible and allow access through security groups from the application tier.
• A DB subnet group should span multiple Availability Zones for resilient deployments.
• Use Secrets Manager for credential storage and rotation; do not embed database credentials in application code.
• Use RDS Proxy when connection management, especially from Lambda or other highly parallel clients, is the bottleneck.
• Automated backups support point-in-time recovery; manual snapshots are retained until deleted.
• The RDS endpoint abstracts the underlying instance. Applications should not depend on a fixed private IP address.
• Storage autoscaling increases capacity but does not normally shrink allocated storage.
• Always delete temporary lab databases, replicas, snapshots, and retained backups when they are no longer needed.

Architecture Decision Guide

Requirement	Recommended RDS approach	Important qualification
Small development database	Single-AZ RDS instance with an appropriate low-cost class	Not suitable for production availability requirements
Automatic failover for a production database	Multi-AZ deployment or an appropriate Multi-AZ DB cluster	Multi-AZ is primarily for availability, not read scaling
Scale read-heavy traffic	Read replicas or Aurora readers	Replication is asynchronous for standard RDS read replicas
Many short-lived database connections	RDS Proxy	Adds cost and does not solve query or storage bottlenecks
Private application-to-database traffic	RDS in private subnets, public access disabled	Configure security groups from the application tier
Point-in-time recovery	Automated backups with an appropriate retention period	Retention and backup storage affect cost
Long-lived rollback or archival copy	Manual DB snapshot	Snapshot remains until deleted and may incur charges
Cross-Region read or DR capability	Cross-Region read replica, where supported	Account for lag, promotion procedures, network, and cost
Centralized secret storage and rotation	AWS Secrets Manager	Applications still need correct endpoint and retry behavior
Detailed host-level metrics	Enhanced Monitoring	Requires additional configuration and may add operational overhead

Common Exam Traps

• Selecting a read replica for automatic failover: A read replica is not the standard answer for synchronous high availability. Choose Multi-AZ when the requirement is managed failover.
• Assuming Multi-AZ standby handles reads: Traditional Multi-AZ standby instances are not read-scaling targets. Use read replicas or Aurora reader instances for read traffic.
• Opening MySQL to the internet: Allowing port 3306 from 0.0.0.0/0 is usually an insecure design. Permit only trusted application sources.
• Treating “private subnet” as complete security: Security groups, routing, credentials, encryption, and database permissions remain essential.
• Using a read replica for strongly consistent reads: Asynchronous replication can produce stale results.
• Assuming Secrets Manager changes application endpoints automatically: Secrets Manager handles secret values and rotation workflows; endpoint selection and failover-aware connection behavior must be designed.
• Confusing a DB snapshot with automated backups: A manual snapshot and an automated point-in-time recovery window have different retention and operational behavior.
• Choosing a free-tier configuration for production: Low cost does not provide Multi-AZ resilience, sufficient performance, or appropriate capacity.
• Forgetting replica and snapshot cleanup: Deleting the primary instance does not necessarily remove every related replica, snapshot, or retained backup resource.

Real-World Engineer Notes

• Use separate security groups for the load balancer, application tier, and database tier. Permit database access from the application security group rather than from broad CIDR ranges.
• Use the RDS endpoint or proxy endpoint in application configuration. Never pin the application to an instance’s private IP address.
• Plan connection behavior explicitly: connection pooling, timeouts, retries, and backoff are especially important during failover.
• Multi-AZ improves availability but does not eliminate the need for backups, tested recovery procedures, or application-level resilience.
• Test read-after-write behavior before routing reads to replicas. Some workloads require session stickiness to the writer or a consistency strategy.
• Enable encryption at rest and in transit when required by security or compliance policies. These settings should be considered during initial provisioning because changing them later may require migration steps.
• Choose maintenance windows that minimize business impact, and validate engine upgrades in a nonproduction environment.
• Monitor CPU, memory pressure, storage space, IOPS, latency, connections, and replica lag. A database can appear healthy at the instance level while the application is experiencing connection or query problems.

Quick Reference Summary

• RDS: managed relational database service.
• Engine: determines SQL compatibility, features, and licensing model.
• DB subnet group: identifies subnets where RDS can place the database.
• Public access disabled: preferred for application databases.
• Security group: restricts access to the database port from trusted sources.
• Multi-AZ: high availability and managed failover.
• Read replica: asynchronous read scaling and selected DR or migration use cases.
• RDS Proxy: connection pooling and connection storm protection.
• Automated backup: point-in-time recovery within retention period.
• Manual snapshot: user-controlled retained backup.
• Endpoint: DNS address applications use to connect to the database.
• Secrets Manager: secure credential storage and rotation.

Flashcards

1. Q: What is the primary purpose of Amazon RDS Multi-AZ?

A: High availability and managed failover across Availability Zones.

1. Q: What is the primary purpose of an RDS read replica?

A: Scaling read traffic and offloading reporting or read-heavy workloads.

1. Q: Is replication to a standard RDS read replica synchronous?

A: No. It is asynchronous, so replica lag and stale reads are possible.

1. Q: What port does MySQL normally use on RDS?

A: TCP port 3306.

1. Q: What is an RDS DB subnet group?

A: A collection of VPC subnets where RDS can deploy database resources.

1. Q: Why should application databases usually have public access disabled?

A: They should be reachable privately by trusted application resources rather than directly from the internet.

1. Q: What AWS service stores and rotates RDS credentials?

A: AWS Secrets Manager.

1. Q: What problem does RDS Proxy address?

A: Excessive database connections and connection storms from highly parallel or short-lived clients.

1. Q: What is the difference between an automated backup and a manual snapshot?

A: Automated backups support point-in-time recovery within a retention window; manual snapshots are user-created backups retained until deleted.

1. Q: Should an application connect to an RDS private IP address?

A: No. Use the RDS DNS endpoint or an RDS Proxy endpoint.

1. Q: Can a traditional Multi-AZ standby be used to scale read traffic?

A: Generally no. Use read replicas or Aurora readers for read scaling.

1. Q: What does storage autoscaling do?

A: It can automatically increase allocated database storage as usage grows; it does not normally reduce allocated storage.

Practice Questions

Question 1

A company runs a production MySQL database on Amazon RDS. The database must automatically fail over to another Availability Zone if the primary instance becomes unavailable. Read traffic is moderate and does not require additional scaling. Which configuration should the architect choose?

A. A single-AZ DB instance with a manual snapshot every hour
B. A Multi-AZ RDS deployment
C. A same-Region read replica
D. An ElastiCache cluster

Correct answer: B. A Multi-AZ RDS deployment

Explanation: Multi-AZ is designed for managed high availability and failover. A read replica is asynchronous and primarily supports read scaling; snapshots do not provide automatic failover.

Question 2

A serverless application uses Lambda to connect to an RDS PostgreSQL database. During traffic spikes, the database reaches its maximum connection limit even though CPU utilization is low. Which solution best addresses the immediate problem?

A. Create a read replica and send all traffic to it
B. Place Amazon RDS Proxy between Lambda and RDS
C. Increase the DB storage size
D. Enable public access on the DB instance

Correct answer: B. Place Amazon RDS Proxy between Lambda and RDS

Explanation: RDS Proxy pools and reuses database connections, reducing connection storms from concurrent Lambda invocations. Storage changes and public access do not solve connection exhaustion.

Question 3

An application performs frequent updates and then immediately reads the updated records. The team wants to route some read traffic away from the primary RDS database. Which concern must the architect address before using a read replica?

A. Read replicas cannot have their own endpoints
B. Read replicas use asynchronous replication and may return stale data
C. Read replicas require public access
D. Read replicas cannot be deployed in a VPC

Correct answer: B. Read replicas use asynchronous replication and may return stale data

Explanation: Replica lag can cause a read immediately following a write to return an older value. The application needs an appropriate consistency strategy, such as sending consistency-sensitive reads to the writer.

Question 4

A three-tier application runs in a VPC. The architect must ensure that only application servers can connect to a private RDS MySQL database. Which security group rule is the most appropriate?

A. Allow TCP 3306 from 0.0.0.0/0
B. Allow TCP 3306 from the database subnet’s CIDR only
C. Allow TCP 3306 from the application server security group
D. Allow all inbound traffic from the VPC CIDR

Correct answer: C. Allow TCP 3306 from the application server security group

Explanation: A security group reference expresses the intended tier-to-tier relationship and avoids broad internet or VPC-wide access. The database should also have public access disabled.

Question 5

A team needs to restore an RDS database to an exact point shortly before an accidental data deletion. Which capability should the architect use?

A. A read replica endpoint
B. Automated backups and point-in-time recovery
C. Enhanced Monitoring
D. A DB instance identifier change

Correct answer: B. Automated backups and point-in-time recovery

Explanation: Automated backups allow point-in-time recovery within the configured retention period. Enhanced Monitoring provides metrics, while a read replica is not a substitute for point-in-time recovery.