SQL Server 2012AlwaysOn Availability Groups

1. SQL Server 2012AlwaysOn Availability Groups Roger Breu @rogerbreu Technology Solution Professional DataPlatform Microsoft Corporation

2. Agenda • What is an Availability Group? • Availability Group Setup Step by Step • Active Secondaries • Tuning and Monitoring • Q&A

3. What is an Availability Group? SQL Server 2012 AlwaysOnAvailability Groups

4. HA/DR Features before ... List is not complete, Replication only for Table Level HA...

5. AlwaysOn Availability Groups • AlwaysOn Availability Groups is a new feature that enhances and combines failover cluster, database mirroring and log shipping capabilities Flexible Efficient Integrated • 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 • Flexible failover policy • Automatic Page Repair • Application failover using virtual name • Configuration Wizard • Dashboard • System Center Integration • Rich diagnostic infrastructure • File-stream replication • Replication publisher failover • Active Secondary • Readable Secondary • Backup from Secondary • Automation using power-shell

6. Availability Group Scenarios • Direct Attached Storage • local, regional and geo secondaries AG AG AG AG AG • Example • Primary in Calgary • Failover Partner in Vancouver • Sync DR in Toronto Synchronous Data Movement Asynchcronous Data Movement • Async Secondary in Montreal (Reporting) • Async Secondary in London (Geo DR)

7. Availability Group Scenarios • Shared Storage • local, regional and geo secondaries • mixed with Direct Attached Storage AG AG AG • Example • Primary Failover Cluster in Calgary • Sync Secondary Failover Cluster in Vancouver Synchronous Data Movement Asynchcronous Data Movement • Async DR Standalone Server in Toronto

8. DB DB The Availability Group DB AG AG_VNN AG_IP • «Container» • 1 or more Databases • Listener (aka virtual networkname) • 1 or more IP Addresses (DHCP or fix, aka virtual IP) • Replica Definition • Primary/Secondaries • Automatic Failover Partner • Sync/Async Secondaries

9. Availability Group uses Windows Server Failover Cluster (WSFC) for Inter-node health detection Failover coordination Primary health detection Distributed data store for Availability Group settings and state Distributed change notifications Availability Group Architecture AG Windows Server Failover Cluster AG_VNN AG_IP Database Active Log Synchronization Database Active Log Synchronization DB DB DB DB DB DB • SQL Server synchronizes databases • Based on Transaction Log • Via TCP Endpoint • Synchronous or asynchronous • Compressed Logstream • Encrypted Logstrem

10. Availability Group Setup Step by Step SQL Server 2012 AlwaysOnAvailability Groups

11. OS Requirements • Windows Server 2008/2008 R2 • Failover Cluster Feature installed • Windows Server Failover Cluster created • Needs Cluster Networkname and IP Address • No Shared Storage needed • Quorum can be configured with Fileshare Witness

12. SQL Server Requirements • SQL Server 2012 • Installed as Standalone (or SQL Server Failover Cluster*) Instance on every Windows Server Failover Cluster Node • Can be named or default instance • Instance names don’t have to be the same • All Nodes have to be part of the same Windows Server Failover Cluster • All Instances must use the same collation • Domain Service Account • AlwaysOn Availability Groups feature enabled via SQL Server Service Configuration Manager

13. Availability Group Requirements • Each availability group name must be unique on the WSFC cluster • For a given availability group, the availability replicas must be hosted by server instances running on different nodes of the same WSFC cluster • # of availability groups on a given server instance is unlimited • Recommended maximum is ten availability groups per instance • # of database in a given availability group is unlimited • Recommended maximum is 100 databases per availability group

14. Database Requirements • Must be a user database • System databases cannot belong to an availability group • Must be a read-write database • Read-only databases cannot be added to an availability group • Must be a multi-user database • Not use AUTO_CLOSE • Use the full recovery model • Possess a full database backup • Reside on the SQL Server instance where you are creating the availability group and be accessible to the server instance • Not belong to another availability group • Not be configured for database mirroring

