SQL Server 2012 Always On - PowerPoint PPT Presentation

SQL Server 2012 Always On
Download
1 / 23

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

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

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

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


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


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


Questions

Questions?


Conclusion

Conclusion

  • AlwaysOn provides many High Availability Options

  • Enables Multi Site Failover with minimal effort

  • Allows maintenance and read activity to be distributed


  • Login