【FWD】Windows Azure Table Storage vs. Windows SQL Azure

By Jim White (Director of Training and Instructor)

Last week, my fellow Intertech colleague and Microsoft MVP, Tim Star, and I presented the Windows Azure Bootcamp for the Twin Cities.  According to Microsoft reps, we broke the record for the most attended bootcamp in the US with nearly a hundred people at the event.

A common question that I received during and after the bootcamp was "Why would I want to use Windows Azure Table Storage versus Windows SQL Azure to store my application data?"  A good question with the answer dependent on your application and data needs.

Table Storage and SQL Azure Defined

First, allow me to backup and set the stage a little for this discussion.  SQL Azure is essentially SQL Server in the Microsoft cloud computing environment known as Azure.  That is not quite true in that SQL Azure currently has a number of limitations and unsupported features that SQL Server 2008 has (here is a starter list of limitations:  http://msdn.microsoft.com/en-us/library/ee336245.aspx).  I like to say that SQL Azure is either SQL Server 2008 minus or SQL Express plus depending on how you want to view it.  Table Storage is one of three alternate storage mechanisms built into Azure that is collectively called Windows Azure Storage Services.  The other two being queues and blobs.  Table Storage allows you to store serialized entities in a table, but the term table here  is not a relational database table.  To provide people with some analogy they can use to get their arms around Table Storage, I like to tell people to think of Table Storage as a fancy spreadsheet.  You can store the state of your entities in the columns of the spreadsheet.  However, there is no linkage or relationship (therefore joins) between entities - at least none that is automatically managed and maintained by Azure.  There are no custom indexes - at least not today.

Interestingly, when Azure was first introduced in 2008, SQL Server was not part of the original picture.  Because of developer negative reactions, SQL Azure was added to the next preliminary release in 2009.  There is a growing faction that is trying to get the software community to look at SQL alternatives.  The "No-SQL" community (see here and here), to some extent, has influenced part of the Azure cloud computing platform through the Table Storage option, but not enough to eliminate it from the Microsoft cloud.

While both SQL Azure and Azure Table Storage provide data persistence via table structure, there are a number of differences between them.  The sections below outline some of the key differences and factors you want to weigh before building an application for Azure that requires some form of table persistence.

Scale and Performance

When looking at sheer volume, Table Storage is today far more scalable than SQL Azure.  Given a storage account (storage accounts hold blobs, queues and tables) is allowed to be 100TB in size, in theory your table could consume all 100TB.  At first glance, a 100TB chunk of data may seem overwhelming.  However, Table Storage can be partitioned.  Each partition of Table Storage can be moved to a separate server by the Azure controller thereby reducing the load on any single server.  As demand lessens, the partitions can be reconsolidated.  Reads of Azure Table Storage are load balanced across three replicas to help performance.

Entities in Table Storage are limited to 1MB each with no more than 255 properties (3 of which are required partition key, row key, and timestamp).  That seems like an absurd number, and it is, but remember that there are no relationships and joins in Table Storage.  Therefore, you might need some wide tables to handle associated data.

Today, SQL Azure databases are limited to 1GB or 10GB.  However, sometime this month (June 2010), a 50GB limit is supposed to be available.  What happens if your database is larger than 10GB today (or 50GB tomorrow)?  Options include repartitioning your database into multiple smaller databases or sharding (Microsoft's generally recommended approach).  Without getting into the database details of both of these database design patterns, both of these approaches are not without issue and complexity, some of which must be resolved at the application level.

Data Access

Data in the cloud, be it in SQL Azure or Azure Table Storage, can be accessed from in or out of the cloud.  To access data in SQL Azure, all the standard tools and APIs apply that work with SQL Server.  Meaning, your existing .NET/SQL Server knowledge and experience can be heavily leveraged.  ADO.NET and ODBC APIs can be used by application code to access the SQL Azure database.  Tools like SQL Server Management Studio and Visual Studio can be pointed to the SQL Azure instance and manipulate the schema and data just as you do today with SQL Server. 

Access to Azure Table Storage is accomplished either via REST API or Storage Client Library provided with the Windows Azure SDK.  Using the REST API allows client applications to communicate and use data from Table Storage without having detailed and specific knowledge of an Azure API, but it is more complex and difficult to work with.  The Storage Client Library (which leverages LINQ to Objects) provides a layer of convenience but requires the application reference the Storage Client Library APIs.  REST and the Storage Client Library incur a learning curve that is typically not there when using SQL Azure.

Portability

As mentioned, data in SQL Azure and Table Storage can be accessed from applications in and out of the cloud.  That means applications can be moved in or out of the cloud and still deal with the data in the cloud in the same way.  However, one question that may need to be considered is whether the data must always live in the cloud?  Applications generally view data in SQL Azure similar enough to data in a normal SQL Server database as to allow the data to migrate back and forth between the cloud and on-premise databases.  In fact, there are even migration tools to help move data between instances of SQL Server and SQL Azure. 

However, given the unique nature and access APIs of Table Storage, portability of the data is not as straight forward.  Table Storage tightly couples your data to the cloud.  Moving the data out of the cloud would require an on-premise data storage alternative, a data migration strategy, and likely require application code changes.

Transactions and Concurrency

SQL Azure supports typical ACID transactions for work within the same database.  Transactions across databases are not supported.  SQL Azure allows for typical optimistic and pessimistic concurrency strategies.

Table Storage supports transactions for entities in the same table and table partition, but not across tables or partitions.  Additionally, only 100 operations or less (what is called a batch in Azure Table Storage) can be part of the transaction.  Only one operation can be performed on each entity in the batch, and the batch must be limited to 4MB.  Table Storage abides strictly by an optimistic concurrent strategy.  If, on commit of the transaction, data has been changed by another process the whole transaction must be rolled back and retried.  Due to this single concurrency strategy, a built-in retry option is provided with the Storage Client Library.

Queries

Using Table Storage, queries are limited to 1000 entities by default.  If more than 1000 entities are found, a continuation token is returned and must be used by the application to retrieve the next set of entities.  Queries that take longer than 5 seconds also return a continuation token.  Queries that take longer than 30 seconds are cancelled.  Data in Table Storage is organized by partition key and indexed by row key.  Because there are no custom indexes in tables, queries by partition key and row key are fast, but queries that do not use partition key and row key are slow.

Generally speaking, SQL Azure has no limitations, issues or special programming requirements to work with large queries.  Good database and index design can help improve performance of queries; especially large ones.

Column types

Columns in Table Storage are limited to the types in the table below.

byte[]

bool

DateTime

double

Guid

Int32 or int

Int64 or long

String

Cost

Perhaps the most unique aspect to designing and architecting applications for the cloud is that it requires developers to think like businessmen.  Each technical choice often has direct costs associated with it when developing for the cloud.  The choice in data storage can have a huge impact on the cost of running an application.

Azure Table Storage costs 15? per GB of storage per  month.  Additionally, you pay 1 cent per 10,000 transactions with Table Storage.  SQL Azure costs are $9.99 for 1 GB of storage per month ($99.99 for 10GB). 

See Microsoft's sight for more details and specifics on costs here.

Bottom Line

Chris Hay and Brian Prince in their forth coming book Azure in Action (published by Manning - see here) provide a synopsis of the SQL Azure vs. Table Storage in a few paragraphs.  "If size is the issue, that would be the first flag that you might want to consider Azure Tables. As long as the support Tables has for transactions and queries meets your needs. The size limit surely will, at 100TB."  Further they suggest sophisticated transactions, or a complex authorization model might require the services of SQL Azure.  And as shown by the cost table above, "The final consideration is cost. I can store a lot of data in Azure Tables for a lot less money than I can in SQL Azure. SQL Azure is giving me a lot more features to use (joins, relationships, etc.), but it does cost more."

Future

We are given every indication by Microsoft that SQL Azure will have far more capability in the future - akin to the SQL Server you might find in your data centers today.   So some of the comparison above may be moot or less important over time.  Additional functionality is also being proposed to Table Storage as well.  For example, support of secondary (non-key) indexes is already been suggested for a future release (see here).  However, key architectural differences between SQL Azure and Table Storage will remain and leave application designers having to pick the best option for their systems.  Welcome to cloud computing.  There is a lot of ROI to be had by running in the cloud, but only with proper application design and architecture.

转载于:https://www.cnblogs.com/xixifusigao/archive/2011/11/28/2266341.html