15. Checklist • Setup Windows Server Failover Cluster Nodes • Configure WSFC • Setup SQL Server Instances • Same collation • Recommended to configure same DB/Tlog File pathes • Enable AlwaysOn Availability Groups via SQL Server Service Configuration Manager • Instance restart needed • Start deploying Availability Groups

16. Demo Scenario – Application Failover • Application reconnects using a virtual name (listener) after a failover to a secondary TechdaysAG1 WSFC DENALIWINCLU1 Techdays1 (VNN) CALGARY TORONTO VANCOUVER DB2 DB2 DB2 AG_IP DB1 DB1 DB1 Primary Secondary Primary Secondary Secondary • Application retry during failover • Connect to new primary once failover is complete and the listener is online • -server Techdays1

17. Initiate a failover • Via SQL Server Management Studio • Expand the Management node, the AlwaysOn High Availability node, and the Availability Groups node. • Right-click the availability group to be failed over, and select the Failover command. • Via T-SQL • ALTER AVAILABILITY GROUP TechdaysAG1 FORCE_FAILOVER_ALLOW_DATA_LOSS; • Via PowerShell • Switch-SqlAvailabilityGroup -Path SQLSERVER:\SQL\secondarymachine\secondaryinstance\AvailabilityGroups\TechdaysAG1 -AllowDataLoss • Via Failover Cluster Manager

18. What about Server Objects? • Introducing Contained Databases or CDB’s • Unit of application programmability in Denali • A DB which establishes a boundary between application and server • CDBs sever the user–login relationship • Windows users no longer need matching logins • Users with passwords replace SQL logins • CDB can be part of an availability group and can failover across replicas • Authentication information moves with the CDB • Limitation • Other objects, like jobs are not contained and have to be managed

19. Active Secondaries SQL Server 2012 AlwaysOnAvailability Groups

20. AlwaysOn Active Secondary • IT efficiency and cost-effectiveness is critical for businesses • Idle hardware is not an option anymore • AlwaysOnActive Secondary enables efficient utilization of high availability hardware resources thereby improving overall IT efficiency • Active Secondary can be utilized for • Balancing read-only workloads • Offloading Backup Operations

21. Read-Only Client Connectivity • Read-Only client connection behavior determined by Availability Replica Option+ ApplicationIntent Property • ApplicationIntent is a connection property • Replica option determines whether a replica is enabled for read access when in a secondary role • Read-Only Routing enables redirection of client connection to new secondary on role change • Enable seamless redirection of application connection across replicas without manual intervention • Configuration with Read-Only Routing Lists

22. Demo Scenario – Active Secondary Reports • Readable secondary allows offloading read queries to secondary • Automatic redirect with Routing Lists or manual connect to Secondary TechdaysAG1 WSFC DENALIWINCLU1 Techdays1 (VNN) CALGARY TORONTO VANCOUVER DB2 DB2 DB2 AG_IP DB1 DB1 DB1 Primary Secondary Primary Secondary Secondary • Application retry during failover • -server Techdays1 • -server Techdays1; ApplicationIntent=ReadOnly • Connect to new primary/new secondary once failover is complete and the listener is online

23. Log is first hardened and then applied Redo thread is asynchronous and runs in the background Latency (typically seconds) can be larger for log intensive operations like bulk import or index create/rebuild Sync Replica minimizes latency due to network issues Close to real-time reads Secondary Primary DB1 DB1 Log Capture Network Log Recieve Log Pool Redo Thread Commit Log Cache Log Cache Redo Pages Log Flush Log Harden Page Updated DB1 Log DB1 Data Acknowledge Commit DB1 Log DB1 Data

24. Enabling Backup On Secondary R/W workload Backups can be done on any replica of a database (Full WITH COPY_ONLY or Tlog) Backups on primary replica still works Log backups done on all replicas form a single log chain Database Recovery Advisor makes restores simple Backups Backups Backups Primary Secondary Secondary

25. Tuning and Monitoring SQL Server 2012 AlwaysOnAvailability Groups

