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.
- Global query filters in Entity Framework Core 2.0
- Implementing tenant providers on ASP.NET Core
- Implementing database per tenant strategy on ASP.NET Core
- Handling missing tenants in ASP.NET Core
- Unit testing multi-tenant database provider
- Defensive database context for multi-tenant ASP.NET Core applications
- Tenant-based dependency injection in multi-tenant ASP.NET Core applications
- Using configurable composite command in multi-tenant ASP.NET Core application
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.
Pingback:The Morning Brew - Chris Alcock » The Morning Brew #2412
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?
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.
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!
Pingback:在ASP.NET Core中如何支持每个租户数据存储策略的数据库 | Codeba
Great post Gunnar, I just did a walk-through of the entire series and I am having problem creating migration for the tenant based dbcontext. Can you please give some advice on this?.
Thank you
Hi,
How would you solve it so that a user can belong to several different tenants?
I’ve thought about creating a Users, Tenants and a UsersTenants table in the main context, so that we can lookup all the tenants one can access from there, and then have a default one it chooses automatically. And then in turn let the user pick from a dropdown list in the application to change the tenant (and always send a x-tenantId in a header with every request).
A very big problem with this is that the users recide outside of the tenants database. So when for instance a user wants to save CreatedBy or ModifiedBy and any other action based on users, it would have to store and ID located in a different database, that’s not good at all (securitywise a nightmare also).
I’ve thought about solutions to this but haven’t came up with any good ones, maybe a sync so that the users that are created in the main catalog is also added to the specific user databases, but what happens if a customer admin wants to create a new user, we would have to create it in two different places.
Any thoughts on how this can be solved?
Hi,
I doesn’t matter much for your scenario if it is database per tenant or one database per all tenants strategy. You have need to add SSO support where users have separate database and there they are mapped to allowed tenants. This is first thing that comes to my mind.
Hi there,
The ASP.NET Identity Core folks had this to say about multi-tenancy via the UserManager and RoleManager classes:
“We don’t support this, Identity was not designed for multi-tenancy.”
People have tried, such as Scott Brady (add a TenantId to the database, use same database for all tenant auth operations).
Do you have any opinion on this? Have you tried it? Implementing a custom UserManager looks like a pain, and we don’t have time to do that. We may have time to do Scott’s solution, but I was wondering if there was another.
Hi,
I have tried it with ASP.NET Core Identity and had no issues. I didn’t faced any problems during my experiments as I changed EF Core model caching the way it uses different key for every tenant.
Do you have more information about what pieces of UserManager and RoleManager classes may run into trouble with multi-tenancy?
Well interestingly enough it tries to sign me into the right database, but on application startup when I am trying to seed the databases for all the tenants, it uses the default context.
E.g., in my AccountController, SignInManager.UserManager.Store.Context has the proper value in its Tenant field, at runtime, when I’m hitting the URL for that tenant. But on startup, if I try to run migrations on all the available tenants, UserManager from IServiceScope only has the original, default context.
So it’s not broken but short of a script to populate the AspNetUsers and AspNetRoles (etc.) tables, I don’t see how I’ll deal with this programatically.
If you want to look the code is here:
https://github.com/chaim1221/Machete/blob/feature/multi-tenancy-sqlserver/Machete.Web/StartupConfiguration.cs#L40
(UserManager.Scope.Context does _not_ get proper Tenant)
Compare:
https://github.com/chaim1221/Machete/blob/feature/multi-tenancy-sqlserver/Machete.Web/Controllers/AccountController.cs#L53
(All fields have the proper context)
Basically I feel like I’d need to inject the tenants’ databases into the UserManager, which is of course impossible, or at least change what’s in the IServiceScope object, which is a chicken-and-egg problem.
Okay, you are trying to go with migrations. For upgrades I’m using Visual Studio database projects and
SQL Server Management Studio. Just want to keep everything under my strict control.
If you are in Startup class then it’s not so easy to just go through all possible tenants and make EF Core to return correct instances through dependency injection. My solution has special cache key factory for models so EF Core doesn’t get confused on serving models from cache. Example is here:
https://github.com/gpeipman/AspNetCoreMultitenant/blob/master/AspNetCoreMultitenant/AspNetCoreMultitenant.Web/Data/DynamicModelCacheKeyFactory.cs Here is how database context is configured to use custom cache key factory: https://github.com/gpeipman/AspNetCoreMultitenant/blob/master/AspNetCoreMultitenant/AspNetCoreMultitenant.Web/Data/ApplicationDbContext.cs
One dirty trick that comes to my mind is building kind of traversal mode to tenant provider. You need some artifacts in static scope of tenant provider:
– property for index of current tenant
– tenants count
– property to switch traversal mode on or off
When traversal mode is on then tenant provider is always returning the tenant with given index. You can go through all tenants using for loop.
for(var i = 0; i < tenantProvider.TenantsCount; i++)
{
tenantProvider.CurrentTenantIndex = i;
// do here stuff with EF Core
}
This is just an idea that came to my mind after first early morning coffee :) Although I don't like this approach much it is still clean considering all kind of bad hacks. It is hack but at least it doesn't need any changes to Identity code.
P.S. The solution I linked here has migrations but don't get confused. These migrations were for demo where tenants are in same database and all tables have tenant ID to make it easier to detect cross-tenant operations before data is modified.
Thanks for the reply! I have implemented the TenantId part (that’s in the articles) of course, but it looks like you are overriding the cache in your override of the OnConfiguring method, if I’m understanding you correctly. I have an override of OnConfiguring but I haven’t touched the cache. Although I wonder if it’s that the UserManager and RoleManager are using the cache, or if it’s something else, because IServiceProvider doesn’t seem to have any problems switching the context based on tenant. Consider:
https://github.com/chaim1221/Machete/blob/feature/multi-tenancy-sqlserver/Machete.Web/StartupConfiguration.cs#L41
and a few lines below…
https://github.com/chaim1221/Machete/blob/feature/multi-tenancy-sqlserver/Machete.Web/StartupConfiguration.cs#L60
The migrations are properly applied and the schema is seeded to all of the tenant DBs. But using the same pattern with UserManager (which I had to separate out, because everything in there runs as a Task), and replacing the context with the tenant each time, RoleManager and UserManager still use the default context. That context is provided here:
https://github.com/chaim1221/Machete/blob/feature/multi-tenancy-sqlserver/Machete.Web/Startup.cs#L54
…but like I said, IServiceProvider has no problem with it switching around, and that should be what’s providing the database to UserManager (one would think).
I will try to implement the cache replacement when I get some time. Due to time constraints, I may have to ship with what I have and populate the Users table with a console app or something.
Thank you!
If UserManager and RoleManager are registered with services.AddScoped() then they will probably use the same connection that is specified in Startup no matter what. One dangerous hack would be removing DI registrations made with AddScoped() temporarily and register these to use AddTransient().
I’m still not very sure what could be possible consequnces. I think we are in point now where we need some better and more general mechanism for using UserManager in Startup class with multi-tenant applications.
Another worrying thing is that product group doesn’t support Identity for multi-tenant scenarios. It means there’s possibility they will introduce internal changes to API-s that conflict with multi-tenancy. Anyway it seems more and more reasonable to me to start working out custom UserManager, RoleManager and other implementations for Identity to make sure there will be no conflicts in future.
Well, for us, at some point it will all be in the same database, so it will be less of an issue. “If Identity request, redirect to main schema [dbo]” etc. But yeah I am also worried about changes to the underlying API.
In any case the workaround for now is to use DataGrip to copy the AspNetUsers, AspNetRoles, and AspNetUserRoles tables between databases. Easy peasy. I’ll check back the next time we’re making changes and see if there’s been any updates.
Cheers! And thanks!
This is all great but how would you handle schema update to target all databases?
There are multiple options for schema updates. It’s possible to run migrations before/after new deployment is done. It’s also possible to use database tools and scripts to update schemas of all databases.
I just want to ask where to put the database for the host, this usually contains data for back-office processing like data about all the tenants, inventories of products etc.?
You can put this database wherever you host tenant administration application. Getting tenant definitions to front-end server should be easy to automate. One option, by example, is to keep tenant definitions on Azure blob storage as json file.
Hi Gunnar,
Great post. What is your opinion regarding migrations for tenant-specific databases, how to manage it?
And with this approach how to implement tenant-specific tables? For example, I would like to have one or more tables in TenantA database but don’t want those tables in TenantB, and TenantC databases.
Thanks
Hi Gunnar,
I would love if you can do an article on using hangfire or any other background job package to demonstrate how to run background jobs for tenants in database per tenant scenario.
I have done something similar but noticed you have a backing file that has a list of tenants and their connection strings. I have all of the identity wired up so I know who tenants are, etc. but I’m a bit confused on how to add a new tenant. I don’t believe simply passing in a new connection string with trusted authentication will create a new database. I am lucky in that I don’t need any sort of seeded or default data.
Anyway, ultimately I’m not sure how to create a new database when a new tenant is created in a smooth way.
In ideal world you should have some central data store where tenants configuration is held and there should be also application to manage tenants. This application should set up new tenants or trigger some serverless function or some other process to create new tenant.
Depending on how web applications read tenants information you may need some endpoint where you send request to load tenants information again after new tenant is created.