AlwaysOn: Availability Groups

1. AlwaysOn: Availability Groups

2. MODIFY THIS SLIDE FOR ACTUAL PRESENTER, DELETE THIS BAR AFTER MODIFICATION ILIKESQL.COM dandyw@microsoft.com Dandy Weyn @ilikesql

3. SQL Server Delivers Required 9sMaximize uptime for mission critical applications GREATER UPTIME • AlwaysOn High Availability • Windows Server Core Support • Online Operations Enhancements IMPROVED PRODUCTIVITY • Integrated Configuration Wizard • Windows PowerShell Support • AlwaysOn Dashboard • Microsoft System Center Monitoring GREATER HARDWARE UTILIZATION • Active Secondaries • Scale Read-Only Applications • Offload Backups • Fast Application Failover

4. Greater Uptime with High AvailabilityKey technologies to deliver required 9s ALWAYSON WINDOWS SERVER CORE SUPPORT ONLINE OPERATIONS ENHANCEMENTS • Availability Groupsprovide database level protection • Multiple Secondaries • Failover Cluster Instances provide instance level protection • Reduce OS patching by as much as 50-60%with new support for Windows Server Core1 • Reduce planned downtime by reducing OS patching • Maintain uptime during maintenance operations using enhanced online operations • Reindexing of large object data types • Adding non-null columns 1The percentage reduction in patching varies & can be less based on the server roles that are enabled & the type of patches that are applied.

5. SQL Server 2012 HA & DR Solutions

6. AlwaysOn Availability Groups is a new feature that enhances and combines database mirroring and log shipping capabilities AlwaysOn Availability Groups • Multi-database failover • Multiple secondaries • Total of 4 secondaries • 2 synchronous secondaries • 1 automatic failover pair • Synchronous and asynchronous data movement • Built in compression and encryption • Auto-page repair • Automatic and manual failover (new design) • Flexible failover policy Flexible • Application failover using virtual name • Configuration Wizard • Dashboard • System Center Integration • Rich diagnostic infrastructure • File-stream replication • Replication publisher failover Integrated • Active Secondary • Readable Secondary • Backup from Secondary • Improves primary server performance by offloading work to secondary • Monitoring and Troubleshooting enhanced • Automation using PowerShell Efficient

7. AlwaysOn Availability Group Listener Availability Groups Listener allow applications to failover seamlessly to any secondary; reconnecting through Virtual Network Name ServerA ServerC ServerB 2 DB 2 DB 2 DB TechAG1 TechListener1 Secondary Secondary Primary Primary Secondary Application retry during failover Parameter Sample: -server TechListener1;-catalog HRDB Connect to new primary once failover is complete and the listener is online

8. AlwaysOn Availability Group Architecture Windows Server Failover Clustering (WSFC) Database Active Log Synchronization Database Active Log Synchronization WSFC is a Common Microsoft Availability Platform Availability Group uses WSFC for • SQL Server AlwaysOn Failover Cluster Instances • SQL Server AlwaysOn Availability Group • Microsoft Hyper-V • Microsoft Exchange • Built-in WSFC workloads (e.g. file share, NLB, etc.) and third party workloads • Inter-node health detection • Failover coordination • Primary health detection • Distributed data store for settings and state • Distributed change notifications

9. New Topology Benefits • Multiple no data loss secondaries • Better data loss protection for DR secondaries through continuous replication • Faster failover to DR secondaries through virtual name failover Better SLAs Easier Deployment & Management • Unified solution • Simple deployment • Unified dashboard • Rich diagnostics • Centralized management of client connection topology • Multi-DB failover New Management Dashboard

10. AlwaysOn Active Secondaries AlwaysOn Active Secondary enables efficient utilization of high availability hardware resources to improve overall IT efficiency IT EFFICIENCY AND COST-EFFECTIVENESS ARE CRITICAL FOR BUSINESSES Idle hardware is no longer an option. Active Secondary uses Read-only workloads Offloading Backups

11. Active Secondary – Readable Secondary SQLservr.exe SQLservr.exe Readable secondary allow offloading read queries to secondary Low data latency After failover, the read applications can be automatically redirected to the new Secondary (require explicit connection request) Not a replacement for replication scenarios Secondary Primary Secondary Primary CRASH Database Log Synchronization InstanceA InstanceB Reports Reports DB1 DB1 DB2 DB2

