SQL Server 2012 Always On
1 / 23

SQL Server 2012 Always On - PowerPoint PPT Presentation

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

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' - bevis-solomon

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

Alwayson technologies managed by wsfc
AlwaysOn Technologies – Managed by WSFC

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