Windows Azure SQL Database VS SQL Server in Windows Azure Virtual Machines VS Alternatives

In this blog post, I will be looking at the different flavors of SQL Database on Windows Azure and their alternatives.

Beware, you may be surprised by some of the numbers in this blog post!

Typical Scenario – Using SQL Database

Lets take a look at a typical web solution. It contains three Extra Small (A0) Web Roles and a 40GB Database.

Definition of SQL Database

Windows Azure SQL Database is a feature-rich, fully managed relational database service that offers a highly productive experience, incorporates proven SQL Server technology, and delivers business-class capabilities.

SQL Database allows customers to scale business apps for burst and global reach by removing the high costs of building an infrastructure which would accommodate occasional peak loads. Customers can also remove the security risks and hassles associated with hosting public-facing apps & websites from within a datacenter by quickly & cost-effectively building websites and mobile & social apps directly on SQL Database. Learn more about business analytics cloud services based on SQL Server.

Yearly Operational Cost

The approximate annual operation cost of this solution would be of about 1943.28$. This price includes 100GB of monthly bandwidth.

Pros & Cons

• It’s highly available
• It’s data is redundant (3 copies at all times)
• It’s response times are unpredictable
• It doesn’t support a high volume of concurrent queries
• It can be federated (scaled out)
• It’s constantly updated by Microsoft
• It’s maintained by Microsoft

Alternative 1 – Using Federations in Windows Azure SQL Database

Lets take a look at a web solution what employs Federations in order to provide more through put for the end-users. It contains three Extra Small (A0) Web Roles, a 1GB master Database and four 10GB federated Databases.

Definition of Federations in Windows Azure SQL Database

Federations in SQL Database are a way to achieve greater scalability and performance from the database tier of your application through horizontal partitioning. One or more tables within a database are split by row and portioned across multiple databases (Federation members). This type of horizontal partitioning is often referred to as ‘sharding’. The primary scenarios in which this is useful are where you need to achieve scale, performance, or to manage capacity.

SQL Database can deliver scale, performance, and additional capacity through federation, and can do so dynamically with no downtime; client applications can continue accessing data during repartitioning operations with no interruption in service.

Yearly Operational Cost

The approximate annual operation cost of this solution would be of about 2,998.20$. This price includes 100GB of monthly bandwidth.

Pros & Cons

• It’s highly availability
• It’s data is redundant (3 copies at all times)
• It can handle more concurrent queries that a single database, but you still need to be cautious of the number of concurrent requests.
• It’s quite complex to backup
• It’s response time is unpredictable
• It’s constantly updated by Microsoft
• It’s maintained by Microsoft

Alternative 2 – Using SQL Server in Windows Azure Virtual Machines

Lets take a look at a web solution what employs a Virtual Machine to host an SQL Server in order to provide more through put for the end-users. It contains three Extra Small (A0) Web Roles, a Medium (A2) Virtual Machine
with a 50GB VHD.

Definition of SQL Server in Windows Azure Virtual Machines

For applications that need full SQL Server functionality, Virtual Machines is an ideal solution. You will find SQL Server 2012 and SQL Server 2008 R2 images offered, including standard, web and enterprise editions. If you have an existing SQL Server license with software assurance, as an additional benefit you can move your existing license to Windows Azure and simply pay for compute and storage. Running SQL Server in Virtual Machines is a great solution for the following scenarios:

• Developing and testing new SQL Server applications quickly – you do not need to wait weeks for on-premise provisioning of hardware, just grab a the right SQL Server image from the image gallery and off you go. You can choose to deploy in production or back on-premises with little effort.
• Hosting your existing Tier 2 and Tier 3 SQL Server applications – with many VM sizes to choose from and with full SQL Server compatibility you can easily move your existing on-premises SQL Server applications and gain the efficiencies of cloud computing.
• Backing up and Restoring your On-Premises databases – backup your on-premises database to Windows Azure blob storage and have the ability to restore the database to a Windows Azure Virtual Machine in case of on-premises disaster recovery.
• Extending On-Premises Applications – create hybrid applications that utilize on-premise assets as well as Windows Azure Virtual Machines for greater efficiency and global reach.
• Create Multi-Tiered Cloud Applications – create a multi-tiered application that utilizes the unique scale out capabilities of SQL Database service for the application tier and leverages the full SQL Server compatibility in Windows Azure Virtual Machines for the database tier.

Yearly Operational Cost

