Amazon RDS Overview

Amazon Relational Database Service (RDS) is an industry-standard, fully managed relational database service that automates common administrative tasks such as backups, software patching, failure detection, and recovery. It supports multiple database engines including Aurora, MySQL, MariaDB, PostgreSQL, Oracle, and Microsoft SQL Server.

Key features include:

Automated and manual backups with snapshot restore capabilities.
High availability through Multi-AZ deployments with synchronous replication.
Read Replicas for scaling read-heavy workloads (MySQL, MariaDB, PostgreSQL).
Up to 40 DB instances per AWS account.
Flexible instance classes for CPU and memory scaling.
Storage size configurable based on engine and workload.

Supported Database Engines

MySQL

Supports up to 64 TiB storage and 80,000 IOPS.
Table limits depend on storage type and size:
- 10,000 tables for Provisioned IOPS or General Purpose storage ≥ 200 GiB.
- 1,000 tables for Standard or General Purpose storage < 200 GiB.
Point-In-Time Recovery (PITR) and snapshot restores require the InnoDB storage engine (recommended).
Supports password policies via validate_password plugin.
Database names must be unique within the instance.

MariaDB

Supports crash-recoverable storage engine for restores.
Up to 64 TiB storage and 80,000 IOPS.
Requires unique database names within the instance.

PostgreSQL

Benefits from autovacuum and bulk load optimizations for performance.
Supports up to 64 TiB storage and 80,000 IOPS.
Database name is optional during DB instance creation but must be unique.

Microsoft SQL Server

Licensing options:
- License Included (up to 10 DB instances).
- Bring Your Own License (BYOL) (up to 40 DB instances).
Maximum databases per instance vary by instance class and availability mode:

Instance Class	Single-AZ	Multi-AZ (DB Mirroring)	Multi-AZ (Always On AGs)
db..micro to db..medium	30	N/A	N/A
db.*.large	30	30	30
db..xlarge to db..16xlarge	100	50	75
db.*.24xlarge	100	50	100

Best Practices:

Use RDS DB events to monitor failover.
Cache DNS with TTL under 30 seconds to prevent stale entries.
Avoid Simple Recover, Offline, or Read-only modes as they disable transaction logging.
Conduct failover testing regularly.
Deploy applications across multiple Availability Zones for resilience.

Oracle

Licensing:
- License Included: up to 10 DB instances.
- BYOL: up to 40 DB instances.
The database name corresponds to ORACLE_SID required for client connections.
Supports up to 64 TiB storage and 80,000 IOPS.

DB Instance Fundamentals

Instance Classes

RDS instances come in different classes optimized for various workloads:

Standard: Balanced CPU and memory.
Memory Optimized: Higher memory for in-memory databases.
Burstable Performance: Cost-effective with baseline CPU and burst capacity.

You can scale CPU and memory by changing the instance class, customizing CPU cores and threads per core as needed.

Endpoints

The standard endpoint format is:

Storage Options

RDS storage uses Amazon EBS volumes except for Aurora, with three main types:

Storage Type	Supported Engines	Size Range	IOPS Range
General Purpose SSD (gp2)	MySQL, MariaDB, Oracle, PostgreSQL	20 GiB – 64 TiB	N/A
Provisioned IOPS SSD (io1)	All except Magnetic	100 GiB – 64 TiB	1,000 – 80,000
Magnetic	Legacy/limited support	Up to 3 TiB	Up to 1,000

Recommendations:

Use Multi-AZ deployments with Provisioned IOPS for OLTP workloads requiring fast, predictable performance.
Avoid Magnetic storage due to lack of scalability and absence of elastic volume support.

Instance Lifecycle Management

Includes creation, modification, backups, maintenance, reboot, and deletion.
SQL Server Multi-AZ instances cannot be stopped.
Instances can be stopped for up to 7 days; afterwards, they auto-start.
Cannot stop instances that have or are Read Replicas.
Stopped instances cannot be modified.
Deletion requires confirmation; optional final snapshot can be taken.
Enable Deletion Protection to prevent accidental removal (default is disabled).

RDS Storage Auto Scaling

Automatically increases storage capacity as database workload grows.
Supported for MariaDB, MySQL, PostgreSQL, SQL Server, and Oracle.
Monitors actual usage to scale storage before exhaustion.
Applies to both new and existing instances with zero downtime.

Security Best Practices

Security Groups

DB Security Groups control access for EC2-Classic (non-VPC) DB instances.
VPC Security Groups manage access for VPC-based DB instances.
EC2 Security Groups regulate access to EC2 instances, which may be used with DB instances.

Recommendations

Use IAM roles and accounts dedicated for RDS management instead of root credentials.
Apply least privilege access principles using IAM groups.
Regularly rotate IAM credentials.
Restrict inbound access to DB instances via security groups.
Deploy DB instances within a VPC for fine-grained network control.
Encrypt data in transit using SSL/TLS (supported by MySQL, MariaDB, PostgreSQL, Oracle, SQL Server).
Use AWS KMS for managing encryption keys protecting data at rest.
Encryption is supported across all engines and storage types.
Encrypted instances cannot replicate to unencrypted instances, and vice versa.
Backups restored to encrypted instances must also be encrypted.
IAM database authentication is supported for MySQL and PostgreSQL.

