Patterns & Best Practices For Moving From RDBMS to Azure Storage

Moving from storing your data in an RDBMS like SQL Server to a NoSQL data store like Azure Table requires a programming paradigm shift. This talk will share the knowledge the Halo Services Team gained while making the jump from SQL to Azure Storage while working on Halo 4. I’ll start with an overview of the core differences between the two data stores. In addition I’ll discuss the importance of partitions in Azure Table, and how to perform transactions. Finally I’ll cover how to incorporate other data best practices, and explore how to achieve transaction like behavior across partitions using the Saga Pattern.

Transcript

1. Patterns & Best Practices For Moving From RDBMS to Azure Storage DotNetConf 2013 1 Friday, April 26, 13
2. Caitie McCa!rey S w r D v E r 343 I r @CaitieM20 CaitieM.com 2 Friday, April 26, 13
3. 3 Friday, April 26, 13
4. Azure Storage Blob: Unstructured Binary Table: Structured Non Relational SQL Database: Relational Storage 4 Friday, April 26, 13
5. “W w Az r b r f r r r r , non-relational .” 5 Friday, April 26, 13
6. 6 R N R 6 Friday, April 26, 13
7. 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
8. 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 <Name, Value> Property <Name, Value> Property <Name, Value> Property <Name, Value> Property <Name, Value> 8 Friday, April 26, 13
9. • Only One Partition Key + Row Key = Unique Identifier • Azure Table supports • Key per Entity 9 Friday, April 26, 13
10. 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
11. 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
12. Table Partitions •Single Server • handles all requests for a •Partition 12 Friday, April 26, 13
13. “This Partition Size is Too Big. It Won’t Scale!” Goldilocks & Azure Storage 13 Friday, April 26, 13
14. “This Partition Size is Too Small. Not Enough Transaction Support!” Goldilocks & Azure Storage 14 Friday, April 26, 13
15. “This Partition Size is Just Right! Scale & Just Enough Transaction Support” Goldilocks & Azure Storage 15 Friday, April 26, 13
16. Moral of the Story: SCALE & Structure Data for CONSISTENCY 16 Friday, April 26, 13
17. Partition Key Space • Inserts into Partition Key • Space Should be “Random” 17 17 Friday, April 26, 13
18. Partition Key Space • Inserts into Partition Key • Space Should be “Random” 18 Partition Key Space 1 18 Friday, April 26, 13
19. Partition Key Space • Inserts into Partition Key • Space Should be “Random” 19 Partition Key Space 1 2 19 Friday, April 26, 13
20. Partition Key Space • Inserts into Partition Key • Space Should be “Random” 20 Partition Key Space 1 2 3 20 Friday, April 26, 13
21. Partition Key Space • Inserts into Partition Key • Space Should be “Random” 21 Partition Key Space 1 2 3 4 21 Friday, April 26, 13
22. Partition Key Space • Inserts into Partition Key • Space Should be “Random” 22 Partition Key Space 1 2 3 4 22 Friday, April 26, 13
23. Partition Key Space • Inserts into Partition Key • Space Should be “Random” 23 Partition Key Space 23 Friday, April 26, 13
24. Partition Key Space • Inserts into Partition Key • Space Should be “Random” 24 Partition Key Space 12 24 Friday, April 26, 13
25. Partition Key Space • Inserts into Partition Key • Space Should be “Random” 25 Partition Key Space 12 28 25 Friday, April 26, 13
26. Partition Key Space • Inserts into Partition Key • Space Should be “Random” 26 Partition Key Space 12 28 1 26 Friday, April 26, 13
27. Partition Key Space • Inserts into Partition Key • Space Should be “Random” 27 Partition Key Space 12 28 1 6 27 Friday, April 26, 13
28. Partition Key Space • Inserts into Partition Key • Space Should be “Random” 28 Partition Key Space 12 28 1 6 28 Friday, April 26, 13
29. Simple Stats Example 29 Friday, April 26, 13
30. Simple Stats: Stats Data 30 Friday, April 26, 13
31. Simple Stats: SQL 31 Friday, April 26, 13
32. Simple Stats: SQL 32 Friday, April 26, 13
33. Simple Stats: SQL 33 Friday, April 26, 13
34. 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
35. Simple Stats: Azure Storage What Transactions do I want?  Player Data updates  Write Game Stats 35 Friday, April 26, 13
36. 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
37. Simple Stats: Creating a Table 37 Friday, April 26, 13
38. Simple Stats: PlayerGameEntity 38 Friday, April 26, 13
39. Simple Stats: Player Entity 39 Friday, April 26, 13
40. 40 Simple Stats: Game Data 40 Friday, April 26, 13
41. Code Break “We Don’t Know How To Make Perfect Software” ~N M 41 Friday, April 26, 13
42. Code Break The Worst Error Is Data Loss or Data Corruption 42 Friday, April 26, 13
43. Data Immutability Cr R U D 43 Friday, April 26, 13
44. Data Immutability Cr R U D 44 Friday, April 26, 13
45. 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
46. More Immutability Benefits •Flexibility to add new views later •Data Audit Log •Idempotent Writes to a Partition 46 Friday, April 26, 13
47. Simple Stats: Process Stats 47 Friday, April 26, 13
48. 48 Simple Stats: Process Stats Idem otent Writes 48 Friday, April 26, 13
49. 49 Simple Stats: Data 49 Friday, April 26, 13
50. Distributed Transactions 50 Friday, April 26, 13
51. 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
52. 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
53. Distributed Transactions Are Hard 53 Friday, April 26, 13
54. Saga Pattern Transaction Model for Distributed Transactions or Long Lived Activities 54 Friday, April 26, 13
55. “Saga is a Failure Management Pattern” ~Clemens Vasters 55 Friday, April 26, 13
56. Store Stats Player One Store Stats Player Two Store Stats Player Three Store Stats Player Four Game Stats 56 Friday, April 26, 13
57. Store Stats Player One Store Stats Player Two Store Stats Player Three Store Stats Player Four Game Stats 57 Friday, April 26, 13
58. 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
59. Store Stats Player One Store Stats Player Two Store Stats Player Three Store Stats Player Four Game Stats 59 Friday, April 26, 13
60. Summary Tradeoff: Consistency vs Scale 60 Friday, April 26, 13
61. Summary Make Data Immutable 61 Friday, April 26, 13
62. Summary Saga Pattern for Distributed Transactions 62 Friday, April 26, 13
63. 63 Y H S rv T 63 Friday, April 26, 13
64. Summary Make Data Immutable Saga Pattern for Distributed Transactions Tradeoff: Consistency vs Scale 64 Friday, April 26, 13