As an aspiring AWS Certified Database Specialist, I've spent a considerable amount of time diving into the extensive world of AWS Relational Database Services (RDS). The certification demands a deep understanding of AWS RDS, encompassing the various database engines it supports, their distinct features, capabilities, and how they can be optimally used to meet specific application needs.

Yet, as I ventured further into my studies, I quickly realized that grasping the full range of offerings across the different flavors of RDS was no small feat. The variety is astounding - MySQL, MariaDB, PostgreSQL, Oracle, and SQL Server, each with its own set of strengths and functionalities. It became apparent that a comprehensive comparison of these systems, highlighting what each one can do, would be an invaluable resource not just for me, but for anyone on a similar path.

This sparked the idea to create this blog post, a concentrated effort to demystify the capabilities of the different RDS systems. The aim was to dissect the key aspects of each RDS flavor, focusing on categories like Backup, Storage, Security, Read Replica, Monitoring, and others, and present a clear, concise, and useful comparison that could serve as a go-to guide for AWS RDS.

Breakdown

General

The above table gives a quick comparison of the AWS RDS offerings, indicating whether they support Parameter Group (PG) and Option Group (OG).

Availability

The above table showcases the high availability options supported by each RDS engine.

Automatic Backup, Manual Backup and Multi-AZ

All systems in RDS support automatic backup, manual backup (Snapshots) and Multi-AZ for additional availability.

Database Mirroring and Always on for SQL Server

Amazon RDS supports Multi-AZ deployments for Microsoft SQL Server by using either SQL Server Database Mirroring (DBM) or Always On Availability Groups (AGs).

When setting up SQL Server Multi-AZ in RDS, it automatically configures all databases on the instance to use DBM or AGs. Amazon RDS manages the primary, the witness, and the secondary DB instance for you. Because the configuration is automatic, RDS selects DBM or Always On AGs based on the version of SQL Server that you deploy.

Refer to the following official documentation for more details:

• Amazon RDS - SQL Server Multi-AZ

Security

Using SSL on MySQL and MariaDB

Both MySQL and MariaDB support the use of SSL/TLS connections for specific user accounts. For example, you can use one of the following statements:

ALTER USER 'encrypted_user'@'%' REQUIRE SSL;

Users can then connect over SSL with the following commands:

• MySQL 5.7+:

mysql -h mysql–instance1.123456789012.us-east-1.rds.amazonaws.com --ssl-ca=global-bundle.pem --ssl-mode=VERIFY_IDENTITY -P 3306 -u myadmin -p

• MySQL < 5.7:

mysql -h mysql–instance1.123456789012.us-east-1.rds.amazonaws.com --ssl-ca=global-bundle.pem --ssl-verify-server-cert -P 3306 -u myadmin -p

• MariaDB:

mysql -h mysql–instance1.123456789012.us-east-1.rds.amazonaws.com --ssl-ca=global-bundle.pem --ssl-mode=REQUIRED -P 3306 -u myadmin -p

Refer to the official documentation for more details:

• MariaDB SSL connections

• MySQL SSL connections

Using SSL on PostgreSQL and SQL Server

For PostgreSQL and SQL Server, you can set rds.force_ssl to 1 in the parameter group to require SSL. By default, this setting is off (0) in SQL Server and PostgreSQL versions below 15. It's on (1) in PostgreSQL version 15 and above.

Refer to the official documentation for more details:

• PostgreSQL SSL

• SQL Server SSL

Using SSL on Oracle

To enable SSL encryption for an Oracle DB instance, add the Oracle SSL option to the option group associated with the DB instance.

Refer to the official documentation for more details:

• Oracle SSL

Using Transparent Data Encryption on SQL Server and Oracle

Amazon RDS supports TDE to encrypt stored data on your DB instances running Microsoft SQL Server or Oracle. TDE automatically encrypts data before it is written to storage and automatically decrypts data when it is read from storage.

Refer to the official documentation for more details:

• SQL Server TDE

• Oracle TDE

Read Replica

Second Tier Read Replica for MySQL

