• <1 minute

Data Series: SQL Server in Windows Azure Virtual Machine vs. SQL Database

This blog post is not up-to-date. For the latest information, please see: Understanding Azure SQL Database and SQL Server in Azure VMs Azure SQL Database SQL Server in Azure Virtual Machines &…

This blog post is not up-to-date. For the latest information, please see:

 

The key criteria in determining which of these two cloud database choices will be the better option for a particular solution are:

  • Full compatibility with SQL Server box product editions
  • Control vs. cost
  • Database scale-out requirements

In general, the two options are optimized for different purposes:

  • SQL Database is optimized to reduce costs to the minimum amount possible.  It provides a very quick and easy way to build a scale-out data tier in the cloud, while lowering ongoing administration costs since customers do not have to provision or maintain any virtual machines or database software.
  • SQL Server running in a Windows Azure VM is optimized for the best compatibility with existing applications and for hybrid applications.  It provides full SQL Server box product features and gives the administrator full control over a dedicated SQL Server instance and cloud-based VM.

Compatibility with SQL Server Box Product Editions

From a features and compatibility standpoint, running SQL Server 2012 (or earlier edition) in a Windows Azure VM is no different than running full SQL Server box product in a VM hosted in your own data center: it is full box product, and the features supported just depend on the edition of SQL Server you deploy (note that AlwaysOn availability groups are targeted for support at GA but not the current preview release; and that Windows Clustering will not be available at GA).  The advantage of running SQL Server in a Windows Azure VM is that you do not need to buy or maintain any infrastructure whatsoever, leading to lower TCO.  Existing SQL Server-based applications will “just work” with SQL Server running in a Windows Azure VM, as long as you deploy the correct edition.  If your application requires full SQL Server Enterprise Edition, your existing applications will work as long as you deploy SQL Server Enterprise Edition to the Windows Azure VM(s).  This includes features such as SQL Server Integration Services, Analysis Services and Reporting Services. No code migration will be required, and you can run your applications in the cloud or on-premise.  Using the new Windows Azure Virtual Network, also announced this month, you will even be able to domain-join your Windows Azure VM running SQL Server to your on-premise domain(s).  This is critical to enabling development of hybrid applications that can span both on-premises and off-premises under a single corporate trust boundary.  Also, VM images with SQL Server can be created in the cloud from stock image galleries provided within Windows Azure, or created on-premises from existing deployments and uploaded to Windows Azure. Once deployed, VM images can be moved between on-premises and the cloud with SQL Server License mobility, which is provided for those customers that have licensed SQL Server with Software Assurance (SA).

Windows Azure SQL Database, on the other hand, does not support all SQL Server features.  While a very large subset of features are supported (and this set of features is growing over time), it is not full SQL Server Enterprise Edition, and differences will always exist based on different design goals for SQL Database as pointed out above.  A guide is available on MSDN that explains the important feature-level differences between SQL Database and SQL Server box product.  Even with these differences, however, tools such as SQL Server Management Studio and SQL Server Data Tools can be used with SQL Database as well as SQL Server running on premises and in a Windows Azure VM. In a nutshell, running SQL Server in a Windows Azure VM will most often be the best route to migrate existing applications and services to Windows Azure given its compatibility with the full SQL Server box product, and for building hybrid applications and services spanning on-premises and the cloud under a single corporate trust boundary.  However, for new cloud-based applications and services, SQL Database might be the better choice for reasons discussed further below.

Control vs. Cost

While SQL Server running in a Windows Azure VM will offer the same database features as the box product, SQL Database aims as service to minimize costs and administration overhead.  With SQL Database, for example, you do not pay for compute resources in the cloud.  Rather, you just pay a consumption fee  per database based on the size of the database—from as little as $5.00 per month for a 100MB database, to $228.00 per month for a 150GB database (the current size limit for a single SQL Database database).

And while SQL Server running in a Windows Azure VM will offer the best application compatibility, there are two important features of SQL Database that customers should understand:

  • High Availability (HA) and 99.9% database uptime SLA built-in
  • SQL Database Federation

With SQL Database, high availability is a standard feature at no additional cost.  Each time you create a Windows Azure SQL Database, that database is actually operating across a primary node and multiple online replicas, such that if the primary fails, a secondary node automatically replaces it within seconds, with no application downtime.  This is how we are able to offer a 99.9% uptime SLA with SQL Database at no additional charge.

For SQL Server in a Windows Azure VM, the virtual machine instance will have an SLA (99.9% uptime) at commercial release. This SLA is for the VM itself, not the SQL Server databases. For database HA, you will be able to configure multiple VMs running SQL Server 2012 and setup an AlwaysOn Availability Group; but this will require some manual configuration and management, and you will pay extra for each secondary you operate—just as you would for an on-premises HA configuration.

