AWS Certified Database – Specialty — Question 169

A company is migrating an IBM Informix database to a Multi-AZ deployment of Amazon RDS for SQL Server with Always On Availability Groups (AGs). SQL
Server Agent jobs on the Always On AG listener run at 5-minute intervals to synchronize data between the Informix database and the SQL Server database. Users experience hours of stale data after a successful failover to the secondary node with minimal latency.
What should a database specialist do to ensure that users see recent data after a failover?

Answer options

• A. Set TTL to less than 30 seconds for cached DNS values on the Always On AG listener.
• B. Break up large transactions into multiple smaller transactions that complete in less than 5 minutes.
• C. Set the databases on the secondary node to read-only mode.
• D. Create the SQL Server Agent jobs on the secondary node from a script when the secondary node takes over after a failure.

Correct answer: A

Explanation

Setting the TTL to less than 30 seconds for cached DNS values on the Always On AG listener allows clients to quickly recognize the new primary node after a failover, reducing the duration of stale data visibility. Breaking up large transactions into smaller ones does not address the issue of data visibility after failover. Setting the databases on the secondary node to read-only mode would prevent updates from being processed, which is counterproductive. Creating SQL Server Agent jobs from a script on the secondary node does not resolve the immediate issue of stale data post-failover.