AWS DB Services RDS - devian-al/AWS-Solutions-Architect-Prep GitHub Wiki

Relational Database Service Simplified

RDS is a managed service that makes it easy to set up, operate, and scale a relational database in AWS. It provides cost-efficient and resizable capacity while automating or outsourcing time-consuming administration tasks such as hardware provisioning, database setup, patching and backups.

RDS Key Details

  • RDS comes in six different flavors
    • SQL Server
    • Oracle
    • MySQL Server
    • PostgreSQL
    • MariaDB
    • Aurora
  • Think of RDS as the DB engine in which various DBs sit on top of.
  • RDS has two key features when scaling out
    • Read replication for improved performance
    • Multi-AZ for high availability
  • In the database world, Online Transaction Processing (OLTP) differs from Online Analytical Processing (OLAP) in terms of the type of querying that you would do. OLTP serves up data for business logic that ultimately composes the core functioning of your platform or application. OLAP is to gain insights into the data that you have stored in order to make better strategic decisions as a company.
  • RDS runs on virtual machines, but you do not have access to those machines. You cannot SSH into an RDS instance so therefore you cannot patch the OS. This means that AWS is responsible for the security and maintenance of RDS. You can provision an EC2 instance as a database if you need or want to manage the underlying server yourself, but not with an RDS engine.
  • Just because you cannot access the VM directly, it does not mean that RDS is serverless. There is Aurora serverless however (explained below) which serves a niche purpose.
  • SQS queues can be used to store pending database writes if your application is struggling under a high write load. These writes can then be added to the database when the database is ready to process them. Adding more IOPS will also help, but this alone will not wholly eliminate the chance of writes being lost. A queue however ensures that writes to the DB do not become lost.

RDS Multi-AZ

  • Disaster recovery in AWS always looks to ensure standby copies of resources are maintained in a separate geographical area.
  • When you provision a Multi-AZ DB Instance, Amazon RDS automatically creates a primary DB instance and synchronously replicates the data to a standby instance in a different Availability Zone (AZ).

    Each AZ runs on its own physically distinct, independent infrastructure, and is engineered to be highly reliable.

  • With a Multi-AZ configuration, EC2 connects to its RDS data store using a DNS address masked as a connection string.

    If the primary DB fails, Multi-AZ is smart enough to detect that failure and automatically update the DNS address to point at the secondary (CNAME is Flipped). No manual intervention is required and AWS takes care of swapping the IP address in DNS.

  • Multi-AZ is supported for all DB flavors except aurora. This is because Aurora is completely fault-tolerant on its own.
  • Multi-AZ feature allows for HA across availability zones and not regions.

    During a failover, the recovered former primary becomes the new secondary and the promoted secondary becomes primary. Once the original DB is recovered, there will be a sync process kicked off where the two DBs mirror each other once to sync up on the new data that the failed former primary might have missed out on.

  • You can force a failover for a Multi-AZ setup by rebooting the primary instance
  • With a Multi-AZ RDS configuration, backups are taken from the standby.

RDS Read Replicas

  • Read Replication is exclusively used for performance enhancement.
  • With a Read Replica configuration, EC2 connects to the RDS backend using a DNS address and every write that is received by the master database is also passed onto a DB secondary so that it becomes a perfect copy of the master. This has the overall effect of reducing the number of transactions on the master because the secondary DBs can be queried for the same data.

However, if the master DB were to fail, there is no automatic failover. You would have to manually create a new connection string to sync with one of the read replicas so that it becomes a master on its own. Then you’d have to update your EC2 instances to point at the read replica. You can have up to five copies of your master DB with read replication.

  • You can promote read replicas to be their very own production database if needed.
  • Read replicas are supported for all six flavors of DB on top of RDS.
  • Each Read Replica will have its own DNS endpoint.
  • Automated backups must be enabled in order to use read replicas.
  • You can have read replicas with Multi-AZ turned on or have the read replica in an entirely separate region. You can even have read replicas of read replicas, but watch out for latency or replication lag.

The caveat for Read Replicas is that they are subject to small amounts of replication lag. This is because they might be missing some of the latest transactions as they are not updated as quickly as primaries. Application designers need to consider which queries have tolerance to slightly stale data. Those queries should be executed on the read replica, while those demanding completely up-to-date data should run on the primary node.

