RDS Monitoring, RDS Snapshots and Failover – A Deeper Dive into AWS RDS

There’s a lot more to AWS RDS than simply spinning up a multi-AZ RDS instance and connecting to it.

DB (Manual) Snapshots versus Automated Snapshots

You have the option to manually snapshot your database. Usually, for scheduled maintenance upgrades etc. This is different from the automatic snapshot – and will not contain transaction logs (of up to 5 minutes) as the automatic snapshot will. Hence, point in time restores are only possible with automated snapshots (and that too, up to 5 minutes of your last transaction).

Can Invididual Databases Be Restored?

No. The entire instance needs to be restored

Restoring from a snapshot

You get a brand new instance. The 3 things that change – Security Group, DB Parameter Group and IP Address (use DNS Name to connect to your DB Instance as the underlying IP address can change – that’s why we needed a DB Subnet group to include two AZ subnets – when we originally built the instance).

• Make sure you keep a copy of the parameter groups for any DB snapshots you have so that you can associate a restored instance with the correct parameter file.
• The SG will be still available (something like a prod-rds-sg that you may have previously created). Remember that, by default, the default DB parameter and default security groups are associated with the restored instance.
• To restore the functionality of the DB instance to that of the DB instance that the snapshot was created from, you must modify the DB instance to use the security group and parameter group used by the previous DB instance. These changes must be manually applied using the RDS console’s Modify command, the ModifyDBInstance API, or the aws rds modify-db-instance command line tool, once the DB instance is available.

Read Replica versus Multi AZ Deployment

• Multi AZ is for failover. It is a full replica of your primary RDS zone instance.  Database (MySQL, MariaDB, Oracle, and PostgreSQL) engines utilize synchronous physical replication to keep data on the standby up-to-date with the primary.
• The SQL Server engine use synchronous logical replication to achieve the same result, employing SQL Server-native Mirroring technology.
• Read Replicas are different. They use async replication and can be cross region as well. They require a full blown license to have a full read replica available.

The Real Reason you should use a Multi-AZ Deployment – Automatic failover

• If a storage volume on your primary instance fails in a Multi-AZ deployment, Amazon RDS automatically initiates a failover to the up-to-date standby (or to a replica in the case of Amazon Aurora).
• Compare this to a Single-AZ deployment: in case of a Single-AZ database failure, a user-initiated point-in-time-restore operation will be required. Single AZ Failover operation can take several hours to complete, and any data updates that occurred after the latest restorable time (typically within the last five minutes) will not be available.
• Multi AZ Deployments cause more $ (see appendix C below), but you DO NOT need a second license, since, at a time, only a SINGLE licensed database is up and running.

Read Replica – Finer Points

Read Replicas are provided for most db engines ( MySQL, MariaDB, PostgreSQL, Oracle, and SQL Server database engines). AWS RDS creates a second DB instance using a snapshot of the source DB instance.

• It uses native engine asynchronous replication to update the read replica whenever there is a change to the source DB instance.
• The read replica operates as a DB instance that allows only read-only connections; applications can connect to a read replica just as they would to any DB instance.
• AWS RDS replicates all databases in the source DB instance.

Monitoring RDS

Monitoring is a constant, ongoing activity on your aws resource.

There are 5 broad tools for RDS specific monitoring (see appendix A below). Some are alert capable (2 out of 5). The other 3 will provide dashboards that can be viewed.

While each tool is important in it’s own right, the log files (and cloudwatch alarms) as well as the RDS Events (and event notification) are the more frequently used ones.

Alerting (Event Notification Subscription) on Snapshot Failure

SNS supports various forms of notifications such as email, a text message, or a call to an HTTP endpoint.

Cloudwatch Log Based RDS Alarms

