Transcript SQL Server 2012 - Columbus SQL Server Users Group

SQL Server 2012 Always On
Lisa Gardner
Premier Field Engineer
Microsoft Corporation
http://blogs.msdn.com/sqlgardner
Outcome
Understanding of High Availability
Options in SQL Server 2012
Benefits of AlwaysOn
HA design patterns utilizing AlwaysOn
Agenda
High Availability Options
AlwaysOn Demo
Readable Secondaries
Readable Secondaries Demo
AlwaysOn Design Patterns
High Availability Options
Pre-SQL Server 2012
What's New in SQL Server Failover Clustering?
SQL Server 2012 AlwaysOn
Pre-SQL Server 2012 High Availability Options
Backup/Restore
Windows/SQL Server Failover Clustering
Log Shipping
Database Mirroring
Third Party
SAN Replication
AlwaysOn Technologies – Managed by WSFC
WSFC
FCI
AG
What’s New in SQL Server Failover Clustering?
AlwaysOn Failover Cluster Instance provides instance level failover
Key SQL Server 2012 Clustering Enhancements
Multi-site geo-clustering across subnets
Flexible Failover Policy
Improved system diagnostics
Support for network attached storage(NAS) user SMB
Support for TempDB on local drive (SSD)
Flexible Failover Policy (SQL Server 2012)
Control over when automatic failover should be initiated
Configurable options eliminate false failover
Improved logging for better diagnostics
New Cluster Properties
HealthCheckTimeout
FailureConditionLevel
Failure Condition Levels
5 – Failover or restart on any
qualified failure conditions
Query Processing errors
4 – Failover or restart on
moderate SQL Server errors
Resource errors
3 – Failover or restart on critical
SQL Server errors
System errors
2 – Failover or restart on server
unresponsive
1 – Failover or restart on server
down
0 – No Automatic Failover or
restart
No response from
sp_server_diagnostics
Service is
down
Always On Availability Groups
Allows a group of databases to failover as a logical unit
Utilizes Windows Failover Cluster to report health
Defines a primary instance and up to four secondary instances
Provides automatic client redirection
AlwaysOn Concepts
Availability Group
Availability Replica
Availability Database
Availability Group Listener
SQL Server High Availability Options Recap
High Availability and Disaster
Recovery SQL Server Solution
Potential
Data Loss
(RPO)
Potential
Recovery
Time
(RTO)
Automatic
Failover
Readable Secondaries (1)
AlwaysOn Availability Group –
synchronous-commit
Zero
Seconds
Yes(4)
0-2
AlwaysOn Availability Group –
asyncronous-commit
Seconds
Minutes
No
0-4
AlwaysOn Failover Cluster Instance
NA(5)
Secondsto minutes
Yes
NA
Database Mirroring(2) – High-safety
(sync + witness)
Zero
Seconds
Yes
NA
Database Mirroring(2) – High
Performance (async)
Seconds(6)
Minutes(6)
No
NA
Logshipping
Minutes(6)
Minutesto-hours(6)
No
Not during a restore
Hours(6)
Hours-todays(6)
No
Not During a restore
Backup Copy Restore(3)
Demonstration
Creating an Availability Group
Readable Secondaries
Mirrored copy of data on secondary server
Active Secondary servers provide off-loading functionality
Reading of data for reporting
Backups
DBCC
Connect via Instance name
Active Secondary – Readable Routing
Allows for application to specify read Intent on Connection
ApplicationIntent – A New Connection Property
Connect via listener
Read-Only Routing
Optimized for automatic routing of read only applications
Routes must be create created manually
Readable Secondary – Data Latency
Secondary reads are behind primary
Log is first hardened and then applied
Redo thread is asynchronous and runs in the background
Latency (typically seconds) can be larger for log intensive operations like bulk import or
index create/rebuild
Sync Replica minimizes latency due to network issues
Demonstration
Leveraging Active Secondaries
Query Performance on Secondary
SQL Server Uses Cost based optimizer
Relies on object Statistics
If statistics are missing SQL Server creates and persists
Auto-stat on readable secondary will require updates?
Active Secondary : Enabling Backup on Secondary
Backups can be done on any replica
Must be able to communicate with primary
Log backups done on all replicas form a single log chain
Send all backups to a single UNC path
Database Recovery advisor makes restores simple
Must include backups from other instances manually
Differential Backups are not supported
Copy-Only backups are the only type
I have a 4 part blog series on this topic for more details
AlwaysOn Troubleshooting
AlwaysOn Dashboard
Sp_server_diagnostics
Catalog Views Examples
Sys.availability_groups
Sys.availability_replicas
DMV are named sys.dm_hadr*
New Performance Monitor Counter Objects
SQLServer:Database Replica
SQLServer:Availability Replica
New Information Logged to the System Event Logs
AlwaysOn Design Patterns
White Board/Flip Discussion
Conclusion
AlwaysOn provides many High Availability Options
Enables Multi Site Failover with minimal effort
Allows maintenance and read activity to be distributed