Comparing and contrasting Windows Azure Table Storage and Windows Azure SQL Database

Original author: Valery Mizonov, Seth Manheim
  • Transfer
This article compares two structured storage services supported by Windows Azure: Windows Azure Table Storage and Windows Azure SQL Database, formerly known as SQL Azure. The purpose of this article is to compare these technologies so that you can understand their general and various characteristics. This comparison will help you make a more informed decision about which technology will be better suited to your scenario.


When it comes to persistent data storage in Windows Azure, there are two cloud options: Windows Azure SQL Database and Windows Azure Table Storage.

Windows Azure SQL Databaseis a relational database service that extends the core functionality of SQL Server to the cloud. With SQL Database, you can deploy relational database solutions in the cloud, and the benefits of this approach include a managed infrastructure, high availability, scalability, a familiar development model, and various data access frameworks and utilities - everything looks like what we see with traditional SQL Server environment. SQL Database also offers the functionality of migrating, exporting, and real-time synchronizing local SQL Server databases with databases in Windows Azure SQL (using SQL Data Sync ).

Windows Azure Table Storage- It is a fault-tolerant, ISO 27001 certified NoSQL storage service (key-value), which can be useful for applications that store large amounts of non-relational data, for which additional structures are required. This service provides key-based access to data that does not have a schema. Moreover, if you store structured data without a scheme, you cannot create relationships between data.

Despite the notable differences, both services are highly accessible managed services for which a 99.9% monthly SLA is provided.

Table Storage vs. SQL Database

Like SQL Database, Windows Azure Table Storage stores structured data. The main difference between the two services is that SQL Database is a relational database management system based on the SQL Server engine and is built on standard principles and relational fundamentals, thus providing features such as Transact-SQL queries, ACID transactions, stored procedures, and executable procedures server side.

Windows Azure Table Storage is a flexible key-value entity store that allows you to quickly create a cloud-based application without locking the application data model to a specific set of schemas. This service is not a repository of relational data and does not provide such capabilities for managing relational data as SQL Database (for example, joins and stored procedures). Windows Azure Table Storage has limited server-side query support, but has transactional features. In addition, different records within the same table can have a different structure, and this approach in Windows Azure Table Storage allows you to efficiently store and operate with simple relational data.

If your application stores and operates with large amounts of data for which there is no need to use relational functions, Windows Azure Table Storage might be a better option. If your application needs to process datasets connected by any schemas, SQL Database looks like a more suitable option than Windows Azure Table Storage. There are several other factors to consider before deciding whether to use SQL Database or Windows Azure Table Storage, some of which are listed below.

Considerations for choosing a technology

When determining the storage technology that is appropriate for a particular solution, the architect and developer should consider the following recommendations:

Consider usingWindows Azure Table Storage when:

· Your application must store large amounts of data (for example, many terabytes) in cheap storage.

· Your application stores and operates with large data sets that do not have complex relational relationships, for which you need to use join-s on the server side, secondary indexes or some kind of complex logic that runs on the server side.

· Your application needs a flexible data scheme for storing objects that are heterogeneous in its scheme, the structure of which is difficult to determine at the design stage.

· Your business needs the ability to store data in the event of catastrophic situations using geographic diversity. Windows Azure tables are geographically replicated over two data centers, located several hundred miles apart, but on the same continent, which provides additional confidence in the safety of data in the event of a disaster.

· You need to store more than 150 GB of data without using sharding or partitioning.

· You need a high level of scaling without manually sharding data.

Consider using the Windows Azure SQL Database when:

· Your application processes data that has a high degree of structured relationships and schemas.

· Your data is essentially relational and needs to implement the key principles of the software model of relational data to ensure data integrity due to the rules of data uniqueness, restrictions and various types of keys.

· The amount of your data may not exceed 150 GB per one allocated storage unit (database), however, you can partition your data into several databases if this limit is exceeded, which may be changed in the future.

· Your existing data-oriented applications already use SQL Server, but you need access to cloud storage using existing frameworks and the ability to transparently migrate between your on-premises infrastructure and Windows Azure.

