10k租户和多租户数据库设计 [英] 10k Tenants and Multi-Tenant DB Design
问题描述
我们正在尝试确定新Web应用程序的数据库设计。我们预计将有近10,000个租户，并希望在合理的情况下将其数据保存在单独的数据库中。每个租户将拥有一个大约20MB的数据库，他们主要跟踪50至100个年轻人和100至300个成年人的个人数据以及事件，出勤，奖励等。我不知道一次有多少用户登录，但是每个用户承租人可能有几个。
We are trying to decide on a database design for a new web application. We expect to have close to 10,000 tenants and would like to keep their data in separate databases if it makes sense to do so. Each tenant will have a DB that is around 20MB they are tracking mostly the personal data of 50 to 100 youth and 100 to 300 adults plus events, attendance, awards etc. I do not know how many total users would log in at once but each tenant could have several.
据我所知，我的选择是：
My options as I understand them is to have:
- 什么都没有共享的方法，给每个租户一个单独的数据库。
- 一种架构方法，把每个租户的表分开。
- 一个共享所有的方法每个租户都有自己的tenantID
我理想地希望采用无共享方式，但是我不清楚您可以拥有多少个数据库在SQL Server中遇到性能问题之前。管理多个数据库对我们来说不是主要的问题，但是如果SQL Server由于DB的数量而变得无响应，这就是我要寻找的信息。请记住，数据库平均只有20MB。
I would ideally like the shared-nothing approach but I am unclear of how many databases you can have in SQL Server before you run into performance issues. Managing multiple databases is not a major concern for us but if SQL Server becomes unresponsive due to the number of DBs this is the kind of information I am seeking. Keep in mind the databases are small 20MB on average.
性能明智地选择哪种方法是我们最好的选择？还请考虑拥有多个SQL Server许可证不是理想的成本明智之举。但是，如果有必要，请让我知道。
Performance wise which approach would be the best route for us to take? Please also take into consideration of having multiple SQL Server Licenses would not be ideal cost wise. But if it is necessary please let me know.
我也在这里阅读了几篇文章，也多租户数据体系结构，但我还没有看到拥有大量租户但数据库很小的真正赢家。
I have also read several post here and also Multi-Tenant Data Architecture but I haven't seen a real clear winner with a large number of tenants but really small databases.
还有哪个版本的SQL Server是理想的？
Also which version of SQL Server would be ideal?
如果有人有类似的经验，请告诉我您如何处理以及是否运作良好。任何现实世界的经验/建议都将不胜感激。
If anyone has experience with something similar please let me know how you handled this and if it is working out well. Any real world experience/advise would be greatly appreciated.
谢谢。
推荐答案
这有点意见，因为实际上没有正确的答案。所有这三个都可以根据您的情况工作。以下是一些需要考虑的事情：
This is somewhat of a opinion question because there really is no right answer. All 3 would work in your situation. Here are some things to think about:
为什么必须将其放置在单独的数据库中？：如果因为必须这样做而必须这样做直接访问数据库，那么当然必须这样做。似乎认为用户会使用某种应用程序，所以这不是最佳解决方案吗？使此选项更好的另一件事是，如果有可能，以后可以将租户移至单独的服务器。示例：ABC_Widgets可能会在某个时间将数据托管在自己的服务器上。目前，他们正与所有其他租户一起托管。
Why does it have to be in separate databases?: If they have to do it because they have direct access to the database then of course by all means you have to do this. It sounds thought like the users will have some sort of an application they are using so this wouldn't be the best solution? The other thing that would make this a better option is if its possible the tenant may be moved to a separate server later on. Example: ABC_Widgets might host the data on their own server at some point in time. Right now they are being hosted together with all the other tenants.
每个数据库都有自己的开销，尽管很小，但请记住，SQL会为每个数据库分配一些空间。现在，将其乘以10,000个DB，您将浪费大量空间。
Each db will have its own overhead Albeit small, remember SQL will allocate some space for each db. Now multiply that by 10,000 DB's and you have a chunk of wasted space.
连接字符串从开发的角度来看，当您连接到数据库时您要么必须具有多个连接字符串，要么必须根据正在使用的数据库来动态创建连接字符串。
Connection Strings From a development standpoint, when you connect to the database you will either have to have multiple connection strings or a dynamically created connection string based on the database being used.
模式方法最好的用例是，例如，承租人A将不会拥有相同的表，而承租人G或承租人F可能需要针对自定义情况的专用表/方案。
The Schema Approach I think the best use case for this is, for example Tenant A is not going to be having the same tables and Tenant G or Tenant F might need a specialized table/schema for a custom situation.
恕我直言，我认为您最好使用为每个租户分配 tenantID 并将其存储在1个数据库中的方法。
IMHO I think you would be best using the assign a tenantID to each tenant and store it in 1 database.
这篇关于10k租户和多租户数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
