36 points of comparison between Windows Azure Table Storage & Windows Azure SQL Database
The MSDN article Windows Azure Table Storage and Windows Azure SQL Database - Compared and Contrasted not only provides comprehensive information but it has also been updated and unlike for most MSDN articles, there is even a date of update. I wish more MSDN articles were like this.
The article is pretty long but it has multiple tables which summarize key points. Tables and infographics make information easier to digest. I consolidated the info in multiple tables into a single giant table.
As with anything about the Cloud and Azure, this information can change (especially the costs) so look out for changes in the original article and elsewhere.
The article is pretty long but it has multiple tables which summarize key points. Tables and infographics make information easier to digest. I consolidated the info in multiple tables into a single giant table.
Comparison Criteria
|
Windows Azure Table Storage
|
SQL Database
|
Data relationships
|
No
Windows Azure Table Storage does not provide a way to represent relationships between data. You can obtain simple relationships by using schema-less properties of tables and structuring the data in the required format.
|
Yes
Similar to SQL Server, SQL Database allows you to define relationships between data stored in different tables by using foreign keys.
|
Server-side processing
|
No
Supports basic operations such as insert, update, delete, and select, but it does not support joins, foreign keys, stored procedures, triggers, or any processing on the storage engine side.
|
Yes
Provides standard SQL Server features such as stored procedures, views, multiple indices, joins, and aggregation.
|
Transaction support
|
Limited
Supports transactions for entities in the same table and the same partition. Up to 100 operations are supported in a transaction. Supports optimistic concurrency. For more information, see Entity Group Transactions.
|
Yes
Supports typical ACID transactions within the same database. Transactions are not supported across databases. SQL Database also supports optimistic concurrency.
|
Geo-replication
|
Yes
By default, a table is replicated to other regions. This replication provides a high degree of disaster recovery capabilities.
|
No
As of this writing, a SQL Database instance is not replicated to other regions. This behavior may change in the future.
|
Table schema
|
Relaxed
Each entity (row) can have different properties. For example, in the same table you can store order information in one row and customer information in another row.
|
Managed
Fixed schema for the entire table once defined but can be altered at any time. All rows must adhere to the schema rules. Consider using the XML type or sparse columns for additional flexibility.
|
Similarity to existing data stores used on-premises
|
No
Cloud-based storage with no on-premises alternatives at present.
|
Yes
Similar to SQL Server with some limitations. For more information, see General Guidelines and Limitations.
|
Scale-out
|
Automatic
Partitioned based on the PartitionKey property. A table might be stored in different partitions on different storage devices. This structure allows clients to access data in parallel.
|
Manual
Sharded across a managed group of database instances by using SQL Federations or a custom sharding approach.
|
Data types
|
Simple
For more information about supported data types, see the table in the “Additional Information” section.
|
Simple, Complex, and User Defined
SQL Database supports a rich set of data types, including custom user-defined types.
|
Accessible from on-premises applications or applications hosted in non-Windows Azure platforms
|
Yes
|
Yes
|
Consistency model
|
Strong
|
Strong
|
Windows Communication Foundation (WCF) Data Services client support
|
Yes
|
Yes
|
REST client support
|
Yes
Supports REST-based access out of the box.
|
Yes
Supports REST-based access by adding an OData layer on top of a SQL database.
|
Firewall protection (restricted IP range access)
|
No
|
Yes
|
Transaction throttling behavior
|
Yes
|
Yes
|
Fault tolerance
|
Yes
To provide a high degree of fault tolerance, the stored data is replicated three times within the region, and replicated an additional 3 times in another region more than 400 miles (644 kilometers) apart.
|
Yes
Three copies of a SQL Database instance are maintained within the chosen data center.
|
Logging and metrics
|
Yes
|
No
|
Transaction logs
|
No
|
Yes
Transaction log size is capped at 10 GB with a 1 GB limit on a single transaction.
|
Maximum row size
|
1 MB
With no more than 255 properties, including three required properties: PartitionKey, RowKey,Timestamp.
|
2 GB
Can contain up to 1024 columns (or 30,000 if sparse columns are used). The use of varchar(max), varbinary(max), xml,text, or image columns offers up to 2 GB of off-row storage.
|
Maximum data size
|
200 TB per table
A single storage account (containing tables, blobs, and queues) can contain up to 200TB of blob, queue, and table data if it was created June 8th, 2012, or later; for storage accounts created before that date, total capacity is 100TB. Therefore, the maximum size of a Windows Azure table is 200 TB.
|
150 GB per database
While the maximum allowed database size might be increased in the future, consider using SQL Federations (or custom sharding) to store larger data sets.
|
Maximum number of rows retrieved per query
|
1,000
No more than 1,000 rows (entities) are returned in response to a single request. If a query has more results than this amount, a continuation token is returned to allow the query to continue with additional requests.
|
Unlimited
If not tuned correctly, connection and query timeouts can limit the number of rows fetched.
|
Management protocol and tools
|
REST over HTTP/HTTPS
You can use the Windows Azure Storage Explorer or another third-party tool, such as Cloud Storage Studio.
|
ODBC/JDBC
REST over HTTP/HTTPS
You can use the Windows Azure Management Portal or SQL Server Management Studio to manage a SQL Database instance.
|
Data access
|
OData Protocol Interface
You can access data by using the HTTP(S) REST API or the .NET Client Library for WCF Data Services that is included in the Windows Azure SDK.
|
ODBC/JDBC
You can use applications written using existing technologies such as ADO.NET and ODBC that communicate with SQL Server to access SQL Database instances with minimal code changes.
|
Java API support
|
Yes
|
Yes
|
Node.js API support
|
Yes
|
Yes
|
PHP API support
|
Yes
|
Yes
|
LINQ support
|
Yes
|
Yes
|
Python support
|
Yes
|
No
|
Offline developer experience
|
Yes
Provided by the local storage emulator included in the Windows Azure SDK.
|
No
SQL Express or other editions of SQL Server are different products and do not offer full simulation of a Windows Azure SQL Database environment.
|
Authentication
|
Symmetric Key
Shared Access Signatures
512-bit HMAC key is used to authenticate users.
|
SQL Authentication
Standard SQL Authentication is used to authenticate users.
|
Role-based access
|
No
|
Yes
Supports standard SQL database and application roles.
|
Windows Azure Active Directory (formerly ACS) support
|
No
|
No
|
Identity provider federation
|
No
|
No
|
Storage cost
|
$0.125
per gigabyte stored per month based on the daily average.
|
Billed on a graduated rate based on the size of the database.
|
Transaction cost
|
$0.01
per 100,000 storage transactions.
|
$0.00
SQL Database does not charge for transactions.
|
Billable operations
|
All
In addition to storage costs, transaction cost is computed based on the volume of transactions against tables.
|
None
Cost does not depend on the volume of transactions, only on the database size.
|
Egress costs
|
$0.12 - $0.19
per gigabyte, based on a graduated, region-specific scale
|
$0.12 - $0.19
per gigabyte, based on a graduated, region-specific scale
|
As with anything about the Cloud and Azure, this information can change (especially the costs) so look out for changes in the original article and elsewhere.
Comments
Post a Comment