Multi-AZ Deployments Read Replicas
Synchronous replication - highly durable Asynchronous replication - highly durable
Only DB engine on primary DB is active All read replicas are accessible and can be used for read scaling
Automated backups are taken from standby no backups configured by default
Always span 2 AZ with single Region can be within an AZ, cross-AZ or cross -Region
DB engine version upgrades happen on primary DB engine version upgrade is independent from source instance
Automatic failover to standby when a problem is detected can be manually promoted to a standalone DB instance

RDS Backups

  • When it comes to RDS, there are two kinds of backups
    • automated backups
    • database snapshots

Automated backups

  • allow you to recover your database to any point in time within a retention period (between one and 35 days).
  • Automated backups will take a full daily snapshot and will also store transaction logs throughout the day.
  • When you perform a DB recovery, RDS will first choose the most recent daily backup and apply the relevant transaction logs from that day.
  • Within the set retention period, this gives you the ability to do a point in time recovery down to the precise second.

Automated backups are enabled by default. The backup data is stored freely up to the size of your actual database (so for every GB saved in RDS, that same amount will freely be stored in S3 up until the GB limit of the DB). Backups are taken within a defined window so latency might go up as storage I/O is suspended in order for the data to be backed up.

  • With automated backups, when DB is deleted the backed up data in S3 is wiped clean along with the RDS engine. This is why you are asked if you want to take a final snapshot of your DB when you go to delete it

DB snapshots

  • are done manually by the administrator.
  • 100 per region
  • A key difference from automated backups is that they are/can retained even after the original RDS instance is terminated.

When you go to restore a DB via automated backups or DB snapshots, the result is the provisioning of an entirely new RDS instance with its own DB endpoint in order to be reached.

RDS Security

  • You can authenticate to your DB instance using IAM database authentication. IAM DB authentication works with MySQL and PostgreSQL
  • With this authentication method, you don't need to use a password when you connect to a DB instance. Instead, you use an authentication token.
    • An authentication token is a unique string that Amazon RDS generates on request
    • Authentication tokens have a lifetime of 15 minutes. You don't need to store user credentials in the database because authentication is managed externally using IAM.

  • IAM database authentication provides the following benefits

    • Network traffic to and from the database is encrypted using Secure Sockets Layer (SSL).
    • You can use IAM to centrally manage access to your database resources, instead of managing access individually on each DB instance.
    • For applications running on Amazon EC2, you can use profile credentials specific to your EC2 instance to access your database instead of a password, for greater security
  • Encryption at rest is supported for all six flavors of DB for RDS.
    • Encryption is done using the AWS KMS service.
    • Once the RDS instance is encryption enabled, the data in the DB becomes encrypted as well as all backups (automated or snapshots) and read replicas
  • After your data is encrypted, Amazon RDS handles authentication of access and decryption of your data transparently with a minimal impact on performance. You don't need to modify your database client applications to use encryption.
  • Amazon RDS encryption is currently available for all database engines and storage types. However, you need to ensure that the underlying instance type supports DB encryption.

    • You can only enable encryption for an Amazon RDS DB instance when you create it, not after the DB instance is created and DB instances that are encrypted can't be modified to disable encryption.

RDS Enhanced Monitoring

  • RDS comes with an Enhanced Monitoring feature.
    • Amazon RDS provides metrics in real time for the operating system (OS) that your DB instance runs on.
    • You can view the metrics for your DB instance using the console, or consume the Enhanced Monitoring JSON output from CloudWatch Logs in a monitoring system of your choice.
    • By default, Enhanced Monitoring metrics are stored in the CloudWatch Logs for 30 days. To modify the amount of time the metrics are stored in the CloudWatch Logs, change the retention for the RDS OS Metrics log group in the CloudWatch console.

Take note that there are key differences between CloudWatch and Enhanced Monitoring Metrics.

CloudWatch gathers metrics about CPU utilization from the hypervisor for a DB instance, and Enhanced Monitoring gathers its metrics from an agent on the instance. As a result, you might find differences between the measurements, because the hypervisor layer performs a small amount of work that can be picked up and interpreted as part of the metric.