130 likes | 221 Views
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
E N D
SQLintersectionSQL37SQL Server 2012 Availability Groups:High Availability for Your Most Important Data Aaron Bertrand, abertrand@sqlsentry.net, @AaronBertrand Kevin Kline, kekline@sqlsentry.net, @KEKline
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”
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.
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.
Flexible Failover Policy Levels Damaged databases and suspect databases? Nope, not detected by any failure-condition level.
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)
Demo Comparison of Native Tooling to SQL Sentry for SQL Server Availability Groups A.K.A. “You get what you pay for”.
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.
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
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.
Don’t forget to complete a session evaluation form and drop it off at the conference registration desk. Session: SQL37 Thank you! Questions?