always on, multi-site design considerations

John Q MartinSales Engineer

Multi-Site Always On Design Considerations

About Me John Q Martin Sales Engineer for SQL Sentry

Over ten years experience with SQL Server

Previously worked as a DBA, SQL Dev, BI Dev

Worked as a SQL Server Premier Field Engineer (PFE) for Microsoft in the UK

Twitter : @SQLDiplomatEmail : Jmartin@SQLSentry.comBlog : http://blogs.sqlsentry.com/author/JohnMartin/

E-Mail ebooks@sqlsentry.com for free copies of our $10 e-books.

Tuning blog: http://www.sqlperformance.com/

Book a Demo with me:http://SQLSentry.com/BookJohn

What is Always On? Always On is a set of features

Failover Cluster Instances (FCI)

Availability Groups

Availability Groups Database Level Protection

Local Storage

Multiple Replicas

Failover Cluster Instances Instance Level Protection

Shared Storage

Single Database Replica

Why Multi-site?Improved Resilience

High Availability and Disaster Recovery

Service Versatility & Flexibility

RPO and RTO requirements

Multi-site HA Vs DR

Before you start on the multi-site road, understand what your

objective is. Are you looking for Multi-Site High Availability or a

Disaster Recovery option.

Multi-Site Considerations

Storage LayoutNetwork ConnectivitySoftware Versions in useClient Application Design

Storage Configurations

Failover Cluster Instances• SAN Replication

Failover Cluster Instance

Image Source: Windows Server 2008 Multi-Site Clustering Technical Decision-Maker White Paper, November 2007

Storage Configurations

Availability Groups• Local Storage

Image Source: Overview of AlwaysOn Availability Groups (SQL Server) [MSDN]

Network Connectivity Latency Can Hurt

Multiple IP SubnetsClustering Network Config

Network Connectivity Latency Can Hurt

Multiple IP SubnetsClustering Network Config

Site 1 Site 2

Update

Transaction Log Transaction Log

Network Connectivity Latency Can Hurt

Multiple IP SubnetsClustering Network Config

Site 1 Site 2

Transaction Log Transaction Log

Update

Network Connectivity Latency Can Hurt

Multiple IP SubnetsClustering Network Config

Site 1 Site 2

Transaction Log Transaction Log

Update

Network Connectivity Latency Can Hurt

Multiple IP SubnetsClustering Network Config

Site 1 Site 2

Transaction Log Transaction Log

Update

Network Connectivity Latency Can Hurt

Multiple IP SubnetsClustering Network Config

Site 1 Site 2

Transaction Log Transaction Log

Update

Network Connectivity Latency Can Hurt

Multiple IP SubnetsClustering Network Config

Site 1 Site 2

Transaction Log Transaction Log

Local Latency

Remote Latency

Update

Network Connectivity Latency Can Hurt

Multiple IP SubnetsClustering Network Config

Site 1 Site 2

Transaction Log Transaction Log

Local Latency

Remote Latency

UpdateCommit

Cluster Network Configuration

What and where to change.

Demo Code: Cluster DNS Config

## // Set Parameters for the network name you want to modify the DNS behavior for$clusterName = "sqllabcl01"$clusterGroupName = "SqlLabAg01"$clusterResourceType = "Network Name"

## // 0 / False = Register only the active IP Address for the network name## // 1 / True = Register all possible IP Addresses for the network name$ipRegistrationSetting = $false

## // Set reduced TTL (seconds) for DNS Records to use when not registering all network IP addresses$ttlValue = 30

## // If reverting to use all IP addresses then revert TTL for DNS to defaultif($ipRegistrationSetting){ $ttlValue = 1200}

## // Set the Register All Providers IP Address parameterGet-Cluster -Name $clusterName | ` Get-ClusterGroup -Name $clusterGroupName | ` Get-ClusterResource | Where-Object {$_.ResourceType -eq $clusterResourceType} | ` Set-ClusterParameter -Name RegisterAllProvidersIP -Value $ipRegistrationSetting

Demo Code: Cluster DNS Config (cont.)

## // Set the Register All Providers IP Address parameterGet-Cluster -Name $clusterName | ` Get-ClusterGroup -Name $clusterGroupName | ` Get-ClusterResource | Where-Object {$_.ResourceType -eq $clusterResourceType} | ` Set-ClusterParameter -Name RegisterAllProvidersIP -Value $ipRegistrationSetting

## // Set the TTL value appropriately for the configurationGet-Cluster -Name $clusterName | ` Get-ClusterGroup -Name $clusterGroupName | ` Get-ClusterResource | Where-Object {$_.ResourceType -eq $clusterResourceType} | ` Set-ClusterParameter -Name HostRecordTTL -Value $ttlValue

## // Stop the network name resourceGet-Cluster -Name $clusterName | ` Get-ClusterGroup -Name $clusterGroupName | ` Get-ClusterResource | Where-Object {$_.ResourceType -eq $clusterResourceType} | ` Stop-ClusterResource

## // Start the network name resourceGet-Cluster -Name $clusterName | ` Get-ClusterGroup -Name $clusterGroupName | ` Get-ClusterResource | Where-Object {$_.ResourceType -eq $clusterResourceType} | ` Start-ClusterResource

Demo Code: Cross-Subnet settings

# PowerShell Code# Specify the cluster name you want to set the values for$clusterName = "SqlLabCL01"# Specify values$crossSubDelay = 1000 # Min: 250, Max 4000$crossSubThreshold = 5 # Min: 2, Max 120

## Calculation for how long a node can be disconnected."Network Failure Tolerance: " + (($crossSubDelay/1000) * $crossSubThreshold).ToString() + " seconds."

$cluster = Get-Cluster -Name $clusterName

# Frequency that heartbeat is sent in ms$cluster.CrossSubnetDelay = $crossSubDelay# Number of missed heartbeats$cluster.CrossSubnetThreshold = $crossSubThreshold

## Current valuesget-cluster |` select CrossSubnetDelay, CrossSubnetThreshold, SameSubnetDelay, SameSubnetThreshold | ` Format-List

Quorum Configurations

It Depends

Software Versions Windows Server

• 2008 SP2 & Above*

SQL Server Enterprise Ed.• 2012 & Above

Application Design Allow for Latency

• Caching, Queuing etc.

Unreliable Connections• Retry Logic

More Information & Resources

SQL Performance www.sqlperformance.com

Team Blogs http://blogs.sqlsentry.com

Plan Explorer [Free] http://sqlsentry.com/products/plan-explorer

Get in contact with us Twitter : @SQLSentry Facebook : facebook.com/sqlsentry LinkedIn : SQL Sentry, LLC

Questions