Question

You manage a three-node Always On availability group on Azure SQL Server VMs. The primary replica hosts a high-throughput OLTP workload, and business users complain that nightly full backups cause CPU and I/O contention on the primary. You want to offload backup operations away from the primary while still meeting your recovery requirements. The availability group has two synchronous secondary replicas configured for automatic failover. What should you configure?

Accepted Answer

Set the availability group backup preference to 'Prefer Secondary' and configure backup priority values on the replicas, then schedule backup jobs on all replicas using a script that checks sys.fn_hadr_backup_is_preferred_replica

Answer

Set the backup preference to 'Primary' and reschedule the backups to run during a lower-activity maintenance window on the primary replica

Answer

Remove one secondary replica from the availability group and run standalone full backups against it independently

Answer

Configure copy-only backups on the primary replica only, since transaction log backups cannot be taken on secondary replicas