AWS Series — Relational Database in AWS
Database is the heart of many applications. Relational Database is important among them which exists from almost 40 years.
The Data in the database are organized into tables. It is like a traditional spreadsheet. The Rows are the data items. The Columns are the fields in the database.
In AWS, we have 6 different relational database engines which are SQL Server, PostgreSQL, Oracle, MariaDB, MySQL, Amazon Aurora.
Advantages of RDS —
- It is up and running in minutes
- It can also run in multiple Availability Zones.
- It has failover capability
- It also has Automated backups
- A manual install in our own datacenter sometimes could takes days
When would we use an RDS Database?
RDS is generally used for online transaction processing (OLTP) workloads.
What is OLTP?
Processes data from transactions in real time ( e.g., customer orders, banking transactions, payments and booking systems)
It is all about data processing and completing large number of small transactions in real time.
What is OLAP?
Online Analytical Processing — Processes complex queries to analyze historical data ( e.g., analyzing net profit figures from the past 3 years and sales forecasting).
OLAP is all about data analysis using large amounts of data, as well as complex queries that take a long time to complete.
Examples of OLAP are —
It makes sense to store customer orders in a relational database like OLAP. We can run the analysis on this data like —
- Suppose we have to produce a report comparing net profits for car sales in 3 different regions.
- We have to go through large amounts of data like sum of cars sold in each region, look at Unit cost for each region, Sales price of each cat and Sales price compared to the unit cost. Do the math to get the net profits.
RDS is not suitable for analyzing large amounts of data. Use a data warehouse like RedShift, which is optimized for OLAP.
Multi-AZ RDS
With Multi-AZ, RDS creates an exact copy of your production database in another availability zone.
Suppose we have multiple servers requesting data in the Primary database and secondary database is on standby which has the exact replica of production database and is done automatically. This is the network diagram where we have a load balancer which moves the traffic to 3 servers and all of them are connected to Primary database which is in us-east-1a region and the replica is created automatically in us-east-1b.
AWS handles replication for us. When we write to our production database, this write will automatically synchronize to the standby database.
Which RDS Types can be configured as Multi-AZ?
As multi-AZ, We have got SQL Server, MySQL, MariaDB, Oracle and Postgres SQL. Basically all 6 RDS databases including Aurora can be configured as multi AZ, but these 5 can be configured as single instances. We cannot have Aurora in single Availability Zone.
If there is unplanned maintenance or failures?
The servers connects to the RDS instance using the database address. If there is a connection failure, which means the us-east-1a fails, the connection still remains same for us because amazon handles all the DNS for that database address. So if the us-east-1a fails, the Amazon detects that and keep the web address same as we point to, but internally they redirect the request to the secondary or standby database. This is the failover mechanism of the RDS database. This is all automated by the amazon.
RDS will automatically failover to the standby during a failure so database operations can resume quickly without administrative intervention.
This Multi AZ is only for Disaster Recovery and does not improve any performance. So we cannot connect to the secondary database when the primary is active. AWS now offers Multi-AZ deployment clusters, which allows 2 readable standby instances.
How do we provision RDS database in the AWS Console?
Login to your AWS Management Console and search for RDS or Go to Database Services > RDS
Now click on Create Database.
We have got 2 different types of databases → Standard Create and Easy Create
We can do standard create so we can see different options. We can now find Engine options which shows the type of database you would like to create.
As of now selecting MySQL. We can choose the Engine versions
Then select the templates. You can select Production or Dev/Test. If you want to try this database you can go for Free tier
We have Availability and Durability where you can have the number of database instances based on our need. If we have a workload that you think 1 database cannot handle, then go for Multi AZ DB cluster which gives 3 readable standby DB instances. If you just need basic Disaster Recovery and your data is important, you can choose Multi-AZ DB instance. If you think your data is not so important and can be reproduced, then go for Single DB instance.
Next, we can provide the database name and the user name. We can also use AWS Secret Manager to mange your credentials. You can also choose Auto generated password so that it generates strong password.
Next comes the instance configurations which has Standard Class and Memory Optimized classes.
Then comes storage where we have Provisioned IOPS SSD selected by default. We can choose what kind of storage we wanted.
Next we can choose Connectivity to the EC2 instance if we already have one or choose don’t connect and connect later. Also you can choose VPC cloud and subnet.
Then you can choose if you want to connet to your database as public or you would like to connect via EC2 instance.
We can also choose security group. We can choose existing or create new
You can create with all the default settings. If you choose production and not free tier. It is very expensive.
Create the database. Now the database is created and it suggests for some addons. We can choose if we want. but remember it will have additional cost involved.
Create the database
Click on the database you just created and go to Actions. You would be able to see the options that are enabled for the database.
Summary —
- We have RDS Data Types like SQL Server, Oracle, MySQL, PostgreSQL, MariaDB and Amazon Aurora
- RDS is for OLTP workloads — Great for processing lots of transactions, like customer orders, banking transactions, payments and booking systems
- Not Suitable for OLAP workloads — Use Redshift for data warehousing and OLAP tasks, like analyzing large amounts of data, reporting and sales forecasting.
Archives
To know more about EBS Storage , you can read the below article—
AWS Series — What is Elastic Block Store?
To know more about VPC , you can go through this article —
AWS Series — Virtual Private Cloud: An Intranet of AWS
Happy Learning!!
