Advanced HA and DR.

(1)

Advanced HA

and DR

ינא ימ

?

• ןמדירפ ודיע

• ךירדמו ץעוי

SQL Server

תרבחב

רונילאו

• םוחת לש יגולונכט ליבומ

SQL

• [email protected]

(2)

רונילאו

• _{םייעוצקמ}

_{םיתוריש}

_ןתמב

_לארשיב

_{הליבומה}

_הרבחה

םינותנ

יסיסב

םוחתב

• לש

םידברה

ללכב

החמתמה

ליבומ

םיצעוי

תווצ

םינותנה

יסיסב

• לש

בהז

תמרב

םיפתוש

Microsoft

ו

-Oracle

• םינותנה

יסיסב

םוחתב

םיליבומ

םירצומ

יגיצנ

• לעמ

300 לכב

תוחוקל

םירזגמה

http://www.valinor.co.il

רונילאו

(3)

Agenda

• 09:00-10:30 – SQL Server and HA

• _{10:30-10:45 – Break}

• _{10:45-12:30 – Hands on LAB}

(Cluster, log shipping and

replication)

• _{12:30 – 13:30 – Launch}

• 13:30 – 15:00 – What is Always ON + LAB

• _{15:00– 15:15 – Break}

• _{15:15 – 16:15 – Replication in real life}

(Maria Zakourdaev)

• _{15:15 16:30 - Summary}

(4)

DRP

Uptime

Number of 9’s Availability Percentage Total Annual Downtime

2 99% 3 days, 15 hours

3 99.9% 8 hours, 45 minutes

4 99.99% 52 minutes, 34 seconds

(5)

Disaster recovery plan

DRP

RPO

(Recovery

point

objective)

RTO

(Recovery

time

objective)

Scope of

disaster

Scale out

ROI

(6)

Business continuity

Provide DR and HA to

business critical

services ONLY

(7)

What needs protection

Where do we recover

LAN HA WAN DR Synchronous Asynchronous

(8)

SQL Server components

SQL Server DB engine Reporting services Analysis Services Integration Services SQL Server Agent Full text Search

SQL Server component

session state

Stateful

• Database engine

• SQL server agent

• Analysis services

• Full-text search

Stateless

• Reporting services

(9)

Solutions SQL 2008

(10)

Fail Over Cluster instance

Solution

Level

Instance

Pros

Very low failover time No affect on the application Supported on standard edition WSFC can be used for other HA needs

Cons

Requires shared storage “Complex” AD required

FCI

How does it work

FCI Elements

Service management Automatic control of SQL services Storage Management Clustered Disk Network Management

(11)

FCI

How does it work

Failover Active

Active Active

Instance A Instance B Instance C

FCI What’s new in SQL2012

Multi site

support

Enhanced

health checks

Failover

policies

tempDB on

local storage

(12)

Multisite FCI

(13)

Mirroring

Solution

Level

Database

Pros

Synchronous or Asynchronous Automatic failover Share nothing solution

Does not require AD

Cons

Limited access to mirror

(14)

Mirroring

How does it work

Mirroring Elements

Sync type

High safety High safety with auto failover High performance

Servers

Primary Mirror Witness

Mirroring

How does it work

(15)

Log Shipping

Solution

Level

Database

Pros

Multiple Copies

Delayed copy of

data

Very simple

implementation

Cons

Limited access

to copies

Client side

failover

Limited Fail

back

(16)

Physical DB Structure

Log Log file: .ldf Data file: Data file: .mdf or .ndf Extent: 8 contiguous Extent: 8 contiguous 8KB pages Page: 8KB Page: 8KB

SQL Server backups

Full Backup

Transaction

log

(17)

Log shipping

How does it work

Log shipping

Elements

Servers

Primary server Secondary server Monitor Server

Databases

Primary Database Secondary Database

Jobs

Backup Copy Restore

Log shipping

How does it work

(18)

Log shipping

DEMO

Replication

Solution

Level

Object

Pros

High Granularity R/W copy of data

Low latency data sync

Cons

Complex

Client side failover

Requires maintenance

Affects data and structure

(19)

Transactional Replication

How does it work

Transactional Replication

Elements

Servers

Publisher Subscriber Distributor

Objects

Publication Article

Agents

Log reader Snapshot Distribution

Transactional Replication

How does it work

(20)

Transactional replication DEMO

What happens when…

• _{Business requirements are:}

–

_{Automatic failover}

–

_{Of multiple databases together}

–

_{With no single point of failure}

–