Accessing DB Instances in a VPC

DB Instance Location	Accessible By
Inside a VPC	EC2 instances within same/different VPCs; internet clients if publicly accessible
Outside a VPC	EC2 instances or clients outside the VPC

VPC Deployment Requirements:

At least two subnets in different Availability Zones.
Enable VPC attributes: DNS hostnames and DNS resolution for public access.
Create and assign a DB subnet group.
Configure VPC security groups to allow database access.
Ensure CIDR blocks reserve spare IPs for maintenance and failover.
Option groups must match platform type (VPC vs EC2-Classic); reassign when restoring between platforms.

Tagging Amazon RDS Resources

Tags are key-value pairs assigned to RDS resources.
Useful for organizing resources and tracking costs.
Up to 50 tags can be associated with each resource.
Tag values are optional.

High Availability using Multi-AZ

Multi-AZ deployments for Oracle, PostgreSQL, MySQL, and MariaDB DB instances use Amazon’s failover technology. SQL Server DB instances use SQL Server Mirroring.
Amazon RDS for SQL Server offers Always On Availability Groups for the Multi-AZ configuration in all AWS Regions. This is available for both Standard and Enterprise editions.
You can modify a DB instance in a Single-AZ deployment to a Multi-AZ deployment.
The primary DB instance switches over automatically to the standby replica if any of the following conditions occur:
- An Availability Zone outage
- The primary DB instance fails
- The DB instance’s server type is changed
- The operating system of the DB instance is undergoing software patching
- A manual failover of the DB instance was initiated using Reboot with failover

Read Replicas

Updates made to the source DB instance are asynchronously copied to the Read Replica.
You can reduce the load on your source DB instance by routing read queries from your applications to the Read Replica.
You can elastically scale out beyond the capacity constraints of a single DB instance for read-heavy database workloads.
You can create a Read Replica that has a different storage type from the source DB instance.
A source DB instance can have cross-region Read Replicas in multiple regions. Due to the limit on the number of access control list (ACL) entries for a VPC, you cannot have more than five cross-region Read Replica instances.
PostgreSQL uses physical replication, while MySQL and MariaDB use logical replication.
You can create a manual snapshot of a PostgreSQL Read Replica, but you can’t enable automatic backups. You can enable automatic backups on a MySQL or MariaDB Read Replica.
When creating a Read Replica, consider:
- Enable automatic backups on the source DB instance by setting the backup retention period to a value other than 0.
- Automatic backups are supported only for Read Replicas running MySQL 5.6 and later.
Oracle supports Read Replicas with Active Data Guard for customers using the Bring Your Own License (BYOL) model with Oracle Database Enterprise Edition and have licensed the Active Data Guard Option. This allows offloading read workloads from the primary DB Instance and scaling reads over up to five read replicas.
You can promote a Read Replica to become a standalone DB instance.

Backups and Restores

Your DB instance must be in the ACTIVE state for automated backups to occur. Automated backups and automated snapshots don’t occur while a copy is executing in the same region for the same DB instance.
The first snapshot of a DB instance contains the data for the full DB instance. Subsequent snapshots of the same DB instance are incremental.
The default backup retention period is one day if you create the DB instance using the RDS API or the AWS CLI, or seven days if you use the AWS Console.
Manual snapshot limits are 100 per region.
You can copy a snapshot within the same AWS Region, across AWS Regions, or across AWS accounts.
When restoring a DB instance to a point in time:
- The default DB parameter and default DB security group are applied to the new DB instance.
- For Oracle DB instances, you can specify a different Oracle DB engine, license model, and DBName.
You can restore SQL Server native backups onto SQL Server DB instances that have read replicas configured.
When restoring a SQL Server DB instance to a point in time, each database within the instance is restored to a point within 1 second of each other database.
You can retain Amazon RDS automated backups (system snapshots and transaction logs) when you delete a database instance.
You can export Amazon RDS or Amazon Aurora snapshots to Amazon S3 as Apache Parquet format.

Amazon Relational Database Service Monitoring

Amazon CloudWatch
RDS Events
- An Amazon RDS event is created when the reboot is completed.
- Be notified when changes occur with a DB instance, DB snapshot, DB parameter group, or DB security group.
- Uses Amazon Simple Notification Service (SNS) for notifications.
Database log files
Enhanced Monitoring to identify OS issues for:
- MariaDB
- Microsoft SQL Server
- MySQL version 5.5 or later
- Oracle
- PostgreSQL
Enhanced monitoring is available for all DB instance classes except db.m1.small.
Enhanced monitoring metrics include:
- IOPS – number of I/O operations completed per second.
- Latency – elapsed time between submission and completion of an I/O request.
- Throughput – bytes transferred to/from disk per second.
- Queue Depth – number of I/O requests waiting in queue.
CloudWatch gathers CPU utilization metrics from the hypervisor, while Enhanced Monitoring gathers metrics from an agent on the instance.
Instance Statuses and billing:

