Azure SQL Database = Long-Term Storage?

Here is an interesting concept that I would like to share. I always looked at Azure SQL Database (the Microsoft PaaS relational database engine) as a first in class database server; and of course it is. But when you compare SQL Server to Azure SQL Database, it becomes quickly evident that SQL Server has more features, performs better, and has fewer limitations. And it makes total sense: SQL Server is a full-blown, configurable database platform, while Azure SQL Database is a limited version of SQL Server running on a shared server environment.

Some Key Differences


Let’s first review a few key differences between SQL Server and Azure SQL Database. The following differences are not meant to be exhaustive, but they represent key variations that make sense in the context of this blog post.

Performance

SQL Server is a highly scalable database server that can process hundreds or thousands of requests per second, with very high concurrency levels and virtually unlimited throughput (at least as much as the server allows). For example, there are almost no limits on memory access, or disk I/O, as long as the underlying hardware allows it. While SQL Server has internal limitations, they usually far exceed those of Azure SQL Database. Some of the performance limitations of Azure SQL Database are implemented in the form of Throttling, with specific error codes, to prevent a single user of a database to impact other customers on the same server.

Database Features

SQL Server also comes with many additional features than purely its relational engine, such as Linked Servers, Encryption, Full Text Indexing, SQL Agent and more. As a result, with a couple of exceptions, it is fair to think of Azure SQL Database as a subset of SQL Server (basically most of the relational engine). With Azure SQL Database you can create databases, tables, stored procedures, run most T-SQL statements and build a complete database. However some of the more advanced features are not available. On the other hand, one of Azure SQL Database’s amazing feature is the ability to create new databases quickly and easily, without having to worry about low-level configuration or to figure out on which server the database will reside.

Availability Features

Azure SQL Database has a significant advantage over SQL Server in the area of high availability, up to 99.9% of monthly uptime. While SQL Server offers configuration options that can exceed 99.9%, Azure SQL Database’s availability is provided by default, without any specific configuration, which is not the case for SQL Server. This means that high availability is built directly into the service and doesn’t require specialized knowledge to install or maintain.

Cost

Another important aspect of Azure SQL Database is cost: you pay for what you use. The larger the database, the higher the cost. And the longer you keep the database, the more you pay over time. This means that there are no licenses to worry about, and if you create a database for 24 hours, then drop it, you will pay for 24 hours of uptime; in the US, a 1GB database costs about $9.99 per month for the entry level editions, which is not very expensive.

A Parallel with Long-Term Storage Disks


Keeping the above information in mind, Azure SQL Database offers interesting capabilities that are difficult to achieve with SQL Server, at a reasonable price. Specifically, the ability to programmatically (and quickly) create databases, with high availability, is unparalleled. Let’s draw a parallel with long-term storage disks. Long-term storage is considered cheaper than hot disks, and is usually slower. The primary purpose of long-term storage is recoverability at a reasonable price. So if we assume that 99.9% availability monthly is acceptable for roughly $9.99 per month per 1GB of data, Azure SQL Database can be used to offload data that is not accessed very often and for which slower access time is reasonable.

This means that Azure SQL Database could be used to store temporary processing units of work (like batch processes), store historical data away from the primary database, create temporary tables for reporting purposes and more. And because SQL Server can communicate directly to Azure SQL Database using Linked Server, the abstraction can be total from the perspective of an end user. Stored procedures could be reading data directly from the cloud, or even merge with local data, to provide a unified view of the information to end users. Using Azure SQL Database as a long-term backend store for SQL Server seems to make a lot of sense for many scenarios.

 

About Herve Roggero


Herve Roggero, Windows Azure MVP, @hroggero, is the founder of Blue Syntax Consulting (http://www.bluesyntaxconsulting.com). Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress, a PluralSight author, and runs the Azure Florida Association.

Print | posted @ Monday, March 10, 2014 2:08 PM

Comments on this entry:

Comments are closed.

Comments have been closed on this topic.