SQL Server HA and DR Design Patterns, Architectures, and Best Practices Using Microsoft SQL Server 2012 AlwaysOn

2. DBI316 SQL Server HA and DR Design Patterns, Architectures, and Best Practices Using Microsoft SQL Server 2012 AlwaysOn Michiel Wories Principal Development Lead Microsoft Corporation Sanjay Mishra Program Manager Microsoft Corporation

3. Setting the Stage • Assumed Pre-requisites for this presentation: Basic knowledge of • AlwaysOn Failover Cluster Instances (FCI) • AlwaysOn Availability Groups (AG)

4. Setting the Stage • AlwaysOn ≠ Availability Groups • AlwaysOn = { SQL Server Failover Cluster Instances, Availability Groups } • Availability Groups ≠ Database Mirroring

5. SQL Server 2012 AlwaysOn HA+DR Design Patterns Slight variations of these design patterns are occasionally observed as well.

6. Friday, June 29, 10:15 AM – 11:30 AM DBI360: SQLCAT: SQL Server 2012 AlwaysOn Lessons Learned from Early Customer Deployments D 204

7. title Multi-site Failover Cluster Instance for HA and DR

8. Multi-site Failover Cluster Instance for HA and DR Windows Server Failover Cluster Node 3 Node 1 Node 4 Node 2 Primary Site DR Site Passive SQL-FCI Passive Active Passive Storage Replication

9. Multi-site Failover Cluster InstanceKey Elements • A single SQL Server failover cluster instance (FCI) providing HA as well as DR • spanning across multiple sites (usually multiple subnets as well) • Key components: • Storage • Storage level replication • Cluster Enabler • Provided by the storage vendor • Work with your storage vendor to get the appropriate software and best practices • Network • Multi-subnet support in SQL Server configuration and engine • Key improvement in SQL Server 2012 • IP address OR dependency set within SQL Server setup • SQL Engine skips binding to any IP’s which are not online at start-up • RegisterAllProvidersIP for Network Name improves application failover time

10. Multi-site Failover Cluster InstanceDeployment Considerations • Storage Validation • Storage Validation Check Requirement is relaxed due to make-up of multi-site storage infrastructure (but still get the pop-up!) • Multi-site FCI Solution does not require passing the storage validation tests, to be supported. http://support.microsoft.com/kb/943984 • Appropriate Quorum Model • Validation suggests “Node and Disk Majority” which can be ignored • Consider “Node and File Share Majority” or “Node Majority” based on number of nodes

11. Multi-site Failover Cluster InstanceDeployment Considerations • TEMPDB on Local Disk • Not specific to “multi-site” FCIs, but has some great positive side effects for “multi-site” scenarios • Enables use of local storage for TEMPDB • Can use solid state storage to improve performance of TEMPDB-heavy workloads • Saves money on storage replication licensing • Reduces cross-data center storage replication traffic

12. title Availability Groups for HA and DR

13. Availability Groups for HA and DR

14. Availability Groups for HA and DRDeployment Considerations • Pre-requisites: • Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) • Unit of Failover • Group of databases – not the instance • Consider Contained Database for containing logins for failover • For jobs and other objects outside the database, simple customization needed • Considerations for Replacing Log Shipping • No delayed apply on the secondary • Removing log shipping means the regular log backup job is removed • Need to re-establish periodic log backup (essential for truncating the log)

15. Special Case: Automatic Failover for DRUse of 3rd Data Center

16. title Failover Cluster Instance for HA, and Availability Group for DR

17. FCI for HA + AG for DR

18. FCI for HA + AG for DRDeployment Considerations • Pre-requisites: • Windows Server Service packs / QFEs: • Asymmetric Storage • Windows Server 2008 with http://support.microsoft.com/kb/976097 • OR, Windows Server 2008 R2 SP1 • Node Votes: http://support.microsoft.com/kb/2494036 • Validate disk test QFE: http://support.microsoft.com/kb/2531907 • Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) • Different units of failover for HA and DR • Instance-level failover for local HA (FCI) • Group of databases (AG) for DR • AG Failover Mode • In FCI+AG configuration, FCI provides automatic failover, and AG provides manual failover

19. FCI for HA + AG for DRDeployment Considerations • Asymmetric Storage • Key concept behind this architecture • New Windows Server Failover Clustering capability introduced in: • Windows Server 2008 R2 SP1 • Windows Server 2008 with QFE • Symmetric storage = a cluster disk that is shared between all the WSFC nodes • Asymmetric storage = a cluster disk that is shared between a subset of nodes • Instance Naming • Each FCI within the WSFC needs to have a different instance name • Database File Paths • (recommended) use identical drive letters for the disks for each FCI • (recommended) use identical file paths for data and log files for each FCI

20. title Quorum Considerations

21. Quorum Considerations • Quorum is managed by the WSFC, irrespective of the number of SQL Server instances (FCI or standalone), number of nodes, number of availability groups • Important goal: Design to ensure • Unavailability of the DR site, or loss of network connectivity between sites should not impact the quorum of the WSFC • Two steps: • Node votes: First decide which nodes should have a vote • Quorum Model: Then choose the appropriate quorum model • Node Votes • By default, every node has a vote => May not be ideal for your specific HA / DR goals • Windows Server hotfix: http://support.microsoft.com/kb/2494036 • Guidelines: http://msdn.microsoft.com/en-us/library/hh270280.aspx#RecommendedAdjustmentstoQuorumVoting • Odd number of votes • Quorum Models: http://technet.microsoft.com/en-us/library/cc770620(v=WS.10).aspx

22. Quorum Model and Node VotesAvailability Groups for HA and DR Note: The Fileshare Witness always has 1 vote.

23. Quorum Model and Node VotesFCI for HA + AG for DR Note: The Fileshare Witness always has 1 vote.

24. Quorum Model and Node VotesHow to set / view To View Quorum Model To Change Quorum Model • Windows Failover Cluster Manager GUI • PowerShell • Cluster.exe • SQL Server DMVs • AlwaysOn Dashboard in SSMS • Windows Failover Cluster Manager GUI • PowerShell • Cluster.exe To View Node Votes To Change Node Votes • PowerShell • Cluster.exe • PowerShell • Cluster.exe • SQL Server DMVs • AlwaysOn Dashboard in SSMS Note: Only cluster.exe can be used to set quorum model to “Node and (asymmetric) Disk Majority” or “(asymmetric) Disk Only”

25. demo Monitoring HA/DR Solution

26. title Migration from Earlier HA/DR Solutions

27. Planning and Key Considerations • Hardware: new hardware, reuse existing hardware? • Windows Server: Upgrading the OS as well? • Windows Clustering: involve the Windows System Administration team and the networking team • Quorum considerations across multiple data centers • Cluster network communication across multiple data centers • Stages: migrate the whole configuration at once, or migrate the DR afterwards? • Application connection string change • If the secondary is a Failover Cluster Instance: needs re-seeding • Uninstall existing SQL FCI on the DR site nodes • Destroy existing WSFC at the DR site • Join DR site nodes to Windows cluster created in the primary data center • Re-install SQL FCI on DR nodes • Backup database(s) from primary, and Restore on the secondary

28. Summary

29. SQL Server 2012 AlwaysOn HA+DR Design Patterns Slight variations of these design patterns are occasionally observed as well.

30. Friday, June 29, 10:15 AM – 11:30 AM DBI360: SQLCAT: SQL Server 2012 AlwaysOn Lessons Learned from Early Customer Deployments D 204

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

32. www.sqlcat.com sanjaymi@microsoft.com @sqlcat Sanjay Mishra

33. 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

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

35. © 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.