DBI 312 SQL Server Always On Active Secondaries

DBI 312 SQL Server Always. On: Active Secondaries Luis Vargas Program Manager Microsoft Corporation

Understand how Always. On (Availability Groups) Active Secondaries allow leveraging HA/DR hardware Read Workloads Backups

Offloading read workloads Offloading backups

Always. On Active Secondaries allow using hardware for more than HA/DR: Offload work from primary Read workloads Backups

Active Secondaries Offloading Read Workloads

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

SQL Server Primary Active Secondary SQL Server Log Synchronization DB 1 DB 2 Reports DB 1 DBs in active secondaries are readable Committed transactions are visible eventually When depends on log synchronization latency DB 2

SQL Server DB 1 Up to 4 secondaries Sync or async DB 2 Primary

SQL Server Secondary Primary Manual Failover Secondary Primary SQL Server Log Synchronization DB 1 DB 2 Reports

NO READ_ONLY ALL Don’t allow connections Only allow connections specifying READ_ONLY intent Allow all connections ALTER AVAILABILITY GROUP ag_name MODIFY REPLICA ON 'server_instance' WITH ( SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } ) )

Clients can connect to readable secondary without changes Set ALLOW_CONNECTIONS = ALL Application Intent New connection string property Specifies workload type: READ_ONLY / READ_WRITE Opens new capabilities Disallow connections Automatically route READ_ONLY connections to an active secondary

Configure Routing URL for each secondary ALTER AVAILABILITY GROUP ag_name MODIFY REPLICA ON 'server_name' WITH ( SECONDARY_ROLE ( READ_ONLY_ROUTING_URL = ‘TCP: //system-address: port’ ) )

ALTER AVAILABILITY GROUP ag_name MODIFY REPLICA ON 'server_name' WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST = {'server_name' [, . . n]} ) )

demo Always. On: Active Secondaries Offloading Read Workloads

Mitigations: Use Resource Governor No workload on auto failover target

Contention between Redo and read workload avoided internally Workload executed on Snapshot Isolation Locking hints ignored Redo thread never a deadlock victim DDL Redo can still be blocked by read workload XEvent lock_redo_blocked

Row versions must be created on the active secondary Stored in Temp. DB 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)

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 Temp. DB sys. stats: is_temporary=‘true’ Use most recent statistics Remove on failover, restart, or DROP STATISTICS

Active Secondaries Offloading Backups

Database Mirroring R/W workload Failed Backups Primary Only on primary Impacts primary workload Fails after failover Secondary

R/W workload Backups Active Secondary Backups Primary Active Secondary On primary or any secondary No need to configure secondaries as readable

Backup on sync or async secondaries Full Backup (Copy-Only) Log Backup automation No Differential Backup

Full Backup (Copy-Only) and Log Backup on secondary If Differential Backup is needed: Full Backup and Differential Backup on primary Log Backup on secondary

Role Preference Primary Only Secondary If Possible None Replica Priority 0 (never). . . 100 (highest)

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

Store backups centrally Use Recovery Advisor

demo Always. On: Active Secondaries Offloading Backups

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

Always. On Resource Center http: //msdn. microsoft. com/en-us/sqlserver/gg 490638. aspx Always. On Team Blog http: //blogs. msdn. com/b/sqlalwayson/ SQL Server 2012 Whitepapers http: //msdn. microsoft. com/en-us/library/hh 403491

luis. vargas@microsoft. com social. msdn. microsoft. com/Forums/en-gb/sqlhadr/

mva

Complete an evaluation on Comm. Net and enter to win!

Scan the Tag to evaluate this session now on my. Tech. Ed Mobile