Scenario - Download Center

BCDR Tiered Model
Azure SQL Datenbanken as a Service
B
)
Transactions
per hour
Transactions
per minute
Transactions
per second
RTO<24h*,
RPO<24h
RTO<24h*,
RPO<24h
RTO<24h*,
RPO<24h
RTO<2h
RPO<30m
RTO<2h
RPO<30m
RTO<1h,
RPO<5m
* Target only, actual time depends on the data size and scale of restores
Cloud Db Hardware – DTU & Quota
Datenbankdurchsatzeinheit (Database Throughput Unit, DTU): Die auf den jeweiligen Leistungsstufen verfügbaren Ressourcen werden in
DTUs ausgedrückt. Hierbei werden CPU, Arbeitsspeicher, physische Lesevorgänge und Transaktionsprotokoll-Schreibvorgänge in einer einzigen
Einheit kombiniert. Eine Leistungsstufe mit 5 DTUs bietet fünfmal mehr Leistung als eine Leistungsstufe mit 1 DTU.
High availability platform
Single Logical
Database
• Critical capabilities:
 Create new replica
 Synchronize data
 Stay consistent
 Detect failures
 Fail over
 99.99% availability
DB
Ack
Value Read
Write
P
Ack
S
Write
P
Ack
Write
S
Reads are completed at the primary
Writes are replicated to secondaries
Point In Time Restore
RA-GRS
sabcp01bl21
sabcp02bl21
• Automatic Backup
• Full backups weekly, diff backup daily,
log backups every 5 min
• Daily and weekly backups automatically
uploaded to geo-redundant Azure Storage
sabcp03bl21
Copy backups to Azure Storage
LS XYZ
• Self-service restore
• REST API, PowerShell or Portal
• Creates a new database in the same logical
DB
Backups
server
DB1
• Tiered Retention Policy
• Basic - 7 days
• Standard - 14 days
• Premium - 35 days
Backups
Restore as a
new database
from local
backups
DEMO
Point in time restore
Standard geo-replication
LS OPQ
DB
LS ABC
Failover and
activation of
secondary
(during incident)
•
•
•
•
•
LS XYZ
DB
North Central
US
West US
RTO<2h, RPO<30m
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
DB
East US
Active Geo-replication
North Central US
For Premium Database
LS DFE
DB1
Failover and
activation of
secondary
(any time)
LS OPQ
LS XYZ
DB1.old
DB1
DB1
LS ABC
•
•
•
•
•
West US
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
DB1
East US
South Central US
DEMO
geo-replication
Auditing - Overview
• Where to start?
New Auditing Feature in Azure SQL DB
• Configurable to track & log database activity
• Dashboard views in the portal for at-a-glance
insights
• Interactive & customizable Power View and
Power Pivot reports for deep analysis on Audit
log data
• Audit logs reside in your Azure Storage
account
• Available in Basic, Standard, and Premium
Audit
log
Azure Storage
SQL Database
Auditing
Application
data
Setting up Auditing
• Per DB
Demo
Azure SQL DB - Auditing
Source: http://blogs.technet.com/b/dataplatforminsider/archive/2014/11/05/microsoft-announces-major-update-to-azure-sql-database-adds-free-tier-to-azure-machine-learning.aspx
Terminology
(2) Sharding Key
Order Table
Customer Table
Customers
Cust
ID
First
Name
Last
Name
Order
ID
Cust
ID
Date
1
Bob
Smith
1
1
Jan 1
1
2
Alice
Lamp
2
1
May 2
1
3
Joan
Lake
3
1
May 5
2
4
2
May 7
2
5
3
Jun 8
3
Data Center Table
DC
City
State
1
Seattle
WA
2
Duluth
MN
3
Ames
IA
(3) Shard Map
Manager
DC
Data
Center
Order
Terminology
(2) Sharding Key
(3) Shard Map
Manager
(6) Sharded Table
Order Table
Customer Table
(8) Shardlet
Customers
Cust
ID
First
Name
Last
Name
Order
ID
Cust
ID
Date
1
Bob
Smith
1
1
Jan 1
1
2
Alice
Lamp
2
1
May 2
1
3
Joan
Lake
3
1
May 5
2
4
2
May 7
2
5
3
Jun 8
3
Data Center Table
DC
City
State
1
Seattle
WA
2
Duluth
MN
3
Ames
IA
Order
Customers
(4) Shard
Order
Customers
...
DC
Data
Center
Data
Center
(1) Database
(5) Shard Set
(8) Shardlet
Data
Center
Order
Data Dependent Routing (DDR)
Scenario: query a shard with a specific shardlet key
Shard Map
Manager
Client App
Application
Developer
DDR APIs ( )
Admin/
DevOps
SELECT *
FROM customers
WHERE customer ID = 104
DB1
DB2
DB3
DB4
DB5
DB5
[0-100)
[100-200)
[200-300)
[300-400)
[400-500)
[500-600)
...
DBn
(n - [n+100])
Multi-shard Query (MSQ)
Scenario: execute a query across a set of shards (returns a UNION ALL
result set)
Shard Map
Manager
Client App
Application
Developer
Admin/
DevOps
MSQ APIs ( )
SELECT count(*)
FROM customers
UNION ALL result set
DB1
DB2
DB3
DB4
DB5
DB5
[0-100)
[100-200)
[200-300)
[300-400)
[400-500)
[500-600)
...
DBn
[n-n+100)
Shard Elasticity (SE)
Scenario: automation to vertically scale a shard or
horizontally scale a shardset
• Vertical scale: increase/decrease the performance level of the shard
• Horizontal scale: add/remove a shard to the shard set
Azure
Automation (SE)
Application
Developer
DB1
DB2
DB3
[0-100)
[100-200)
[200-300)
Basic
Basic
Basic
DB4
DB5
Horizontal scaling
DB5
[500-600)
[400-500)
[300-400)
S2
...
P1
P3
time
DBn
[n-n+100)
Admin/
DevOps
Weiteres
• Columnstore Indexe in Azure
• Query Store – jetzt wirklich 
• XEvents in der Database
Source: http://blogs.technet.com/b/dataplatforminsider/archive/2014/11/05/microsoft-announces-major-update-to-azure-sql-database-adds-free-tier-to-azure-machine-learning.aspx

Download Report