_{Redundancy across}

(21)

SQL Server 2008 R2 or prior

No

single

solution

Mirroring

Log shipping

Cluster

Replication

SQL Server 2012

(22)

Always ON

Availability Group

Solution

Level

Solution

Level

Availability Group

Pros

Multi database solution Sync or Async

Listener Multiple secondaries Read Only secondaries

Low latency failover More..

Cons

Enterprise edition only

Requires AD

Cluster

Log shipping

Replication

(23)

A

SQL Server 2012

Always On Availability Groups

What happens when…

• _{Business requirements are:}

–

_{Automatic failover}

–

_{Of multiple databases together}

–

_{With no single point of failure}

–

_{Redundancy across}

(24)

SQL Server 2008 R2 or prior

Database mirroring provides automated failover

of a single database

Redundant copies of the database exist on

principle and mirror server

Failing over multiple databases at the same time

requires custom code

Application failover is accomplished through the

use of FailoverPartner connection string value

SQL Server 2012

Always ON AG

up to four copies of the

databases

Synchronously or

Asynchronously

Multiple-database

failover is handled

automatically by the

Availability Group

Application failover

through the Availability

Group Listener and

Application Virtual

(25)

Availability group architecture

WSFC role in AG

Inter-node health detection

Failover coordination

Primary health detection Distributed data store for

settings and state Distributed change notifications between nodes

Mixed synchronous and

asynchronous in one AG

Replaces multiple technology implementations in a single

solution

Support for up to three synchronous replicas, one primary

and two secondaries

Support for multiple Availability Groups with separate failure paths

for different applications

Architecture – availability

group failover

• Clients connect to primary replica through the Availability Group Listener virtual name

• A failover is initiated using WSFC Manager

– SQL-AG1 confirms failover and informs WSFC

– WSFC tells the AG resource DLL to bring SQL-AG1 offline

• Clients are disconnected from SQL-AG1

• WSFC tells the AG Resource DLL to bring SQL-AG2 online

– Availability Group Listener Virtual Name resource moves to SQL-AG2 and brought online by WSFC

– Clients are automatically redirected to SQL-AG2 through the Availability Group Listener virtual name

• WSFC provides notification of the new primary server to all secondary replicas

– Secondaries reconnect to the new primary replicas mirroring endpoint and synchronization resumes

A v a il a b il it y G ro u p Li st e n e r

(26)

Always ON Availability Group

DEMO

Always ON AG

Summary

SQL Server 2012 offers

higher availability and

disaster recovery

Up to four secondary replicas

Combination of synchronous and asynchronous replicas

Choice of automatic or manual failover modes

SQL Server 2012 offers more

flexibility for configuring

your HA/DR topology

Ability to read from secondary

replicas

Groups of databases can fail over together, removing need

for complex logic

Broad cross-feature support, including FILESTREAM, RBS, FILETABLE, and Service Broker

(27)

Always ON

(28)

SQL HA And Virtualization

YES

ITS OK to virtualize SQL Server

Common

mistakes

No need for SQL HA Over sharing resources Highly complicated deployments

FCI and

Virtualization

Supported with limitations

Reduce planed downtime

Windows

Server core

Online

Operations

Rolling

Upgrade and

Patching

(29)

SQL HA and the cloud

PaaS

SQL Azure Amazon RDS

IaaS

Amazon EC2 Windows Azure

SaaS

Office 365 Sales Force

SQL HA and the cloud

The cloud is not

bullet proof

Planed down

time still occur

Check what site

level protection

is provided

(30)

Upgrading HA to SQL 2012

SQL

Server failover

clustering

Failover

Cluster

instance

Log

shipping

Log

shipping

Mirroring

AlwaysON

AG

Summary

Feature/Tech. Cluster Mirror Log shipping Replication Always ON AG

Automatic failover

₊

_-

₊

Transparent client redirect

₊

_-

₊

Scope of protection Instance DB DB Object AG

Number of database copies 1 2 Multiple Multiple Multiple

Geo scope LAN* LAN/WAN LAN/WAN LAN/WAN LAN/WAN

Shared storage required

₊

_-

-Database level filter

_-

₊

Object level filter

_-

₊

-Active / -Active

(31)

Feature/Tech. Cluster Mirror Log shipping Replication Always ON AG

Data loss potential Non Non* Medium Medium Non*

Database changes required

-

+

Summary

* In Synchronized mode (high-protection)

Summary

Identify

Business

needs

Select

correct

solution

Test and

implement

solution

Achieve

HA

(32)