Orchardcore: Some questions about multi-tenancy

[sevenmay]

I really want to use Orchard Core SaaS functionalities, mainly modules and multi-tenancy.
I just started to look at it and I have some questions.

I understand that OrchardCore is the foundation of OrchardCore CMS and in this case multi-tenancy means serving multiple sites from one instance of the app.

I will surely use OrchardCore CMS if I will have the need, but now I find much more interesting the underlyng
infrastructure, modules are awesome.
Maybe separate it better from the CMS and from YesSql, it's kind of difficult to pick up in this big codebase.

I watch all the videos that I have found (thanks Sébastien Ros for all the efforts) but they are more from a usage point of view, it would be very usefull to have something more deep for who want to use the underlying framework.

In my case multi-tenancy means that I have a single app, with a single url, that serves multiple
users grouped in tenants.
A tenant is essentially a group of users that can access, with different permissions, the same set of data.
I have no need to have different urls for different tenants altrough it can be useful for their vanity.

The important thing is that I want to identify the tenant trough the autenticated user and be able to
instantiate a DbContext that point to a specific db or with automatic filtering by a TenantId column.
Is it possible?

Finally I get to my second concern, performances.

If I understand correctly the tenant functionality creates different service containers for each tenant.
And also different middlewares pipelines, I don't know how this impact the hosting environment, I'm new to ASP.NET Core.
I would really appreciate an explanation of how it works.

Is it possible to have a single service graph with per-request tenant injection in some services (es: DbContext)?

I expect to have thousand (at least 2000) of tenants with 1-10 users each.
The app usage will be intense, lots of requests and heavy realtime data access.
Is this too much for OrchardCore to support?

Thanks!

[pgy866]

@sevenmay Have you tested Orchard Core? Or did not fully public relations implementation, you can recommend a https://github.com/Tsingbo-Kooboo/KoobooMvc5, but I believe Orchard Core will be more perfect, it will be a project using the best program。
《你是否测试过Orchard Core?或者没有完全公关实施，可以推荐一个https://github.com/Tsingbo-Kooboo/KoobooMvc5，，但是我相信Orchard Core 会更加完美，会是一个使用最好方案的一个项目》

[dodyg]

@sevenmay have you viewed this https://www.youtube.com/watch?v=HeDjv3blBjQ&list=PL1rZQsJPBU2StolNg0aqvQswETPcYnNKL&index=1

[sevenmay]

Yes, I watched it, thanks @dodyg.
The usage described in that video is pretty clear to me, but I want to support a different scenario.
Tomorrow I will dive deeper into the code to find out if I can adapt OC to my needs.
In the meantime I would like to get some explanation on how multi-tenancy works in regard to DI and middlewares. And something about perfs too.

[sebastienros]

Maybe you don't need our way of doing multi-tenancy then which is url based. But the part that handles different services and middlewares will surely be useful as you can then have different DbContext in the DI.

You can also do it with a single Orchard tenant, and create your own kind of multi-tenancy, call it "groups". This way you can still use the modularity. It might also be that we need to provide a way to configure how tenants are discovered, which should be super easy to do.

But somehow your need is different because you still need a common authentication service/database, then use a secondary database to the rest of the app that you can only load when the user is authenticated. Do you really intend to use different databases even if groups all share the same app?

[sevenmay]

But the part that handles different services and middlewares will surely be useful as you can then have different DbContext in the DI.

This is the part that concerns me. The performances. Is this suitable for an high number of tenants and user in a lob app?

The number of tenants will be in the order of thousand. The project is a rewrite of an existing app, we already have the customers. The usage will be very intensive.
I saw in a video that you talk about some perf test with 500 tenants, but in a real app the number of services and middlewares is different.

About having different services for each tenant do you mean only the instances or that each tenant has
its own container graph created at app start?

I'm a newbie regarding Asp.NET Core but I think that having a different middleware chain for each tenant impact too.

So my question is: with the numbers that I have exposed what do you think about the perfs?

You can also do it with a single Orchard tenant, and create your own kind of multi-tenancy, call it "groups".

The problem is only about how to create the DbContext for the tenant.

