1 / 13

Aaron Bertrand, abertrand@sqlsentry , @ AaronBertrand

SQLintersection SQL37 SQL Server 2012 Availability Groups: High Availability for Your Most Important Data. Aaron Bertrand, abertrand@sqlsentry.net , @ AaronBertrand Kevin Kline, kekline@sqlsentry.net , @ KEKline. Agenda. The Basics. Inherits from DBM: Sync / Async data protection

trynt
Download Presentation

Aaron Bertrand, abertrand@sqlsentry , @ AaronBertrand

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. SQLintersectionSQL37SQL Server 2012 Availability Groups:High Availability for Your Most Important Data Aaron Bertrand, abertrand@sqlsentry.net, @AaronBertrand Kevin Kline, kekline@sqlsentry.net, @KEKline

  2. Agenda

  3. The Basics • Inherits from DBM: • Sync / Async data protection • Automatic or manual failover • Automatic page repair • Compressed data stream • TCP endpoints • With WSFC: • Does NOT require instances to be Failover Cluster Instances (FCIs) • Does NOT require that servers start out as clustered if using standalone instances • Database-level protection • Built upon the foundation of database mirroring • Requires a Windows Server Failover Cluster. • Availability Groups (AGs) are what most people mean when they say “AlwaysOn”

  4. What Does WSFC Mean for a Data Pro? • You need to know WSFC thoroughly. • Quorum matters. • Nodes must be part of the same domain. (Different subnets are ok). • Quorum matters. • WSFC validation is very important. • AG feature doesn’t check like in setup for FCI • It’s on YOU to check it. • %windir%/Cluster/Reports • Apply hotfixes per Windows version • Quorum matters.

  5. I Know Mirroring. How’s This Different? • Enterprise Edition only. DB in full recovery mode only. • Not a 1:1 ratio of principals to mirrors. Per AG: • Up to 4 additional replicas, for a total of 5 • Up to 2 synchronous secondary replicas • Up to 2 automatic failover pairs • Multiple DBs in an AG will failover at the same time.*** • Easily span subnets. • Replicas usable for read-only access (w/o snapshots!) and backups. • More flexible routing – apps don’t need to know where replicas live.

  6. Availability Groups Fundamentals

  7. Flexible Failover Policy Levels Damaged databases and suspect databases? Nope, not detected by any failure-condition level.

  8. Monitoring Availability Groups - Native • SSMS (in demo) • DMVs: • sys.availability_* • sys.dm_hadr_* • XEvents(availability_replica_state_change, data_movement_suspend_resume) • Performance counters (SQLServer:Availability Replica/Database Replica) • sp_server_diagnostics(checks for health and diagnostic info)

  9. Demo Comparison of Native Tooling to SQL Sentry for SQL Server Availability Groups A.K.A. “You get what you pay for”.

  10. What about Cloud? • Windows Azure is a supported destination for secondaries. • Within Azure, can have sync replicas and automatic failover • SQL 2014: On-premise can have async replicas (requires VPN tunnel) • Can provide some serious benefits: • No servers to buy. Might be cheaper. • Geographically disbursed for better business continuity in the event of a natural / physical disaster. • Built-in management.

  11. Additional Resources • Twitter and #SQLHelp • http://SQLCAT.com • http://SQLSkills.com • Community Sites: • http://SQLPASS.org • http://SQLServerCentral.com • http://SQLBlog.com • http://SQLTeam.com • http://SQLPerformance.com • http://dba.stackexchange.com • Brent OzarAvailability Groups Checklist http://i.brentozar.com/sql-server-2012-alwayson-availability-groups-setup-checklist.pdf • AG Troubleshooting and Monitoring Guidehttp://blogs.msdn.com/b/sqlalwayson/archive/2013/06/06/alwayson-availability-groups-troubleshooting-and-monitoring-guide-published.aspx

  12. SUMMARY • Based upon older database mirroring technologies & WSFC. • Provides continuous uptime, great recoverability, and usefulsecondaries. • Useful secondaries are usually read-only centric workloads and preventative maintenance. • The cloud is an option for secondaries. • Native tooling is adequate for small infrastructures.

  13. Don’t forget to complete a session evaluation form and drop it off at the conference registration desk. Session: SQL37 Thank you! Questions?

More Related