Which solution will accomplish this?
Configure a latency-based Amazon Route 53 CNAME with health checks so it points to both the primary and replica endpoints. Subscribe an Amazon SNS topic to Amazon RDS failure notifications from AWS CloudTrail and use that topic to invoke an AWS Lambda function that will promote the replica instance as the primary.
Create an Aurora custom endpoint to point to the primary database instance. Configure the application to use this endpoint. Configure AWS CloudTrail to run an AWS Lambda function to promote the replica instance and modify the custom endpoint to point to the newly promoted instance.
Create an AWS Lambda function to modify the application’s AWS CloudFormation template to promote the replica, apply the template to update the stack, and point the application to the newly promoted instance. Create an Amazon CloudWatch alarm to invoke this Lambda function after the failure event occurs.
Store the Aurora endpoint in AWS Systems Manager Parameter Store. Create an Amazon EventBridge event that detects the database failure and runs an AWS Lambda function to promote the replica instance and update the endpoint URL stored in AWS Systems Manager Parameter Store. Code the application to reload the endpoint from Parameter Store if a database connection fails.
Explanations:
Although Route 53 can be used with health checks, AWS CloudTrail is not the correct service for RDS failure notifications. RDS events should be used instead. Furthermore, automatically invoking a Lambda function based on CloudTrail events to promote the replica is not the ideal way to handle this.
CloudTrail is not used for handling RDS failures or promoting replicas. Custom endpoints can be created, but they cannot automatically handle failover. Lambda functions triggered by CloudTrail are also not the right approach.
While modifying the CloudFormation template via Lambda could automate actions, it is overly complicated and inefficient for handling database failover. AWS already provides mechanisms to promote read replicas and modify endpoints directly.
This option uses AWS Systems Manager Parameter Store to store the database endpoint, and an EventBridge event is correctly used to detect a failure. The Lambda function promotes the replica and updates the endpoint, and the application can reload the endpoint from Parameter Store, ensuring smooth failover.
I think the answer is:
Store the Aurora endpoint in AWS Systems Manager Parameter Store. Create an Amazon EventBridge event that detects the database failure and runs an AWS Lambda function to promote the replica instance and update the endpoint URL stored in AWS Systems Manager Parameter Store. Code the application to reload the endpoint from Parameter Store if a database connection fails.