Comprehensive Overview of SQL Server 2012 Availability Groups and Their Key Enhancements

1. SQLintersectionSession SQL37SQL Server 2012 Availability Groups Aaron Bertrand abertrand@sqlsentry.net

2. Overview • Why were Availability Groups introduced? • What do they do? • How do they work? • What do they NOT do? • What enhancements will come in SQL Server 2014?

3. Why were Availability Groups introduced? • Many ways of achieving high availability and/or disaster recovery: • Database Mirroring • Log Shipping • Replication • Failover Cluster Instances • All have shortcomings • Availability Groups are, essentially, Mirroring++ • More importantly, overcome most of the shortcomings

4. What do Availability Groups do? • Coordinate failover of multiple databases as a single unit • Provide for multiple replica partners (“secondaries”) – up to 4 (2 sync) • Reduce load on primary by allowing read-only workloads on secondaries • Reporting, log backups, full backups (well, COPY_ONLY) • Applications don’t have to know where their queries are routed • Teach you new buzzwords like RPO, RTO and RLO • Control over whether availability has (a)synchronous commit • Control over whether failover is automatic or manual • Control preference for where secondary replica backups occur • Many of same benefits as mirroring – log compression, auto page repair • Faster log synchronization than log shipping or replication • Flexible Failover Policies

5. How do Availability Groups work? • Availability Groups use Windows Server Failover Clustering (WSFC) • Failover Clustered Instances (FCI) are possible, but not necessary • Same domain is required, but a WSFC can cross subnets • Shared storage not required • Quorum is used to determine whether enough nodes are up • Health Detection uses various metrics to determine state • Can fail over automatically or manually; automatic can be tuned • Synchronous / asynchronous commit trade availability for performance • Automatic failover requires synchronous commit • Listener is used to route connections, vs. transparent client redirect • Depending on availability and application intent • Can load balance reads across secondaries, and offload after failover

6. Flexible Failover Policy

7. Asynchronous vs. Synchronous Commit • Synchronous commit waits for log to be hardened on secondary • This eliminates data loss potential, at the cost of performance • Usually combined with automatic failover with “close” replica(s) • Asynchronous commit does not wait for log to harden • This improves performance, but introduces risk of data loss • Only supports manual failover, and should be the only option for remote / DR replicas

8. Quorum • Quorum means enough “votes” to keep the resource up • To ensure health and to prevent “split brain” • You want an odd number of quorum votes : up = “more than half” • If even number of nodes, use disk (shared) or file share (non shared) • Node majority (for odd number of nodes) • Node and file share majority (for even number in asymmetric storage) • Node and disk majority (for even number in shared storage) • Disk only (for shared storage) • You may want certain nodes to not have a vote (NodeWeight). E.g.: • Nodes on another subnet on a less reliable network • Priority in a multi-group or multi-instance scenario

9. Quorum Example

10. Common Questions • How do I migrate from database mirroring? • http://blogs.msdn.com/b/sqlalwayson/archive/2012/10/16/how-to-migrate-to-alwayson-alwayson-from-prior-deployments-combining-database-mirroring-and-log-shipping-part-1.aspx • http://msdn.microsoft.com/en-us/library/jj635217.aspx • What is involved in forcing a manual failover? • http://technet.microsoft.com/en-us/library/ff877957.aspx • How do I monitor Availability Groups? • DMVs, SSMS (dashboard/Object Explorer Details), Extended Events • We’ll talk a bit about our software during this afternoon’s keynote • http://technet.microsoft.com/en-us/library/ff877954.aspx

11. What do Availability Groups NOT do? • Handle cross-database transactions – consistency is not guaranteed • Commits are db-specific, so failover can cause out of sync • Distributed transactions cause an additional wrinkle after failover • Coordinate logins, jobs, linked servers, dbowner, trustworthy • Allow delayed apply, like log shipping • Go across domains – all WSFC nodes must be in the same domain • Wizard is inflexible – can’t specify WITH MOVE, for example • Run on Standard Edition, without a WSFC, or anything but full recovery • Save you money (active secondaries must be licensed) • Keep read-only secondaries available if primary is down • Solve every single HA/DR problem – e.g. suspect/damaged database

12. SQL Server 2014 Enhancements • Now up to 8 secondary replicas (still 2 sync + automatic) • Readable secondaries stay available if network or quorum is down • Enhanced diagnostics • More specific error messages • XEvents in UTC time • More XEvents • Integration with Windows Azure (IaaS) • Can have sync + automatic failover replica inside Windows Azure • Can have async secondary replica for on-premise (requires VPN tunnel)

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

14. Final Comments • Availability Groups are one of the most compelling reasons to upgrade • We talked about: • Why were Availability Groups were introduced • What Availability Groups do • How Availability Groups work • What Availability Groups don’t do • What enhancements will come in SQL Server 2014 • More info: come see the keynote this afternoon • Questions? • abertrand@sqlsentry.net

15. Don’t forget to complete a session evaluation form and drop it off at the conference registration desk (or use the mobile app) Session: SQL37 Thank you! Questions?