slide1
Download
Skip this Video
Download Presentation
SQL Server 2012 Always On

Loading in 2 Seconds...

play fullscreen
1 / 23

SQL Server 2012 Always On - PowerPoint PPT Presentation


  • 365 Views
  • Uploaded 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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' SQL Server 2012 Always On' - davina


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

SQL Server 2012 Always On

Lisa Gardner

Premier Field Engineer

Microsoft Corporation

http://blogs.msdn.com/sqlgardner

high availability outcome options

High Availability OutcomeOptions

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

Agenda

  • High Availability Options
  • AlwaysOn Demo
  • Readable Secondaries
  • Readable Secondaries Demo
  • AlwaysOn Design Patterns
high availability options
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
Pre-SQL Server 2012 High Availability Options
  • Backup/Restore
  • Windows/SQL Server Failover Clustering
  • Log Shipping
  • Database Mirroring
  • Third Party
    • SAN Replication
what s new in sql server failover clustering
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
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
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

always on availability groups
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
AlwaysOn Concepts
  • Availability Group
  • Availability Replica
  • Availability Database
  • Availability Group Listener
demonstration

Demonstration

Creating an Availability Group

readable secondaries
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
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
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
demonstration1

Demonstration

Leveraging Active Secondaries

query performance on secondary
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
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 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
AlwaysOn Design Patterns
  • White Board/Flip Discussion
conclusion
Conclusion
  • AlwaysOn provides many High Availability Options
  • Enables Multi Site Failover with minimal effort
  • Allows maintenance and read activity to be distributed
ad