Export (0) Print
Expand All

Windows Azure Table Storage and Windows Azure SQL Database - Compared and Contrasted

Updated: January 24, 2014

Authors: Valery Mizonov and Seth Manheim

Reviewers: Brad Calder, Jai Haridas, Paolo Salvatori, Silvano Coriani, Prem Mehra, Rick Negrin, Stuart Ozer, Michael Thomassy, Ewan Fairweather

This topic compares two types of structured storage that Windows Azure supports: Windows Azure Table Storage and Windows Azure SQL Database, the latter formerly known as “SQL Azure.” The goal of this article is to provide a comparison of the respective technologies so that you can understand the similarities and differences between them. This analysis can help you make a more informed decision about which technology best meets your specific requirements.

Introduction

When considering data storage and persistence options, Windows Azure provides a choice of two cloud-based technologies: Windows Azure SQL Database and Windows Azure Table Storage.

Windows Azure SQL Database is a relational database service that extends core SQL Server capabilities to the cloud. Using SQL Database, you can provision and deploy relational database solutions in the cloud. The benefits include managed infrastructure, high availability, scalability, a familiar development model, and data access frameworks and tools -- similar to that found in the traditional SQL Server environment. SQL Database also offers features that enable migration, export, and ongoing synchronization of on-premises SQL Server databases with Windows Azure SQL databases (through SQL Data Sync).

Windows Azure Table Storage is a fault-tolerant, ISO 27001 certified NoSQL key-value store. Windows Azure Table Storage can be useful for applications that must store large amounts of nonrelational data, and need additional structure for that data. Tables offer key-based access to unschematized data at a low cost for applications with simplified data-access patterns. While Windows Azure Table Storage stores structured data without schemas, it does not provide any way to represent relationships between the data.

Despite some notable differences, Windows Azure SQL Database and Windows Azure Table Storage are both highly available managed services with a 99.9% monthly SLA.

Table Storage vs. SQL Database

Similar to SQL Database, Windows Azure Table Storage stores structured data. The main difference between SQL Database and Windows Azure Table Storage is that SQL Database is a relational database management system based on the SQL Server engine and built on standard relational principles and practices. As such, it provides relational data management capabilities through Transact-SQL queries, ACID transactions, and stored procedures that are executed on the server side.

Windows Azure Table Storage is a flexible key/value store that enables you to build cloud applications easily, without having to lock down the application data model to a particular set of schemas. It is not a relational data store and does not provide the same relational data management functions as SQL Database (such as joins and stored procedures). Windows Azure Table Storage provides limited support for server-side queries, but does offer transaction capabilities. Additionally, different rows within the same table can have different structures in Windows Azure Table Storage. This schema-less property of Windows Azure Tables also enables you to store and retrieve simple relational data efficiently.

If your application stores and retrieves large data sets that do not require rich relational capabilities, Windows Azure Table Storage might be a better choice. If your application requires data processing over schematized data sets and is relational in nature, SQL Database might better suit your needs. There are several other factors you should consider before deciding between SQL Database and Windows Azure Table Storage. Some of these considerations are listed in the next section.

Technology Selection Considerations

When determining which data storage technology fits the purpose for a given solution, solution architects and developers should consider the following recommendations.

As a solution architect/developer, consider using Windows Azure Table Storage when:

  • Your application must store significantly large data volumes (expressed in multiple terabytes) while keeping costs down.

  • Your application stores and retrieves large data sets and does not have complex relationships that require server-side joins, secondary indexes, or complex server-side logic.

  • Your application requires flexible data schema to store non-uniform objects, the structure of which may not be known at design time.

  • Your business requires disaster recovery capabilities across geographical locations in order to meet certain compliance needs. Windows Azure tables are geo-replicated between two data centers hundreds of miles apart on the same continent. This replication provides additional data durability in the case of a major disaster.

  • You need to store more than 150 GB of data without the need for implementing sharding or partioning logic.

  • You need to achieve a high level of scaling without having to manually shard your dataset.

As a solution architect/developer, consider using Windows Azure SQL Database when:

  • Your application requires data processing over schematic, highly structured data sets with relationships.

  • Your data is relational in nature and requires the key principles of the relational data programming model to enforce integrity using data uniqueness rules, referential constraints, and primary or foreign keys.

  • Your data volumes might not exceed 150 GB per a single unit of colocated data sets, which often translates into a single database. However, you can partition your data across multiple sets to go beyond the stated limit. Note that this limit is subject to change in the future.

  • Your existing data-centric application already uses SQL Server and you require cloud-based access to structured data by using existing data access frameworks. At the same time, your application requires seamless portability between on-premises and Windows Azure.

  • Your application plans to leverage T-SQL stored procedures to perform computations within the data tier, thus minimizing round trips between the application and data storage.

  • Your application requires support for spatial data, rich data types, and sophisticated data access patterns through consistent query semantics that include joins, aggregation, and complex predicates.

  • Your application must provide visualization and business intelligence (BI) reporting over data models using out-of-the-box reporting tools.

