SQL Server - PowerPoint Presentation

462 views
Uploaded On 2016-07-09

SQL Server - PPT Presentation

AlwaysOn Active Secondaries Luis Vargas Program Manager Microsoft Corporation DBI312 Understand capabilities limitations and considerations Understand how AlwaysOn Active Secondaries allow leveraging HADR hardware ID: 397734

secondary read backup active read secondary active backup microsoft primary secondaries server workloads backups workload alwayson log failover sql connections routing replica

Link:

Copy

Embed:

<iframe width="560" height="315" src="https://www.docslides.com/embed/397734" frameborder="0" allowfullscreen></iframe>

Download Presentation from below link

Download Presentation The PPT/PDF document "SQL Server" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.

Presentation Transcript

Slide1

SQL Server AlwaysOn: Active Secondaries

Luis VargasProgram ManagerMicrosoft Corporation

DBI312Slide2

Understand

capabilities

, limitations, and considerations

Understand how

AlwaysOn

Active Secondaries allow leveraging HA/DR hardwareRead WorkloadsBackups

Session ObjectivesSlide3

Agenda

AlwaysOn

Active Secondaries

Offloading

read workloads

Offloading backupsSlide4

AlwaysOn

SQL Server 2012 HA/DR technologies

Pre-existent

Server

failover

Shared Storage (SAN

SMB)

Failover takes minutes

Multi-Node

Clustering

Passive secondary nodes

Failover Cluster

Instances

for servers

NewMulti-database FailoverDirect attached storageFailover takes secondsMultiple SecondariesActive Secondaries

Availability Groupsfor groups of databases

Failover Cluster

Instancesfor servers

Availability

Groupsfor groups of databases

Active

SecondariesSlide5

Example Availability Group TopologySlide6

AlwaysOn

Active

Secondaries

Cost-effectiveness is critical

Idle hardware

is wasted

Database mirror server similar to primary

Utilization

low (~5% CPU, 25% I/O)

Active Secondaries allow using hardware for more than HA/DR: Offload work from primary

Read

workloads

BackupsSlide7

Active SecondariesOffloading Read WorkloadsSlide8

Running Read Workloads - Before

Database Mirroring

Transactional Replication

Run on

primary

Impacts

workload

Run on

mirror w/snapshots

Data Staleness

Additional management

No failover of read workloads

Run on a s

ubscriber

Pros:Large number of subscribersTargeted indexes

Cons: Separate solution to configure/manageReplication latencyNo failover of read workloads Slide9

Running Read Workloads - Now

DB2

DB1

SQL Server

DB2

DB1

Primary

Active

Secondary

Reports

Log Synchronization

DBs in active secondaries are readable

Committed transactions

visible eventually (depends on log synchronization latency)

Same data center: <1sDifferent data center: 1-5sSlide10

Running Read Workloads - Now

DB2

DB1

SQL Server

Primary

Up to 4 secondaries

Sync or

asyncSlide11

Running Read Workloads - Now

DB2

DB1

SQL

Server

SQL Server

DB2

DB1

Primary

Secondary

Log Synchronization

Reports

Primary

Secondary

Reports

Manual

Failover

Read workloads can be automatically routed to an active secondarySlide12

ALLOW_CONNECTIONS setting

Configuring Secondary as Readable

Don’t allow connections

ALL

Allow all connectionsREAD_ONLYOnly allow connections specifying READ_ONLY intent

ALTER AVAILABILITY GROUP

ag_name

MODIFY REPLICA

server_name'WITH ( SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | ALL |

READ_ONLY } ) )Slide13

Clients can connect to readable secondary without

changes

Set ALLOW_CONNECTIONS = ALL

Clients Connectivity

If possible, use

Application

Intent

New connection string property

Specifies workload type: READ_ONLY /

READ_WRITE

Opens new capabilities

Disallow

connections

Automatically route READ_ONLY connections to an active secondarySlide14

Configure

Routing URL

for each secondaryEndpoint for read-only connections

Configuring Routing of Read-Only Connections

ALTER AVAILABILITY GROUP

ag_name

MODIFY REPLICA

server_name

