Proper use of the azure vault. (When to use SQL, Tables and Blobs)

Question

I am relatively new to Azure storage and have been implementing the solution for some time. And I continue to run into obstacles, making me feel like I'm not applying the right type of storage for the data that I store.

So this is a more general question:

• When should you use Azure SQL?
• When should I use Azure table storage?
• When should you use Azure Blobs?

So far I have used a lot of table storage, and now I am paying for it. As the requirements for the solution grow, I cannot access the data as needed.

For example, I need to get the last 50 records in the table, but I can’t fulfill the OrderBy order in the query. I need to get the total number of records, but not use Count.

I get the impression that any data that I plan to receive regularly, not knowing exactly RowKey and PartitionKey, should be indexed in Azure SQL, and also stored in a table. Is it correct?

I also believe that recreating objects as Entity objects, but with very severe restrictions on data types, I often end up just serializing the object into an array of bytes. And although the table row can contain up to 1 MB, the byte array in this row can only contain 64 KB, after which I end up using Blob storage.

So, in the end, I feel that I would be better off just putting all my data in Azure SQL and indexing the big data, but saving them as drops. Of course, this is not entirely correct, since this would leave the table storage without a real purpose.

So, I am wondering if there are any recommendations for using that type of storage.

In my case, I have a very large amount of data in some areas, some of them consume enough space (often above 64 KB), but I also need to access the data very often and they will need to filter and sort it by certain values.

• Do I need to index all the data that I plan to get in SQL?
• And am I better off avoiding spreadsheets for any data that may exceed 64 KB?

I feel that something is wrong there. I do not understand. What am I missing here?

Answer 1

I get the impression that any data that I plan to receive regularly, not knowing exactly RowKey and PartitionKey, should be indexed in Azure SQL, and also stored in a table. Is it correct?

Table storage does not support secondary indexes, so any efficient queries must contain RowKey and PartitionKey. There may be workarounds, such as storing the same data twice in the same table with different RowKeys. However, this can quickly become a pain. If possible consistency is in order, you can do it. You need to take care of transactions and rollbacks.

In my case, I have a very large amount of data in some areas, some of them consume enough space (often above 64 KB), but I also need to access the data very often, and they will need to be able to filter and sort it by certain values.

Use table storage for basic NoSQL functions and the ability to scale quickly. However, if you need secondary indexes and other similar functions, you might have to take a look at something like DynamoDB on AWS, which afaik seems to better support secondary indexes, etc. If you have data that has a complex relationship, then other data that requires an RDBMS works with SQL Azure.

Now, as for your options on Azure, I would think that you would need to store everything on SQL Azure and large objects in the form of blocks or on table storage.

Do I need to index all the data that I plan to get in SQL?

Hard to say. If each section will contain only 100 rows, you can request a section key and any of the columns. Partition scanning will be pretty fast at this point. However, if you have a million lines, this can be a problem.

I feel that there is something that I am not doing right. I do not understand. What am I missing here?

A bunch of early Azure users started using table storage, not realizing what the absence of NoSQL entails (and in this case a particularly backward version of NoSQL).

Answer 2

The best recommendation I can make is basically: "Try not to use Azure Table Storage." As other people have noted, this is not just a No-SQL data warehouse, it is an especially low-level, limited, and very low-level instance of No-SQL storage. The only thing that can be said is that you can invest a lot of data in it, very quickly and with minimal storage costs. However, you basically cannot hope to get this data again if you are unlucky to have a use case that magically matches its storage model of key-sections / strings. If you do not - and I suspect that there are very few people, you will do a lot of section scanning and process the data yourself.

In addition, Azure Table Storage seems to be at a dead end in terms of development. If you look at the “Secondary Index Support” query on the Azure feedback forums ( https://feedback.azure.com/forums/217298-storage/suggestions/396314-support-secondary-indexes ), you can see that secondary index support It was promised back in 2011, but progress was not achieved. Also, no progress has been made on any of the other top queries for storing tables.

Now I know that Scott Guthrie is a quality guy, so I hope that all this stagnation at the front of Table Storage is a preface to Azure, fixing him and coming up with something really cool. This is my hope (although I have no evidence of this case). But right now, if you have no choice, I highly recommend using Azure Table Storage. Use Azure SQL use your own instance of MongoDB or some other No-SQL database; or use Amazon DynamoDB. But do not use Azure Table Storage.

EDIT: 2014-10-09 - Being forced to use a script where I needed to use it, I changed my mind about Azure Table Storage a bit. In fact, it has all the unfortunate restrictions that I attribute to it above, but it also has its (limited) uses. I get a little bit of them in a blog post here .

EDIT: 2017-02-09 - Nah, ATS is still terrible. Stay away from him. He hasn't improved significantly in 7 years, and MS obviously wants him to just leave. And this probably should be - they, apparently, only support it for people who made a mistake in placing bets on it initially.

Answer 3

take a look at this: Windows Azure Table Storage and Windows Azure SQL Database - compare and contrast

does not contain a drop, but reads well anyway ...