AWS: RDS (Relational Database Service)

Manual Backup (Snapshots)

Are not used for point in time recovery

Are user initiated, not RDS service initiated

Stored in Amazon S3

They are not deleted automatically when you delete your RDS instance, rather, they wills tay on S3 until you go ahead and delete them.

It is recommended to take a final snapshot before deleting your RDS DB instance

• It comes in handy if you want to restore your DB instance in the future.

Can be shared with other AWS accounts directly.

DB Automated Backups- Restore/Recovery

You can specify a point-in-time restore to any given second during the retention period.

When you initiate a point-in-time recovery, transaction logs are applied to the most appropriate daily backup to restore your DB to that point-in-time.

When you restore a DB instance, only the default DB parameters & security groups are associated with the restored instance.

• Once the restore is complete, you need to associate/apply the customer DB parameters and security group settings.

Restoring from a DB snapshots

You can NOT restore from a DB snapshot into an existing DB instance, rather it has to create a new DB instance

Then new DB instance will have a new endpoint.

Restoring from a backup or a DB snapshot changes the RDS instance endpoint

A new DB instance will be created when you perform a restore

• This new DB instance have a new RDS endpoint.

You can change the storage type (magnetic,provisioned IOPS, General purpose) during a restore process.

Multi-AZ RDS-Event Notifications

You will be alerted by a DB instance event when a failover occurs.

AWS RDS uses AWS SNS to send RDS event via SNS notifications

• You can use API calls to the AWS RDS service to list the RDS events in the past 14 days (Describe Events API)
• You can view past 14 days events using CLI
• Using AWS Console, you can only view RDS events for the last 1 days (24 hours)

Processes that happen on Standby first

The following procedures are done on standby first, then on primary

• OS patching
• System upgrades
• DB Scaling

In Multi-AZ, Snapshots and Automated backups are done on Standby instance to avoid I/O suspension on Primary instance.

RDS Multi-AZ deployment-Maintenance

The sequence is done as follows:

Maintenance on Standby is performed

Standby promoted to primary

Maintenance performed on old primary (current standby)

Version Upgrade

You can manually upgrade a DB instance to a supported DB engine version from AWS console as follows:

• RDS> DB instances> Modify instance> Set DB Engine version

By default, Changes will take effect during the next maintenance window

OR

You can force an immediate upgrade if you need to

In Multi-AZ deployments:

• Version upgrade will be conducted on both primary and Standby at the same time, which will cause an outage (unavailable for both)
• Advised to do it during change/maintenance windows.

Security Groups and N ACLs

In a Multi-AZ scenario

Make sure that your security groups and NACLs will allow your APP servers to communicate with both primary and standby instances.

In case of a fail over the standby becomes the primary.

Encryption of Existing Unencrypted RDS Instances

You can not encrypt an existing un-encrypted DB instance

To do that, you need,

Create a new, encrypted instance and migrate your data to it (from the un-encrypted to the encrypted)

OR

Restore from a backup/snapshot into a new encrypted RDS instance.

App to DB Instance encryption

RDS supports SSL encryption for communication between the App Instances and the RDS DB instances

RDS generates a certificated for the instance which is used to encrypt this communication.

RDS service supports encryption at rest for all DB engines using AWS KMS.

RDS DB Encryption at Rest

For an encrypted at Rest DB instance:

• All its snapshots
• Backups
• Data storage
• Read Replicas created from the DB instance

Are all encrypted as well

Encryption and decryption is handled transparently

RDS DB Instance_Security Best Practices

Use AWS IAM accounts to control access to RDS API actions

Assign an individual IAM account to each person who manages RDS resources

Grant the least permissions required by each user to perform the assigned duties

Use IAM groups to manage/grant permissions to multiple users at one time.

Rotate your IAM credentials regularly

Read Replicas

When the required read I/O capacity is reached but still more I/O capacity is required for heavy/intensive read applications, read replicas can come in handy.

Read replica is a replica of the primary RDS DB instance, but they can only be used for read actions.

Primary DB instance becomes the source of the application to this read replica.

• The data is first written to the source, primary DB instance, then using asynchronous replication, it gets replicated to the read replica.

Multi-AZ with read replicas can be combined in one deployment

• However, It is not possible to create Multi-AZ for your read replicas (i.e created standby read replica DB instances for your read replica instances)

