What is the MOST operationally efficient solution that meets these requirements?
Deploy a MySQL primary node on Amazon EC2 in one Availability Zone. Deploy a MySQL read replica on Amazon EC2 in a different Availability Zone. Configure a scheduled scaling event to increase the CPU capacity and RAM capacity within the MySQL read replica the day before each known traffic surge. Configure a scheduled scaling event to reduce the CPU capacity and RAM capacity within the MySQL read replica the day after each known traffic surge.
Deploy an Amazon Aurora MySQL DB cluster. Select a Cross-AZ configuration with an Aurora Replica. Create an Aurora Auto Scaling policy to adjust the number of Aurora Replicas based on CPU utilization. Direct all read-only reporting traffic to the reader endpoint for the DB cluster.
Deploy an Amazon RDS for MySQL Multi-AZ database as a write database. Deploy a second RDS for MySQL Multi-AZ database that is configured as an auto scaling read-only database. Use AWS Database Migration Service (AWS DMS) to continuously replicate data from the write database to the read-only database. Direct all read-only reporting traffic to the reader endpoint for the read-only database.
Deploy an Amazon DynamoDB database. Create a DynamoDB auto scaling policy to adjust the read capacity of the database based on target utilization. Direct all read traffic and write traffic to the DynamoDB database.
Explanations:
Scaling by adjusting CPU and RAM on EC2 instances is not ideal as it requires manual intervention, could cause downtime, and doesn’t scale efficiently.
Aurora MySQL with Auto Scaling replicas provides automated scaling for read traffic, has no downtime during scaling, and uses a highly available architecture.
Using AWS DMS for replication introduces additional complexity and is not the most efficient or scalable solution for managing read traffic.
DynamoDB is not compatible with MySQL workloads and would require a complete redesign of the application to accommodate a NoSQL database.
As far as I’m aware, the answer is:
Deploy an Amazon Aurora MySQL DB cluster. Select a Cross-AZ configuration with an Aurora Replica. Create an Aurora Auto Scaling policy to adjust the number of Aurora Replicas based on CPU utilization. Direct all read-only reporting traffic to the reader endpoint for the DB cluster.