· You plan to use T-SQL stored procedures in your application to perform calculations within the data layer, thereby minimizing the number of round trips between the application and the data store.

· Your application should use spatial data, various data types, and data access patterns with unions, aggregations, and complex predicates.

· Your application provides visualization and BI data models using utilities.
Many applications in Windows Azure can take advantage of both technologies, but we recommend that you use them in conjunction.

Comparing Windows Azure Table Storage and SQL Database

The tables below group the comparison factors for both services.

Key features

This section compares the key features provided by Windows Azure Table Storage and SQL Database.
Comparison criterionWindows Azure Table StorageSQL Database
Relationship between dataNo

Windows Azure Table Storage provides methods for creating relationships between data. Instead, you can implement simple relationships using diagramless table properties and structuring data in a specific format.

Like SQL Server, SQL Database allows you to define relationships between data stored in different tables using foreign keys.
Server side processingNo

Operations supported: insert , update , delete , select , not supported: joins, foreign keys, stored procedures, triggers, server-side processing.

The standard set of SQL Server functions is stored procedures, views, complex indexes, joins, aggregations.
Transaction supportLimited

Transactions are supported within the same table and one partition, in a transaction - up to 100 operations, while optimistic concurrency is supported.

Traditional ACID transactions are supported within the same database; between databases, they are not supported. Optimistic concurrency is supported.
Geographic replicationYes

By default, the table is replicated to other data centers within the region.

At the moment, SQL Database is not replicated to other data centers within the region.
Scheme in the tableRelaxed

Each entry can have its own set of properties.

Schema is defined, but can be changed at any time, all records must comply with this scheme. Consider using an XML type or sparse columns for added flexibility.
Comparable to existing local data warehousingThere are no

local alternatives to cloud storage.

An analogue of SQL Server with certain limitations - General Guidelines and Limitations .
Vertical scalingAutomatic

Partitioned based on the PartitionKey property . The table can be stored in different partitions on different devices, which allows clients to perform parallel access.

sharding between a database group using SQL Federations or a proprietary sharding approach.
Data typesSimple

See table in “More Information”.
Simple, complex, user-defined

SQL Database supports a wide range of data types, including the ability to determine the type of user.

Additional Information

· When creating a table, you do not have a single column and the table itself is not structured and has no schema. Column names are parts of the records stored in the table, and may be different for different records within the same table. A table can even consist of two entities with the same property name but different types, however property names must be unique within the same record.

· Windows Azure Table Storage does not support relational - joins and aggregations in queries and transactions. Entities with a single partition key are served together in a single repository, and you can efficiently operate on this data, as well as modify them within the same query using Entity Group Transactions .

· There are some restrictions that must be considered when using entity group transaction, for example, the maximum packet size is 4 MB and that all entities in the package must have one partition key.

· Windows Azure Table Storage provides one clustered index, and the results are always sorted by PartitionKey and RowKey , in ascending order. The PartitionKey and RowKey values uniquely identify each record in the table, and if you try to create two records with the same values ​​for these properties, an exception will be thrown.

· Bandwidth criterion - a complex equation with many variables, including query types and their complexity, data access patterns, size of the result set, distance to the storage infrastructure and network latencies, so it’s good advice to constantly test performance and evaluate various factors, taking into account features of specific applications. More about best practices for tables: post .

· The table below summarizes the data types supported by tables for properties. Supported data types for SQL Database: Data Types (Windows Azure SQL Database) .
A typeDetails
BinaryArray of bytes up to 64 KB.
Datetime64-bit UTC time. The range of values ​​is from 1/1/1601 to 12/31/9999.
Double64-bit floating point value.
GUID128 bit GUID.
Int32-bit integer.
Int6464-bit integer.
StringThe value in UTF-16 is up to 64 Kb.

Advanced features

Comparison criterionWindows Azure Table StorageSQL Database
Available from local applications or other platforms (except Windows Azure)YesYes
Consistency ModelStrictStrict
Support for Windows Communication Foundation (WCF) Data ServicesYesYes
REST supportYes

