Deploying Kentico to Microsoft Azure – Know your database options

You can check out my first blog in this series here.

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 Database
SQL 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
• Can handle as large a database as hardware will allow
• Can fully customize the server / environment
• Can clone VM image for replication / offsite storage

Cons

• Not as scalable as SQL Database
• Hardware scaling is a manual process
• Replication requires SQL Cluster setup / configuration
• Limited replication options
• Increased IT costs for administration
• Developers will be responsible for patching / updating OS / SQL Server
• Backing up to Azure Storage requires additional programming

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.

SQL Database

• Applications using standard Kentico database structures and components
• 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 3^rd 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 3^rd 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.

Good luck!