Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

When should i use Sql Azure and when should I use table Storage? I was thinking , use table storage for transaction processing scenarios e.g. debit credit accounts kind of scenario and use Sql Azure when data will not be used for transactional purposes e.g reporting. What do you think?

like image 956
Allan Rwakatungu Avatar asked Feb 08 '11 06:02

Allan Rwakatungu


People also ask

When should I use Azure table storage?

Azure tables are ideal for storing structured, non-relational data. Common uses of Table storage include: Storing TBs of structured data capable of serving web scale applications. Storing datasets that don't require complex joins, foreign keys, or stored procedures and can be denormalized for fast access.

What are the differences between the Azure table storage and the Azure SQL service?

There is a fundamental difference between a SQL table and Azure table. While azure tables are capable of storing one type of data in a row and another type of data in an another row, SQL database tables are designed to store the same type of data in each and every row.

What is a benefit of the azure cosmos DB table API as compared to Azure table storage?

What is a benefit of the Azure Cosmos DB Table API as compared to Azure Table storage? Multi-master support for Azure Cosmos DB is now available in all public regions. Azure CosmosDB table API is a key-value storage hosted in the cloud. It's a part of Azure Cosmos DB, that is Microsoft's multi-model database.

What are the advantages of Azure tables over logical SQL Server tables?

Azure SQL Database offers Database-as-a-service (DBaaS-PaaS). With SQL Database, you don't have access to the machines that host your databases. In contrast, Azure Virtual Machine offers Infrastructure-as-a-service (IaaS).


2 Answers

This is an excellent question and one of the tougher and harder to reverse decisions that solution architects have to make when designing for Azure.

There are mutliple dimensions to consider: On the negative side, SQL Azure is relatively expensive for gigabyte of storage, does not scale super well and is limited to 150gigs/database, however, and this is very important, there are no transaction fees against SQL Azure and your developers already know how to code against it.

ATS is a different animal all together. Capable of mega-scalability, it is dirt cheap to store, but gets expensive to frequently access. It also requires significant amount of CPU power from your nodes to manipulate. It basically forces your compute nodes to become mini-db servers as the delegation of all relational activity is turned over to them.

So, in my opinion, frequently accessed data that does not need huge scalability and is not super large in size should be destined for SQL Azure, otherwise Azure Table Services.

Your specific example, transactional data from financial transactions is a perfect place for ATS, while meta information (account profiles, names, addresses, etc.) is perfect for SQL Azure.

like image 108
Igorek Avatar answered Sep 28 '22 04:09

Igorek


Igor and Mark gave great answers. Let me add just a bit more...

With SQL Database (formerly named SQL Azure), you can now have databases up to 500GB. To go beyond that, you'd need to partition your data. Note: Originally I suggested shards with SQL Federations, but this feature has since been retired.

ATS does offer transactions at the partition level (entity group transactions). See this MSDN article for more information. This is not as robust as SQL Azure transactions, but it does allow for batch operations in a single transaction.

EDIT It's been over a year since this question was asked (and answered). One comparison point was on pricing. While SQL Azure is still more expensive than ATS, the cost of SQL Azure has dropped significantly in the past year. Databases now have tiered pricing, starting at $4.99 for 100MB, increasing to $225 for 150GB (a big drop from the $9.99 / GB pricing from last year. Full pricing details are here.

EDIT Aug 2014 Another year later, another update. While web/business tiers continue to exist, they are being sunsetted (and SQL Federations is no longer available). The new Basic, Standard, and Premium tiers are now available (see here for details).

like image 20
David Makogon Avatar answered Sep 28 '22 05:09

David Makogon