Multiple tenants with Entity Framework Core and SQL Server 2017 in ASP.NET Core application

Question

I have an ASP.NET Core 2.1 application which uses Entity Framework Core 2.1 and SQL Server 2017 (Web edition). I read a lot of how to solve problem with multiple tenants, but more or less the answer is:

it depends on your application, setup.

Setup:

• every client gets a new IIS site with own configuration for database
• some tables (client defines on setup which one) must be shared between tenants, for example: it has common tables for users, groups, rights, user-settings, but separate for offers, prices, events ...
• every table has identity column (int) which is the primary key
• all tenants have exactly the same table structure (when upgrading client, all tenants should update all tables structures)
• I am using a code-first approach with EF Core

Options:

1. separate database for every client and tenant (Db name client_tenant)
2. separate database for every client and use schema for tenant (Db name client.tenant)
3. separate database for every client and use column in every table (TenantId)

Details:

I know every option has cons and pros, so here is more information about the setup:

• every client has 1 or more (up to 1000) tenants (average around 20)
• clients must share some tables between tenants
• there is approximately 500 hundred tables, approx 10 of them are shareable (common) between tenants (those clients who has the need to share)
• biggest table can produce 10 million records per year

Pros / cons as I see them:

1. separate databases (separate Db for tenant):

   • pros
     • security
     • table structure changes are fully supported by EF (migrations)
     • tables are transparent
   • cons
     • not so easy to dynamically change database
     • left join with EF Core over databases is not supported yet (related with next point)
     • no solution at the moment how to solve common tables (users, groups, rights, user-settings)¹.
     • a lot of databases: client1_tenant1, client1_tenant2, ..., client2_tenant1 ...

2. separate schemes (1Db, separate scheme for tenant):

   • pros
     • there is no problem with sharing data (I can have one default schema for common tables)
     • secure (less then 1. options)
   • cons
     • not so easy to dynamically change schemas
     • I can not use schemas for separating tables in modules
     • managing tables structure changes with EF²

3. TenantId column (1 Db, 1 scheme):

   • pros
     • fully supported by EF Core - global query filters, migrations ...
   • cons
     • can have very large tables (1 client * 500 tenants * 2000000 records * 10 years = 100 billions rows in 10 years - the biggest client, biggest table)
     • security³
     • potential performance problems
     • harder to maintain (very large tables - they aren't transparent)

Summary:

Which approach (1, 2 or 3) should I choose if I:

• am using EF Core
• need to share some tables between tenants (sync should happened in less then a minute).⁴
• can have very large tables
• I have more then 500 tables, and they logically belong to 1 module

Related topics: (just to prove I did my homework and search for duplicates)

• Sync 2 tables to different databases: 1, 2
• Multi tenant - code first in EF 6 (not Core)
• Migrations with multiple Db schemas
• Same question with very different setup
• and many many more, so that I can understand better the problem and possible solutions

References:

¹ One way is SQL Server replication - problems with identity.
Each client has some common tables to manage rights about witch tenant can see, edit, settings which columns want to see for some table ...

² Not sure if this is even possible. Probably I should call in for loop context.Database.Migrate(); for every tenant.

³ All data belongs to the same table, so bug where one tenant would see data of another tenant is awful but not devastating. Also with global filtering this risk is minimised.

⁴ I could write API to sync changes in code (some background thread), because these changes doesn't happen very often.

Answer 1

Yea, it depends on your application.

I'd go with 1.

some tables (client defines on setup which one) must be shared between tenants, for example: it has common tables for users, groups, rights, user-settings, but separate for offers, prices, events

If you need tables for settings, user management, groups, rights, etc you should create a separate database per client that will have one purpose: user handling - crud, rights, groups, checks, etc.

You'll need to pass this information along as claims or any other suitable authorization system.

So I'll create like a client1_user_management, client1_tenant1, client1_tenant2, etc

If you're using claims (jwt, cookies) or any other way to pass along groups, rights, etc you won't need left join with EF Core over databases. Correct me if I'm wrong.

As for the other con: not so easy to dynamically change database;

In order to avoid this type of configuration, you can inject your database based on a subdomain or something like that:

public void Configure()
{
  Dictionary<string, string> connStrs = new Dictionary<string, string>();
  connStrs.Add("DB1", Configuration["Data:DB1Connection:ConnectionString"]);
  connStrs.Add("DB2", Configuration["Data:DB2Connection:ConnectionString"]);
  DbContextFactory.SetConnectionString(connStrs);
}

You'll probably have one application that will handle multiple clients/multiple tenants. It's useful to identify and inject one client & one tenant per request.

Receiving request for: https://client1.myapp.com/tenant1 -> i can identify client1 and tenant1 and i can figure out database name (hardcoded like client1_tenant1 or configured if you have a tenant management system). As i can figure out a database name, I'll use that to create a DBContext per request (scoped: What is the difference between services.AddTransient, service.AddScope and service.AddSingleton methods in Asp.Net Core?).

P.S: This is an opinion based on current information. I'll try to push this solution if it's possible.