WITH ( SECONDARY_ROLE ( READ_ONLY_ROUTING_URL = ‘TCP://system-address:port’ ) )Slide15

Create

outing List at each replica that can be primary

List of readable secondaries

Primary returns first one available

Load balancing not offered (implementable)

Configuring Routing of Read-Only Connections

ALTER AVAILABILITY GROUP

ag_name

MODIFY REPLICA

'server_name'WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST

= {'server_name' [,..n]} ) )Slide16

demo AlwaysOn

: Active SecondariesOffloading Read WorkloadsSlide17

Log Synchronization to Secondary

Redo is

async

Transactions visible after

commit

redo (no dirty reads) Slide18

Impact of Read Workload

Workload can take

resources from Redo

thread

Redo can fall behind



impact RTO

Mitigations:

Use Resource

Governor

No workload on auto failover

targetSlide19

Contention between Redo and read workload avoided internally

Workload

executed on Snapshot IsolationLocking hints ignoredRedo thread never

a deadlock victim

Impact of Read Workload

DDL Redo can still be blocked by read workload

DDL is infrequent - if possible run when low activity

XEvent

lock_redo_blockedSlide20

Query Optimization on Active Secondary

Query optimization relies

on statisticsCreated by indexes and read workloads

Auto-create

statistics on

secondary

Store

TempDB

sys.stats: is_temporary

=‘true

’

Use most recent statisticsRemove on failover, restart, or DROP STATISTICSStatistics created on primary are redone on secondary But

, read workloads at secondaries are different from primary workloads Slide21

Active SecondariesOffloading BackupsSlide22

Creating Backups - Before

Primary

Secondary

Failed

Backups

Database Mirroring

R/W workload

Only on primary

Impacts primary

workload

Fails after failoverSlide23

R/W workload

Primary

Backups

Active

Secondary

Backups

Active

Secondary

Backups

Creating Backups - Now

On primary or any secondary

No need to configure secondaries as readableSlide24

Capabilities

Backup on sync

async

secondaries

Full Backup (Copy-Only)

Log Backup

Backup automation

No Differential Backup

Slide25

Single Log Backup ChainSlide26

Backup Strategy

Full Backup (Copy-Only) and

og Backup on secondary

If Differential Backups are needed:

Full Backup and Differential Backup on primary

Log Backup on secondarySlide27

Replica Priority

0 (never) ... 100 (highest)

Backup Automation

Role Preference

Primary Only

Secondary OnlySecondary If Possible

NoneSlide28

Backup Automation

Filter

out replicasnot onlinedon’t meet

role

preference

Select highest-priority replicaBreak ties with replica name

Not enforced by

BACKUP command

Implemented as a system function

sys.fn_hadr_backup_is_preferred_replica

dbname

')Used byMaintenance Plan Wizard

Log Shipping WizardSlide29

Recommendations

Use Recovery

AdvisorEasier to restore to point in time

Store backups

centrally

Easier to trackSlide30

demo AlwaysOn

: Active SecondariesOffloading BackupsSlide31

announcing

Read WorkloadsBackups

DRare notActive Secondaries in Windows AzureSlide32

Active Secondaries make

AlwaysOn

cost-effectiveSummary

Use hardware for more than

HA/DR: Offload

work from primaryRead workloads

Without client changes

With

Application Intent

: Read-only Routing (provides HA)

Backups

Full Backup (copy-only) and Log Backup

Automation supported

Windows Azure support is comingSlide33

Resources

AlwaysOn

Resource Center

http://msdn.microsoft.com/en-us/sqlserver/gg490638.aspx

SQL Server 2012 Whitepapers

http://

msdn.microsoft.com/en-us/library/hh403491

AlwaysOn

Team Blog

http://blogs.msdn.com/b/sqlalwayson/Slide34

Contacts

luis.vargas@microsoft.com

social.msdn.microsoft.com/Forums/en-

sqlhadr

Slide35

DBI Track Resources

@sqlserver

teched_europe

msTechEd

Microsoft Virtual Academy

SQL Server 2012 Eval Copy

Get Certified!

Hands-On LabsSlide36

Resources

Connect. Share. Discuss.

http

://europe.msteched.com

Learning

Microsoft Certification & Training Resources

www.microsoft.com/learning

TechNet

Resources for IT Professionals

http://microsoft.com/technet

Resources for Developers

http://microsoft.com/msdn Slide37

Evaluations

http://europe.msteched.com/sessionsSubmit your evals online Slide38

2012 Microsoft

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the

part

of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation.

MICROSOFT

MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.