A database for Umbraco

Posted on September 8, 2017 in umbraco

What database is your Umbraco site running on?

In production, it should be SQL Server (or MySql). If you are running SqlCe (aka SQL Server Compact) in production... well, that is a bad idea. SqlCe has issues, and according to MS it is "in deprecation mode with no new releases planned near future". And it is slow.

For development, things are a little different. SQL Server Express is free and gives developers the whole power of SQL Server, but it can be cumbersome to use. It requires creating databases, managing connection strings, remembering to delete those databases... and so in most cases, when creating an Umbraco site for a quick test, developers fall back to SqlCe.

SqlCe is also the database which is used when restoring an Umbraco Cloud site locally. Because it does not require anything to be installed: we just ship some DLLs with Umbraco. And, it's easy to manage, we can create and use a database all from our code. No need to ask for passwords, nothing.

And yet, it is slow. According to some of our benchmarks, it can be 4-5 times slower than SQL Server.

Enters LocalDb

SQL Server LocalDb is a feature of SQL Server Express targeted to developers. Its installation copies a very minimal set of files necessary to start the SQL Server Database Engine. Once installed it becomes possible to manage per-user server instances and, using special connection strings, to connect to databases whose files live in your project, much like SqlCe files.

As of today, the latest version is SQL Server 2016 Express LocalDB, which can be installed by downloading the Express Installer (about 5MB), then select Download Media in order to download SqlLocalDB.msi (about 44MB), which you can execute to install LocalDb only. Though... you probably want to install Express at that point.

You might also want to install SQL Server Management Studio (SSMS) as it can connect to LocalDb instances and view all attached databases, but this is absolutely not a requirement.

For the cost of a small install, LocalDb provides databases that can be managed pretty much the way we manage SqlCe databases, yet have SQL Server performance level.

SqlLocalDB utility

After LocalDb has been installed, the SqlLocalDB.exe utility becomes available in %PROGRAMFILES%\Microsoft SQL Server\version\Tools\Binn where version is 110, 120, 130... depending on the SQL Server version. This utility can create new instances, start and stop an instance, etc. For example, SqlLocalDB i on my machine reports:

MSSQLLocalDB
Umbraco
UmbracoTests

This means I (my Windows user) have three instances: the default MSSQLLocalDB, one instance used to run Umbraco sites, and one instance used to run Umbraco tests. SSMS can connect to these instances using server names such as (localdb)\Umbraco.

Databases

Databases can be created pretty much exactly the way databases are created with SQL Server, either via SSMS or via SQL code. Such code would look like:

CREATE DATABASE MyDatabase 
    ON (NAME=N'MyDatabase', FILENAME='\path\to\myDatabase.mdf')
    LOG ON (NAME=N'MyDatabase_log', FILENAME='\path\to\myDatabase_log.ldf')

This creates an attached database named "MyDatabase". The interesting part here is that \path\to can point to some place in your own project. An Umbraco site would therefore set FILENAME to Map("~/App_Data/umbraco.mdf") and end up with an umbraco.mdf file right in App_Data.

The connection string to the database would then be:

Server=(localdb)\Umbraco;Database=MyDatabase;Integrated Security=True;

And that is all. Still, the annoying part is that the instances knows about the database name. What if another Umbraco site on the same machine tries to create its own database? The files would not be in the same place, but still LocalDb would complain because of a name collision.

The solution is to detach the named database (it will not be visible in SSMS anymore):

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC sp_detach_db @dbname='MyDatabase'

And to use a slightly different connection string:

Server=(localdb)\Umbraco;AttachDbFileName=\path\to\myDatabase.mdf;Integrated Security=True;

LocalDb then auto-attaches the database using a unique name derived from the MDF file path (basically, some characters are removed from the name, and then everything is uppercased, so here it would be "\PATH\TO\MYDATABASE.MDF"). The database becomes visible again in SSMS, under than name.

Running

Running a LocalDb database is, in some ways, very similar to running a SqlCe database. If you try to delete the files... it probably will fail, until the application which is using them is stopped. You can backup the files, moves them around, and then restart the application.

And yet, once the application is running, you get the (almost) full power of SQL Server (well, some high-level features are not available of course, such as replication...).

If the files are removed while the database is attached to an instance, you will not be able to connect to the database (of course) but you will be able to drop it. LocalDb may throw some errors but it will drop the database.

We now have prototypes of

• Umbraco tests fully running on LocalDb instead of SqlCe
• Umbraco Cloud restoring sites to LocalDb
• Umbraco installing on LocalDb

In all three cases, everything runs waaaaaay faster!

It still needs a bit of polish, but you can expect it to be released in a near future (v7-time, not v8-time).

Appendix: IIS

Then I tried running a LocalDb-based Umbraco site under IIS, and things turned ugly. Basically, getting "network-related or instance-specific" errors while establishing a connection to the database.

See this StackOverflow question, which points to two very interesting MSDN articles: LocalDb with Full IIS, part 1 and part2. It turns out that a LocalDb instance is, well... local. To the user that is executing the current process. So any LocalDb instance I (my own user) can create will not be visible to the user that is executing IIS. And that user might not even have all the required permissions to detect LocalDb in Program Files and would think that LocalDb is not available.

Also note that MS LocalDb notes recommends to run LocalDb using a normal windows account as the owner, as built-in accounts can have manageability issues (due to Windows file system redirection).

After trying various ideas, I ended up running the site under a dedicated application pool running with my own user identity (this is approach 1 in part2, and configured to load my user profile and set its environmnent (this is loading user profile in part 1. This obviously has some security implications, but in most cases will be OK for development and tests.