The approximate annual operation cost of this solution using SQL Server Web Edition would be 2,738.28$. Using SQL Server Standard Edition would cost 7,246.92$ and using SQL Server Enterprise Edition would cost 21,085.32$.These prices include 100GB of monthly bandwidth..

Pros & Cons

• It’s a full SQL server
• It’s resources aren’t shared with other Windows Azure tenants
• You configure the maximum number of concurrent queries
• You must maintain and patch the server yourself
• You don’t benefit from high availability.
• You must build and maintain a high availability infrastructure yourself
• You will probably need an I.T professional to help you manage the Virtual Machine

Learn more about SQL Server in Windows Azure Virtual Machines

• Windows Azure Virtual Machines overview
• Optimizing SQL Server in Windows Azure Virtual Machines

Alternative 3 – Using Premium for SQL Database

Lets take a look at a web solution what employs Premium for SQL Database in order to provide predictable response times for the end-users. It contains three Extra Small (A0) Web Roles and a 40GB Database.

Definition of Premium for SQL Database

Windows Azure SQL Database, with this preview of the forthcoming Premium database offering, will help deliver greater performance for cloud applications by reserving a fixed amount of capacity for a database including its built-in secondary replicas. Microsoft will continue to add business-class functionality to Premium databases over time, to further support higher end application requirements, enabling you to make a bet on a business-optimized cloud platform.

Yearly Operational Cost

The approximate annual operation cost of this solution using a P1 SQL Database(Premium) would be 6298.08$. If a P2 SQL Database(Premium) is used instead it would cost 11,878.08$. These prices are based on preview pricing for SQL Database(Premium) and includes 100GB of monthly bandwidth.

Pros & Cons

• It has dedicated resources
• It’s response time is predictable
• It’s highly available
• It’s data is redundant (3 copies at all times)
• It’s maintained by Microsoft
• It can be federated (scaled out)
• It’s constantly updated by Microsoft

Alternative 4 – Using SQL Database & Windows Azure Storage

Lets take a look at a web solution what employs a mix of SQL Database and Windows Azure Storage in order to provide acceptable response times for the end-users. It contains two Extra Small (A0) Web Roles, an Extra Small (A0) Worker Role, a 5GB  SQL Database and 50GB of data stored across various Windows Azure Storage Services.

This scenario has a radically different approach to providing end-users with data. The data is processed by a Worker Role who stores the data in Windows Azure Blob Storage. Then a subset of the data is inserted into Windows Azure SQL Database to allow end-users to search.

Data Flow

The data is pushed into the system through an import mechanism, which could be a Windows Azure Queue or a Windows Azure Blob Storage Container. It’s then processed by Cloud Services who prepare and pre-calculate data for the end-users. Once the data is in the desired format, it is then stored in the appropriate storage service. Documents like datasets for charts or user profiles are stored in Windows Azure Blob Storage Service. Events, logs and index data is stored in the Windows Azure Table Storage Service. Finally, referential data that is necessary for reporting and search are stored in SQL Database.

The primary goal here is to keep the database as small and as optimized are possible. This will ensure that queries execute as fast as possible. Consequently, SQL Database will be able to serve more requests.

Because the data is stored in Windows Azure Blob Storage, it can be versioned and distributed using the Windows Azure CDN (Content Delivery Network). Therefore, most of the heavy lifting is done by the Windows Azure Storage Services (CDN, Blob Storage Service) who stream documents directly to the end-user’s. Consequently, we are able to reduce the number of Web Roles necessary to achieve the performance targets set by the project owner. 

Yearly Operational Cost

The annual operation cost of this solution would be of about 1,161.24$. This price includes 100GB of monthly bandwidth and 100 Million transactions (storage + CDN) per month.

Pros & Cons

• It’s data is redundant (3 copies at all times)
• It’s highly available
• It  can handle up to 20,000 Blob Storage Service transactions per second
• It  can handle up to 20,000 Table Storage Service transactions per second
• It can read blob data from the Windows Azure CDN (Content Delivery Network)
• It can respond to more end-user requests because most of the streaming is done by Windows Azure Storage
• It’s maintained by Microsoft
• It’s constantly updated by Microsoft

Take Away

The numbers in this post speak for themselves. It’s important to take the application’s performance targets into consideration when we decide on which flavor of SQL Database to use for our solutions. Furthermore, each flavor has pros and cons, which shouldn’t be overlooked. Be sure to choose the right combination of services to satisfy your needs, SQL Database alone might not be the most economical solution.