Denali – Always On Deep Dive

1. 30 th Sept 2011 Denali – Always On Deep Dive Bob Duffy Database Architect Prodata SQL Centre of Excellence

2. Speaker Profile – Bob Duffy • Database Architect at Prodata SQL Centre Excellence, Dublin • One of about 25 MCA for SQL Server globally (aka SQL Ranger) • SQL Server MVP, 2009+ • MCM on SQL 2005 and 2008 • 20 years in database sector, 250+ projects • Senior SQL Consultant with Microsoft 2005-2008 • Blog http://blogs.prodata.ie/bob

3. Typical HADR Requirements Always On Installation Migrating to Availability Groups Planned Failover Automated Failover Active Secondary Replicas Integration with Failover Clustering Agenda

4. Case Study – Dell DVD Store Enterprises • Need to accept 24 x 7 transactions • For High Availability • Zero down time and data loss (automatic) • SLA to cover storage failure • “I see web site unavailable and you are fired” • “Reporting” • Near real time. No more than 1 minute behind • With minimal impact on live transactions • Disaster Recovery • Have second data centre. No more than 30 mins down time and 5 minutes data loss • Upgrade • Any upgrade needs to be with no down time or data loss

5. Typical HA/DR/Reporting Architecture DR Site

6. Denali Always On Synchronous Asynchronous Asynchronous DR Site

7. Installation • Always On relies on WSFC • Always On is NOT a clustered install of SQL • Does not need Shared Disk • Windows 2008 Server+ EE needed • Need to Enable HadrService • Via configuration manager • Or via PowerShell • Some Patches and QFE Needed • KB 976097KB 2494036  

8. Installation Cluster + HadrService

9. Migrating to Always On / Denali • Any normal migration is possible: • Backup / Restore • Attach / Detach • Mirroring • Log Shipping • Replication • Might need some post migration tasks • Compatibility • Use of new features such as Containment

10. Migrating to Denali

11. Creating your First Availability Group

12. Failover to Secondary

13. Quorum Changes • Required for automatic failover • Built in feature of WSFC • Replaces the concept of “witness” to avoid split brain • Two recommended Steps • 1. Select nodes to vote • 2. Select the Quorum Type • If Odd number use “Node Majority” • If even number use “Node Majority plus File Share Witness” • Requires QFE KB KB 2494036  to enable Voting • May need to adjust quorum after failover outsideautomatic target

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

15. New Failure Condition Levels

16. Automated Failover

17. SecondaryReplica Features • Failover • Backup \ DBCC • Reporting Queries • AlwaysOn AutoStat • Automatic Page Repair

18. Read Only Replicas

19. SecondaryReplica – Active Log Sync • Secondary read is behind primary • 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 DB1 DB1 Network Log Capture 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

20. Auto-Stats on Readable Secondary • Query Optimization and Statistics • SQL Server uses cost based optimizer that heavily relies on object statistics • If statistics are missing, SQL Server automatically creates them and persists • Auto-stat on readable secondary will require physical Changes • Example: Table T1 (C1, C2, C3) • Query on primary with predicate (C3 > 10). • SQL Server auto-create the statistics, if needed, on column C3 on primary replica • Won’t work on Readable Secondary as it will cause physical changes to the database. • Similar issue if the statistical information is stale on the secondary replica. • Solution • Auto-create statistics on the secondary replica but persist them in TempDB • Existing catalog views (e.g. sys.stats) show temporary statistics

21. Unplanned Disaster!

22. Availability Groups and Failover Cluster Instance Availability Groups integrates seamlessly with AlwaysOn Failover Cluster Instance Failover Cluster Instance used for local high availability • Availability Groups provide ability to failover to a disaster recovery site • Automatic failover of the availability group is disallowed when one of the replicas is an FCI

23. Coming up… #SQLBITS

24. Questions ?

25. AlwaysOn Resources • “Denali” AlwaysOn Resource Center: http://msdn.microsoft.com/en-us/sqlserver/gg490638(en-us,MSDN.10) • CTP download • Documentation • MSDN forums • Microsoft Connect • AlwaysOn Blog

26. Trace Flag 9532 TSQL features • CREATE AVAILABILITY GROUP Transact-SQL options: • Using more than two REPLICA ON clauses • FAILURE_CONDITION_LEVEL • HEALTH_CHECK_TIMEOUT • SESSION_TIMEOUT • Using any of the following ALTER AVAILABILITY GROUP Transact-SQL options: • ADD REPLICA ON • REMOVE REPLICA • ADD DATABASE • REMOVE DATABASE • ENDPOINT_URL • FAILURE_CONDITION_LEVEL • HEALTH_CHECK_TIMEOUT • SESSION_TIMEOUT

27. Thank You!