By creating a second-tier Read Replica, you can potentially distribute some of the replication load from the master database instance to a first-tier Read Replica.

AWS RDS allows the creation of 5 first-tier read replicas. Each read replica can create another 5 replicas. Thus, a total of 30 replicas can exist with one master using this feature. However, note that second-tier read replicas may experience higher replication lag.

Refer to this article for more details.

Cross Region Read Replica

AWS RDS can create cross-region read replicas on any system. Notably, SQL Server began supporting cross-region read replicas recently.

Refer to this document for more details.

Write on Read Replica in MySQL and MariaDB

To enable write operations on read replicas in MySQL and MariaDB, set the read_only parameter to false for the DB parameter group associated with your DB instance. Other Amazon RDS engines, such as Amazon Aurora, do not permit the modification of the read_only parameter.

Refer to this article for more details.

External Database Read Replica with MySQL and MariaDB

Replication can be set up between an RDS for MySQL or MariaDB DB instance and a MySQL or MariaDB instance that is external to Amazon RDS using binary log file replication.

Refer to this document for more details.

Backup on Read Replica in MySQL and MariaDB

Automatic backups and manual snapshots are supported on RDS for MySQL, RDS for Oracle or RDS for MariaDB read replicas.

For RDS for PostgreSQL, you can create a manual snapshot of read replicas. However, automated backups for read replicas are only supported for RDS for PostgreSQL 14.1 and higher versions. If you're using RDS for PostgreSQL versions earlier than 14.1 and want a backup, create a snapshot from a read replica.

Refer to this document for more details.

Monitoring

Common Metrics and Enhanced Monitoring

Check the available Cloudwatch Metrics here.

Notes on Enhanced Monitoring:

• Currently, Physical Devices graphs are not available for Microsoft SQL Server DB instances.

• Currently, viewing OS metrics for a Multi-AZ standby replica is not supported for MariaDB or Microsoft SQL Server DB instances.

Event Notifications

RDS Event Notifications resources include:

• DB instance

• DB snapshot

• DB parameter group

• DB security group

• RDS Proxy

• Custom engine version

RDS Recommendations

Amazon RDS provides automated recommendations for database resources such as DB instances, read replicas, and DB parameter groups. These recommendations offer best practice guidance by analyzing DB instance configuration, usage, and performance data.

Read more here.

AWS Trusted Advisor

From the Trusted Advisor dashboard, you can review the following cost optimization, security, fault tolerance, and performance improvement checks:

• Amazon RDS Idle DB Instances

• Amazon RDS Security Group Access Risk

• Amazon RDS Backups

• Amazon RDS Multi-AZ

Read more here.

CloudWatch Application Insight

When you add your applications to Amazon CloudWatch Application Insights, it scans the resources in the applications and recommends and configures metrics and logs on CloudWatch for application components. Example application components include SQL Server backend databases and Microsoft IIS/Web tiers.

Watch this video for more information.

Read more here.

Others

• Oracle Does not yet support RAC

Conclusion

We have examined various features of popular relational database services (RDBS) including MySQL, MariaDB, PostgreSQL, Oracle, and SQL Server within Amazon's RDS framework. These databases each possess unique strengths and offer different capabilities that can be leveraged depending on the specific requirements of your application. With careful consideration of these features, you can select the RDBS that best fits your needs, thus maximizing performance, cost-efficiency, and overall productivity.

Remember that, as cloud technologies and AWS services continue to evolve, the features and capabilities of these RDBS could change over time. Hence, it is crucial to keep up-to-date with the latest AWS documentation, updates, and best practice guidelines to ensure the optimal use of the selected RDBS.

Disclaimer

This document is intended as a general guide to the features of relational database services within AWS RDS as of the time of writing in July 2023. While every effort has been made to ensure accuracy, the rapidly evolving nature of cloud services means that some information may become outdated or inaccurate over time.

The document does not constitute professional advice, and decisions should not be made solely based on this content. It is recommended that readers refer to the latest official AWS documentation, or consult with an AWS certified professional or the AWS support team for the most accurate and up-to-date information.

Feel free to correct me if I made any mistakes :)