But somehow your need is different because you still need a common authentication service/database, then use a secondary database to the rest of the app that you can only load when the user is authenticated. Do you really intend to use different databases even if groups all share the same app?

My plan is:

1. Only one domain.
2. One instance of IdentityServer4 with its own DB for the users/tenants.
3. A number of DBs with the same schema for the app data. (sharding)
   
   
   
   • with a TenantDb column
   
   
   • every DB can contain multiple tenants
   
   
   • the data of each tenant is in one and only one DB
   
   
4. I would like to identify the tenant through the authenticated user and be able to:
   
   
   
   • pass the tenant identifier to the DbContext
   
   
   • pass the correct connection string to the DbContext based on the tenant (from some config or lookup table)
   
   
5. Apply a global filter to the DbContext (EF) to query only the tenant data.

The only problem I see is if I can get the authenticated user early enough to instantiate the DbContext for the right tenant.
Or maybe is better to use a factory that create the DbContext when requested for the first time.

[sebastienros]

I think that can work with Orchard. We just need to expose how to select the tenant. Super simple to do actually. When a tenant is initialized (the first time it's detected) the DbContext for this tenant will be initialized, the same way it is for any asp.net core app. I don't know if EntityFramework supports table prefix to be able to use different sets of tables in the same db (YesSql and NHibernate support it, can also be done in Dapper as you type your own queries).

When I tested multi -tenancy on blogs, I tried with 5000 on 8GB machine. But you have to try with your own apps. But Orchard CMS has lots of services too, so a custom app should not be different. Also tenants are lazy loaded, and you can add some custom logic to unload them after some time of inactivity.

I'd suggest you start working on a prototype to validate your ideas. I think you can totally do it with Orchard Core.

[pgy866]

@sebastienros Why do I create a new Tenants name site1, into site1 management, open the Tenants module, can not see Tenants management menu?
《为何我新建一个Tenants 名称site1，进入site1管理，开启Tenants模块 ，无法看到Tenants 的管理菜单呢？》

[awyl]

@pgy866 This is by design I believe, so that you cannot created nested tenants.

https://github.com/OrchardCMS/OrchardCore/blob/5918d144cdddfd488efd27cda13848d434a404d5/src/OrchardCore.Modules/OrchardCore.Tenants/AdminMenu.cs#L27-L31

[pgy866]

@awyl Wow, you analyze the source, you are amazing, if nested, how beautiful。
《哇，你分析源码，你真厉害，要是可以嵌套，多么美好。》

[sevenmay]

Thanks @sebastienros

When I tested multi -tenancy on blogs, I tried with 5000 on 8GB machine. But you have to try with your own apps. But Orchard CMS has lots of services too, so a custom app should not be different. Also tenants are lazy loaded, and you can add some custom logic to unload them after some time of inactivity.

This is encouraging.

I'd suggest you start working on a prototype to validate your ideas. I think you can totally do it with Orchard Core.

I'll start working on prototyping my ideas and I will dig into your multi-tenancy implementation.

Don't know if you want to keep this issue as a generic discussion on multi-tenancy or if you want to close it. You decide. I will report my progress (and questions).

[dodyg]

Please keep it open. I'm very interested on this topic.

[pgy866]

I also really like the realization of this function

https://github.com/OrchardCMS/OrchardCore/issues/1234

[mdockal]

May I ask why you need a different DBContext for each client? Is the schema different for each client? If not then maybe all you need to do is the group the data by client. For instance have a primary key of ClientId, RecordId for each table in the db:

Client ID   Record ID   Data
1       1       Test for Client #1
1       2       Another Test for Client #1
1       3       Yet another Test for Client #1
2       1       Test for Client #2
2       2       Another Test for Client #2

[sevenmay]

The schema is the same and yes, there's a TenantId column to query the right data for each tenant.
I need different DBContext instances with different connection strings because I have multiple dbs to distribute the work and the data (the amount of data is massive).
Some tenants are on Db1, some on Db2, etc...
If a customer has particular needs in terms of amount of data or workload I can move it to a dedicated db on a dedicated machine.
With Entity Framework Core is quite simple to set the correct connection string based on the user logged in and set up a global query filter by TenantId.

[sebastienros]

We are also looking into a way to use the same schema,and use a TenantId column in all tables, there are scenarios that could benefit from this.

[sevenmay]

We are also looking into a way to use the same schema,and use a TenantId column in all tables, there are scenarios that could benefit from this.

As food for thought remember that if you are using (auto-incremental) integer IDs could be complicated to move data between dbs or perform other tenant specific automations. In this case with GUIDs everthing is much easier.

[mdockal]

@sevenmay that was just simply an example of how to put multiple tenants in the same database table. You can certainly use whatever is practical for the IDs. I tend to prefer integral or string Ids, GUIDs would not produce very friendly URLs in MVC. For instance, let's say that each tenant had their own set of invoices.

mycompany.com/contoso/invoices/1000
vs.
mycompany.com/contoso/invoices/435cde7b-b690-4ac6-a43b-439564049876

Yuck!

That invoice number cannot change when moved across DBs, but each tenant could have the same invoice number. It is not practical to use a GUID in this case.

mycompany.com/fabrikam/invoices/1000

Sample Data:

TenantId | InvoiceID | Title | Total
-- | -- | -- | --
contoso | 1000 | Test for Contoso | $100.00
contoso | 1001 | Another Test for Contoso | $50.00
contoso | 1002 | Yet another Test for Contoso | $250.00
fabrikam | 1000 | Test for Fabrikam | $900.00
fabrikam | 1001 | Another Test for Fabrikam | $100.00

The primary key would be TenantId(string), InvoiceId(int or string)

@sebastienros you might be interested in this. This comes from my days of working for a company that used Epicor ERP Software. The database was structured that way because you could have multiple companies. Basically the same thing.
`

[Skrypt]

The invoice ID should never be only an integer anyways. In some systems it could be INV-0001 ...
Using GUID is more demanding than ID's when doing SQL Queries. Indexing an integer is easier. Though there's nothing preventing you if you want to use GUID's for securing your URL's ; hiding your Id(int) primary key from client website side of things. Example : exposing a User Id would just give hackers hints on which Id to use from the Db when finding a SQL injection exploit... Some CMS's just use a random first Id starting number randomly.

[mdockal]

@Skrypt I have seen the InvoiceId both as an integer and string in various systems just depends on the developers, engineers, and business requirements. I would not want to hide InvoiceId, but definitely would want to hide the UserId. I think for the sake of being more flexible in this case the string InvoiceId would be better.

[sevenmay]

@mdockal I don't want to fuel the eternal debate on int ids vs guid ids, it's a matter of preference and business requirements. And yes, guid ids can have drowbacks like in indexing performances.
In general, probably, int ids are better than guids ids but in my case i prefer the latter.

I don't mind the problem about urls, you can use other params for that (and I read many times that you shouldn't expose ids in urls, sadly I don't always follow the advice). About the example of invoice numbers, those shouldn't be the primary key so they can be the same for each tenant.

For us moving data between dbs is a top requirement and with guids is much easier. We heavyly monitor db operations, workload and amount of data for each tenant and try to balance the usage moving tenants between dbs (to maximize machine utilization). Of course is doable with any type of id but can be painful (need to change ids and maintain relationships). Another strategy I have read about is to centralize int id counters but I don't like it.

About Orchard, that is a general purpose solution it's complicated to please everyone, mine was only a statement on a thing to consider and if you have only one db this is a non-problem. I haven't looked at the data access services so I don't know if you can choose the type of id.

[Skrypt]

I think the debate about GUID's vs Id's have been resolved by @bleroy arguments a while ago. He's the one that did the deployment module in 01 and for him it was easier to use Id's. I don't remember all the story but I guess he could enlighten people about it here if needed. Let's not get side tracked on the main topic either. YesSQL uses int primary keys for now, it could surely be extended if needed.

[sevenmay]

@Skrypt I wasn't talking about Orchard and YesSql but in general. I was responding to a question by mdockal about why I need multiple DbContext and I have mentioned IDs as a side argument.
Sorry for the confusion, I was not discussing the design choices in Orchard.
To be honest I didn't know that YesSql make use of int IDs.

I opened this issue asking info about my particular use case, but in the end I have decide to not use Orchard because I have different needs and a different strategy for multi-tenancy.

If you want you can close this and maybe create a new issue focused on Orchard implementation of multi-tenency and future improvements.

[Skrypt]

It's been triaged to backlog, so this is not a no. 1 priority because it's not a show stopper ; but something that could be added in Core by anyone who wants to contribute and add it if he wants to. So it's all fine. We keep this thread opened and we appreciate all your comments. Don't get me mistaken in my intentions either I was just explaining why we are using mainly integers as primary keys in Orchard Core. You are surely courageous if you plan working on your own CMS by yourself because of this small design difference. I'm pretty sure this is something that could be implemented in a week or less in Orchard Core if that makes you change your mind. 😄

[paulsay70]

@sevenmay Just curious if you ended up going with Orchard or another option for your Multitenancy implementation?

[sevenmay]

@sevenmay Just curious if you ended up going with Orchard or another option for your Multitenancy implementation?

I took a different route because my use case was different.
In the end the type of multitenancy i needed was simply choose the correct connection string and
filter data by TenantId based on the user logged in.

[yithril]

I like Orchard Core very much and I'm thinking it's the solution for our problem, however, because we have so many tenants and the modules they might need changes a lot (we pick for them, not the other way around), I was wondering, can the data in the tenants.json file be put into a database so we can change the modules a tenant needs on the fly? Or do we have to redeploy the app if we want to change in real time?

[sebastienros]

Yes, there is a service that abstracts how the tenants are saved, and they can fit in a shared storage. But you'll need some custom configuration with the connection string for this storage. You would register your custom service in your main Startup.cs through DI.

However I am not sure this is your problem. You can already change the list of tenant through the UI, and the file is updated in this case. Having a custom storage implementation is only necessary if you want to handle multiple servers (and you are not using Azure App Service)

[LockTar]

@sebastienros Can you explain a bit more?

The tenants (in SaaS recipe) are stored in tenants.json. You can manage those tenant through the UI. How will this scale with Azure App Service with multiple instances? Because it is a physical file and those files aren't duplicated on the other instances right? Or I'm not understanding your last comment:

However I am not sure this is your problem. You can already change the list of tenant through the UI, and the file is updated in this case. Having a custom storage implementation is only necessary if you want to handle multiple servers (and you are not using Azure App Service)

Another question:
How can we manage custom configuration in the SaaS recipe? So how can we use @settings.ShellConfiguration["CustomTitle"] with a SaaS recipe? In normal Orchard Core you would manage those configuration in appsettings just like the samples repo.

[sebastienros]

@LockTar

Azure app Services uses a shared folder for all instances. If an instance changes a file in App_Data, all other instances will use the same. You will still need some specific feature to reload the tenants on the other instances if you do it dynamically. PRs are ready for that (https://github.com/OrchardCMS/OrchardCore/pull/5815)

SaaS recipe

Do you mean how to define these settings if you manage tenants dynamically from the UI, and not from the file?

[LockTar]

@LockTar

Azure app Services uses a shared folder for all instances. If an instance changes a file in App_Data, all other instances will use the same. You will still need some specific feature to reload the tenants on the other instances if you do it dynamically. PRs are ready for that (#5815)

Cool! Didn't knew that one. I will look into this. Thanks.

SaaS recipe

Do you mean how to define these settings if you manage tenants dynamically from the UI, and not from the file?

Yes. Can you create new settings like CustomTitle through the UI or do you still define them in the appsettings?

[sebastienros]

Good question about custom settings for tenants. I don't have an answer for now, and we'd need to think about it.

[jtkech]

Yes, it will be possible with the stateless settings / config sources (blob / shared database) that have already been done, and when we will implement shells oriented events as reloading a tenant.

[LockTar]

@jtkech can you create a sample or add a link to the docs when it is ready?

[jtkech]

Okay

About stateless things there are already some docs
https://docs.orchardcore.net/en/dev/docs/reference/core/Shells/
https://docs.orchardcore.net/en/dev/docs/reference/modules/Media.Azure/
https://docs.orchardcore.net/en/dev/docs/reference/modules/DataProtection.Azure/

To keep in sync the related data that are cached, we have some pending PR as e.g #5249 and #5815