sql server 2012 high availability and dr n.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server 2012 High Availability and DR PowerPoint Presentation
Download Presentation
SQL Server 2012 High Availability and DR

Loading in 2 Seconds...

play fullscreen
1 / 47

SQL Server 2012 High Availability and DR - PowerPoint PPT Presentation

  • Uploaded on

SQL Server 2012 High Availability and DR. Joey D’Antoni SQL Saturday # 142 Waltham, MA 19 May 2012. About Me. @ jdanton on Twitter Principal Architect SQL Server, Comcast Cable Joedantoni.wordpress.com Videos and Blogs at SSWUG.org Vice President of the Philadelphia SQL Server User Group

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 High Availability and DR' - diem

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 high availability and dr

SQL Server 2012High Availability and DR

Joey D’Antoni

SQL Saturday #142 Waltham, MA

19 May 2012

about me
About Me
  • @jdanton on Twitter
  • Principal Architect SQL Server, Comcast Cable
  • Joedantoni.wordpress.com
  • Videos and Blogs at SSWUG.org
  • Vice President of the Philadelphia SQL Server User Group
    • SQL Saturday #121 Philadelphia—June 9th

SQL Server 2008 to 2012—What’s Changed in HA and DR


All about Availability Groups

learning objectives
Learning Objectives

SQL Server HA and DR

What’s involved in SQL Clustering

Understand the Difference Between Windows Cluster and SQL Failover Cluster Instances

How clustering and Availability Groups work

What’s new in 2012 HA/DR

Blog Post with Resources--


licensing what s new
Licensing (What’s New)

The Availability Group features will require the Enterprise Edition of SQL Server

The licensing model for SQL Enterprise Edition has changed. Consult your friendly Microsoft sales representative for more details

AlwaysOn read-only replicas will need to be licensed

windows core support
Windows Core Support

No GUI version of Windows

Allows for fewer patches

Uses PowerShell and MMCs for support

high availability ha and disaster recovery dr options in sql 2008
High Availability (HA) and Disaster Recovery (DR) Options in SQL 2008

Backup and Recovery

Failover Cluster Instances (FCI)


Log Shipping


SAN Replication*


high availability ha and disaster recovery dr options in sql server 2012
High Availability (HA) and Disaster Recovery (DR) Options in SQL Server 2012

Backup and Recovery

Failover Cluster Instances (FCI)


Availability Groups (2012)

Log Shipping


SAN Replication*


what s new in sql server 2012 ha dr
What’s new in SQL Server 2012 HA/DR

AlwaysOn Availability Groups

SMB Support for Failover Cluster Instances

Multi-subnet clustering is supported

Flexible Failover

sql failover clustering in 2008
SQL Failover Clustering in 2008

SQL Clustering required 1 subnet to be used across the whole cluster

Cluster failover is controlled by isAlive/looksAlive processes, which check the SQL service and run @@servername

sql failover clustering in 2012
SQL Failover Clustering in 2012
  • Full support for geo-distributed clusters
  • SMB Storage (File Shares) Supported for FCI
  • Flexible failover model based on sp_server_diagnostics
  • TempDB on Non-shared Disk Resource
    • Makes PCI-based Solid State Drive an option

It’s not just bad cologne anymore


Are you there?

Why Yes I am here

understanding quorum
Understanding Quorum
  • There are a several slides on this topic—it is critical!
    • In a nutshell, you cluster has to be able to talk to itself to keep the cluster service up in running
    • This applies to both SQL Server Failover Cluster Instances and AlwaysOn Availability Groups

Quorum is critical—contains master copy of the cluster’s configuration

Serves as a tiebreaker if network communications between cluster nodes fail

If Quorum fails—cluster is shut down until it’s restored

quorum models
Quorum Models

Node and Disk Majority (Default)

Node Majority

No Majority (Quorum Disk Only)

Node and File Share Majority (Good for Geo Clusters)

