1 / 27

Denali – Always On Deep Dive

30 th Sept 2011. Denali – Always On Deep Dive. Bob Duffy Database Architect Prodata SQL Centre of Excellence. 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+

barry-witt
Download Presentation

Denali – Always On Deep Dive

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

More Related