SQL Server 2012 Always On
Sponsored Links
This presentation is the property of its rightful owner.
1 / 23

SQL Server 2012 Always On PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

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.

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

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

Sql server high availability options recap

SQL Server High Availability Options Recap



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

  • Login