Choosing between SQL Server in Windows Azure VM & Windows Azure 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

  Editor's Note: This post was co-written by Madhan Arumugam and Guy Bowerman from the SQL Server Cloud Infrastructure Team. Introduction Windows Azure gives you a choice in where to host your SQL data, with both SQL Server running in a VM (henceforth referred to as SQL/VM), and Windows Azure SQL database (henceforth referred to as SQL DB). SQL/VM and SQL DB come from a single code base, and each offering has its own strengths when it comes to cloud deployment. In this article we will build upon the earlier blog post on SQL Server in Windows Azure Virtual Machine vs. SQL Database and provide a simple decision tree to help choose the best SQL database solution in Windows Azure for your application/service. Windows Azure SQL DB is a Microsoft Platform as a Service (PaaS) offering that has been in production for a few years. SQL Server in Windows Azure Virtual Machine is a Microsoft Infrastructure as a Service (IaaS) offering with an integrated database server instance, available as a set of pre-configured platform images. The Windows Azure Virtual Machine offering is currently in Public Preview. This post is focused on SQL Engine rather than BI and reporting. Future posts will looking into BI/Reporting choices in more detail. Why choose Azure for SQL? Windows Azure offers deep integration across the Microsoft stack, for example between applications such as SharePoint and Active Directory, and also integration of licensing across different deployment models with a unified developer experience. This level of integration is built upon and supported by extensive testing of Microsoft products working together. It also offers a significant level of interoperability. In addition to supporting multiple operating systems including Linux, Windows Azure VMs are based on the standard VHD format, making them easy to import to and export from the Azure environment. This level of integration, standardization and interoperability makes Azure the ideal cloud environment to run SQL Server, providing a seamless, consistent experience across Microsoft assets from traditional on-premises, to private cloud, to public cloud. Ongoing innovations in SQL/VM and SQL DB enable both low overall TCO for management of existing applications/services and an efficient platform for the new breed of cloud applications/services. Additionally the ability to mix and match existing and new applications in the same hosted service, and to integrate with on-premises applications and networks in hybrid models provides tremendous flexibility. Choosing the right database solution The range of Microsoft database products available, across physical on-premises machines, private cloud environments, 3^rd party hosted private cloud environments and public cloud provide a continuum of SQL Server offerings amounting to a cohesive, flexible and open data platform in the cloud. Each offering can be characterized by the level of control you have over the infrastructure, and by the degree of cost efficiency achieved by database level consolidation and automation. There are other dimensions by which these offerings can be characterized, such as compatibility, which we will look at in more detail in the decision tree below. This post will focus on the contrast between SQL/VM and SQL DB, and offers a path to choosing a database service which matches your desired level of cost efficiency and control. Decision tree In this decision tree, the following key application/service specific decision points are examined:

1. New or existing application/service: Migrating existing applications (“lift and shift”) typically requires a higher need to emulate on-premises application behavior than new applications which typically don’t have that need.
2. Application/Service requirements: This decision point checks for any compatibility, networking or security requirements which might require SQL Server in a VM. See SQL Server in Windows Azure Virtual Machine vs. SQL Database for more details, and for a full list of feature compatibility differences between SQL DB and SQL/VM refer to the MSDN article Guidelines and Limitations (Windows Azure SQL Database).
3. Database size: There are size limits by SQL Database editions, and the current maximum size of a single Azure SQL DB is 150 GB. For SQL/VM, the current maximum VM size is ExtraLarge (8 CPU cores, 14 GB RAM and up to 16 TB of disk space). See the MSDN article How to Configure Virtual Machine Sizes for more details on VM sizes.
4. Willingness to (Re)architect/partition for economies of scale: This decision point checks for elasticity and scale-out requirements which can be met using SQL DB scale-out technologies such as Federations in Windows Azure SQL Database. Typically to fully take advantage of the economies of scale and elasticity features offered by SQL DB, a degree of re-architecture is required (see design considerations for scale out in SQL DB here).

As you progress beyond these high-level decision points you may have lower-level design criteria and sizing expectations which can impact the choice of database solution, for example a new application might have a specific feature requirement that is not available in SQL DB. The purpose of this decision tree is to provide an easy to use framework to quickly evaluate your application needs. An important consideration is the SLA provided by an IaaS vs. a PaaS database solution. For example in IaaS, Windows Azure has an SLA which covers all VM storage. When Windows Azure VM becomes generally available there will be a Windows Azure VM-level SLA of 99.95% availability for two or more instances in an availability set. In SQL DB there is a database level availability SLA. For more details refer to https://azure.microsoft.com/en-us/support/legal/sla/. Any operations not covered by an SLA become the customer’s responsibility. An application/service does not have to choose exclusively between IaaS or PaaS. Mixed mode application patterns which include both PaaS and IaaS services (for example, SQL DB and SQL/VM in Azure) represent a real and practical possibility for application development and deployment that allows applications to run components in VMs which require compatibility and control such as BI or applications migrated from on-premises, and also use databases in SQL DB to minimize DBA and database costs. Conclusion Both Windows Azure SQL DB and SQL Server running on Windows Azure virtual machines are valid approaches for satisfying distinct database application needs. In summary choose SQL/VM if:

• You require full compatibility with SQL Server on-premises.
• You want to make minimal changes to an existing application.
• You require infrastructure level Isolation.
• Your application/service is designed for “planned database scale” (AKA scale up).

Choose SQL DB if:

• You are building a net new cloud based application.
• You want to reduce the cost of database management and focus on the application layer.
• You need database level isolation.
• Your app/service is designed for “unplanned & unbounded database scale” (elasticity via scale out).