1 / 23

SQL Server 2012 Always On

SQL Server 2012 Always On. Lisa Gardner. Premier Field Engineer. Microsoft Corporation. http://blogs.msdn.com/sqlgardner. High Availability Outcome Options. Understanding of High Availability Options in SQL Server 2012 Benefits of AlwaysOn HA design patterns utilizing AlwaysOn. Agenda.

bevis-solomon
Download Presentation

SQL Server 2012 Always On

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2012 Always On Lisa Gardner Premier Field Engineer Microsoft Corporation http://blogs.msdn.com/sqlgardner

  2. High Availability OutcomeOptions • Understanding of High Availability Options in SQL Server 2012 • Benefits of AlwaysOn • HA design patterns utilizing AlwaysOn

  3. Agenda • High Availability Options • AlwaysOn Demo • Readable Secondaries • Readable Secondaries Demo • AlwaysOn Design Patterns

  4. High Availability Options • Pre-SQL Server 2012 • What's New in SQL Server Failover Clustering? • SQL Server 2012 AlwaysOn

  5. Pre-SQL Server 2012 High Availability Options • Backup/Restore • Windows/SQL Server Failover Clustering • Log Shipping • Database Mirroring • Third Party • SAN Replication

  6. AlwaysOn Technologies – Managed by WSFC

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

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

  9. 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 No response from sp_server_diagnostics 1 – Failover or restart on server down Service is down 0 – No Automatic Failover or restart

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

  11. AlwaysOn Concepts • Availability Group • Availability Replica • Availability Database • Availability Group Listener

  12. SQL Server High Availability Options Recap

  13. Demonstration Creating an Availability Group

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

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

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

  17. Demonstration Leveraging Active Secondaries

  18. 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?

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

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

  21. AlwaysOn Design Patterns • White Board/Flip Discussion

  22. Questions?

  23. Conclusion • AlwaysOn provides many High Availability Options • Enables Multi Site Failover with minimal effort • Allows maintenance and read activity to be distributed

More Related