Patterns & Best
Practices For Moving
From RDBMS to Azure
Storage
DotNetConf 2013
1
Friday, April 26, 13
Slide 2
Slide 2 text
Caitie McCa!rey
S w r D v E r
343 I r
@CaitieM20
CaitieM.com
2
Friday, April 26, 13
Slide 3
Slide 3 text
3
Friday, April 26, 13
Slide 4
Slide 4 text
Azure Storage
Blob: Unstructured Binary
Table: Structured Non Relational
SQL Database: Relational Storage
4
Friday, April 26, 13
Slide 5
Slide 5 text
“W w Az r b r
f r r r r ,
non-relational .”
5
Friday, April 26, 13
Slide 6
Slide 6 text
6
R N R
6
Friday, April 26, 13
Slide 7
Slide 7 text
Azure Table Structure
Tables: Collection of Entities
Entities: Set of Pro erties
Properties: Name Value Pair
Table
Entity
Entity
Entity
Property Property Property
Property
Property Property
Property
7
Friday, April 26, 13
Slide 8
Slide 8 text
Entities
Partition Key: identifies which artition an entity belongs to
Row Key: uniquely identifies an entity in a artition
Timestamp: last time entity was modified
Entity
PartitionKey
RowKey
TimeStamp
Property
Property
Property
Property
Property
8
Friday, April 26, 13
Slide 9
Slide 9 text
• Only One
Partition Key + Row Key = Unique Identifier
• Azure Table supports
• Key per Entity
9
Friday, April 26, 13
Slide 10
Slide 10 text
Type Numerics Date & Time Binary Character Strings Other
Azure
Table
Int32
DateTime byte[] Strings (UTF 16
encoded Value)
Guid
Azure
Table Int64 DateTime byte[] Strings (UTF 16
encoded Value) Bit
Azure
Table
Double
DateTime byte[] Strings (UTF 16
encoded Value)
SQL
BigInt
Bit
SQL
Int
Bit
SQL
SmallInt Date Char
Bit
SQL
TinyInt DateTime Text
Bit
SQL Money DateTime2 Binary Varchar
Bit
SQL
SmallMoney DateTimeOffset Image Nchar
Bit
SQL
Numeric SmallDateTime VarBinary Ntext
Bit
SQL
Float Time NVarChar
Bit
SQL
Decimal
Bit
SQL
Real
Bit
Properties are Name Value Pairs
10
Friday, April 26, 13
Slide 11
Slide 11 text
Table Partitions
•Azure Table supports
Batch Transactions on
entities that are in the
same Table & Partition
E w S P r K
11
Friday, April 26, 13
Slide 12
Slide 12 text
Table Partitions
•Single Server
• handles all requests for a
•Partition
12
Friday, April 26, 13
Slide 13
Slide 13 text
“This Partition Size is
Too Big. It Won’t
Scale!”
Goldilocks & Azure Storage
13
Friday, April 26, 13
Slide 14
Slide 14 text
“This Partition Size
is Too Small. Not
Enough Transaction
Support!”
Goldilocks & Azure Storage
14
Friday, April 26, 13
Slide 15
Slide 15 text
“This Partition Size
is Just Right! Scale
& Just Enough
Transaction
Support”
Goldilocks & Azure Storage
15
Friday, April 26, 13
Slide 16
Slide 16 text
Moral of the Story:
SCALE &
Structure Data for
CONSISTENCY
16
Friday, April 26, 13
Slide 17
Slide 17 text
Partition Key Space
• Inserts into Partition Key
• Space Should be “Random”
17
17
Friday, April 26, 13
Slide 18
Slide 18 text
Partition Key Space
• Inserts into Partition Key
• Space Should be “Random”
18
Partition Key Space
1
18
Friday, April 26, 13
Slide 19
Slide 19 text
Partition Key Space
• Inserts into Partition Key
• Space Should be “Random”
19
Partition Key Space
1 2
19
Friday, April 26, 13
Slide 20
Slide 20 text
Partition Key Space
• Inserts into Partition Key
• Space Should be “Random”
20
Partition Key Space
1 2 3
20
Friday, April 26, 13
Slide 21
Slide 21 text
Partition Key Space
• Inserts into Partition Key
• Space Should be “Random”
21
Partition Key Space
1 2 3 4
21
Friday, April 26, 13
Slide 22
Slide 22 text
Partition Key Space
• Inserts into Partition Key
• Space Should be “Random”
22
Partition Key Space
1 2 3 4
22
Friday, April 26, 13
Slide 23
Slide 23 text
Partition Key Space
• Inserts into Partition Key
• Space Should be “Random”
23
Partition Key Space
23
Friday, April 26, 13
Slide 24
Slide 24 text
Partition Key Space
• Inserts into Partition Key
• Space Should be “Random”
24
Partition Key Space
12
24
Friday, April 26, 13
Slide 25
Slide 25 text
Partition Key Space
• Inserts into Partition Key
• Space Should be “Random”
25
Partition Key Space
12 28
25
Friday, April 26, 13
Slide 26
Slide 26 text
Partition Key Space
• Inserts into Partition Key
• Space Should be “Random”
26
Partition Key Space
12 28
1
26
Friday, April 26, 13
Slide 27
Slide 27 text
Partition Key Space
• Inserts into Partition Key
• Space Should be “Random”
27
Partition Key Space
12 28
1 6
27
Friday, April 26, 13
Slide 28
Slide 28 text
Partition Key Space
• Inserts into Partition Key
• Space Should be “Random”
28
Partition Key Space
12 28
1 6
28
Friday, April 26, 13
Slide 29
Slide 29 text
Simple Stats Example
29
Friday, April 26, 13
Slide 30
Slide 30 text
Simple
Stats:
Stats
Data
30
Friday, April 26, 13
Slide 31
Slide 31 text
Simple
Stats:
SQL
31
Friday, April 26, 13
Slide 32
Slide 32 text
Simple Stats: SQL
32
Friday, April 26, 13
Slide 33
Slide 33 text
Simple Stats: SQL
33
Friday, April 26, 13
Slide 34
Slide 34 text
34
Simple Stats: Azure Storage
What Queries do I need?
Total player statistics
All games a player has played
Show stats for single game
34
Friday, April 26, 13
Slide 35
Slide 35 text
Simple Stats: Azure Storage
What Transactions do I want?
Player Data updates
Write Game Stats
35
Friday, April 26, 13
Slide 36
Slide 36 text
36
How Do I Partition The Data
Game Id or Player Id?
Simple Stats: Azure Storage
Table = Games
PK=GameId(1) RK=PlayerId(2)
PK=GameId(1) RK=PlayerId(1)
PK=GameId(2) RK=PlayerId(3)
PK=GameId(2) RK=PlayerId(2)
PK=GameId(3) RK=PlayerId(1)
PK=GameId(3) RK=PlayerId(3)
Table = Players
PK=PlayerId(1) RK=GameId(3)
PK=PlayerId(1) RK=GameId(1)
PK=PlayerId(2) RK=GameId(1)
PK=PlayerId(2) RK=GameId(2)
PK=PlayerId(3) RK=GameId(2)
PK=PlayerId(3) RK=GameId(3)
OR
36
Friday, April 26, 13
Slide 37
Slide 37 text
Simple Stats: Creating a Table
37
Friday, April 26, 13
Slide 38
Slide 38 text
Simple Stats: PlayerGameEntity
38
Friday, April 26, 13
Slide 39
Slide 39 text
Simple Stats: Player Entity
39
Friday, April 26, 13
Slide 40
Slide 40 text
40
Simple Stats: Game Data
40
Friday, April 26, 13
Slide 41
Slide 41 text
Code Break
“We Don’t Know
How To Make
Perfect Software”
~N M
41
Friday, April 26, 13
Slide 42
Slide 42 text
Code Break
The Worst Error Is
Data Loss or
Data Corruption
42
Friday, April 26, 13
Slide 43
Slide 43 text
Data Immutability
Cr
R
U
D
43
Friday, April 26, 13
Slide 44
Slide 44 text
Data Immutability
Cr
R
U
D
44
Friday, April 26, 13
Slide 45
Slide 45 text
Immutability in the Real World
“Event” Entities: Source of Truth is Immutable
Aggregate “View” Entities: use all of CRUD.
Player
Game
Player
Game
Player
F( Player
Game
Player
Game
Player
Game
)
, , , ,
45
Friday, April 26, 13
Slide 46
Slide 46 text
More Immutability Benefits
•Flexibility to add new views later
•Data Audit Log
•Idempotent Writes to a Partition
46
Friday, April 26, 13
Slide 47
Slide 47 text
Simple
Stats:
Process
Stats
47
Friday, April 26, 13
Slide 48
Slide 48 text
48
Simple Stats: Process Stats
Idem otent Writes
48
Friday, April 26, 13
Slide 49
Slide 49 text
49
Simple Stats: Data
49
Friday, April 26, 13
Slide 50
Slide 50 text
Distributed Transactions
50
Friday, April 26, 13
Slide 51
Slide 51 text
Google Spanner
“Spanner is Google’s scalable, multi-version,
globally distributed, and synchronously-
replicated database. It is the first system to
distribute data at global scale and support
externally-consistent distributed
transactions.”
~James C. Corbett et al
51
Friday, April 26, 13
Slide 52
Slide 52 text
Google Spanner
“The key enabler of these properties is a new
TrueTime API and its implementation…using
multiple modern clock references (GPS and
atomic clocks).”
~James C. Corbett et al
52
Friday, April 26, 13
Slide 53
Slide 53 text
Distributed
Transactions
Are Hard
53
Friday, April 26, 13
Slide 54
Slide 54 text
Saga Pattern
Transaction Model for
Distributed Transactions
or Long Lived Activities
54
Friday, April 26, 13
Slide 55
Slide 55 text
“Saga is a Failure
Management
Pattern”
~Clemens Vasters
55
Friday, April 26, 13
Slide 56
Slide 56 text
Store Stats
Player One
Store Stats
Player Two
Store Stats
Player Three
Store Stats
Player Four
Game Stats
56
Friday, April 26, 13
Slide 57
Slide 57 text
Store Stats
Player One
Store Stats
Player Two
Store Stats
Player Three
Store Stats
Player Four
Game Stats
57
Friday, April 26, 13
Slide 58
Slide 58 text
Store Stats
Player One
Store Stats
Player Two
Store Stats
Player Three
Store Stats
Player Four
Compensate
Player One
Compensate
Player Two
Compensate
Player Three
Compensate
Player Four
58
Friday, April 26, 13
Slide 59
Slide 59 text
Store Stats
Player One
Store Stats
Player Two
Store Stats
Player Three
Store Stats
Player Four
Game Stats
59
Friday, April 26, 13
Slide 60
Slide 60 text
Summary
Tradeoff:
Consistency
vs Scale
60
Friday, April 26, 13
Slide 61
Slide 61 text
Summary
Make Data
Immutable
61
Friday, April 26, 13
Slide 62
Slide 62 text
Summary
Saga Pattern
for Distributed Transactions
62
Friday, April 26, 13
Slide 63
Slide 63 text
63
Y H S rv T
63
Friday, April 26, 13
Slide 64
Slide 64 text
Summary
Make Data
Immutable
Saga Pattern
for Distributed Transactions
Tradeoff:
Consistency
vs Scale
64
Friday, April 26, 13