RDS (Relational Database)

Basics

Easy to setup, operate and scale common RDB engines on AWS cloud.

Environment:

  • On cloud

  • On premises - RDS on VMWare

Feature & Benefit:

  • Flexible (not like bare metal, CPU/memory/IO etc. easy to scale up & down)

  • Common administration tasks (backup, patching software, encryption, etc.) managed

  • NO shell access to DB instances & restrict certain system procedures & table access

  • High Availability

  • IAM & VPC help security

DB Instance

Instance name:

Will be part of DNS name

DB name

See doc

Engine, Edition and Version

  1. MariaDB

  2. Microsoft SQL Server

  3. MySQL

  4. Oracle

  5. PostgreSQL

  6. Amazon Aurora (in separate documentation)

DB Instance class

Determines CPU, memory

Storage

Type: Magnetic, SSD, Provisioned IOPS

Size:

  • important to provide sufficient storage

  • auto-scaling can be enabled (threshold can be set)

Endpoint

Network address to access DB instance

VPC

  • Cloud instance must choose a VPC at creation, and cannot change VPC afterwards

  • Default VPC - already supports RDB

    • when first creates an DB instance, creates the default DB subnet group

  • User created VPC:

    • must has a "corresponding DB Subnet Group"

    • newly created VPC - can create new, requires minimum two subnets covering 2 AZs

  • VPC Security Group

    • must provide or create upon DB instance creation

    • just the same, standard 'EC2-VPC' security group

      • If created for the new instance, initially only accept connection from creater's IP on the connection port

  • DB Instance IP address

    • Check the created network interface in VPC service, also can configure IP address

  • No VPC option (not recommended)

    • Security group will be created


DB parameter group

Controls behavior

Options Group

To control optional functions (encryption, memcache, etc.)

Deployment

Multi-AZ deployment (see HA)


Access & Operation

AWS Console, CLI, API & SDK

Manage instance. Console - from "RDS", does not show up in EC2 console.

Modification of DB Instance

Most modification can be applied immediately or deferred to next maintenance (put in a pending change queue). Some (change parameter group) require manual reboot.

Some result in an outage - for the reboot.

Maintenance

AWS do maintenance (patch software & OS, etc.). Some require db go offline for short time.

"Maintenance" column in RDB console can show:

  • required – will be applied, cannot defer

  • available – available but not automatically, do it manually

  • next window – (can be deferred)

  • In progress

Can view pending maintenance item in "Maintenance & Backup" tab in console.

Maintenance window can be set, indicate when to start maintenance - but not how long it lasts.

Multi-AZ maintenance:

  • Maintain stand-by

  • Promote stand-by to primary

  • Maintain old primary

Upgrade Engine Version

Major version upgrade - must do manually and can introduce breaking change

Minor version upgrade - can do manually or automatically

Rename Instance

Endpoint will change. Old DNS name will be deleted.

Read replica still associated.

Metric & events - associate by name (so may be associated with another if old name reused)

Tag & snapshot retained.

Use case:

  • promote Read Replica

  • restore data from snapshot

  • PITR (point in time restoration)

Step:

  • stop traffic to old

  • rename old

  • rename another to use the now available name

  • re-associate read replica etc.

Reboot

For multi-AZ, can reboot with fail-over (can use as a simulation of failure for testing)

Stop

Cannot stop for more than 7 days (to keep maintenance). During stop, instance time not charged, storage still charged.

Limitation:

  • Cannot stop that has a Read Replica, or that is a Read Replica

  • Cannot stop SQL Server DB instance in a Multi-AZ configuration

  • Can't modify a stopped DB instance.

  • Can't delete an option group that is associated with a stopped DB instance.

  • You can't delete a DB parameter group that is associated with a stopped DB instance.

Delete

Can choose creating a Final Snapshot and Retaining Automated Backups

High Availability

Multi-AZ aka Primary Instance & a synchronous Secondary Instances (Fail Over)

Multi-AZ deployment:

  • Automatically run a secondary standby DB instance in a different AZ

  • Synchronously replicated

  • Benefit: DB redundancy, fail over, eliminate IO freeze, minimize latency spike during backup

Set a DNS time-to-live (TTL) value of less than 30 seconds. Because the underlying IP address of a DB instance can change after a failover.

Replicas to increase read scaling

Proxy (preview)

Security

DB security group:

  • controls access to a DB instance that is not in a VPC

VPC security group:

  • Controls access to a DB instance inside a VPC

EC2 security group:

  • controls access to an EC2 instance and can be used with a DB instance.

DB Authentication:

  • Password authentication - traditional

  • Password & IAM authentication:

    • for MySQL and PostgreSQL

    • do not use password, use an authentication token (15min life)

    • Benefit:

      • SSL for networking

      • Use IAM to centrally manage access

      • Use same credential for EC2, greater security

Read replica

Can be in same or different region (a secure communication channel is set when cross region).

Usage scenario:

  • Scaling

  • Serving read while main instance not available (maintenance etc.)

  • Different scenario - business reporting, data warehousing...

  • Disaster recovery


Backup & Restore

Snapshot

  • Creation

    • Automatic / manual

  • Restore - create new instance

  • Copy - to new snapshot, same region or cross region, add/remove encryption

  • Share - with other accounts (private) or public

  • Migrate - to other engine (Aurora)

  • Delete

Backup

Retention period

Backup Window (time)

Monitoring

CloudWatch:

  • performance & health

  • subscribe events

Charge

Limits

40 instances (soft)

10 for each SQL Server edition (Enterprise, Standard, Web, and Express) under the "license-included" model

40 for MySQL, MariaDB, or PostgreSQL

40 for Oracle under the "bring-your-own-license" (BYOL) licensing model


Backup / restore