noteNote
Many Windows Azure applications can take advantage of both technologies. Therefore, it is recommended that you consider using a combination of these options.

Comparing Windows Azure Table Storage and SQL Database

The tables in the following sections provide a logical grouping of features and let you compare, at a glance, the capabilities available in both Windows Azure Table Storage and Windows Azure SQL Database.

Foundational Capabilities

This section compares some of the fundamental storage capabilities that Windows Azure Table Storage and SQL Database provide.

 

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.

Additional Information

  • When you create a Windows Azure table, you do not have to define any columns. The table itself is not structured and does not have a design-time schema. The column names are part of the entities (rows) that are stored in the table, and they can be different for different entities within a single table. A Windows Azure table can even have two entities with the same property name, but different types for the property value. However, property names must be unique within a single entity.

  • Windows Azure Table Storage does not support relational features, such as joins and aggregations in queries or transactions to coordinate modifications across multiple tables. Entities that are stored in Windows Azure tables with the same partition key are served together in the store. You can retrieve these entities efficiently, and you can modify them in a single request by using Entity Group Transactions.

  • There are some limitations to be aware of when using the entity group transactions. These limitations include a 4 MB maximum batch size, and all entities in the batch must share the same partition key value. For more information, see this article.

  • The Windows Azure Table Storage type provides one clustered index and results are always sorted by PartitionKey and RowKey, in ascending order. The PartitionKey and RowKey values uniquely identify a row in a table. If you try to create two rows with the same PartitionKey and RowKey, an exception is generated.

  • This article provides a decision tree for choosing between SQL Database and on-premises SQL Server. You can also apply these decision criteria to SQL Database versus Windows Azure Table Storage.

  • The throughput criteria applied to both technologies is a complex equation with many variables. These factors include the types of queries and their complexity, data access patterns, size of result sets, proximity to the storage infrastructure, and network latencies. It is always advisable to perform your own performance testing to better and more reliably measure the relevant indicators while taking into account the individual specifics of a particular class of applications. For more information about best practices for Windows Azure tables, see this blog post.

  • The following table lists the supported data types for property values in Windows Azure tables. For a list of data types supported by SQL Database, see Data Types (Windows Azure SQL Database).

     

    Property Type Details

    Binary

    An array of bytes up to 64 KB in size.

    Bool

    A Boolean value.

    DateTime

    A 64-bit value expressed as UTC time. The supported range of values is 1/1/1601 to 12/31/9999.

    Double

    A 64-bit floating point value.

    GUID

    A 128-bit globally unique identifier.

    Int

    A 32-bit integer.

    Int64

    A 64-bit integer.

    String

    A UTF-16-encoded value. String values can be up to 64 KB in size.

Advanced Capabilities

This section compares advanced capabilities that Windows Azure Table Storage and SQL Database provide.

 

Comparison Criteria Windows Azure Table Storage SQL Database

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.

Additional Information

  • You can restrict access to a SQL Database instance at the network level by using the built-in firewall functionality. Also, you can configure the firewall access rules through the Windows Azure portal. In contrast, any client that can connect via HTTP/HTTPS to a Windows Azure storage account endpoint can obtain access to Windows Azure tables.

  • Windows Azure Table Storage provides ACID transaction guarantees for all the insert/update/delete transactions for a single entity in a table and for Entity Group Transactions. Snapshot isolation is provided for each single query request to the service. A query maintains a consistent view of the partition from the start time of the query and throughout the transaction. Application developers are responsible for maintaining consistency across multiple tables.

  • Windows Azure Tables supports logging, allowing you to see every request that is performed against your service. Logging also provides aggregate metrics of the requests against your service.

  • Windows Azure SQL Database does not currently offer logging and metrics; however it provides a subset of dynamic management views (DMV) to diagnose query performance problems, monitor database connections, view active transactions, and inspect query plans.

  • Since Windows Azure SQL Database is built on top of the SQL Server engine, some concepts, such as TempDB and transaction logs, are still relevant. To prevent the transaction log files from growing unexpectedly, SQL Database imposes a 10 GB limit on the log size. The SQL Database infrastructure manages these transaction logs, which you cannot access directly. Windows Azure Table Storage has no equivalent to the transaction log. The logging and metrics functionality that Windows Azure Table Storage supports is different from transaction logging, since it tracks requests to the service, and not the actual data being modified.

  • To prevent excessive use of resources in a multi-tenant environment, Windows Azure Table Storage and SQL Database both use a mechanism that controls system thresholds. This mechanism is known as throttling, and its behavior varies between the two services. For example, SQL Database uses two throttling strategies: soft throttling and hard throttling. These throttling mechanisms are explained in detail in this article.

Capacity and Quotas

This section compares Windows Azure Table Storage and SQL Database from the perspective of capacity and quotas that might apply. Note that all capacities and quotas shown here are subject to change in the future.

 

