Database per tenant infrastructure

In the first post in this series, we’re going to talk about the infrastructure. We need some database server to host each tenant’s database and the shared database that contains information on all the tenants (like which database belongs to whom). We also need infrastructure to host the API on and off course, need something for configuration and secret management.

TL;DR: All templates and the pipeline can be found in my repository.


Probably the most crucial piece of infrastructure in this demo is the database service. We need something that allows us to host all the individual databases for each tenant. We want to quickly spin up a new one whenever a new tenant joins, and it needs to be cost-efficient. Luckily Azure has SQL Elastic Pools. Within such a pool, we can host multiple databases. We pick a performance plan for the entire pool instead of per individual database. This way, we don’t have to optimize for one database’s peak performance but can share that amongst multiple. Setting a resource limit per database will ensure that a single database can never consume all resources. We also pay for the pool’s usage, not per database, making it much more cost-efficient. We can have multiple pools per SQL server, and each pool can host up to 500 databases.

Configuration and secrets

To store configuration and secrets, we’re going to use a combination of App Configuration and KeyVault. I already wrote a blog on that, so I won’t repeat too much here. The only thing to add is that we will use KeyVault to store a unique username and password per database. That allows higher security compared to having one account that can read from every database.

Other infrastructure

Besides the infrastructure mentioned above, we need a few additional things:

  • Azure App Service and App Service Plan
  • Storage account
  • Application Insights

The ARM Templates and pipeline

We will be deploying all the initial infrastructure using ARM templates and a pipeline in Azure DevOps. What’s not included in this infrastructure are the databases for the tenants. We will, described in a separate post, deploy them on demand initiated by our API. All the ARM-templates and the complete pipeline (in yaml) can be found in my repository. A few interesting things:

Publish templates

The first stage in the pipeline will publish the templates so that we can use them in a deployment to a specific environment later on. It does that by creating a folder, using the build Id as the name, on an Azure Blog Storage Container, and then copying the templates to that.

Current access policies on KeyVault

There is currently a bug in the deployment of KeyVaults that will remove all existing access policies when you do not specify them explicitly. I therefore run a script in the pipeline to fetch the current ones and push that into the template using a parameter. See ‘LoadCurrentKeyVaultAccessPolicies.ps1’ in the scripts folder.

Folder structure

Within the templates folder, you will find three subfolders.

  • Nested
    • This folder contains all the building blocks we need in our infrastructure. All these files do just one particular thing. We have, for example, a template that creates a KeyVault, one that creates the App Service, and one that creates a SQL server
  • Composing
    • This is where we find templates that use our building blocks to come to meaningful infrastructure. You will never find a template in this folder that creates anything itself; that work will always be deferred to a template in the nested folder.
  • scripts
    • Folder used for additional PowerShell or Azure CLI scripts for stuff that we cannot do with ARM-templates