SAA-C03: AWS Database Services

Relational Databases

contains at least one table, which you can visualize as a spreadsheet with columns and rows.

In a relational database, columns can be called…rows can be called…

Columns - attributes

Rows - records or tuples

Before adding data to a relational database table, you must…

predefine each column’s name and what data types it can accept.

TRUE or FALSE: Columns are ordered, and you can’t change the order after you create the table

True. Think of when you created the metrics table for CloudWatch in Athena. I kept deleting and recreating the table.

Flexible queries

The major advantage of relational databases. Allows you to craft queries to get the data you want, the way you want as long as the data is consistent in the database table.

TRUE or FALSE: You can’t add more columns to a table after creating it.

FALSE - you can add more columns, you just can’t mess with the order of the columns.

NOTE: deleting a column will also delete all the data in said column.

TRUE or FALSE: Creating parent-child tables are considered best practice

TRUE - creating these relationships allows you to organize the data to avoid duplications and keeps querying fast. Having one table with all the data, slows the querying process.

foreign key constraints

This is how the database knows how the columns are related in different tables

What language is used to store and query data?

SQL

online Transaction Processing (OLTP)

suited to application that read and write data frequently, on the order of multiple times per second

Online Analytic Processing (OLAP)

Optimized for complex queries against large data sets.

Amazon Relational Database Service (RDS)

managed database service that lets you run relational database systems in the cloud

RDS vs. EC2

AWS fully manages the RDS database instances and you can’t establish an SSH session

Database Engines

Software that stores, organizes and retrieves data in a database

Types of database engines (6)

1. MySQL

2. MariaDB

3. Oracle

4. PostgreSQL

5. Amazon Aurora

6. Microsoft SQL Server

MySQL

designed for OLTP applications such as blogs and e-commerce

MariaDB

drop-in binary replacement for MySQL. supports the XtraDB and InnoDB storage engines

Oracle

most widely deployed relational database management systems

PostgreSQL

good choice when you have in-house applications that were developed for Oracle but want to keep costs down. (what crosslink uses)

Amazon Aurora

offers better write performance than both by using a virtualized storage layer that reduces the number of writes to the storage

Microsoft SQL Server

migrate an existing SQL Server database from an on-premises deployment to RDS without having to perform any database upgrades

TRUE or FALSE: Only Oracle databases offer the bring your own license model.

TRUE. MariaDB and MYSQL offer the license included platformm

Database Instance Classes

• Standard

• Memory Optimized

• Burstable Performance

• Storage

Standard Database Instance

The all-around database:

• 512 GB of Memory

• 128 vCPU

• 40 Gbps network bandwidth

• 50,000 mbps disk throughput

Memory Optimized database

Databases that have hefty performance requirements which provides more memory which results in faster query times.

• 3904 GB

• 128 vCPU

• 25 Gbps network bandwidth

• 14,000 mbps disk throughput

Burstable Performance Database

Used for development, testing and other nonproduction databases.

Read Replicas

Scaling your database vertically or horizontally depending on bottleneck

How many read replicas can you have for RDS and Aurora?

RDS - 5

Aurora - 15

multi-AZ deployment

Deploying your PRIMARY database in multiple regions to ensure high availability

Causes for database instance outage

• Availability zone outage

• Changing a database instance type

• Patching of the instance’s operating system (AWS doing or System Manager)

Best practice to enable Multi-AZ RDS

Do it in a maintenance window (Xlink has a document for all IRS maintenance windows)

Two options with Multi-AZ with Aurora

• Single-Master

• Multi-Master

Aurora Single-Master

cluster that consists of a primary instance with replicas. They all share the same single cluster volume.

Aurora Multi-Master

all instances can write to the database. Because there are multiple instances, there’s no failover.

RDS Backup and Recovery

Take EBS volume snapshots of your database instances and stored in multiple zone.

Metrics to consider with backup and recovery

Recovery time objective(RTO) - maximum acceptable time to recover data and resume processing

Recovery point objective(RPO) - maximum period of acceptable data loss

Who is responsible for maintenance of RDS patching and upgrades?

AWS

RDS Maintenance items include…

operating system security and reliability patches

Amazon RDS Proxy

proxies connections between your applications and database instances

Amazon Redshift

Amazon’s managed data warehouse service. It’s not a part of RDS

Types of Redshift Compute Nodes

• dense compute nodes - store up of 326 TB of data

• leader node - coordinate communication among the compute nodes as well as communicate with clients

Redshift Spectrum

service that allows you to query data from filed stored in S3 without having to import the data into your cluster.

AWS Database Migration Service

automatically copy an existing database and its schema to another database

Nonrelational Databases (NoSQL)

consistently handle tens of thousands of transactions per second. Although it can store relational data it is optimized for unstructured data.

Types of Nonrelational Databases

• key/value stores

• document-oriented stores

• graph databases (Neptune)

DynamoDB

managed nonrelational database that can handle thousands of reads and writes per second via partitions over multiple availability zones

Partition and Hash keys

the primary key and value data type in DynamoDB (Ex: First name(key)/Last name(value)

hot partitions

when a there’s a lot of read or write activity occurring against the same partition which can negatively affect performance

Three types of attributes (DynamoDB)

• Scalar

• Set

• Document

Scalar data type

can only have one value (string, number, binary, Boolean, and null)

Set Data Type

holds an unordered list of scalar values

Document data type

designed to hold different types of data that fall outside the constraints of scalar and set data types.

Throughput Capacity options (DynamoDB)

on-demand - for when you don’t know what the workload demand is

provisioned - specify the number of reads and writes per second your application will require

What TWO issues do secondary tables solve?

• lets you look up data by attribute

• you get to choose what gets copied to the secondary table from the base table

Global Secondary Index(GSI)

A secondary table that does NOT have the same partition and hash keys as the base table

Local Secondary Index(LSI)

must be created at the same time as the base table and everything is the same locally

Global Tables

improves availability by replicating tables across multiple regions. To use Global Tables Auto scaling MUST be enabled.

TRUE or FALSE: You get unlimited backups and can restore a backup to the same region or a different region than the table from which the backup was taken.

TRUE