When should I use Azure Sql and when should I use table storage?

Question

When should I use Azure Sql and when should I use table storage? I thought using table storage for transaction processing scripts, for example. Debit credit accounts of the scenario type and use Sql Azure when the data will not be used for transactional purposes, for example, for reporting. What do you think?

Answer 1

This is a great question and one of the toughest and most complex decisions that solution developers must make when designing for Azure.

You can consider several dimensions: On the other hand, SQL Azure is relatively expensive for a gigabyte of memory, it does not scale super well and is limited to 150gigs / database, however, and this is very important, transactions are not charged with SQL Azure, and your developers already know how to deal with it fight.

ATS is another animal all together. Capable of mega-scalability, it is cheap to store, but expensive. It also requires a significant amount of processor power from your nodes to manage. This basically makes your compute nodes become db mini-servers as delegation of all relational activity is passed to them.

Thus, in my opinion, often accessible data that does not need huge scalability and does not have a large size should be intended for SQL Azure, otherwise Azure Table Services.

Your specific example - transaction data from financial transactions - is an ideal place for ATS, and metadata (account profiles, names, addresses, etc.) are ideal for SQL Azure.

Answer 2

Igor and Mark gave excellent answers. Let me add a little more ...

With SQL Database (formerly called SQL Azure), you can now have databases up to 500 GB in size. To go beyond that, you need to break your data. Note. I originally proposed shards with SQL federations, but since then this feature has been removed.

ATS offers section-level transactions (group of person transactions). See this MSDN article for more information. This is not as reliable as SQL Azure transactions, but allows you to perform batch operations in a single transaction.

EDIT More than a year has passed since this question was asked (and answered). One of the comparative points was regarding pricing. Although SQL Azure is still more expensive than ATS, the cost of SQL Azure has dropped significantly over the past year. Databases now have tiered prices, starting at $ 4.99 per 100 MB, increasing to $ 225 per 150 GB (a big decrease from the price for $ 9.99 / GB from last year. Detailed pricing information here .

EDIT Aug 2014 Another year later, another update. As networks / business layers continue to exist, they drag out (and SQL federations are no longer available). New Basic, Standard, and Premium levels are now available (see here for more details).

Answer 3

Some of these answers do not seem complete, so I will add my 2 cents.

Azure table Good points:

• The strength is its ability to store a lot of small data; The Azure table is based on Azure Blob, but focuses on smaller data.
• Much cheaper than Azure SQL Server
• At any time, when you know both the section key and the row key, data access is very fast.
• Entity transactions possible if you put two different “schemes” in the same key of the section.
• If the total row size is less than 980K (SQL row)
• If each property is LESS than 64 KB (SQL column)
• He can act like a poor man of SQL.

Bad points on the Azure table:

• SQL is a weak point. Do not expect to use this on any large SQL table or you will experience performance problems.
• Limited SQL is available, do not expect a join of any type except what you implement in Linq
• Azure SQL table does not scale, and the ability to store an infinite amount of data
• Anytime you do not specify a partition key and a row key in a WHERE clause, expect a slow table scan.
• Expect table scan performance to degrade as more rows are added.
• Do not expect Azue table queries to be faster when you add more rows.
• Bottom line, if you use an Azure table to act like SQL, don't add a lot of data. If you have a lot of data (gigabytes), just do not plan to receive high-performance SQL queries from it. You will save money if you do not need these regular SQL functions.

Answer 4

When it comes to transactions, it's just the opposite: SQL Azure supports transactions; storage table no.

SQL Azure is basically SQL Server running inside Windows Azure, so if you have an existing application using SQL Server, SQL Azure provides a good migration path . However, there are limitations on how large the database you can have on SQL Azure (currently 150 GB), so there are limits on how much it can scale.

Table storage, on the other hand, is extremely scalable, but requires a different way of thinking. This is not a relational database . See this article for a nice introduction: http://msdn.microsoft.com/en-us/magazine/ff796231.aspx

Answer 5

The real answer is: "Try not to use Azure Table Storage." Whenever you switch from a relational database to a database without sql, you will of course have to change how you think about your storage architecture. But problems with ATS are coming, and not just "thinking differently." 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. It is not a question of “thinking differently” about ATS; this is an ATS question that does not provide you with the tools you need to complete your work - tools that other no-sql data stores provide you with.

The only thing that can be said about ATS is that you can quickly and easily insert a lot of data into it 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 for key / string partitions. 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 Secondary Indexes” query on the Azure feedback forums ( http://feedback.windowsazure.com/forums/217298-storage/suggestions/396314-support-secondary-indexes ), you will see that the secondary indexes support was promised back in 2011, but no progress was made. 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 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.