31

It's quite a topic, blobs vs tables vs SQL, and despite all I read so far I still can't find some proper reasoning on what to use when.

We have a multi-tenant SaaS web-application which we are about to move to Azure. We use an SQL Server 2008 database. We store documents and log information that belongs to the documents. Kinda like dropbox does.

The forums state that you better use Azure Tables when you are considering "large" objects. We typically store hundreds of documents per user where the size of the documents vary from 5kb to 30mb where the vast majority will be around 1MB?

Are there some ground rules when to go for Blobs, Tables, Sql? I already learned that I shouldn't store my documents in SQL since it is too expensive. But when does it get "beneficial" to store the documents in Blobs and when would I be better of with tables? Is there some kind of formula like :

if (objects * MB/object * objectrequested > y) then blobs, else tables

p.campbell
  • 98,673
  • 67
  • 256
  • 322
bas
  • 13,550
  • 20
  • 69
  • 146
  • 1
    Check out my two answers to similar questions: http://stackoverflow.com/questions/4930368/when-should-i-use-sql-azure-and-when-should-i-use-table-storage/4930557#4930557 http://stackoverflow.com/questions/11540516/azure-mvc-web-app-architecture-how-to-split-data-between-sql-azure-and-azure/11541394#11541394 – Igorek Dec 24 '12 at 20:09
  • Heya, many thanks (I found 1 of them before i posted my question). It helps a lot, but still leaves me with the ultimate question: should I go for tables for documents that are retrieved often and have an average size of 1MB-5MB? The application "is all about the documents" so they will be retrieved frequently. But, I think your explanation of the transaction might apply. The meta data will be retrieved a lot more frequently. So, would you suggest putting the meta data in Sql Azure and store the documents in Tables? – bas Dec 24 '12 at 20:34
  • updated the question a bit with your input – bas Dec 24 '12 at 20:37
  • Similar question http://stackoverflow.com/questions/32563175/performance-azure-blob-vs-azure-table – Michael Freidgeim Jul 13 '16 at 12:56

2 Answers2

24

I think Igorek has addressed your SQL Azure concerns. You seem to still have questions about Tables vs Blobs, though.

In your case using Table storage would be annoying. Each property/column in ATS can be at most 64KB, so you would have to split the documents across multiple properties and then reassemble them. There is also a limit of 4MB per entity, which would be a problem. Blob storage has neither of these limitations.

I would tend to use Azure Table Storage when you have smallish entities with many properties that need to be stored and queried separately. So it works wells for stored objects, or small documents with lots of metadata.

Blob storage works better for things without a ton of metadata. It's good for things that might work well as files on a filesystem.

Brian Reischl
  • 7,216
  • 2
  • 35
  • 46
10

I would store documents themselves in the Azure Blob storage (not table storage). Outside of the fact that it is pretty expensive to store documents in a SQL Azure database that charges a penny per meg (or less depending on volume), SQL database is generally not a good place for documents. SQL is a relational database that provides benefits of ability to do queries, joins, etc. There is usually no benefit to storing large documents or images in a SQL database, especially when there is a highly scalable central storage system that is pretty cheap to store/access.

Now, if you need to search thru the documents themselves, I'd use something like Lucene.NET to provide a search capability for document-based repository.

HTH

Igorek
  • 15,716
  • 3
  • 54
  • 92
  • I would just add that for the meta data, if you need to run aggregate queries or queries against a wide variety of columns SQL is probably your best bet. In all other cases Azure Tables are probably best – knightpfhor Dec 25 '12 at 05:43
  • Can you define a set of ground rules when to go for blobs and when to go for tables? I got 1 step further now, I do not want to store documents in SQL because it's too expensive. Thx for that. Some ground rules for decision making blobs/tables would be highly appreciated. – bas Dec 25 '12 at 10:03
  • There are no ground rules. But I think of Blob storage as a scalable file system. Data that I usually store in a file system in a traditional on-premise app, I would use Blob storage in Azure. Choice between SQL Azure and Table storage is mostly based on needing the scale. Table Storage is hard to use. Much harder to use than traditional SQL. Thus, only use it (TableStorage) when SQL Azure can't handle your volume or your scale. – Igorek Dec 25 '12 at 19:36