quorum failure tolerance
Quorum Failure Tolerance
  • Assuming Disk is Up Calculation is: Cluster Up = RoundUp(Total # of Nodes/2)
  • Assuming Disk is Down Calculation is: ClusterUp = RoundUp (Total # of Nodes/2)-1
why do clusters failover
Why Do Clusters Failover?
  • Initiated by failures in hardware or software
  • Checked by isAlive/LooksAlive processes (in 2008R2 and below)
flexible failover new for 2012
Flexible Failover—New for 2012
  • Replaces looksAlive/isAlive functionality in SQL Clusters (and is used for Availability Groups)
  • Now runs sp_server_diagnostics
    • Accepts two parameter
      • HealthCheckTimeout (Default 60 sec/Minimum 15 sec)
      • Failover Condition Level
what is stretch clustering
What is Stretch Clustering

Also known as Geo-Clustering

geo distributed clustering
Geo-Distributed Clustering
  • Requires SAN replication ($$$$)
  • Two of everything
  • Requires really fast network connection
  • Requires some trickery at the network/DNS level for connectivity
  • Witness Disk (Quorum)
    • Can be physical (SAN) disk, or cluster file share
geo distributed failover clustering
Geo-distributed Failover Clustering

Was available in SQL 2008, but easier to implement in 2012

Won’t be used by most organizations due to cost and complexity

review dr options in sql 2008
Review—DR Options in SQL 2008
  • Mirroring
    • Allowed automatic failover, but only one target
    • Mirror target is unreadable
  • Log Shipping
    • Allowed multiple targets, but failover a manual process, requiring a connection string change
  • Replication
alwayson requirements
AlwaysOn Requirements

Windows Enterprise (Clustering is a requirement)

SQL Server Enterprise Edition

Windows Cluster

No shared storage is required

Quorum Disk (File Share if multi-site or local storage)

sql clusters and always on
SQL Clusters and Always On

SQL Failover Clusters can be members of an Availability Group

FCI can only be configured for manual failover

Only one (the active) node can own the Always On Replica

flexible ag failover
Flexible AG Failover

Similar to how a failover clustered instance fails over

Connects to instance every 30 seconds to perform health check

Also, similar quorum model to Windows Failover Clustering

allows for san less ha dr
Allows for SAN-Less HA/DR

This is not a huge thing for SQL Server in larger organizations, but big win for medium sized businesses

Allows much easier native SQL DR in Virtual Environments

considerations for availability groups
Considerations for Availability Groups
  • All SQL servers (including the secondary in the DR site) in the same Windows domain
  • All the databases must be in FULL recovery model
  • The unit of failover (for local HA, as well as DR) is at the AG level, i.e., group of databases – not the instance
read only replicas
Read Only Replicas
  • Can have up to 4 (1 synch, 3 asynch)
  • SQL Client 2012 will allow for this routing specifically
  • Can take backups from read-only copies*
    • Copy Only Backups (only full copy, does not affect primary log)
    • Can backup primary log from replica
  • Indexing must be same on replicas
  • Bad queries can affect status of replica
read only vs read intent
Read-only vs Read Intent

Read only replica databases are open to any client that can connect to SQL Server

Read Intent routing is used for the Application Intent functionality in the SQL 2012 client

Read intent routing automatically directs connections to either the primary or listener to a secondary replica

client connections in this model
Client Connections in This Model
  • Availability Group Listener
    • Works just like a failover clustering instance (single instance, single IP)
    • Creates a VCO (AD Virtual Computer Object)—similar to a cluster virtual object
  • Read-only Connections
    • Requires 2012 native ODBC client
  • You can determine whether the current replica is the preferred backup replica by calling the sys.fn_hadr_backup_is_preferred_replica function
  • This checks for replica status
  • Allows for post-failover backup jobs to run unchanged in the event of a failure
  • Logic is:

If (top-priority replica is local) Run backup job

Else Exit with success

client connections1
Client Connections
  • Always specify Multi-Subnet Failover=True in listener connection
  • From Books Online

“will significantly reduce failover time for single and multi-subnet AlwaysOn topologies.”

  • SQL Server Failover Cluster Instances as well

Lots of Change in the HA/DR Space

Licensing also changes—talk to your MS rep

SQL Server Failover Clusters still a good HA option

AlwaysOn Availability Groups add a lot more flexibility to DR

contact info
Contact Info

Twitter: @jdanton


Blog: joedantoni.wordpress.com