Cloud Savings: Bundle Up Your Databases

I run a few different side projects or demo sites in Azure. Each site generally costs about $15/month to host. Thats about $10 for the Shared Azure Website (D1) and about $5 for the smallest Basic Azure SQL Database (B).

I wanted to get that price down a bit more. There’s not much I can do about the $10 per website (App-Service as its called these days). If I want a custom domain, and not *.azurewebsites.net, the D1 is the cheapest option. After I get up to about 7 or 8 sites, it then makes sense to switch to the Standard service tier (S1), where I can cram all the sites into a single underlying VM for about $72/month. For each new site after that, the cost per-site is less than the shared D1 plan.

The databases are a different story: they are priced per database and have different tiers for various performance needs. I don’t need performance, and the Basic (B) tier is plenty for my hobby projects.

But I’d like to not have to spend $5/month for each hobby project.

Luckily with some creative coding I was able to cram multiple site’s data into the same B database. The trick is to use SQL Server Schemas.

Entity Framework Considerations

With EntityFramework, the DBContext wants to check version and migration history, which it stores in a table. Trying to mix multiple applications into the same schmea could be a recipe for pain and suffering as each application looks at the same migration history table.

But with a schema for each application, you can keep things nice and separate.

App1DbContext.cs
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema("app1");
}

The app1 value could come from appSettings or some other configuration tool if you wanted.

Raw SQL Considerations

You can set a DEFAULT_SCHEMA for each database user on the database.

So for example, Application 1 could have its own database login, like wwwapp1, which is configured to use the default schema of app1.

SetDefaultSchema.sql
ALTER USER wwwapp1 WITH DEFAULT_SCHEMA = app1;

If you’re using raw SQL and the DEFAULT_SCHEMA trick is not possible for you, you’ll have to use string interpolation to build your SQL. Just make sure the schema name comes from a trusted source so that its not vulnerable to SQL Injection.