<body><script type="text/javascript"> function setAttributeOnload(object, attribute, val) { if(window.addEventListener) { window.addEventListener('load', function(){ object[attribute] = val; }, false); } else { window.attachEvent('onload', function(){ object[attribute] = val; }); } } </script> <div id="navbar-iframe-container"></div> <script type="text/javascript" src="https://apis.google.com/js/plusone.js"></script> <script type="text/javascript"> gapi.load("gapi.iframes:gapi.iframes.style.bubble", function() { if (gapi.iframes && gapi.iframes.getContext) { gapi.iframes.getContext().openChild({ url: 'https://www.blogger.com/navbar.g?targetBlogID\x3d8211560\x26blogName\x3dTech+Tips,+Tricks+%26+Trivia\x26publishMode\x3dPUBLISH_MODE_BLOGSPOT\x26navbarType\x3dBLUE\x26layoutType\x3dCLASSIC\x26searchRoot\x3dhttp://mvark.blogspot.com/search\x26blogLocale\x3den\x26v\x3d2\x26homepageUrl\x3dhttp://mvark.blogspot.com/\x26vt\x3d-5147029996388199615', where: document.getElementById("navbar-iframe-container"), id: "navbar-iframe" }); } }); </script>

Tech Tips, Tricks & Trivia

by 'Anil' Radhakrishna
An architect's notes, experiments, discoveries and annotated bookmarks.

Search from over a hundred HOW TO articles, Tips and Tricks


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.


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
Uses the Windows Azure firewall that is configurable from the portal, or using command-line tools.
Transaction throttling behavior
Yes
For more information, see this blog post.
Yes
For more information, see this article.
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
For more information, see this blog post.
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.
See Windows Azure Pricing Overview for pricing details.
Billed on a graduated rate based on the size of the database.
See Windows Azure Pricing Overview for pricing details.
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.

Labels:

Tweet this | Google+ it | Share on FB

« Home | Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »

»

Post a Comment