Let us say you wanted to be alerted when the average deadlocks exceeded 1 deadlock (every 5 minute for an evaluation period of 15 minutes (3 periods of 5 minutes each).

Other Thresholds of interest

• Average Deadlocks
• Snapshot Storage Used > Pre Set GB Limit
• Sum of Login Failures (to database) exceeds one

 

Enhanced Monitoring showing OS Processes 

Enhanced Monitoring is used to see real time OS level metrics and processes on the OS level. It is a dropdown option available from the monitoring tab on AWS RDS.

RDS Event Subscriptions

Instance Events of Interest – Low Storage, Instance Backup, Instance Creation, Instance deletion, Configuration Change

Parameter Group Events of Interest – Configuration Change

Snapshot Events of Interest – Creation, deletion, notification, restoration

AWS Schema Conversion Tool

This is always your FIRST step when moving data over to RDS. When you fire up this tool, chances are you will encounter a) high memory usage b) 100% CPU usage…To ensure that SCT is not starved for resources, try the tips below (Note that both 1 and 2 need to be done in conjunction. If you just increase the JVM memory size but have only a large instance, you will still encounter memory and CPU issues).

1. First and Foremost – ensure that you INCREASE The jvm’s memory (see section below)
2. Ensure that you are using at least a 2X large (8vCPUs and 32GB RAM). Stop your instance –> Actions –> Instance Settings –> Change Instance Size (select 2x large)
3. Source –> The privileges required for Oracle as a source are listed following: • CONNECT • SELECT_CATALOG_ROLE • SELECT ANY DICTIONARY • SELECT on SYS.USER$ TO <sct_user>

Increasing SCT’s JVM memory

For converting large database schemas, for example a database with 3,500 stored procedures, you can configure the amount of memory available to the AWS Schema Conversion Tool.

[JVMUserOptions]
-Xmx48960m 
-Xms4096m 

Summary

There’s a lot more to RDS than simply spinning up a multi-AZ instance and connecting to it. RDS Monitoring has five main tools – two of which allow alerts to be sent to your email, SMS or any SNS topic. In effect, the monitoring allows an on premises DBA to have as many of the options as they are used to (for e.g. seeing OS level Processes).

Creating Snapshots has two flavors – manual and automated. You would mostly want automated snapshots turned on.

Read replicas are different from multi AZ deployments, and require additional licensing for the read only instance. Multi AZ Deployments are primarily for failover and would result in automatic DNS Resolution (as will any recovery from a snapshot).

Hopefully, this helps with some of the common questions customers have when moving to AWS RDS.

Need help with your next steps? Ready to start a conversation?

Anuj Varma has helped create Cloud Centers of Excellence for PepsiCo, the State of Texas, Shell and other Fortune 100 Companies.

Set up a 1 on 1 appointment with Anuj to assist with your cloud journey.

Appendix A  – Automated Monitoring

• Amazon RDS Events – Subscribe to Amazon RDS events to be notified when changes occur with a DB instance, DB snapshot, DB parameter group, or DB security group. For more information, see Using Amazon RDS Event Notification.
• Database log files – View, download, or watch database log files using the Amazon RDS console or Amazon RDS API operations. You can also query some database log files that are loaded into database tables. For more information, see Amazon RDS Database Log Files.
• Amazon RDS Enhanced Monitoring — Look at metrics in real time for the operating system. For more information, see Enhanced Monitoring.
• Amazon RDS Performance Insights — Assess the load on your database, and determine when and where to take action. For more information, see Using Amazon RDS Performance Insights.
• Amazon RDS Recommendations — Look at automated recommendations for database resources, such as DB instances, read replicas, and DB parameter groups. For more information, see Using Amazon RDS Recommendations.

Appendix B – RDS Recommendations

You can find examples of these recommendations in the following table.

Appendix C – Multi AZ RDS Deployment Cost – No additional license is required except your single BYOL

Table 1: Amazon RDS for MYSQL and Oracle BYOL On-Demand Multi-AZ Deployment Prices for M1.Small DB Instance

Region Old Price New Price Savings US East (Northern Virginia) $0.180 $0.153 15% US West (Northern California) $0.230 $0.167 27% US West (Oregon) $0.180 $0.153 15% AWS GovCloud (US) $0.240 $0.187 22% Europe (Ireland) $0.230 $0.167 27% Asia Pacific (Singapore) $0.230 $0.196 15% Asia Pacific (Tokyo) $0.240 $0.204 15% Asia Pacific (Sydney) $0.230 $0.196 15% South America (Sao Paulo) $0.300 $0.204 32%

Need an experienced AWS/GCP/Azure Professional to help out with your Public Cloud Strategy? Set up a time with Anuj Varma.