With SQL Server running in a Windows Azure VM, not only can you control the operating system and database configuration (since it’s your dedicated VM to configure). But it is up to you to configure and maintain this VM over time, including patching and upgrading the OS and database software over time, as well as installing any additional software such as anti-virus, backup tools, etc. With SQL Database, you are not running in a VM, and have no control over a VM configuration. However, the database software is automatically configured, patched and upgraded by Microsoft in the data centers, so this lowers administration costs.

With SQL Server in a Windows Azure VM, you can also control the size of the VM, providing some level of scale up from smaller compute, storage and memory configurations to larger VM sizes. SQL Database, on the other hand, is designed for a scale-out vs. a scale-up approach to achieving higher throughput rates.  This is achieved through a unique feature of SQL Database called Federation.  Federation makes it very easy to partition (shard) a single logical database into many physical nodes, providing very high throughput for the most demanding database-driven applications.  The SQL Database Federation feature is possible because of the unique PaaS characteristics of SQL Database and its almost friction-free provisioning and automated management.  SQL Database Federation is discussed in more detail below.

Database Scale-out Requirements

Another key evaluation criterion for choosing SQL Server running in a Windows Azure VM vs. SQL Database will be performance and scalability.  Customers will always get the best vertical scalability (aka ‘scale up’) when running SQL Server on their own hardware, since customers can buy hardware that is highly optimized for performance. With SQL Server running in a Windows Azure VM, performance for a single database will be constrained to the largest virtual machine image possible on Windows Azure—which at its introduction will be a VM with 8 virtual CPUs, 14GB of RAM, 16 TB of storage, and 800 MB/s network bandwidth.  Storage will be optimized for performance and configurable by customers.  Customers will also be able to configure and run AlwaysOn Availability Groups (at GA, not for preview release), and optionally get additional performance by using read-only secondaries or other scale out mechanisms such as scalable shared databases, peer-to-peer replication, Distributed Partitioned Views, and data-dependent routing.

With SQL Database, on the other hand, customers do not choose how many CPUs or memory: SQL Database operates across shared resources that do not need to be configured by the customer.  We strive to balance the resource usage of SQL Database so that no one application continuously dominates any resource. However, this means a single SQL Database is in nature limited in its throughput capabilities, and will be automatically throttled if a specific database is pushed beyond certain resource limits.  But via a feature called SQL Database Federation, customers can achieve much greater scalability via native scale-out capabilities.  Federation enables a single logical database to be easily portioned into multiple physical nodes.

This native feature in SQL Database makes scale-out much easier to setup and manage.  For example, with SQL Database, you can quickly partition a database into a few or even hundreds of nodes, with each node adding to the overall capacity of the data tier (note that applications need to be specifically designed to take advantage of this feature).  Partitioning operations are as simple as one line of T-SQL, and the database remains online even during re-partitioning.  More information on SQL Database Federation is available here.

Summary

We hope this blog has helped to introduce some of the key differences and similarities between SQL Server running in a Windows Azure VM (IaaS) and Window Azure SQL Database (PaaS).  The good news is that in the near future, customers will have a choice between these two models, and the two models can be easily mixed and matched for different types of solutions.

Criteria

SQL Server inside Windows Azure VM

Windows Azure SQL Database

Time to Solution    
Migrate Existing Apps

Fast

Moderate

Build New Apps

Moderate

Fast

Cost of Solution

Hardware Administration

None

None

Software Administration (Database & OS)

Manual

None

Machine High Availability

Automated (99.9% Uptime SLA at commercial release)

N/A

Database High Availability

With extra VMs and manual setup via AlwaysOn (at commercial release), DBM; DR via log shipping, transactional replication

Standard Feature (99.9% DB uptime SLA)

Cost

Medium

Low

Scale Model

Scale-Up

X-Large VM
(8 cores, 14GB RAM, up to 16 TB disk space)

Not Supported

Scale-Out

Manual via AlwaysOn read-only secondaries, scalable shared databases, peer-to-peer replication, Distributed Partitioned Views, and data-dependent routing (manual to setup, and applications must be designed for these features)

SQL Database Federation (automated at data tier, with applications designed for Federation)

Control & Customize

OS and VM

Full Control

No Control

SQL Server Database Compatibility, Customization

Full support for SQL Server 2012 box product features including database engine, SSIS, SSAS, SSRS

Large subset of SQL Server 2012 features

Hybrid

Domain Join and Windows Authentication

Yes

Not possible

Data Synchronization via Azure Data Sync

Supported

Supported

Manageability

Resource Governance & Security Level

SQL Instance/VM

Logical DB Server

Tools Support

Existing SQL Server tools such as SSMS, System Center, and SSDT

Existing SQL Server tools such as SSMS, System Center, and SSDT

Manage at Scale Capabilities

Fair

Good

– by Gregory Leake, Director, Product Marketing, SQL Server