1 / 38

SQL Server AlwaysOn : Active Secondaries

DBI312. SQL Server AlwaysOn : Active Secondaries. Luis Vargas Program Manager Microsoft Corporation. Session Objectives. Understand how AlwaysOn Active Secondaries allow leveraging HA/DR hardware Read Workloads Backups. Understand capabilities , limitations, and considerations.

natania
Download Presentation

SQL Server AlwaysOn : Active Secondaries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DBI312 SQL Server AlwaysOn: Active Secondaries Luis Vargas Program Manager Microsoft Corporation

  2. Session Objectives • Understand how AlwaysOnActive Secondaries allow leveraging HA/DR hardware • Read Workloads • Backups • Understand capabilities, limitations, and considerations

  3. Agenda • AlwaysOn • AlwaysOn Active Secondaries • Offloading read workloads Offloading backups

  4. AlwaysOn SQL Server 2012 HA/DR technologies Failover Cluster Instances for servers Failover Cluster Instances for servers Availability Groups for groups of databases Availability Groups for groups of databases • Pre-existent • Server failover • Shared Storage (SAN / SMB) • Failover takes minutes • Multi-Node Clustering • Passive secondary nodes • New • Multi-database Failover • Direct attached storage • Failover takes seconds • Multiple Secondaries • Active Secondaries Active Secondaries

  5. Example Availability Group Topology

  6. AlwaysOnActive Secondaries • Cost-effectiveness is critical • Idle hardware is wasted • Database mirror server similar to primary • Utilization is low (~5% CPU, 25% I/O) • Active Secondaries allow using hardware for more than HA/DR: Offload work from primary • Read workloads • Backups

  7. Active Secondaries Offloading Read Workloads

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

  9. Running Read Workloads - Now Active Secondary SQL Server SQL Server Primary Log Synchronization Reports • DBs in active secondaries are readable • Committed transactions visible eventually (depends on log synchronization latency) • Same data center: <1s • Different data center: 1-5s DB1 DB2 DB1 DB2

  10. Running Read Workloads - Now SQL Server Primary • Up to 4 secondaries • Sync or async DB1 DB2

  11. Running Read Workloads - Now Reports Reports SQL Server SQL Server Secondary Primary Secondary Manual Failover Primary Log Synchronization • Read workloads can be automatically routed to an active secondary DB1 DB2 DB1 DB2

  12. Configuring Secondary as Readable • ALLOW_CONNECTIONS setting ALTER AVAILABILITY GROUP ag_name MODIFY REPLICAON'server_name' WITH ( SECONDARY_ROLE( ALLOW_CONNECTIONS= { NO | ALL | READ_ONLY } ) )

  13. Clients Connectivity • 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

  14. Configuring Routing of Read-Only Connections • Configure Routing URL for each secondary • Endpoint for read-only connections ALTER AVAILABILITY GROUP ag_name MODIFY REPLICAON'server_name' WITH ( SECONDARY_ROLE ( READ_ONLY_ROUTING_URL= ‘TCP://system-address:port’ ) )

  15. Configuring Routing of Read-Only Connections • Create Routing List at each replica that can be primary • List of readable secondaries • Primary returns first one available • Load balancing not offered (implementable) ALTER AVAILABILITY GROUP ag_name MODIFY REPLICAON'server_name' WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST= {'server_name' [,..n]} ) )

  16. demo AlwaysOn: Active SecondariesOffloading Read Workloads

  17. Log Synchronization to Secondary • Redo is async • Transactions visible after commit redo (no dirty reads)

  18. Impact of Read Workload • Workload can take resources from Redo thread • Redo can fall behind  impact RTO • Mitigations: • Use Resource Governor • No workload on auto failover target

  19. Impact of Read Workload • 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 • XEventlock_redo_blocked

  20. Query Optimization on Active Secondary • 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 TempDB • sys.stats: is_temporary=‘true’ • Use most recent statistics • Remove on failover, restart, or DROP STATISTICS

  21. Active Secondaries Offloading Backups

  22. Creating Backups - Before Database Mirroring R/W workload Failed Backups Backups Primary Secondary Only on primary Impacts primary workload Fails after failover

  23. Creating Backups - Now R/W workload Backups Backups Backups Primary Active Secondary Active Secondary • On primary or any secondary • No need to configure secondaries as readable

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

  25. Single Log Backup Chain

  26. Backup Strategy 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

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

  28. Backup Automation • 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

  29. Recommendations • Store backups centrally • Easier to track • Use Recovery Advisor • Easier to restore to point in time

  30. demo AlwaysOn: Active SecondariesOffloading Backups

  31. announcing Active Secondaries in Windows Azure Read Workloads Backups DR are not

  32. Summary • Active Secondaries make AlwaysOncost-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

  33. Resources • AlwaysOn Resource Center • http://msdn.microsoft.com/en-us/sqlserver/gg490638.aspx AlwaysOn Team Blog http://blogs.msdn.com/b/sqlalwayson/ SQL Server 2012 Whitepapers http://msdn.microsoft.com/en-us/library/hh403491

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

  35. DBI Track Resources @sqlserver @teched_europe #msTechEd SQL Server 2012 Eval Copy Hands-On Labs Get Certified! mva Microsoft Virtual Academy

  36. Resources Learning TechNet • Connect. Share. Discuss. • Microsoft Certification & Training Resources http://europe.msteched.com www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet http://microsoft.com/msdn

  37. Evaluations Submit your evals online http://europe.msteched.com/sessions

  38. © 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

More Related