26. Fine Tuning/Tipps • All the availability replicas should run on comparable systems that can handle identical workloads • For best performance, use a dedicated network adapter for AlwaysOn Availability Groups (LogStream) • Every computer on which a server instance hosts an availability replica must possess sufficient disk space for all the databases in the availability group • Keep in mind that as primary databases grow, their corresponding secondary databases grow the same amount • For the New Availability Group wizard to automatically start initial data synchronization, every secondary database must use the same file path as its corresponding primary database • If you add a file to a primary database, secondary replica(s) will be able to add the new file on the corresponding secondary database(s)

27. Fine Tuning/Tipps • Make sure, WSFC network name has „add workstation to domain“ rights otherwise it will only add up to 10 VNNs • Failover Cluster Failure Threshold should be adjusted for Test-runs • In the Failover Cluster Management snap-in navigation pane, expand one of the managed clusters that has a highly available application or service configured. • Expand the Services and Applications category. • Select and then right-click one of the groups, and then click Properties. • Click the Failover tab, and then view the Maximum failures in the specified period setting • TraceFlag 9532 needed for all SQL Server 2012 CTP3 instances to allow more than one replica • Configuration via SQL Server Configuration Manager on every node • Add T9532 on the startup tab • Won’t be necessary with RTM Bits

28. Node weight Quorum Settings • Please consult following KB article for configuration and download Hotfix • http://support.microsoft.com/kb/2494036/en-us • http://msdn.microsoft.com/en-us/library/hh270279(v=SQL.110).aspx • Cluster.exe DENALIWINCLU1 node TORONTO /prop NodeWeight=0 • Remove the DR side vote so that a network interruption doesn’t affect the servers running on the main site for HA

29. Flexible Failover Policy User sets new Cluster properties HealthCheckTimeout and FailureConditionLevel SQL Server Failover Cluster Instance • FailureConditionLevel (0 to 5) • 5 – Failover or restart on any qualified failure • 4 – Failover or restart on moderate SQL Server • errors • 3 – Failover or restart on critical SQL Server errors • 2 – Failover or restart on SQL Server unresponsive • 1 – Failover or restart on SQL Server down • 0 – No Automatic Failover or restart • Diagnostics generated for Health State Components • System • Resource • Query Processing • IO Subsystem • Events WSFC Service Diagnostics exec sp_server_diagnostics AG Res DLL IsAlive/ LooksAlive result based on diagnostics and FailureConditionLevel IsAlive /LooksAlive WSFC asks Res DLL if SQL AG alive

30. Availability Group Monitoring • Availability Group Dashboard in Management Studio • Monitoring will be fully integrated into the SQL Server 2012 Management Pack for SCOM • Including Database Mirroring Monitoring and Replication Monitoring for SQL Server 2008 R2  • Lots of new DMVs: • http://msdn.microsoft.com/en-us/library/ff878305(v=SQL.110).aspx

31. Virtualization with AlwaysOn • Virtualization provides best consolidation isolation • Virtualization without AlwaysOn: • Simplest management story for limited HA/DR: • When to use AlwaysOn for the guest: • Need better HA/DR protection than standalone VM

32. Conclusion SQL Server 2012 AlwaysOnAvailability Groups

33. Conclusion • SQL Server AlwaysOnAvailability Groups is a comprehensive high availability and disaster recovery solution • Integrated • Flexible • Cost-Efficient • «Classical» Failover Clustering, Database Mirroring and Log Shipping are not dead • A combination of all solutions can bring you to meet your SLAs

34. Additional Resources SQL Server 2012 AlwaysOnAvailability Groups

35. Links • SQL Server 2012 Developer Training Kit • http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=27721 • SQL Server 2012 CTP3 Product Guide • http://www.microsoft.com/download/en/details.aspx?id=27069 • swissSQL Blog • http://blogs.technet.com/swissSQL • AlwaysOn Resource Center • http://msdn.microsoft.com/en-us/sqlserver/gg490638(en-us,MSDN.10) • AlwaysOn Availability Group Prerequisites • http://msdn.microsoft.com/en-us/library/ff878487(v=SQL.110).aspx • AlwaysOn Availability Groups together with SQL Server Failover Cluster Instance • http://msdn.microsoft.com/en-us/library/ff929171(v=SQL.110).aspx

36. Give us your feedback! • Please help us make TechDays even better by Evaluating this Session. Thank you!

37. © 2011 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.