130 likes | 283 Views
SQL Server 2012 AlwaysOn and SQLSentry. Kevin Kline Director of Engineering Services, SQL Sentry SQL Server MVP since 2004 Twitter, FB, LI: KEKline Blog: http://KevinEKline.com , http://ForITPros.com. Agenda. The Basics. Inherits from DBM: Sync / Async data protection
E N D
SQL Server 2012 AlwaysOn and SQLSentry Kevin Kline Director of Engineering Services, SQL Sentry SQL Server MVP since 2004 Twitter, FB, LI: KEKline Blog: http://KevinEKline.com, http://ForITPros.com
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 DBA? • You need to know WSFC very 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 3 synchronous 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 and backups.
Flexible Failover Policy Levels Damaged databases and suspect databases? Nope, not detected by any failure-condition level.
AG Metadata • SSMS (in demo) • DMVs: • sys.availability_* • sys.dm_hadr_* • Xevents • SP_server_diagnostics: checks the health_check_timeout value in 30 second intervals, by default.
Additional Resources • Plan Explorer Free: http://sqlsentry.net/plan-explorer/sql-server-query-view.asp • Twitter and #SQLHelp • SQLCAT.com • SQLSkills.com • Community Sites: • SQLPASS.org • SQLServerCentral.com • SQLBlog.com • SQLTeam.com
SUMMARY • SSMS, Traces, and Execution Plans • Test Harness & Testing • Query Tuning Tips • Temp tables • Cursors & Loops • NULL • Error Handling • Transactions • Security isn’t an afterthought • Change Management is risk management
Q & A • Send questions to me at: kkline@sqlsentry.net • Twitter, Facebook, LinkedIn at KEKline • Slides at http://KevinEKline.com/Slides/ • IT Leadership content at http://ForITPros.com • THANK YOU!