support by default.

REST-based access support by adding an OData layer on top of the database.
Firewall Protection (IP restriction)NotYes

Uses the Windows Azure firewall, configured from the portal or through the console.
Transaction throttling behaviorYes

Read more - blog post .

Read more - article .
Error toleranceYes

To ensure a high level of fault tolerance, the stored data is replicated in three copies within the region and three more copies to another data center in the same region.

The data center stores three copies of each instance of SQL Database.
Logging and MetricsYes

Read more - blog post .
Transaction logsNotYes

The transaction log size is limited to 10 GB with a limit of 1 GB per transaction.

Additional Information

· You can restrict access to an instance of SQL Database at the network level using the built-in firewall by configuring its rules on the management portal. Any client can connect to the tables, which can connect via HTTP / HTTPS to the Windows Azure storage.

· Windows Azure Table Storage provides guarantees for all insert, update and delete transactions for a single record and for Entity Group Transaction. Snapshot isolation is provided for each request. The request controls the presentation of the partition from the beginning of the execution of the request and during the execution of the transaction. For consistency between multiple tables, application developers are responsible.

· Windows Azure Tables supports logging, allowing you to see all the running queries. Logging also provides aggregated metrics for queries.

· Windows Azure SQL Database currently does not provide logging and metrics, however, it has a set of dynamic management views (DMV) that are useful for diagnosing query performance problems, monitoring database connections, viewing active transactions, and studying query execution plans.

· Since the Windows Azure SQL Database is based on the SQL Server engine, some concepts have a place to be, for example, TempDB and transaction logs. To prevent uncontrolled growth of the transaction log, SQL Database imposes a limit of 10 GB on the size of the log. The SQL Database infrastructure manages these logs, which are not directly accessible. In Windows Azure Table Storage there is no equivalent to these logs, instead there is logging and metrics that, however, track requests, but not mutable data.

· To prevent excessive use of resources in a multi-tenant environment, both services use the throttling mechanism, which, however, differs by the principles of operation of these services. For example, SQL Database uses two throttling strategies: soft throttling andhard throttling , which you can read about here .

Capacity and quotas

Comparison criterionWindows Azure Table StorageSQL Database
Maximum Record Size1 Mb

No more than 255 properties, which include three mandatory: PartitionKey , RowKey , Timestamp .
2 GB

Up to 1024 columns (30,000 if sparse columns are used). Using varchar (max) , varbinary (max) , xml , text , and image allows you to use an additional 2 GB.
Maximum data size100 TB per table

One storage account (with tables, blobs and queues) can be up to 100 TB in size, the maximum table size is 100 TB.
150 GB per DB

Despite the possibility of increasing the upper limit on database size in the future, pay attention to SQL Federations.
Maximum number of records returned in a single query1,000

No more than 1000 records in one request. If the result is more than this amount, the continuation token is returned.

If set up incorrectly, there may be a problem with the quantity limit due to request and connection timeouts.

Additional Information

· Windows Azure Table Storage uses a continuation token in the response header to indicate that there are more than 1000 records in the dataset. This token can be used to get the remaining data. For each request, there is a snapshot consistency, while for requests with tokens, there is no continuation.

· The total size of all properties in a table record cannot exceed 1 Mb, and this limit includes the size of property names and their values, which also include the required properties of PartitionKey and RowKey.

· SQL Database currently supports databases ranging in size from 5 GB (in the Web edition) to 150 GB (in the Business edition). The developer must control the size of the data so that it remains within these limits, since the configured database size does not increase as the amount of data increases.

· The number of columns in a simple SQL Database table is limited to 1024 (similar to local SQL Server), with sparse columns, a table can contain up to 30,000 columns, 1023 of which may not be sparse, 28976 should be sparse.


Comparison criterionWindows Azure Table StorageSQL Database
Management Protocol and UtilitiesREST over HTTP / HTTPS

