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

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
of
AlwaysOn
SSMS
Tooling
Demo
SQLSentry
Tooling
Demo
SQLSentry.net
The Basics
Inherits from DBM:
With WSFC:
• Sync / Async data
• Does NOT require
• Database-level
protection
protection
instances
to be
•• Built
upon
the
foundation
of
database
mirroring
Automatic or
Failover Cluster
manual failover
• Requires
a Windows Server Failover
Cluster.
Instances (FCIs)
Automatic page
•• Availability
Groups (AGs) are•what
most
people
Does
NOT
require
repairwhen they say “AlwaysOn”
mean
that servers start out
• Compressed data
as clustered if using
stream
standalone instances
• TCP endpoints
SQLSentry.net
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.
SQLSentry.net
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.
SQLSentry.net
Availability Groups Fundamentals
SQLSentry.net
o
Flexible Failover Policy Levels
Failure Condition
Level
On server down. This is the least restrictive level.
1
On server unresponsive.
2
On critical server error. The default level. (1 and 2, plus internal
errors).
3
On moderate server error. (1 – 3, plus other errors like stack dumps).
4
On any qualified failure conditions. (1 – 4, plus other errors like
worker thread exhaustion and unresolvable deadlocks).
5
Damaged databases and suspect databases?
Nope, not detected by any failure-condition level.
SQLSentry.net
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.
SQLSentry.net
Additional Resources
• Plan Explorer Free: http://sqlsentry.net/plan-explorer/sqlserver-query-view.asp
• Twitter and #SQLHelp
• SQLCAT.com
• SQLSkills.com
• Community Sites:
•
•
•
•
SQLPASS.org
SQLServerCentral.com
SQLBlog.com
SQLTeam.com
SQLSentry.net
SUMMARY
SQLSentry.net
Q&A
•
•
•
•
Send questions to me at: [email protected]
Twitter, Facebook, LinkedIn at KEKline
Slides at http://KevinEKline.com/Slides/
IT Leadership content at http://ForITPros.com
• THANK YOU!
SQLSentry.net