12. Secondary read is always behind primary during transaction activity Readable Secondary – Data Latency Primary Secondary DB1 DB1 Network Log Capture Log Capture Log Receive Log Pool Redo Thread Commit Log Cache Log Cache Redo Pages Log Flush Log Hardened Acknowledge Commit DB1 Log DB1 Data DB1 Log DB1 Data

13. Read Workload Impact Concurrency and Blocking REDO can get blocked by reporting workload REDO thread and read workload can deadlock Solution Internally map read workload to non blocking isolation levels (no application changes required) • Read Uncommitted  Snapshot Isolation • Read Committed  Snapshot Isolation • Repeatable Read  Snapshot Isolation • Serializable  Snapshot Isolation • Ignore all locking hints Never choose REDO as deadlock victim Read/Write Read Primary Secondaries • Result • Blocking and deadlock between Reporting workload (i.e. Query) and REDO thread is eliminated • No issues with DML (INSERT/DELETE/UPDATE) as it is not allowed • Will incur additional cost of row versioning.

14. Client Connectivity Read / Write Workload • Connecting using AG Listener • Connection using FAILOVER_PARTNER (if connection string of existing applications can’t be changed) Read Only Workload • Connection using VNN and ApplicationIntent=ReadOnly • Connection to the secondary instance directly • ReadOnly Routing Multi subnet failover scenario: • New client libraries => MultiSubnetFailover=True • Old client libraries configure appropriate client connection timeout Client AG Listener Primary Secondaries

15. Backup Capabilities • Recovery Advisor Backups from any replica Synchronous or asynchronous secondaries Primary backups still work Adds capacity to primary server by off-loading backups to a replica Log backups done on all replicas form a single log chain Recovery Advisor makes restores simple

16. Considerations for Availability Groups All SQL servers (including the secondary in the DR site) in the same Windows domain • One Windows Server Failover Cluster spreads over the primary and DR sites All the databases must be in FULL recovery model The unit of failover (for local HA, as well as DR) is at the AG level, i.e., group of databases – not the instance • Consider using Contained Database for containing logins for failover • For jobs and other objects outside the database, simple customization needed No delayed apply on the secondary like log shipping Removing log shipping means the regular log backup job is removed • Need to re-establish periodic log backup (essential for truncating the log) • New Tools for Monitoring & Alerting • AlwaysOn Dashboard • System Center Operations Manager

17. Failover Clustering Improvements

18. DEMO

19. AlwaysOn Multi-site Failover Cluster Instance Provide High Availability at the Instance Level • Unit of failover = SQL server instance • Maintain same virtual network name after failover. Clients re-connect to same name • Instance restart requires database to go through recovery Provide Disaster Recovery at the Instance Level • Provide Disaster Recovery protection from site failure: be it network, power, infrastructure or other site disasters. • Require storage based replication technology and networking considerations • Multi-subnet support: • HA & DR Solution

20. Multi-subnet Clustering Corpnet Network Name: SqlClust OR IP1: 10.168.0.10 IP2: 192.168.0.10 subnet 1 subnet 2 Node3 Node1 Local Site Remote Site Node4 Node2 SAN Replication

21. TempDB on Local Disk WHY WE ENABLE THIS? • tempdb access occupies large % of SAN I/O • Fast local HDD/SSD becomes standard Server configuration BENEFITS • Better overall performance • Cost saving IMPORTANT NOTE! • Ensure that tempdb local paths are available to SQL Service on all the nodes LOCAL TEMP DB (Fast disk, SSD) LOCAL TEMP DB (Fast disk, SSD) Secondaries primary Shared Disk (SAN)

22. AlwaysOn Failover Cluster InstanceFailover Condition Policy Flexible Failover Policy provides administrators control over the conditions when an automatic failover should be initiated. SQL Server 2008 R2 SQL Server 2012 Resource DLL Resource DLL Select @@servername sp_server_diagnostics Diagnostics SQL Server SQL Server Configurable options eliminate false failover Improved logging for better diagnostics

23. SQL Server Hands-on-Labs SQLSERVERLAUNCH.COM