locked
Users table - use SQL Azure or Table Storage? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hey,

    I come from a SQL RDBMS background and am trying to learn as much as I can about when to use Table Storage.  For my site, I've started by creating my entire database using the Entity Framework 4.1, and plan to put this all in SQL Azure.  However, I'm now looking at the various tables and trying to decide which, if any, of this data can be moved to Table Storage. 

    For example, my users table has First/Last/Email/Password/Phone.  Pretty simple.  I expect a maximum of 10 million users.  The "UserID" identity key is used in many other tables in my database.  It seems easiest for me to just leave this in SQL Azure, so I can contintue to use the entity framework, and so that lookups are easy.  Can someone help explain why I should consider table storage instead, what I may lose or gain in functionality, etc?

    Thanks in advance.

    Matt

    Monday, August 22, 2011 5:37 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    Matt,

    Windows Azure Table Storage is ideal for working with non-relational data. To break that down even further, here are some characteristics of the candidates that make sense for using non-relational data stores like Windows Azure Table Storage.

    • Data that isn't referenced by other data in a foreign key relationships
    • Data that doesn't enfore referential integrity from the data storage layer
    • Not included in distributed transactions
    • Primarily indexed and searched by identity/primary key and not by other fields
    • Need to scale because of volume and/or performance/load needs

    Some of the benefits of using non-relational data like Windows Azure Table Storage over relational data like SQL Azure:

    • Significantly less expensive
    • Scales easily and transparently for performance
    • Scales easily and transparently for volume of data

    User and Profile data is usually a great candidate for Windows Azure Table Storage because it's typically non-relational. The relationships that do exist are commonly managed by your application and not the data store, especially if you are using the ASP.NET Membership Provider. It is also typical that you query the User and Profile data by the identity fields. By using Table Storage, you also get the added benefit of transparent scale. However, if only your user data is designed to scale, it probably isn't all that helpful in the grand scheme of things as it relates to your application.

    In summary, User and Profile data are typically great candidates for storing in Windows Azure Table Storage because of economics and scale.

    Eric

    Eric D. Boyd - Director, Chicago + Cloud at Centare 
    Blog | twitter
    • Proposed as answer by Eric D. Boyd Wednesday, August 24, 2011 3:39 PM
    • Marked as answer by Wenchao Zeng Monday, August 29, 2011 2:54 AM
    Monday, August 22, 2011 7:54 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Although Azure Table storage is cost-effective when compared with SQL Azure, SQL Azure seems be appropriate choice in your case as you seem to be enforcing referential constraints and lot of lookups using UserId.

    HTH.

    Please mark it as answer by clicking on "Propose As Answer", if it helps. My Blog : http://dotnetizen.blogspot.com
    Monday, August 22, 2011 6:37 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Matt,

    Windows Azure Table Storage is ideal for working with non-relational data. To break that down even further, here are some characteristics of the candidates that make sense for using non-relational data stores like Windows Azure Table Storage.

    • Data that isn't referenced by other data in a foreign key relationships
    • Data that doesn't enfore referential integrity from the data storage layer
    • Not included in distributed transactions
    • Primarily indexed and searched by identity/primary key and not by other fields
    • Need to scale because of volume and/or performance/load needs

    Some of the benefits of using non-relational data like Windows Azure Table Storage over relational data like SQL Azure:

    • Significantly less expensive
    • Scales easily and transparently for performance
    • Scales easily and transparently for volume of data

    User and Profile data is usually a great candidate for Windows Azure Table Storage because it's typically non-relational. The relationships that do exist are commonly managed by your application and not the data store, especially if you are using the ASP.NET Membership Provider. It is also typical that you query the User and Profile data by the identity fields. By using Table Storage, you also get the added benefit of transparent scale. However, if only your user data is designed to scale, it probably isn't all that helpful in the grand scheme of things as it relates to your application.

    In summary, User and Profile data are typically great candidates for storing in Windows Azure Table Storage because of economics and scale.

    Eric

    Eric D. Boyd - Director, Chicago + Cloud at Centare 
    Blog | twitter
    • Proposed as answer by Eric D. Boyd Wednesday, August 24, 2011 3:39 PM
    • Marked as answer by Wenchao Zeng Monday, August 29, 2011 2:54 AM
    Monday, August 22, 2011 7:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I agree with Eric. You are not performing any complex queries on user and profile data i guess and you probably don't need a query engine. Saves you a lot money this way!

    regards

    Thursday, August 25, 2011 3:05 PM