slide1 n.
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

  • Uploaded on

SQL Server 2012 Always On. Lisa Gardner. Premier Field Engineer. Microsoft Corporation. High Availability Outcome Options. Understanding of High Availability Options in SQL Server 2012 Benefits of AlwaysOn HA design patterns utilizing AlwaysOn. Agenda.

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

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

Download Now 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

SQL Server 2012 Always On

Lisa Gardner

Premier Field Engineer

Microsoft Corporation

high availability outcome options

High Availability OutcomeOptions

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


  • 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


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


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
  • AlwaysOn provides many High Availability Options
  • Enables Multi Site Failover with minimal effort
  • Allows maintenance and read activity to be distributed