Deploying Kentico to Microsoft Azure – Know your database options

In the first blog in this series, I covered the web hosting options available when deploying your Kentico applications to Azure. While that article focused on the website side of things, this blog will cover deploying your Kentico database to Azure and how to leverage as much of the platform as possible.

A little Azure history

Microsoft Azure has come a long way in the database options available to developers. Initially, SQL Database (formerly Azure Database… formerly SQL Azure) was offered as a scalable, redundant platform for developers. While this initial product offering was very usable, many companies could not simply migrate their data to this SaaS (Software as a Service) option. Specifically, the original size constraints of 50 GB and cost made it a difficult sell for some developers, as well as the limited amount of options for maintaining and backing up the data.

Over the next five years, Microsoft continued to increase the maximum database size (now 500 GB) to keep up with demand, but still found that it lacked some administrative features that companies where accustomed to with traditional SQL hosting environments (maintenance plans, automated backups, etc.). To address this, Microsoft outfitted the IaaS (Infrastructure as a Service) platform with pre-configured images for SQL Server and Windows Server. With this new option, developers could configure and administer their database to the full extent, opening the doors for many applications to be moved to the cloud.

Database Hosting Options

Today, Microsoft continues to offer these two options for hosting your data, but have enhanced both with many features needed for enterprise applications. Each of these platforms has significant benefits for multiple scenarios. Next, I’ll break down these options and highlight the features offered by each one.

SQL DatabaseSQL Databases are still the best bet for most applications. Evolved from the SQL Azure platform, SQL Databases have a ton of scalability, redundancy, and features for a very low cost. This platform is a relational database architecture that offers point-in-time-restores, automated backups to Azure storage, and a redundant, self-healing infrastructure to host the data. Companies can choose to replicate their data to regional or global data centers with a click and the entire platform has a 99.9% SLA-backed uptime. The backend architecture is distributed across thousands of servers and developers have the option to choose from different levels of guaranteed resources when deploying their applications.

SQL Databases aren’t a full version of SQL Server but rather a relational database that is pretty close. Because of the architecture, certain aspects of SQL Server aren’t available (access to the master database, certain keywords, etc.) which may impact on certain functions and procedures for companies. I can say that in 5+ years of working with Azure, I have come across a single instance where this was an issue (using the ENCRYPT function within a stored procedure).

You can find a full list of the un-supported features and keywords here.

IaaS

Akin to traditional hosting, utilizing IaaS for hosting a database involves managing a cloud-hosted virtual machine with SQL Server installed. In this scenario, developers have complete control over the server, allowing them to configure all tasks, functions, and other custom code, as needed. The full SQL Server feature set is available and any customizations can be made at any time. From a hardware standpoint, developers can choose from a number of pre-configured images of varying sizes to accommodate nearly any programming needs.

One of the biggest benefits of IaaS is the ability to host multiple databases on the server. Just like an on-premise solution, IaaS can provide a server to host as many databases that will fit and provide a central location for all management. Additionally, DBAs can just go crazy with the server setup and jobs, which many companies require to ensure their applications are running optimally.

While IaaS is very usable, it comes at cost in the form of additional administration and maintenance. All updates to the server, including OS and SQL Server, are the responsibility of the developer. In addition, the replication capabilities of the database are much more restricted as the database is running on a single VM within the cloud, as are the configuration options for the underlying system.

Database Hosting Pros & Cons

Every database hosting option has pros and cons that a business can leverage to get optimal performance out of their backend data. Below, I will detail the benefits and restraints of each option.

SQL Database
Pros

Extremely scalable

Minimal administration required

Point-In-Time restores up to 30 days

Several pricing / performance tiers

No hardware costs

Fault-tolerant

Geo-replication options

Backups to Azure Storage are automated

Cons

Reduced SQL functionality from SQL Server

Reduced administration options

Cannot run scheduled tasks / jobs

Size limitations (currently 500 GB)

Large databases can be very costly

Large / complex database structures may require sharding the data

Existing databases will have to be migrated, possibly involving data manipulation

Each database is a separate, billable entity

Migrating large, existing databases can be time-consuming

IaaS

Pros

Most like “traditional” hosting

Nearly any SQL Server database can be migrated

Offers full administration capabilities

Scheduled tasks / jobs

Can place as many databases as the hardware can handle for a single cost

So, which one is right for you?

So now that you know the options, the next question is which one is right for your application? As with everything else, it depends. Each application is different and requires specific functionality that SQL Database may not offer. On the flip side, IaaS may be too much of an administration overhead for companies to realize significant savings from the cloud.

Below are my recommendations for the type of applications you should leverage.

Applications that do not require frequent database tuning / administration

Applications that require point-in-time restores

New applications that can be initially created in SQL Database

Smaller databases (<5-10 GB) that can be migrated easily to SQL Database

Smaller databases that will not be extremely expensive to host (<50 GB)

Companies that only have a few databases to host in the cloud

SaaS applications

Companies with limited IT staff for database administration

IaaS

Large, complex database structures

Very large databases (+50 GB) where hosting costs become a factor

Applications that require regular database tuning / jobs

Databases that require specific OS / SQL Server configurations

Databases that rely on 3rd party services / systems to function

Companies with several databases requiring hosting in the cloud

Companies with IT staff available to maintain the OS / SQL Server installations

Final Verdict

As in the first article in this series, I wanted to provide a checklist for selecting the database option for your project. Every project is different, however, this is the process I follow when deploying my solution to Azure:

Start with SQL Database

Is your database larger than 50 GB?

Do you have custom server configurations that have to be completed for your application to function?

Do you have 3rd party services /applications that have to be installed on the SQL Server?

Do you need to schedule custom jobs / tasks?

Do you have T-SQL code using any keywords / functions not supported by SQL Database?

Does your IT department require that they have full access to the server?

Do you have several (10+) databases that all need to be hosted in Azure?

If the answer to any of those are “yes”, you may want consider hosting your database(s) in IaaS. While many of the concerns are handled by both options, IaaS will offer the most in the way of configuration and administration. SQL Database is a fantastic option for smaller, easy to maintain applications that do not require a lot of customization. Chances are, your overall hosting needs will determine which option is the best for your applications.

So there you have it! Hopefully this article helps you plan your deployments to Azure and understand your database options. Combined with the Web Hosting Options article, this series should certainly put you on a path to hosting your applications in Microsoft’s cloud in the best manner possible, while removing a good bit of headache and administration for you. I’ll conclude the series in a few weeks with a look at some of the supporting systems in Azure and how you can leverage them within your projects.

Hello. I am a Technical Evangelist here at Kentico and will be helping the technical community by providing guidance and best practices for all areas of the product. I might also do some karaoke. We'll see how it the night goes...