RDS Flashcards
RDS allows you to
create databases in the cloud that are managed by AWS
- Postgres
- MySQL
- MariaDB
- Oracle
- Microsoft SQL Server
- Aurora (AWS proprietary)
Advantages over using RDS versus deploying DB on EC2
- automated provisioning
- OS patches
- coninuous backups and restore to specific timestamp (Point in time restore)
- monitoring dashboards
- read replicas for improved read performance
- multi AZ setup for disaster recovery
- maintenance windows for upgrades
- scaling capability (vertical and horizontal)
- storage backed by EBS (GP2 or IO1)
what you can’t do with RDS as opposed to deploying DB on EC2
you can’t SSH into your instances
RDS Backups
- are automatically enabled in RDS
2. Automated backups and / or DB Snapshots
RDS Automated backups
- daily full backup for the database - during the maintenance window configured by user
- transaction logs are backed up by RDS every 5 minutes
- therefore you can restore to any point in time (from oldest backup to 5 mins ago)
- 7 days retention (can be increased to 35)
DB shapshots
- are manually triggered by user
2. retention of backup for as long as you want
if you want to speed up reads from your RDS DB instance
You can reduce the load on your primary 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.
RDS Read Replicas how many
up to 5 read replicas
RDS Read Replicas and AZ
can be
- within AZ
- cross AZ
- cross Region
Replication is
ASYNC, so reads are eventually consistent
Replica Lag
Each Read Replica publishes a Replica Lag metric in Amazon CloudWatch to allow you to see how far it has fallen behind the source DB Instance.
how to deal with eventual consistency
DO NOT send SELECT queries to a read slave if the data needs to be immediately available.
You should structure your application such that all real-time requests hit your master, and all other requests hit one of your read slaves.
For things where you don’t need real-time results, you can fool the user quite well using something like AJAX requests or websockets (websockets is going to make your application a lot more resource friendly as you won’t be hammering your backend servers with multiple AJAX requests).
why would you promote a replica to stand-alone DB?
You can use read replica promotion as a data recovery scheme if the primary DB instance fails.
But be aware of the ramifications and limitations of asynchronous replication.
To promote a replica for data recovery
- create a read replica and then monitor the primary DB instance for failures.
In the event of a failure, do the following:
- Promote the read replica.
- Direct database traffic to the promoted DB instance.
- Create a replacement read replica with the promoted DB instance as its source.
Replica traffic
The primary DB instance is the only copy of the database that can accept both read/write traffic; the read replica can only accept read-only traffic.
use case for RDS read replica
you have a production database taking on a normal load
you want to a reporting application to run some analytics
In order to avoid extra load on the main database, you create a read replica and run this new workload on it
Network costs
if your main database is in one AZ and your replica - in another one - there will be a network cost for ASYNC replication of the data. Because when the data is transfered between AZs - there is always a price to pay
So to reduce the costs we can place both the main DB and the replica in one AZ, then we are not charged for the transfer
RDS Mutli AZ purpose
disaster recovery in cases of
- loss of AZ
- loss of network
- instance or storage failure
increase availability
but NOT for scalability
RDS Mutli AZ one DNS name
we have a synchronous replica in another AZ. Automatic failover: App is automatically recovered from failure by redirecting traffic to the standby replica.
no manual intervention necessary
RDS Multi AZ and replicas
read replicas can be set up as Multi AZ for Disaster Recovery
2 types of RDS encryption
- at rest encryption
2. in-flight encryption
at rest encryption
you can use AWS KMS customer master key (CMK) which is AES-256 encryption (symmetric block)
has to be configured at launch time
You don’t need to modify your database client applications to use encryption. Amazon RDS handles authentication of access and decryption of your data transparently with a minimal impact on performance.
at rest encryption - if master not encrypted
read replicas cannot be encrypted
TDE
Transparent Data Encryption is avaialble for Oracle and SQL Server
using TDE and encryption at rest simultaneously might slightly affect the performance of your database. You must manage different keys for each encryption method.
in-flight encryption
SSL certificates to encrypt data to RDS in flight
provide SSL options with trust certificate when connecting to database
in-flight encryption - to enforce SSL for Postgres SQL
in AWS RDS console set
rds.force_ssl = I
in-flight encryption - to enforce SSL for MySQL
within DB
GRANT USAGE ON . TO ‘user’@’%’ REQUIRE SSL
KMS (Key management service)
To manage the customer master keys (CMKs) used for encrypting and decrypting your Amazon RDS resources
AWS KMS combines secure, highly available hardware and software to provide a key management system scaled for the cloud. Using AWS KMS, you can create CMKs and define the policies that control how these CMKs can be used. AWS KMS supports CloudTrail, so you can audit CMK usage to verify that CMKs are being used appropriately. You can use your CMKs with Amazon RDS and supported AWS services such as Amazon S3, Amazon EBS, and Amazon Redshift.
For an Amazon RDS encrypted DB instance, all
logs, backups, and snapshots are encrypted.
Once you have created an encrypted DB instance,
you can’t change the CMK used by that DB instance.
If Amazon RDS loses access to the CMK for a DB instance
then the encrypted DB instance goes into a terminal state. In this case, you can only restore the DB instance from a backup. We strongly recommend that you always enable backups for encrypted DB instances to guard against the loss of encrypted data in your databases.
Snapshots of un-encrypted RDS databases
are un-encrypted
snapshots of encrypeted RDS databases
are encrypted
to encrypte an un-encrypted RDS database
- create a snapshot of the un-encrypted database
- copy the snapshot and enable encryption for the snapshot
- restore the database from the encrypted snapshot
- migrate applications to the new database
- delete the old database
RDS Security
- Network Security
2. Access Management
RDS Network Security
- RDS databases are usually deployed within a private subnet, not in a public one
- Security groups (same concept as EC2) leverage which IPs can communicate with RDS - so it’s our responsibility to check the inbound rules in DB’S security group (ports, IP, SG)
RDS Access Management
- IAM policies help control who can manage AWS RDS through RDS API
- Username and Pwd can be used to login into the DB
- IAM-based authentication can be used to login into RDS MySQL and PostgreSQL
IAM database athentication works with
MySQL and PostgreSQL
you don’t need a PWD, just an authentication token with a lifetime of 15 minutes
IAM database athentication - how does it work
- We have our EC2 security group and EC2 instance. The instance has an IAM role and thanks to it the instance is able to issue an API call to the RDS service to get back an authentication token
- on the other end - we have MySQL RDS database in RDS Security Group
- The EC2 instance is going to pass the token while connecting to the database and make sure the connection is encrypted
IAM database athentication benefits
- Network in/out must be encrypted using SSL
- IAM manages centrally users instead of managing users from within the database (central authorization)
- you can leverage IAM roles and EC2 instance profiles for easy integration
AWS responsibility regarding your RDS instance
- no SSH access
- no manual DB patching necessary
- no manual OS patching
- no way to audit the underlying instance
My company would like to have a MySQL database internally that is going to be available even in case of a disaster in the AWS Cloud. I should setup
Multi AZ
Our RDS database struggles to keep up with the demand of the users from our website. Our million users mostly read news, and we don’t post news very often. Which solution is NOT adapted to this problem?
RDS Multi AZ
ElastiCache and RDS Read Replicas do indeed help with scaling reads.
We have setup read replicas on our RDS database, but our users are complaining that upon updating their social media posts, they do not see the update right away
Read Replicas have asynchronous replication and therefore it is likely our users will only observe eventual consistency
Which RDS Classic (not Aurora) feature does not require us to change our SQL connection string?
Multi AZ keeps the same connection string regardless of which database is up. Read Replicas imply we need to reference them individually in our application as each read replica will have its own DNS name
You want to ensure your Redis cluster will always be available
enable multi AZ
Your application functions on an ASG behind an ALB. Users have to constantly log back in and you’d rather not enable stickiness on your ALB as you fear it will overload some servers. What should you do?
Storing Session Data in ElastiCache is a common pattern to ensuring different instances can retrieve your user’s state if needed.
One analytics application is currently performing its queries against your main production database. These queries slow down the database which impacts the main user experience. What should you do to improve the situation?
Read Replicas will help as our analytics application can now perform queries against it, and these queries won’t impact the main production database.
You would like to ensure you have a database available in another region if a disaster happens to your main region. Which database do you recommend?
Aurora Global Databases allow you to have cross region replication
Your company has a production Node.js application that is using RDS MySQL 5.6 as its data backend. A new application programmed in Java will perform some heavy analytics workload to create a dashboard, on a regular hourly basis. You want to the final solution to minimize costs and have minimal disruption on the production application, what should you do?
this will minimize cost because the data won’t have to move across AZ
You would like to create a disaster recovery strategy for your RDS PostgreSQL database so that in case of a regional outage, a database can be quickly made available for Read and Write workload in another region. The DR database must be highly available. What do you recommend?
create a Read Replica in a different region and enable multi-AZ on the main database
You are managing a PostgreSQL database and for security reasons, you would like to ensure users are authenticated using short-lived credentials. What do you suggest doing?
Use PostgreSQL for RDS and authenticate using a token obtained through the RDS service
In this case, IAM is leveraged to obtain the RDS service token, so this is the IAM authentication use case.
An application is running in production, using an Aurora database as its backend. Your development team would like to run a version of the application in a scaled-down application, but still, be able to perform some heavy workload on a need-basis. Most of the time, the application will be unused. Your CIO has tasked you with helping the team while minimizing costs. What do you suggest?
Aurora Serverless
