Implementing database per tenant strategy on ASP.NET Core

Let’s continue with multi-tenancy in ASP.NET Core web applications and focus on solutions where every tenant has its own database. It’s not only about database – there can be more services where every tenant has its own instance. It makes solution provided here easily extendable to also other services besides SQL Server or any other relational database.

Previous work

Before getting to solution I suggest to skim through my previous posts covering some aspects of multi-tenancy in ASP.NET Core web applications:

NB! The code in this post builds on code of posts referred above.

Moving database connection string to tenant configuration

The problem here is how to decide dynamically which connection string to use and how to make connection strings available the way that web application configuration is not changed. The latter is actually solved in my previous multi-tenancy post Implementing tenant providers on ASP.NET Core where I proposed BlobStorageTenantProvider.

What’s different from previous post is the fact that when tenants use different databases there is no need for tenant ID-s in those databases. Applications that doesn’t support soft deletes can go with classic simple data context when using solution proposed in this post.

New Tenant class has one additional property – DatabaseConnectionString like shown here.


public class Tenant
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Host { get; set; }
    public string DatabaseConnectionString { get; set; }
}

Tenants configuration file held on Azure blob storage will look like this.


[
  {
    "Id": 2,
    "Name": "Local host",
    "Host": "localhost:30172",
    "DatabaseConnectionString": "<connection string 1>"
  },
  {
    "Id": 3,
    "Name": "Customer X",
    "Host": "localhost:3331",
    "DatabaseConnectionString": "<connection string 2>"
  },
  {
    "Id": 4,
    "Name": "Customer Y",
    "Host": "localhost:33111",
    "DatabaseConnectionString": "<connection string 3>"
  }
]

Returning tenant instead of tenant ID

As Tenant may define even more settings then it’s good idea to start dealing with Tenant class instead of tenant ID. It brings change to my ITenantProvider interface and BlobStorageTenantProvider class.


public class BlobStorageTenantProvider : ITenantProvider
{
    private static IList<Tenant> _tenants;

    private Tenant _tenant;

    public BlobStorageTenantProvider(IHttpContextAccessor accessor, IConfiguration conf)
    {
        if(_tenants == null)
        {
            LoadTenants(conf["StorageConnectionString"], conf["TenantsContainerName"], conf["TenantsBlobName"]);
        }

        var host = accessor.HttpContext.Request.Host.Value;
        var tenant = _tenants.FirstOrDefault(t => t.Host.ToLower() == host.ToLower());
        if(tenant != null)
        {
            _tenant = tenant;
        }
    }

    private void LoadTenants(string connStr, string containerName, string blobName)
    {
        var storageAccount = CloudStorageAccount.Parse(connStr);
        var blobClient = storageAccount.CreateCloudBlobClient();
        var container = blobClient.GetContainerReference(containerName);
        var blob = container.GetBlobReference(blobName);

        blob.FetchAttributesAsync().GetAwaiter().GetResult();

        var fileBytes = new byte[blob.Properties.Length];

        using (var stream = blob.OpenReadAsync().GetAwaiter().GetResult())
        using (var textReader = new StreamReader(stream))
        using (var reader = new JsonTextReader(textReader))
        {
            _tenants = JsonSerializer.Create().Deserialize<List<Tenant>>(reader);
        }
    }

    public Tenant GetTenant()
    {
        return _tenant;
    }
}

Configuring data context dynamically

I suppose here that multi-tenant application doesn’t deal with soft deletes. Default data context for mult-tenant application must be changed now to use correct connection string.


public class PlaylistContext : DbContext
{
    private readonly Tenant _tenant;

    public DbSet<Playlist> Playlists { get; set; }
    public DbSet<Song> Songs { get; set; }

    public PlaylistContext(DbContextOptions<PlaylistContext> options,
                            ITenantProvider tenantProvider)
        : base(options)
    {
        _tenant = tenantProvider.GetTenant();
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(_tenant.DatabaseConnectionString);

        base.OnConfiguring(optionsBuilder);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Playlist>().HasKey(e => e.Id);
        modelBuilder.Entity<Song>().HasKey(e => e.Id);

        base.OnModelCreating(modelBuilder);
    }
}

When looking at code it is easy to see that there’s not much code but it results in bold and flexible model how to support database per tenant strategy.

Wrapping up

Dependency injection model in ASP.NET Core and flexibility of Entity Framework Core make it very easy to support also more complex scenarios in ASP.NET Core web applications. This blog post focused on one aspect of multi-tenancy – how to support database per tenant data storing strategy. This model can also be extended to more settings if needed.



See also

5 thoughts on “Implementing database per tenant strategy on ASP.NET Core

  • […] Implementing database per tenant strategy on ASP.NET Core & Handling missing tenants in ASP.NET Core – Gunnar Peipman […]

  • Raul says:

    Great post!! And very timely 🙂

    I was thinking about a similar concept to use with Azure SQL Elastic Pools where every tenant will get its own database.

    There is only one thing that worries me. If the WebAPI server(s) has to serve multiple tenants (say 100) it will need 100 different connection strings. This will create 100 ADO.NET pools (I think as default ADO.NET creates 30 connections for every different connection string). This means 3,000 connections would be created by ADO.NET for all the pools. What will be the impact for the server? Any ideas about how to cope with all these connections?

  • Gunnar says:

    By design connection pool should not kill the machine. If it’s optimally full then it’s full and this is it. But still connection pool gives you some win in performance.

    It’s possible to use multiple app services accounts on Azure and rework my solution the way that application on each account works only with given number of customers. I would solve it by introducing customer groups for applications. Each service account has its own customer group and it serves only tenants in this group. It should be possible to assign group to as cloud service setting in Azure portal.

    I think it makes deployment a little harder but still remains as lightweight solution for big number of tenants. Don’t forget that each tenant has its own host header and this is configured in some DNS server or service. So, it takes also some orchestration effort by service provider.

  • Raul says:

    Thank you Gunnar. This makes a lot of sense!

    I need to support multiple server versions at the same time (the tenants cannot be updated automatically to the newest version). This means that I would need to introduce something like your groups for it. Now I can imagine extending this system to not just route to the server with the right version, but for the right customer group.

    The original idea was to redirect the users to the right server(s) when they login depending on information about their version. Now I could extend it to redirect to a customer group server. Using something like your tenant class would make it possible. Thank you again!

Leave a Reply

Your email address will not be published. Required fields are marked *