sql azure db - bcdr

SQL Azure BCDR Harsh Chawla

Agenda

• What’s BCDR

• BCDR for SQL Azure DB

• Types of Recovery for Azure DB

Business continuity problem

Enabling the application to continuously operate during

unplanned and planned disruptive events

BCDR for on-premises SQL Server

• Maintaining Full Database / Differential / T-log backups

• SQL Cluster

• Log Shipping

• Database Mirroring

• AlwaysON

• Replication

• Database Snapshots

BCDR for SQL Azure DB

• No full/differential/t-log backup support

• No AlwaysON / SQL Cluster / Mirroring / log shipping etc.

Then How?

SQL Azure DB – Database as a Service

• Microsoft takes the responsibility to keep your data safe

• With every tier uptime SLA defined: 99.99% uptime

• Downtime for 24X7 applications can cause huge financial loss

Performance Tier Uptime SLA

Basic 99.99%

Standard 99.99%

Premium 99.99%

Web 99.9%

Business 99.9%

Create a database copyEnsure transactional consistent copy

Export backup to storage accountExport to customer storage account

Repeat as neededCreate additional archive copy as needed

Export a databaseFlexible and portable option but incurs operational overhead

Pros Cons

Portable data format – logical schema and data

Need workaround (DB-Copy) to ensure consistent database

Low cost Slow to restore

Export a database

Types of Recovery

• Recovery from Machine Failure

• Recovery from accidental errors - Oops recovery

• Recovery from regional/datacenter outage

Reads are completed at the primary

Writes are replicated to secondaries

Single logical database

Write

Write Ack

Ack

Read

value write

Ack

Critical capabilities:

Create new replica

Synchronize data

Stay consistent

Detect failures

Failover

99.99% availability

Recovery from Machine Failure

Automatic backup

Full backups weekly, different backup daily, log backups every 5 minutes

Daily and weekly backups automatically uploaded to geo-redundant Azure Storage

Self-service restore

Point-in-time up to a second granularity

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

No additional cost to retain backups

Geo- replicated

Restore from backup

SQL Database

backups

sabcp01bl21

Azure Storage

sabcp01bl21

Oops recovery - Point-in-time restore

Restores the database to the point of deletion

(earlier backups are deleted)

Creates a new database on the server used by

the original database

You can choose to failover to the restored

database or use scripts to recover data

Recovery after accidental database deletion

Self-service

restore to point

of deletion

Backups retained for 7/14/35 days

Restore deleted database

Now -7 daysTime

Geo-restoreSelf-service restore API

Restores last daily backup

No extra cost, no capacity

guarantee

RTO>=24h, RPO=24h

Database URL will change after

restore

Geo- replicated

SQL Database

backups

sabcp01bl21

Azure Storage

sabcp01bl21

Restore to any

Azure region

Demo!!

East US

US West

LS ABC

Failover and activation of

secondary (during incident) West US

DB

LS XYZ

DB

• RTO<2h, RPO<5m

• REST and PowerShell API to opt-in and failover

• Automatic data replication and synchronization

• DMV+REST to monitor and guide failover decisions

• Single offline secondary with matching performance level in

the DR paired region

North Central US

LS OPQ

DB

Recovery from regional/datacenter outage

Standard Replication

Active Geo-replication

LS ABC

South Central US

West US

Failover and activation of

secondary (any time)

East US

DB1

LS XYZ LS OPQ

• RTO<1h, RPO<5m

• REST and PowerShell API to opt-in and failover

• DMV+REST to monitor and guide failover decisions

• Automatic data replication and synchronization

• Up to 4 online secondary databases with matching

performance level in any region

DB1DB1.old

North Central US

LS DFE

DB1

DB1

Demo!!

BCDR Tiered Model

B

Transactions per hour

Transactions per minute

Transactions per second

)ERT*<12hRPO**<1h

ERT<12hRPO<1h

ERT<12hRPO<1h

ERT<30s RPO<5s ERT<30s RPO<5s

ERT<30s RPO<5s

* Estimated Recovery Time (ERT) - The estimated duration for the database to be fully functional after a restore/failover request.** Recovery Point Objective (RPO) - The amount of most recent data changes (time interval) the application could lose after recovery

Q & A!!

Thank You!