Automatic Failover in AWS RDS
-
Databases might fail due to lot of reasons, when they fail applications will be down
-
To recover from failure quickly, the following options
- create a new database from the backup of existing database
- Rather than have one database (One Server) have it on multiple servers and enable replication. When the primary database fails DBA will do failover
-
In AWS RDS can be created with Automatic failover i.e. when the db fails aws will do automatic failover
-
In AWS, if we want automatic failover create rds with multi-az deployments
-
Multi Az deployment creates
- A Primary DB
- A Stand by DB
-
When we do operations they will be routed to primary db and the changes will be synchronously replicated to stand by
-
When primary db fails AWS automatically fails over & promotes the standby db to primary & the endpoint will point to the new primary db
-
To create rds with multi-az it takes approx 20 mins
-
Backups of the database are taken from stand by DB this enhances performance
-
Subnet group is choosing the vpc and subnets from different az’s where you want to deploy your rds
-
Every rds instance will be connected to some security group. so lets create a security group for mysql (3306)
-
Create an rds instance with multi-az deployment (Console)
-
Multi Az Deployments are available for all the database offering with an exception to
- SQL Server Express Edition
- SQL Server Web Edition
-
Multi Az is available for SQL Server Standard & Enterprise Editions.
-
Create an rds instance with multi-az deployment (CLI)
aws rds create-db-instance --db-name 'qtmrsfromcli' --db-instance-identifier 'qtmrsinstfromcli' --allocated-storage 20 --engine mysql --master-username 'root' --master-user-password 'rootroot' --vpc-security-group-ids 'sg-02d8c507bacba5dca' --availability-zone 'us-west-2a' --db-subnet-group-name 'mydbsubnet' --backup-retention-period 0 --port 3306 --multi-az --no-auto-minor-version-upgrade
Create Read Replication
- Consider you have a database which is used by applications to perform CRUD operations.
- Scenario 1:
- Most of the time applications/BI tools are using retrieve operations and this is slowing down your db. So you are looking for a cheaper solution (not multi az) to redirect read queries
- Scenario 2:
- Your organization application & database are hosted in US and the head quarters where the BI applications run is in singapore. The BI applications are having latency issues to query the data from us.
- IN AWS We have a concept of Read replication which can help the above scenarios to be resolved.
- Next steps:
- Understanding Read Replications
- Creating a backup & Restoring from backup
- Understanding Aurora Databases
