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
   

   View Slide

2. Caitie McCa!rey
   S w r D v E r
   343 I r
   @CaitieM20
   CaitieM.com
   2
   Friday, April 26, 13
   

   View Slide

3. 3
   Friday, April 26, 13
   

   View Slide

4. Azure Storage
   Blob: Unstructured Binary
   Table: Structured Non Relational
   SQL Database: Relational Storage
   4
   Friday, April 26, 13
   

   View Slide

5. “W w Az r b r
   f r r r r ,
   non-relational .”
   5
   Friday, April 26, 13
   

   View Slide

6. 6
   R N R
   6
   Friday, April 26, 13
   

   View Slide

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
   

   View Slide

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
   
   Property
   
   Property
   
   Property
   
   Property
   
   8
   Friday, April 26, 13
   

   View Slide

9. • Only One
   Partition Key + Row Key = Unique Identifier
   • Azure Table supports
   • Key per Entity
   9
   Friday, April 26, 13
   

   View Slide

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
    

    View Slide

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
    

    View Slide

12. Table Partitions
    •Single Server
    • handles all requests for a
    •Partition
    12
    Friday, April 26, 13
    

    View Slide

13. “This Partition Size is
    Too Big. It Won’t
    Scale!”
    Goldilocks & Azure Storage
    13
    Friday, April 26, 13
    

    View Slide

14. “This Partition Size
    is Too Small. Not
    Enough Transaction
    Support!”
    Goldilocks & Azure Storage
    14
    Friday, April 26, 13
    

    View Slide

15. “This Partition Size
    is Just Right! Scale
    & Just Enough
    Transaction
    Support”
    Goldilocks & Azure Storage
    15
    Friday, April 26, 13
    

    View Slide

16. Moral of the Story:
    SCALE &
    Structure Data for
    CONSISTENCY
    16
    Friday, April 26, 13
    

    View Slide

17. Partition Key Space
    • Inserts into Partition Key
    • Space Should be “Random”
    17
    17
    Friday, April 26, 13
    

    View Slide

18. Partition Key Space
    • Inserts into Partition Key
    • Space Should be “Random”
    18
    Partition Key Space
    1
    18
    Friday, April 26, 13
    

    View Slide

19. Partition Key Space
    • Inserts into Partition Key
    • Space Should be “Random”
    19
    Partition Key Space
    1 2
    19
    Friday, April 26, 13
    

    View Slide

20. Partition Key Space
    • Inserts into Partition Key
    • Space Should be “Random”
    20
    Partition Key Space
    1 2 3
    20
    Friday, April 26, 13
    

    View Slide

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
    

    View Slide

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
    

    View Slide

23. Partition Key Space
    • Inserts into Partition Key
    • Space Should be “Random”
    23
    Partition Key Space
    23
    Friday, April 26, 13
    

    View Slide

24. Partition Key Space
    • Inserts into Partition Key
    • Space Should be “Random”
    24
    Partition Key Space
    12
    24
    Friday, April 26, 13
    

    View Slide

25. Partition Key Space
    • Inserts into Partition Key
    • Space Should be “Random”
    25
    Partition Key Space
    12 28
    25
    Friday, April 26, 13
    

    View Slide

26. Partition Key Space
    • Inserts into Partition Key
    • Space Should be “Random”
    26
    Partition Key Space
    12 28
    1
    26
    Friday, April 26, 13
    

    View Slide

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
    

    View Slide

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
    

    View Slide

29. Simple Stats Example
    29
    Friday, April 26, 13
    

    View Slide

30. Simple
    Stats:
    Stats
    Data
    30
    Friday, April 26, 13
    

    View Slide

31. Simple
    Stats:
    SQL
    31
    Friday, April 26, 13
    

    View Slide

32. Simple Stats: SQL
    32
    Friday, April 26, 13
    

    View Slide

33. Simple Stats: SQL
    33
    Friday, April 26, 13
    

    View Slide

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
    

    View Slide

35. Simple Stats: Azure Storage
    What Transactions do I want?
     Player Data updates
     Write Game Stats
    35
    Friday, April 26, 13
    

    View Slide

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
    

    View Slide

37. Simple Stats: Creating a Table
    37
    Friday, April 26, 13
    

    View Slide

38. Simple Stats: PlayerGameEntity
    38
    Friday, April 26, 13
    

    View Slide

39. Simple Stats: Player Entity
    39
    Friday, April 26, 13
    

    View Slide

40. 40
    Simple Stats: Game Data
    40
    Friday, April 26, 13
    

    View Slide

41. Code Break
    “We Don’t Know
    How To Make
    Perfect Software”
    ~N M
    41
    Friday, April 26, 13
    

    View Slide

42. Code Break
    The Worst Error Is
    Data Loss or
    Data Corruption
    42
    Friday, April 26, 13
    

    View Slide

43. Data Immutability
    Cr
    R
    U
    D
    43
    Friday, April 26, 13
    

    View Slide

44. Data Immutability
    Cr
    R
    U
    D
    44
    Friday, April 26, 13
    

    View Slide

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
    

    View Slide

46. More Immutability Benefits
    •Flexibility to add new views later
    •Data Audit Log
    •Idempotent Writes to a Partition
    46
    Friday, April 26, 13
    

    View Slide

47. Simple
    Stats:
    Process
    Stats
    47
    Friday, April 26, 13
    

    View Slide

48. 48
    Simple Stats: Process Stats
    Idem otent Writes
    48
    Friday, April 26, 13
    

    View Slide

49. 49
    Simple Stats: Data
    49
    Friday, April 26, 13
    

    View Slide

50. Distributed Transactions
    50
    Friday, April 26, 13
    

    View Slide

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
    

    View Slide

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
    

    View Slide

53. Distributed
    Transactions
    Are Hard
    53
    Friday, April 26, 13
    

    View Slide

54. Saga Pattern
    Transaction Model for
    Distributed Transactions
    or Long Lived Activities
    54
    Friday, April 26, 13
    

    View Slide

55. “Saga is a Failure
    Management
    Pattern”
    ~Clemens Vasters
    55
    Friday, April 26, 13
    

    View Slide

56. Store Stats
    Player One
    Store Stats
    Player Two
    Store Stats
    Player Three
    Store Stats
    Player Four
    Game Stats
    56
    Friday, April 26, 13
    

    View Slide

57. Store Stats
    Player One
    Store Stats
    Player Two
    Store Stats
    Player Three
    Store Stats
    Player Four
    Game Stats
    57
    Friday, April 26, 13
    

    View Slide

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
    

    View Slide

59. Store Stats
    Player One
    Store Stats
    Player Two
    Store Stats
    Player Three
    Store Stats
    Player Four
    Game Stats
    59
    Friday, April 26, 13
    

    View Slide

60. Summary
    Tradeoff:
    Consistency
    vs Scale
    60
    Friday, April 26, 13
    

    View Slide

61. Summary
    Make Data
    Immutable
    61
    Friday, April 26, 13
    

    View Slide

62. Summary
    Saga Pattern
    for Distributed Transactions
    62
    Friday, April 26, 13
    

    View Slide

63. 63
    Y H S rv T
    63
    Friday, April 26, 13
    

    View Slide

64. Summary
    Make Data
    Immutable
    Saga Pattern
    for Distributed Transactions
    Tradeoff:
    Consistency
    vs Scale
    64
    Friday, April 26, 13
    

    View Slide