Read replicas can be created from the console or API

Automatic backups (retention period not zero) must be enabled and remain enabled for read replicas to work.

Is supported with transnational DB storage engines, and are supported on InnoDB engines not MyISAM (MyMAPo):

• MySQL
• MariaDB
• PostgreSQL
• Oracle

Each of these DB engines support up to 5 read replicas per source/primary DB instance

Read Replicas-Use cases

Shifting read intensive applications such as Business (or sales) reporting, or Data warehousing to read from read replicas as opposed to overload the primary DB.

Scaling beyond the I/O capacity of your main DB instance for read heavy workloads.

Service read traffic while the source is unavailable.

Creating Read Replicas

It can be done from API or AWS console

The AZ where you want the read replica to be can be specified

The read replicas storage type or instance class can be different from the source DB instance

DB engine type can not be change though, it is inherited from the source (primary) DB instance.

If you scale the source DB instance, you should also scale the Read Replicas.

You cannot have more than four instances involved in a replication chain

If the source instance of a Multi-AZ deployment fails over to the secondary, any associates Read Replicas are switched to use the secondary as their replication source

You must explicitly delete Read Replicas, using the same mechanisms for deleting a DB instance.

If you delete the source DB instance without deleting the replicas, ecah replica is promoted to a stand-alone, single AZ DB instance.

If you promote a MySQL or Maria-DB Read Replica that is in turn replicating to other Read Replicas, those Read Replicas remain active.

If replication is stopped for more than 30 consecutive days, either manually or due to a replication error, Amazon RDS terminates replication between the master DB instance and all Read Replicas.

RDS Billing- Standalone DB Instance

No up front costs

You Pay for:

• DB instance hours (partial hours charged as full hours)
• Storage GB/mo
• I/O requests/mo -for Magnetic RDS storage instance only
• Provisioned IPS/mo. — For RDS Provisioned IOPS SSD instance
• Internet data transfer
• Backup Storage (DB backups, and Active manual Snapshots) :

This increases by increasing DB backups retention period

Backup storage for automated RDS backups (not the manual snapshots) up to the provisioned RDS instance’s EBS volume size (EBS volume) is free of charge.

RDS Billing

The DB storage provisioned to your DB instance is in a single AWS AZ.

Since AWS backups your DB to multiple AZs for data durability

• AWS charges for the database storage above the free allocated DB storage for your instance
• Free automated backup storage provided to the client/account is in single AZ.

AWS will charge for the following (in addition to the single AZ DB instance charges)

• Multi-AZ DB hours
• Provisioned Storage (Multi-AZ)
• Double write I/Os (writing to the Active/Primary, and Replication from primary to Standby)

You are not charged for DB data transfer during replication from primary to standby

Your DB storage does not change between standalone and Multi-AZ deployments (same DB and same AWS storage volume for that DB in multiple AZs for durability)

RDS Storage Scaling

You can scale an RDS storage up only, you can not decrease or scale down the storage size

• You can NOT decrease the allocated storage for an RDS instance

You can scale storage and also change storage type for all supported DB engines, except MS SQL server

Scaling: You can scale (UP only, not down) the compute and storage capacity of your existing RDS DB instances

Scaling storage can happen while the RDS instance is still running, available/ accessible

• This may cause some performance degradation during the change, but will result in enhanced I/O after

Scaling compute will cause a downtime to your DB instance

You do the required scaling changes, then you can

Immediately have the changes take effect,

OR

Leave it to the default, which will make the changes take effect during the Maintenance window.

Scaling- MS SQL Server

You can NOT change the storage capacity nor type of storage of the MS SQL Server on windows-based RDS instances

• This is due to extensibility limitations of striped storage attached to windows server.

To work around the baove, you can take a snapshot, use it to start a new DB instance with the desired storage type and capacity (must be an increase, not a decrease)

• Note that a new instance, will have a new RDS endpoint.

Scaling beyond RDS instance capabilities

If you hit the largest RDS DB instance, and you still need to scale, you can:

Use portioning and split your RDS DB over multiple RDS instances.

Cloud Trail

Cloud trail logs all API calls to your RDS DB instances

Finally done with RDS and Amazon Aurora will be in my next blog.

Happy Weekend!

Bharathi.