Database infrastructure design considerations for Umbraco

There are lots of options for setting up and running the database behind Umbraco, and in this post we’ll look at some of the technologies Umbraco supports and the design constraints you should consider.

Umbraco is a Microsoft stack CMS so it stands to reason that its database of choice is SQL Server.  It is possible to run Umbraco 7 on MySQL, but at Orcare we’ve never done this, and to be honest I’m a little skeptical – I never like running tiny minority configurations, bugs are more likely and often harder to resolve.

You can run Umbraco on any version of SQL server 2008 or newer and have the choice of the file-based SQL CE database all the way up to enterprise edition.  

Its worth noting that on most Umbraco sites the database load is particularly light, as most content is cached in an XML file and the database is typically accessed when editors publish changes which cause the cache to update.

With these performance considerations in mind, and when the size of our average sites never exceeds 10GB we typically host live Umbraco sites using SQL Server Express Edition.  In development, we do sometimes use SQL CE but we more typically have a shared SQL Server Express database for development.

On sites where resilience and reliability is important - and where load balancing of the web tier is used - we will use SQL Server Standard Edition to take advantage of SQL Servers mirroring technology.

We are big users of Amazon Web Services for our hosting platform, and when we require mirroring the quickest way to get this setup is using Amazon Relational Database Service, which offers a mirroring option.  We can have a mirrored database up and running in under an hour.

Until recently we were using RDS by default for all our database hosting.  It provides such a quick and easy setup.  However, RDS presents two issues that have lead us to change our design standards in favour of setting up and configuring our own database server.  Firstly, creating clones of individual databases is difficult because there is no way of generating .bak or .bacpac files from an RDS database – although you can easily clone an entire instance. There are work around for this, but the killer problem for us is that there is no way to scale the storage on an RDS SQL server instance.  The only solution is to setup a new instance with larger capacity and migrate the content.  

Switching away from RDS back to SQL installed on our own EC2 instances doesn’t come for nothing, it does require additional work in configuring the database which Amazon does by default in RDS.  For example, setting up automated backups requires manual intervention outside of RDS, setting up mandatory SSL/TLS based encryption of database connections is extra work. We also had an issue when moving away from RDS that a piece of code in one of our API’s failed because of issues with the Distributed Transaction Coordinator - we were able to fix it, but it had worked a treat on RDS.

That being said the issue of scaling has caused us so many headaches that for the time being we’ll be using RDS only for mirrored databases.  If you’d like help and advice on how to manage storage for your Umbraco site then contact us.