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 4 – Failover or restart on moderate SQL Server errors 3 – Failover or restart on critical SQL Server errors 2 – Failover or restart on server unresponsive 1 – Failover or restart on server down 0 – No Automatic Failover or restart Query Processing errors Resource errors System errors 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 AlwaysOn Availability Group – synchronous-commit AlwaysOn Availability Group – asyncronous-commit AlwaysOn Failover Cluster Instance Potential Data Loss (RPO)

Zero

Potential Recovery Time (RTO)

Seconds

Automatic Failover

Yes (4) Seconds NA (5) Zero Minutes Seconds to minutes Seconds No Yes Yes

Database Mirroring (2) – High-safety (sync + witness) Database Mirroring (2) – High Performance (async) Logshipping

Seconds Minutes (6) (6) Minutes (6) Minutes to-hours (6) No No

Backup Copy Restore (3)

Hours (6) Hours-to days (6) No

Readable Secondaries (1)

0-2 0-4 NA NA NA Not during a restore Not During a restore

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