DB Instance Status	Billed	Description
available	Billed	The instance is healthy and available.
backing-up	Billed	The instance is currently being backed up.
creating	Not billed	The instance is being created and is inaccessible during this time.
deleting	Not billed	The instance is being deleted.
failed	Not billed	The instance has failed and cannot be recovered by Amazon RDS; perform point-in-time restore instead.
maintenance	Billed	Amazon RDS is applying a scheduled maintenance update to the DB instance.
rebooting	Billed	The instance is rebooting due to customer request or RDS process.
starting	Billed for storage	The DB instance is starting.
stopped	Billed for storage	The DB instance is stopped.
stopping	Billed for storage	The DB instance is stopping.
storage-full	Billed	The instance has reached storage capacity and requires scaling up. CloudWatch alarms are recommended.

RDS Performance Insights monitors DB instance load to analyze and troubleshoot performance by visualizing database load and filtering by waits, SQL statements, hosts, or users.
AWS CloudTrail captures all API calls for RDS as events for auditing and tracking.

Amazon RDS Proxy

Amazon RDS Proxy is a fully managed, highly available database proxy for RDS that makes applications more scalable, more resilient to database failures, and more secure.
RDS Proxy allows applications to pool and share connections established with the database so you won’t have to use up your database memory and compute resources just for database connections.
RDS Proxy lets you create multiple endpoints with their own VPC settings, enabling access to your Aurora or RDS databases from applications in a different VPC.
It also lets you create a read-only endpoint to connect your applications to Amazon Aurora Replicas.

Amazon Relational Database Service Pricing

With Amazon RDS, you pay only for the RDS instances that are active.
The data transferred for cross-region replication incurs RDS data transfer charges.
Instances are billed for DB instance hours (per second), Storage (per GiB per month), I/O requests (per 1 million requests per month), Provisioned IOPS (per IOPS per month), Backup storage (per GiB per month), and Data transfer (per GB).
Amazon RDS is billed in one-second increments for database instances and attached storage. Pricing is still listed on a per-hour basis, but bills are now calculated down to the second and show usage in decimal form. There is a 10-minute minimum charge when an instance is created, restored, or started.
RDS purchasing options:
- On-Demand Instances – Pay by the hour for the DB instance hours that you use.
- Reserved Instances – Reserve a DB instance for a one-year or three-year term and receive a significant discount compared to on-demand DB instance pricing.
You are charged for using Enhanced Monitoring.

Best Practices

Monitor your memory, CPU, and storage usage.
Scale up your DB instance when you are approaching storage capacity limits.
Enable automatic backups and set the backup window to occur during the daily low in write IOPS.
If your database workload requires more I/O than you have provisioned, recovery after a failover or database failure will be slow. Increase the I/O capacity of a DB instance by:
- Migrating to a DB instance class with High I/O capacity.
- Converting from standard storage to either General Purpose or Provisioned IOPS storage, depending on the needed increase.
- If you convert to Provisioned IOPS storage, make sure to also use a DB instance class optimized for Provisioned IOPS.
- If you are already using Provisioned IOPS storage, provision additional throughput capacity.
If your client application is caching the Domain Name Service (DNS) data of your DB instances, set a time-to-live (TTL) value of less than 30 seconds.
Test failover for your DB instance.

Validate Your Knowledge

Question 1

A customer has a popular website with millions of viewers worldwide and read-heavy database workloads. Which of the following is the best option to increase the read throughput on their database?

Enable Multi-AZ deployments
Enable Amazon RDS Standby Replicas
Enable Amazon RDS Read Replicas
Use Amazon SQS to queue up the requests

Correct Answer: 3

Explanation:
Amazon RDS Read Replicas provide enhanced performance and durability by allowing you to elastically scale out beyond a single DB instance’s capacity for read-heavy workloads. You can create one or more replicas of a source DB instance to serve high-volume read traffic, increasing aggregate read throughput. Read replicas are available for MySQL, MariaDB, Oracle, PostgreSQL, and Aurora.

Multi-AZ deployments provide high availability and failover but do not increase read throughput.
Standby replicas are part of Multi-AZ and do not offload read traffic.
Amazon SQS helps manage request queues but does not directly improve database read throughput.

Question 2

A Database Specialist manages an Amazon RDS for MySQL 5.6 Multi-AZ DB instance and wants to offload read-only queries by creating an RDS read replica. After running the AWS CLI command create-db-instance-read-replica, an error occurs, and the read replica was not created. What is most likely causing the issue?

The proper AWS CLI command to create a read replica for RDS is CreateDBInstance.
The backup retention period of the RDS DB instance is 0.
The source DB instance used in the command is another read replica.
The source DB instance has to be converted to Single-AZ first.

Correct Answer: 2

Explanation:
To create a read replica, automatic backups must be enabled on the source DB instance by setting the backup retention period to a value other than 0. Without backups enabled, RDS cannot create the read replica.

The command CreateDBInstance is used for Aurora clusters, not RDS read replicas.
You can create a read replica from a single-AZ or Multi-AZ instance, so converting to Single-AZ is not required.
The source DB instance can be a read replica if it meets the requirements, but the main issue here is the backup retention period.

Amazon RDS