4
AlwaysOn Active Secondaries allow using hardware for more than HA/DR: Offload work from primary Read workloads Backups

5
Active Secondaries Offloading Read Workloads

6
Database Mirroring Transactional Replication Run on primary Impacts workload Run on mirror w/snapshots Data Staleness Additional management No failover of read workloads Run on a subscriber Pros: Large number of subscribers Targeted indexes Cons: Separate solution to configure/manage No failover of read workloads

18
Row versions must be created on the active secondary Stored in TempDB 14 bytes needed for version pointer Primary and secondary must have same physical structure If secondary is configured as active: 14 bytes added to rows on the primary (already if SI/RCSI enabled)

19
Query optimization relies on statistics Created by indexes and read workloads Statistics created on primary are redone on secondary But, read workloads at secondaries are different from primary workloads So, auto-create statistics on secondary Store on TempDB sys.stats: is_temporary=‘true’ Use most recent statistics Remove on failover, restart, or DROP STATISTICS

27
Filter out replicas that are not online don’t meet role preference Select highest-priority replica Replica name as tie-breaker Not enforced by BACKUP Implemented as a system function sys.fn_hadr_backup_is_preferred_replica ('dbname') Used by Maintenance Plan Wizard Log Shipping Wizard

28
Use Recovery Advisor Store backups centrally

29
demo AlwaysOn: Active Secondaries Offloading Backups

30
Active Secondaries make AlwaysOn cost-effective Use hardware for more than HA/DR: Offload work from primary Read workloads Backups