Mission Critical - AlwaysON

makeshiftklipInternet and Web Development

Oct 31, 2013 (3 years and 7 months ago)

136 views

MODIFY THIS SLIDE FOR ACTUAL PRESENTER, DELETE THIS BAR

AFTER
MODIFICATION



1
1
The
percentage reduction in patching varies
&
can be less based on the server roles that are enabled
&
the type of patches that are applied
.

Multi
-
site
Failover
C
luster
Instance (FCI)

for
HA
&
DR


Shared Storage solution


fnstance ievel eA


fnstance ievel ao


Doesn’t require database to be in FULL recovery
model

Availability Group

for HA & DR


Non
-
Shared Storage solution


⡇牯up of⤠aatabase ievel eA


⡇牯up of⤠aatabase ievel ao


ao 牥plica can be Active 卥conda特


oequi牥s database to be in crii 牥cove特 model

Failover Cluster Instance
for
local HA
& Availability
Group for
DR


Combined Shared Storage and Non
-
Shared
Storage


fnstance ievel eA


⡇牯up of⤠aatabase ievel ao


ao 牥plica can be Active 卥conda特


oequi牥s database to be in crii 牥cove特 model


Active Secondary


Monitoring and
Troubleshooting
enhanced


Automation using
PowerShell

AlwaysOn
Availability Groups
is a new feature that enhances

and
combines database mirroring and log shipping capabilities


Multi
-
database failover


Multiple secondaries


Synchronous and
asynchronous data
movement


Built in compression and
encryption


Auto
-
page repair


Automatic and manual
failover (new design)


Flexible failover policy


Application failover using
virtual name


Configuration Wizard


Dashboard


System Center
Integration


Rich diagnostic
infrastructure


File
-
stream replication


Replication publisher
failover

TechAG1

2
DB

2
D
B

Primary

Secondary

TechListener1

Parameter Sample
:
-
server
TechListener1;
-
catalog HRDB

Application retry during failover

Primary

Secondary

Secondary

2
D
B

ServerA

ServerB

ServerC

Availability Groups Listener allow applications to failover seamlessly
to any
secondary; reconnecting through Virtual Network Name

Connect to new primary once

failover is complete

and the listener is online

Availability Group
uses WSFC
for

Database

Active Log

Synchronization

Database

Active Log

Synchronization

WSFC is a Common Microsoft Availability Platform

Multiple no
data loss
secondaries

Faster failover to DR
Rich diagnostics

Multi
-
DB failover

New Management Dashboard

IT EFFICIENCY AND COST
-
EFFECTIVENESS ARE CRITICAL FOR
BUSINESSES

Idle hardware
is
no longer
an option.


ACTIVE
SECONDARY
USES

Read
-
only
workloads

Offloading
Backups

AlwaysOn
Active Secondary
enables efficient utilization of

high
availability hardware resources
to improve overall
IT efficiency

SQLservr.exe

SQLservr.exe

Readable secondary allow
offloading read queries to secondary

Low data latency

After failover, the read applications can be
automatically redirected
to the new Secondary
(require explicit connection request)

N
ot a replacement for
replication scenarios

Primary

Secondary

Primary

Secondary

Database Log
Synchronization

DB2

DB1

InstanceB

Reports

DB2

DB1

InstanceA

Reports

Secondary

Primary

Log Cache

Log Cache

Secondary read is always behind primary during transaction activity

DB1 Log

Log
Capture

Log
Receive

DB1 Data

Redo
Thread

Redo
Pages

DB1 Log

DB1 Data

Log Hardened

Log Flush

Commit

Acknowledge
Commit

Log Pool

Log
Capture


CONCURRENCY AND BLOCKING

REDO

can get blocked by reporting workload

REDO

thread and read workload can deadlock

SOLUTION

Internally map
read workload to non
blocking
isolation
levels (no application changes
required)


Read Uncommitted


Snapshot Isolation


Read Committed


Snapshot Isolation


Repeatable Read


Snapshot Isolation


Serializable


Snapshot Isolation


Ignore all locking hints

Never choose
REDO

as deadlock victim

PRIMARY

SECONDARIES

Read

Read/Write

RESULT

Blocking and deadlock between Reporting workload (i.e. Query) and REDO thread is
eliminated

No issues with DML (INSERT/DELETE/UPDATE) as it is not allowed

Will incur additional cost of row versioning.

READ / WRITE WORKLOAD


Connecting using
AG Listener


Connection using FAILOVER_PARTNER (if
connection string of existing applications can’t be
changed)

READ ONLY WORKLOAD


Connection using VNN and
ApplicationIntent
=
ReadOnly


Connection to the secondary instance directly


ReadOnly

Routing

MULTI SUBNET FAILOVER SCENARIO:


New client libraries =>
MultiSubnetFailover
=True


Old client libraries configure appropriate client
connection timeout


CLIENT

PRIMARY

SECONDARIES

Backups from

any
r
eplica

Synchronous or
asynchronous secondaries

Primary backups still work

Adds capacity
to
primary server by off
-
loading backups to a
replica

Log backups done

on all
replicas form

a
single log chain

Recovery Advisor makes
restores simple

All SQL servers (including the secondary in the DR
site) in the same Windows domain


One Windows Server Failover Cluster spreads over
the primary and DR sites

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


Consider using Contained Database for containing
logins for failover


For jobs and other objects outside the database
,
simple customization needed

No delayed apply on the
secondary like log
shipping

Removing
log shipping means
the regular log
backup job is
removed


Need to re
-
establish
periodic log backup (essential
for truncating the log)

AlwaysOn Dashboard

System Center
Operations Manager


Provide
High Availability
at the Instance Level


Unit of failover = SQL server instance


Maintain same virtual network name after failover. Clients re
-
connect to same name


Instance restart requires database to go through recovery

Provide
Disaster Recovery
at the Instance Level


Provide
Disaster Recovery
protection from site failure: be it network, power, infrastructure
or other site disasters.


Require storage based replication technology and networking considerations


Multi
-
subnet support:

HA & DR Solution

SQL Server 2008
R2

NO


Create stretch sirtual
-
iAk EsiAkF to act as a single
subnet

SQL Server 2012

YES


IP address OR dependency support
within SQL
Server setup


SQL Engine skips binding to IP’s not online on start
-
up

Corpnet

Network Name:
SqlClust

subnet 1

subnet 2

IP1: 10.168.0.10

IP2: 192.168.0.10

SAN Replication

Local Site

Remote Site

OR

WHY WE ENABLE THIS?


tempdb

access occupies large % of
SAN I/O


Fast local HDD/SSD becomes
standard Server configuration

BENEFITS


Better overall performance


Cost saving

IMPORTANT NOTE!


Ensure that
tempdb

local paths are
available to SQL Service on all the
nodes


LOCAL TEMP
DB

(
Fast disk, SSD)

LOCAL TEMP
DB

(
Fast disk, SSD)

SECONDARIES

PRIMARY


Diagnos
tics

Configurable
options eliminate false failover

Improved
logging for
better diagnostics



SQL Server Hands
-
on
-
Labs

SQLSERV
ERLAUNC
H

.COM