You can use Windows Azure Storage Explorer or a third-party utility such as Cloud Storage Studio.
The ODBC / the JDBC

the REST over HTTP / HTTPS

You can use the Management Portal or SQL Server Management Studio.
Data accessOData

You can access data using the HTTP (S) REST API or the .NET Client Library for WCF Data Services, which is part of the Windows Azure SDK.

You can use applications written using traditional data access technologies, such as ADO.NET and ODBC, which can communicate with SQL Server to access an instance of SQL Database with minimal changes to the code.
Java API SupportYesYes

Node.js API Support

PHP API Support

LINQ Support

Python support
Offline DevelopmentYes

Presence of a local storage emulator from the Windows Azure SDK.

SQL Express and other editions of SQL Server are other products that do not fully emulate the Windows Azure SQL Database cloud environment.

Additional Information

· Although SQL Database can be emulated by installing SQL Server locally, this approach will not allow simulating a situation specific to a cloud service, for example, throttling and other restrictions.

· Windows Azure SQL Database provides an interactive query execution environment. You can also access SQL Database from console utilities, such as SSMS or third-party utilities that support ODBC.

· The T-SQL capabilities of SQL Server and SQL Database vary - some functions are limited or not supported at all, some have significant differences (such as creating a database and Federations).

Authentication and Authorization

Comparison criterionWindows Azure Table StorageSQL Database

Shared Access Signatures symmetric key

A 512-bit HMAC key is used to authenticate users.
SQL authentication

Standard user authentication is used to authenticate users.
Role Based AccessNotYes

Standard database roles and applications for SQL are supported.
Windows Azure Active Directory (formerly ACS)NotNot
Federation with Identity ProviderNotNot

Additional Information

· The role-based access model supported by SQL Database provides a full range of options for setting various modes: read-only, write-only, read-write.

· Since none of the services currently supports federated authentication, based on certificates or Active Directory, you must ensure that the credentials are sufficiently protected, for example, encrypted.

Windows Azure Table provides the ability to sign URLs using Table SAS (Shared Access Signature). SAS allows you to issue temporary access without issuing a secret key to your account.


Comparison criterionWindows Azure Table StorageSQL Database
Storage cost$ 0.125

per gigabyte per month, calculated on the basis of the daily load.
It is calculated based on the size of the database.
Transaction cost$ 0.01

per 100,000 transactions.
$ 0.00

In SQL Database, transactions are not paid.
Paid TransactionsAll

Transaction cost is added to the storage cost.

Cost does not depend on the number of transactions, only on the size of the database.
Outbound Cost$ 0.12 - $ 0.19

per gigabyte, depending on the region
$ 0.12 - $ 0.19

per gigabyte, depending on the region

Additional Information

· The cost of outgoing traffic is calculated on the basis of the total amount of data that goes beyond the limits of the data center on the Internet. The quantity is considered for a certain period.

· Unlike SQL Database, Windows Azure Table Storage imposes additional transaction costs. This payment model means that you must consider the frequency of transactions as a factor affecting the total cost.


The decision about whether to use Windows Azure Table Storage or Windows Azure SQL Database in your case depends on many factors, which, in turn, are highly dependent on the specific characteristics of your application, its architecture, type of load, data access patterns.

Windows Azure Table Storage supports the ability to store large amounts of data in scalable cloud storage, up to many terabytes and billions of records. To implement this level of scaling, Windows Azure Table Storage uses a vertical scaling model to distribute records between multiple storage nodes — the service uses the NoSQL model to support this level of scaling with strict consistency.If you need cheap storage that can store huge amounts of non-relational or simple data, consider using Windows Azure Table Storage.

Consider using Windows Azure SQL Database as extending your on-premises SQL Server to the cloud, while offering familiar tools, ACID transaction support with different isolation levels, and the ability to process complex data. If your data is relational and you need to consider this in managing it, SQL Database may be the best option to use.

Please note that the solution may not be to choose one technology - you can decide how to use both technologies in your specific scenario in the best way.

Also popular now: