Skip to content

Latest commit

 

History

History
139 lines (131 loc) · 8.13 KB

9.1. Databases - RDS.md

File metadata and controls

139 lines (131 loc) · 8.13 KB
Raw

RDS (Relational Database Service)

  • Managed Aurora / PostgreSQL / MySQL / MariaDB / Oracle / SQL Server
  • 💡 Use cases: Relational datasets (RDBMS, OLTP), transactional SQL queries.
  • Must provision an EC2 instance & EBS Volume type and size
    • You can reserve RDS Reserved Instances from 1 to 3 years.
  • Maintenance: Auto minor version upgrade, Maintenance window (when)
  • DB parameter group acts as a container for engine configuration values that are applied to one or more DB instances.
    • Changing groups = recreate a new group & assign (you can copy existing)
  • MySQL & PostgreSQL engines
    • MyISAM (storage engine)
      • Use for intense, full-text search capability,
      • Not recommended overall
    • InnoDB is recommended (better crash recovery)
      • Compatible with Aurora
  • Monitoring dashboards
    • CloudWatch monitors metrics from its hyper-visor, very high level.
    • Enhanced Monitoring
      • Collects metrics within the agent
      • For each processes or threads use CPU, memory, disk, their virtual size etc.
      • Pros
        • smaller monitoring interval results
        • pay only for monitoring that exceeds the free tier provided by Amazon CloudWatch Logs.
      • Cons
        • Compute-intensive workload have more OS process activity to report and higher costs.

Security

  • Usually deployed within a private subnet, not in a public one.
  • Network level
    • Are into VPC
    • DB subnet group allows you to specify set of subnets in your VPC for your instances.
    • Security groups
  • Can allow public accessibility (outside VPC access with public IP)
    • Gets an endpoint like sqldb.c2bqxjtofpnk.eu-west-3.rds.amazonaws.com)
    • I.e. does not & cannot have an IP address.
  • IAM policies help control who can manage AWS RDS
  • ❗ You cannot access the SSH into underlying instances directly by design.
  • To connect:
    1. Traditional username and password
    2. IAM DB Authentication (for MySQL & Aurora)
      • Provides an IAM DB authentication.
      • Lifespan of an authentication token is 15 minutes (short-lived)
      • 💡 Recommended over username & password
      • ❗ Ensures SSL must be used when connecting to the database
  • Encryption
    • Encryption at rest capability with AWS KMS - AES-256 encryption
      • ❗ Can only be enabled when you first create the DB instance
        • 💡 You can however: unencrypted EB -> snapshot -> copy snapshot as encrypted -> create DB from snapshot
      • Transparent Data Encryption
        • TDE is an encryption type which encrypts the data and log files of a database.
          • It means encrypting databases both on the hard drive and consequently on backup media.
          • Key is stored in master database -> Protection of backup files as they don't have the DEK (database encryption key)
            • Restoring from back-up requires same DEK in the destination database.
          • Enabling TDE also encrypts your tempdb.
        • ❗ Only for Oracle or SQL Server DB instance only as its their technology.
          • Cannot be used on Postgres or MySQL but similar feature is KMS Encryption
        • TDE can be used on top of KMS - may affect performance.
    • SSL certificates to encrypt data to RDS in flight
      • 📝To enforce SSL
        • PostgreSQL: rds.force_ssl = 1 in the AWS RDS Console (Parameter Groups)
        • MySQL: Within the DB: GRANT USAGE ON *.* TO 'mysqluser'@'%' REQUIRESSL;
      • To connect using SSL
        • Provide the SSL Trust certificate (can be downloaded from AWS).
        • Provide SSL options when connecting
    • Encryption of Read Replicas
      • If in same region => encrypted using same key as the master instance
      • Different regions => you encrypt using the encryption key for that region

Scalability

  • Vertical scaling with EC2 sizing
    • ❗ Resizing makes database temporarily unavailable (generally a few minutes).
      • 💡 For minimal downtime: resize reader instance and then promote it to its own DB.
    • Occur during the maintenance window if you don't choose to apply it directly.
    • Amazon RDS Storage Types
      • General Purpose SSD: Cost effective, can burst to 3.000 for limited time.
      • Provisioned IOPS: I/O-intensive workloads, particularly database workloads, that require low I/O latency and consistent I/O throughput.
      • Magnetic: for backward compatibility, lower storage, not recommended
  • Read replicas
    • Enables horizontal read scaling.
    • Cannot be auto-scaled.
    • Replicate master to replicas asynchronously
    • Each read replica will have separate DNS endpoint.
    • Improves read performance
    • Within AZ, Cross AZ or Cross Region
    • Replicas can be promoted to their own DB -> breaks replication with the read replica.
    • Can have own Multi-AZ copies.
    • MySQL & MariaDB read replicas can have read replicas
    • You can migrate to Aurora by creating Aurora Read Replica (single click).
    • ❗ Must enable back-ups to enable read replica (not Aurora).
      • The replica is built by 1st restoring from the backup, and then only replicating blocks / record of changes since the backup was taken
    • ❗ You can create up to 5 read replicas
    • ❗ Not available for SQL server.
    • ❗ SELECT (=read) only kind of statements (not INSERT, UPDATE, DELETE)
    • ❗ Read replicas does not help with disaster recovery
      • You cannot failover to a read replica, use Multi-AZ instead.
  • Auto-scaling
    • For horizontal scaling: Not available for read replicas
    • For vertical scaling: RDS Storage Auto Scaling automatically scales storage capacity in response to growing database workloads, with zero downtime.

Availability

  • Point in Time Restore: Continuous backups and restore to specific timestamp
    • RDS Backups: Automatically enabled
      • Automated backups
        • Daily full snapshot of the database
        • Capture transaction logs in real time -> ability to restore to any point in time by applying transaction logs to the latest snapshot
        • Retention between 1 to 35 (❗ max) days (default: 7 days)
        • 💡 If you want to copy database: Restore from a snapshot, the database will have schemas and ready & will be quicker than big create query.
      • DB Snapshots
        • Manually triggered by the user
        • Retention of backup for as long as you want
        • When you delete an instance you can choose whether to create a final snapshot of the DB instance.
          • 💡 Should be set as default with CloudFormation.
      • ❗ I/O may be briefly suspended while the backup process initializes (typically under a few seconds), and you may experience a brief period of elevated latency.
      • Restored DBs will always be a new RDS instance with a new DNS endpoint.
      • You can restore up to the last 5 minutes.
  • Multi AZ

    • Seamless sync replication of master DB to standby replica in another AZ

      • You cannot read from it, for it see read replicas.

    • One DNS name - automatic app failover to standby

    • Not used for scaling but for DR.

    • 📝 When rebooting you can select to fail-over to other AZ.

    • Multi AZ vs Read Replicas

      Attribute Multi-AZ Deployments Read Replicas
      Replication Synchronous replication, Durable Asynchronous replication, Scalable
      Accessibility 👎 Only primary instance is active 👍 Accessible and can be used for read scaling
      Backups 👍 Automated backups are taken from standby (no I/O on primary) 👎 No backups configured by default
      Zone Always span two Availability Zones within a single Region Can be within an Availability Zone, Cross-AZ, or Cross-Region
      DR 👍 Automatic failover to standby when a problem is detected 👎 Can be manually promoted to a standalone database instance

    • Single AZ -> Multi AZ

      • AWS does: snapshot primary instance, create new standby instance from snapshot in different AZ, configure synchronous replication between primary and snapshot.
  • Backtrack rewinds the DB cluster to the time you specify
    • Does not replace point-of-time restore to back-ups
    • You can easily undo mistakes.
  • Multi regions for DR (Disaster Recovery).