Comparison Criteria Windows Azure Table Storage SQL Database

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.

Additional Information

  • Windows Azure Table Storage uses a continuation token in the response header to indicate that there are additional results for a query. You can retrieve these results by issuing another request that is parameterized by the continuation token. This scenario enables you to retrieve items beyond the 1,000-entity limit. Snapshot consistency is maintained for each request, but not across continuation token requests for a query.

  • The combined size of all fields (properties) in a Windows Azure table row (entity) cannot exceed 1 MB. This limit includes the size of property names as well as property values or their types, which includes the two mandatory key properties (PartitionKey and RowKey).

  • SQL Database currently supports up to 5 GB databases (in the Web Edition) or up to 150 GB databases (in the Business Edition). In order to keep its size within the given threshold, it is the responsibility of the developer to monitor the database. The SQL Database maximum size is preconfigured via a management operation, and does not automatically increment as the stored data volumes grow. For more information, see ALTER DATABASE (Windows Azure SQL Database) in the SQL Database documentation.

  • The number of columns in a regular SQL Database table is restricted to 1024 (similar to the on-premises SQL Server). With sparse columns, a table can have up to 30,000 columns, of which up to 1023 can be non-sparse. However, at least 28,976 have to be sparse columns. One non-sparse column is used for the column set that is mandatory if the total number of columns is greater than 1024.

Management and Operations

This section compares the management features that Windows Azure Table Storage and SQL Database provide.

 

Comparison Criteria Windows Azure Table Storage SQL Database

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.

Additional Information

  • Although SQL Database can be simulated in a local SQL Server installation, this approach does not allow replicating behavior that only applies to the cloud-based service, such as throttling and other applicable limitations.

  • Windows Azure SQL Database offers a web-based interactive query environment. SQL Database is also accessible from ad-hoc client console tools such as SSMS, or third-party RDBMS query tools that support ODBC.

  • T-SQL capabilities are different between SQL Server and SQL Database. Some features are limited and not supported, and some have notable differences (such as creating databases and Federations).

Authentication and Authorization

This section discusses the authentication and authorization features that Windows Azure Table Storage and SQL Database support.

 

Comparison Criteria Windows Azure Table Storage SQL Database

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

Additional Information

  • The role-based access that SQL Database supports offers full flexibility for configuring read-only, write-only, and read-write modes. This capability can provide a rich set of data access options, depending on the needs of the individual application.

  • Because neither technology currently supports federated, certificate-based, or Active Directory authentication, you must ensure that security credentials (for example, the HMAC key or SQL user name and password) are covered by appropriate protection measures such as encryption. This protection is especially important when access to these credentials is subject to IT compliance.

  • Windows Azure Table storage offers signed URL-based access, known as Table SAS (Shared Access Signature). SAS enables you to grant time-based access to clients without revealing the storage account secret key. For more information, see this blog post.

Cost

This section compares Windows Azure Table Storage and SQL Database from a cost perspective. All costs shown here are subject to change in the future.

 

Comparison Criteria Windows Azure Table Storage SQL Database

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

Additional Information

  • Egress cost is based on the total amount of data that leaves the Windows Azure datacenters via the Internet. The amount is calculated in a given billing period when an application performs queries and receives results from the respective data service.

  • Unlike SQL Database, Windows Azure Table Storage imposes a per-transaction cost. This billing model means that you should include the frequency of storage transactions in cost-related considerations.

Conclusion

The decision on when to use Windows Azure Table Storage and Windows Azure SQL Database depends on a number of factors. These factors can depend heavily on the individual needs of your application, its architecture, as well as workloads and data-access patterns. This section summarizes some of the key considerations.

Windows Azure Table Storage supports storing large amounts of data in massively scalable tables in the cloud. These tables can store terabytes of data and billions of entities. In order to attain this level of scalability, Windows Azure Table Storage employs a scale-out model to distribute entities across multiple storage nodes. It uses a NoSQL data model to support such a massive scale with strong consistency. If you require the persistence of a massive amount of nonrelational or simplified data models at a reduced cost, consider using Windows Azure Table Storage.

You can think of Windows Azure SQL Database as the SQL Server database engine extended to the cloud platform, offering a familiar SQL Server developer experience, rich query semantics, support for ACID transactions with different levels of isolation, and complex data processing capabilities. If your data is highly relational and you require relational data management coupled with these capabilities, SQL Database might be the better choice.

Note that a decision on when to use a particular technology is not always binary, and you might not always be able to decide in favor of a single technology. You can evaluate whether a balanced combination of the two technologies best meets the requirements of your solution and consider applying them both in the respective areas to address the specific class of problems you are solving.

By gaining a deeper understanding of the two technologies, you can make a more informed decision on which data storage technology to use on Windows Azure, and when.

See Also

Community Additions

Show:
© 2014 Microsoft