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 Active Secondaries allow leveraging HA/DR hardware Read Workloads Backups
Offloading read workloads Offloading backups
SQL Server 2012 HA/DR technologies Failover Cluster Instances for servers Availability Groups for groups of databases Pre-existent New Server failover Multi-database Failover Shared Storage (SAN / SMB) Direct attached storage Failover takes minutes Failover takes seconds Multi-Node Clustering Multiple Secondaries Passive secondary nodes Active. Secondaries Active
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 Replication latency No failover of read workloads
SQL Server Active Secondary Primary SQL Server Log Synchronization DB 1 Reports DB 1 DB 2 DBs in active secondaries are readable Committed transactions visible eventually (depends on log synchronization latency) Same data center: Different data center: <1 s 1 -5 s
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 ALL READ_ONLY Don’t allow connections Allow all connections Only allow connections specifying READ_ONLY intent ALTER AVAILABILITY GROUP ag_name MODIFY REPLICA ON 'server_name' WITH ( SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | ALL | READ_ONLY } ) )
Clients can connect to readable secondary without changes Set ALLOW_CONNECTIONS = ALL If possible, use 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 Endpoint for read-only connections 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 DDL is infrequent - if possible run when low activity XEvent lock_redo_blocked
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 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 Backups are 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 not online don’t meet role preference Select highest-priority replica Break ties with replica name Not enforced by BACKUP command Implemented as a system function sys. fn_hadr_backup_is_preferred_replica ('dbname') Used by Maintenance Plan Wizard Log Shipping Wizard
Store backups centrally Easier to track Use Recovery Advisor Easier to restore to point in time
demo Always. On: Active Secondaries Offloading Backups
announcing Active Secondaries in Windows Azure - Read Workloads Backups DR are not
Active Secondaries make Always. On cost-effective Use hardware for more than HA/DR: Offload work from primary Read workloads Without client changes With Application Intent: Read-only Routing (provides HA) Backups Full Backup (copy-only) and Log Backup Automation supported Windows Azure support is coming
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/
SQL Server 2012 Eval Copy Hands-On Labs mva Microsoft Virtual Academy Get Certified! @sqlserver @teched_europe #ms. Tech. Ed
Learning Connect. Share. Discuss. Microsoft Certification & Training Resources http: //europe. msteched. com www. microsoft. com/learning Tech. Net Resources for IT Professionals Resources for Developers http: //microsoft. com/technet http: //microsoft. com/msdn
Evaluations Submit your evals online http: //europe. msteched. com/sessions
- Slides: 38