locked
Blob Storage or SQL Azure : better option? RRS feed

  • Question

  • I have simple questions.

    I want to know more on blob storage.

    In my application, there are users who message each other. I intent to have much more data for message. I was thinking on options where the messaging related data should be stored.  I want to compare if i store the whole message body in SQL azure and if i store in blob storage, which is better option.

    Question: 1. In blob storage, the item -i.e. content, i can keep it for infinite time. i mean i dont want the message to be expired.

    Question: 2. How easy to fetch multiple message items based on criteria from blob storage. i.e. if i want to list all the last 15 days messages for an user, would it be difficult?

    Basically i want to know what are the pros and cons of storing the content in to blob storage.

    the basic idea of storing the messages into blob is i was thinking if i can avoid my database  growing fast just because of messaging data.

    Let me know which is better option and answers to my both the questions.

    Thanks

    Meenxip


    • Edited by meenxip Thursday, August 25, 2011 7:37 AM
    Wednesday, August 24, 2011 11:12 AM

Answers

  • Hi Meenxip,

    You may consider using Table Stroage instead of SQL Azure as Gaurav and Jai suggested. However there is no big problem if you prefer using SQL Azure.

    > 1. In blob storage, the item -i.e. content, i can keep it for infinite time. i mean i dont want the message to be expired.

    Yes, you can keep your content in blob storage for infinite time. Azure Storage won't actively delete your content unless you explictly perform the delete action or upload new content to replace the old one.

    > 2. How easy to fetch multiple message items based on criteria from blob storage. i.e. if i want to list all the last 15 days messages for an user, would it be difficult?

    Yes, it would be a bit difficult or not efficient if you query directly (without involving SQL Azure or Table Storage) from Blob Storage for the last 15 days messages. But if each blob item is referred by the item in SQL Azure, then it is easy to query latest messages from SQL Azure.

    > I want to know what are the pros and cons of storing the content in to blob storage.

    I can think of following pros and cons.

    Disadvantage:

        1. A bit lower performance as you will firstly query messages from SQL Azure and then send multiple subsequent requests to get the blob content.

        2. Not convenient for backup/restore data as you should backup/restore both Blob Storage and SQL Azure.

    Advantage:

        1. Azure Storage (Blog, Table, Queue) is much cheaper than SQL Azure. This is one of the reasons why we recommend using Table Storage.

    Thanks.


    Wenchao Zeng
    Please mark the replies as answers if they help or unmark if not.
    If you have any feedback about my replies, please contact msdnmg@microsoft.com.
    Microsoft One Code Framework
    • Marked as answer by meenxip Wednesday, August 31, 2011 7:03 AM
    Friday, August 26, 2011 9:05 AM

All replies

  • I think you may want to look at table storage as well as blob storage does not have querying capabilities. What you could possibly do is store the actual message content as a blob and put the blob URL in table storage. Since you want to query on date/time basis, you may want to pick PartitionKey value which represents a date/time value.

    Hope this helps.

    Thanks

    Gaurav Mantri

    Cerebrata Software

    http://www.cerebrata.com

     

    • Proposed as answer by Seetha_ Sunday, August 28, 2011 6:05 AM
    Wednesday, August 24, 2011 1:38 PM
  • Blobs and tables allow you to filter it.

    For blob service - the container name can be date and you can have blob names represent the time in which you keep appending to these blobs. The limit on block blob is 200GB in size. You can list the blobs using a prefix and get the list of blobs and download them one by one. As a client side optimization, you can also collate messages and append them to the blob in a single write to reduce round trips.

    For table service, as Gaurav suggested, you can you PK and RK to store the time and message size is limited to 64KB (unless you store it across multiple properties but row size is limited to 1MB).

    However, in both cases, one thing to understand is that you are generating requests in an "append only" pattern on your objects (see this blog for details on this pattern). Depending on the scale requirements (how many messages per second etc.), this is a reasonable solution. How many messages per second do you expect in your scenario? There are ways to avoid this append only pattern but retrieving all messages in a certain time range now involves multiple queries (the same blog covers that).

    Thanks,

    Jai

    Wednesday, August 24, 2011 3:30 PM
  • Thanks Gaurav and Jai,

    Let me know more. I want to compare the storing the whole messages (with all other attributes like sentdate, to, from, subject) a. in SQL Azure b. Azure Storage i.e. blob or table.

    If i compare in terms of cost, which would be better option. I was just thinking on Azure storage part, in case that option is better in terms of cost and performance.

    also question 1 is which is better option A. in SQL Azure or B. Azure Storage i.e. blob or table.

    Quetion 2 is, if i put my messages in blob or storage table, can i keep it for infinite time, i.e i dont want them to expire anytime.

    thanks

    Meenxip

    Thursday, August 25, 2011 7:37 AM
  • I think storing them in Windows Azure storage (Tables and Blobs) would definitely be cheaper than storing them in SQL Azure as with tables and blobs not only the storage is cheaper but also you will pay only for the space used where as with SQL Azure at the very minimum you would pay for 1GB of database. But again it depends on what you want to do with this data. Remember that Azure Table Storage is not a relational data store hence you will have to forego the benefits of a relational database (like joins and stuff).

    As far as data retention is concerned, I don't think Windows Azure has any data expiration policy so your data will stay as long as you want it to be.

    Hope this helps.

    Thanks

    Gaurav

     

    • Proposed as answer by Seetha_ Sunday, August 28, 2011 6:05 AM
    Thursday, August 25, 2011 7:43 AM
  • Thank you Gaurave for fast response.

    Also note, that i am already going to have SQL Azure database. and also note, the messages are also attached in to other data which is stored in SQL azure. that means i need to store the data like the userid and date and all to database, along with reference id of Blob item.

    now tell me when i am already having SQL Azure database then which would be better option.

    thanks

    Meenxip

     

    Thursday, August 25, 2011 10:24 AM
  • Hi Meenxip,

    You may consider using Table Stroage instead of SQL Azure as Gaurav and Jai suggested. However there is no big problem if you prefer using SQL Azure.

    > 1. In blob storage, the item -i.e. content, i can keep it for infinite time. i mean i dont want the message to be expired.

    Yes, you can keep your content in blob storage for infinite time. Azure Storage won't actively delete your content unless you explictly perform the delete action or upload new content to replace the old one.

    > 2. How easy to fetch multiple message items based on criteria from blob storage. i.e. if i want to list all the last 15 days messages for an user, would it be difficult?

    Yes, it would be a bit difficult or not efficient if you query directly (without involving SQL Azure or Table Storage) from Blob Storage for the last 15 days messages. But if each blob item is referred by the item in SQL Azure, then it is easy to query latest messages from SQL Azure.

    > I want to know what are the pros and cons of storing the content in to blob storage.

    I can think of following pros and cons.

    Disadvantage:

        1. A bit lower performance as you will firstly query messages from SQL Azure and then send multiple subsequent requests to get the blob content.

        2. Not convenient for backup/restore data as you should backup/restore both Blob Storage and SQL Azure.

    Advantage:

        1. Azure Storage (Blog, Table, Queue) is much cheaper than SQL Azure. This is one of the reasons why we recommend using Table Storage.

    Thanks.


    Wenchao Zeng
    Please mark the replies as answers if they help or unmark if not.
    If you have any feedback about my replies, please contact msdnmg@microsoft.com.
    Microsoft One Code Framework
    • Marked as answer by meenxip Wednesday, August 31, 2011 7:03 AM
    Friday, August 26, 2011 9:05 AM