Business Continuity and Disaster Recovery

SQL Azure Database

© 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. 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

Sourabh K. Agarwal

Sr. Premier Field Engineer

Blogs : www.SQLUninterrupted.com

Twitter: @SQLSourabh

Facebook – https://www.facebook.com/groups/SQLBangalore/

SQL Azure Database Service Tiers

• 5 DTU’s• Max 2 GB DatabaseBasic

• S0, S1, S2, S3• 10-100 DTU’s• 250 GB Max DB Size

Standard

• P1, P2 and P3• 125-1000** DTU’s• 500 GB Max DB Size

Premium

Click icon to add picture

Demonstration

Changing Service Tiers1. Azure Portal2. PowerShell

Business Continuity Problem

Enabling the application to continuously operate during unplanned and planned disruptive events

Local Redundancy

Reads are completed at the primary

Writes are replicated to secondary's

DB

Single LogicalDatabase

• Transparent automatic failover• Uptime SLA of 99.95%

P

SS WriteWrite

AckAck

ReadValue WriteAck

P

S

S

High availability under the hood

•Critical capabilities:

Create new replica

Synchronize data

Stay consistent

Detect failures

Fail over

Primary Manager Node

Partition Manager

Fabric

Data Node 103

P

SS

Data Node 104

SS

Data Node 102

P

SS

Data Node 105

PSS

Data Node 101

SP

P

P

S SS

Global Partition

Map

Node down

Which replica lost?

Pro

mote

to

pri

mary

Reconfigure

S

Point In Time Restore

• Automatic Backup

– Full backups once a week, diff backup once a day, log backups every 5 min

– Backups automatically uploaded to geo-redundant Azure Storage on a daily basis.

• Self-service restore

– REST API, PowerShell or Portal

– **Creates a new database in the same logical server**

• Tiered Retention Policy

– Basic - 7 days, Standard - 14 days, Premium - 35 days

sabcp01bl21

sabcp02bl21

sabcp03bl21

Restore as a new

database from local backups

LS XYZ

Copy backups to Azure Storage

DB

DB1

RA-GRS

Backups

Backups

Click icon to add picture

Demonstration

Point in Time Restore of Azure SQL Database1. Azure Portal2. PowerShell

Geo-restore

US East

US Westsabcp01bl21

sabcp02bl21 sabcp03bl2

1

LS ABC

Restore to any server

when needed

US West

DB

sabcp01bl21

sabcp02bl21 sabcp03bl2

1

LS XYZ

Automatic copies of

daily backups

DB

RA-GRSRA-GRS Storage geo-replication

• Self-service restore API• Restores last daily backup• ERT < 12h, RPO < 1h• No extra cost, no capacity guarantee• Database URL will change after restore

Click icon to add picture

Demonstration

Geo-Restore of Azure SQL Database1. Azure Portal2. PowerShell

Standard Geo-replication

East US

US West

LS ABC

Self-service activation of secondary

(during incident) West US

DB

LS XYZ

DB

Geo-replication

• ERT < 30s, RPO < 5s• REST and PowerShell API to opt-in and failover• Automatic data replication and synchronization• DMV+REST to monitor and guide failover decisions• Single secondary in the DR paired region with matching

performance level• No Choice in choosing the Region (Pre-Defined DR Paired Regions)

Active Geo-replication

Geo-replication

LS ABC

South Central US

West US

Self service failover to

the secondary database

East US

Geo-re

plicat

ion

DB1

LS XYZ LS OPQ

• ERT < 30s, RPO < 5s• REST and PowerShell API to opt-in and failover• DMV+REST to monitor and guide failover decisions• Automatic data replication and synchronization• User controlled placement of up to 4 secondary's• Creates secondary database with matching performance level• Choice of Server/Region is with the customer

DB1 DB1

Under the Hood

Click icon to add picture

Demonstration

Create Replicated Secondary 1. Azure Portal2. PowerShell

BCDR Scenarios in Service tiers

Scenario Basic Standard Premium

Local failures

Azure DB upgrades and maintenance

Accidental data corruption

Regional disaster

DR Drill

Online application upgrade

Online application relocation

Load balancing

BCDR Capabilities

  Basic Standard Premium

Uptime SLA's 99.99%

Performance Benchmarks 16,600 txns/hour upto 5100 txns/min up to 735 txns/sec

Point In Time Restore Up to millisecond within last 7 days

Up to millisecond within last 14

daysUp to millisecond within last 35 days

Geo Restore ERT < 12h, RPO < 1h

ERT < 12h, RPO < 1h

ERT < 12h, RPO < 1h

Standard Geo Replication (One Non-Readable Secondary) Not Available ERT < 30s, RPO <

5s ERT < 30s, RPO < 5s

Active Geo Replication (upto 4 readable secondaries, in diff

regions)Not Available Not Available ERT < 30s, RPO < 5s

BCDR Pricing

  Basic Standard Premium

Point In Time Restore No Additional Cost (**)

Geo Restore No Additional Cost (**)

Standard Geo Replication (One Non-Readable Secondary) Not Available 75% of Primary (Same Performance

Level)

Active Geo Replication (upto 4 readable secondaries, in diff

regions)Not Available Not Available Same as Primary,

per secondary

Click icon to add picture

Demonstration

Perform failover of Azure SQL Database

Questions