多租户与多个数据库
我正在开发一个定制的 CRM 解决方案，该解决方案将通过 Web/SaaS 模型进行销售。我预计会有数十或数百个客户使用此解决方案。我将使用 MS SQL 作为数据库引擎。
选项 1 是使用单个数据库，并在表上包含 TenantId 列、合适的索引，并在每个数据库访问上使用“wheretenantId={...}”。
选项 2 是为每个客户端拥有单独的数据库，从而避免需要 TenantId 和 where 子句。
我预计每个客户将拥有数十万条记录，而不是数百万条。
正如我所看到的，无论我选择哪个选项，都会有一个数据页总数。这个决定似乎集中在 SQL 是否更适合管理多个数据库，或者管理具有 TenantId 和索引的单个数据库。最初，该解决方案将在单个数据库服务器上运行，但最终将转移到 SAN。
有人对此有什么看法吗？
I am developing a custom CRM solution which will be sold via the Web/SaaS model. I anticipate tens or hundreds of clients using this solution. I will be using MS SQL as the db engine.
Option 1 is to have a single DB, and include a TenantId column on tables, a suitable index and use 'where tenantId={...}' on each db access.
Option 2 is to have an individual DB for each client, avoiding the need for the TenantId and where clauses.
I anticipate that each client will have hundreds of thousands of records, not millions.
As I see it, there will be a total number of data pages whichever option I go for. The decision seems centered on whether SQL is better at managing multiple DBs, or a single DB with TenantId and index. Initially the solution will run on a single DB server, but will eventually move to SAN.
Does anyone have any views on this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
有一篇有趣的 MSDN 文章，标题为多租户数据架构，您可能想查看一下。作者对某种方法可能比另一种方法更合适的情况进行了简要分析：
请注意，根据您的情况，“共享方法”是选项 1，“隔离方法”是选项 2。对于前两点，你们都没有偏见，所以我想我会根据后两点做出决定。
There is an interesting MSDN article, titled Multi-Tenant Data Architecture, which you may want to check out. The authors make a brief analysis on where a certain approach might be more appropriate than another:
Note that the "shared approach" is option 1, and the "isolated approach" is option 2, in your case. You are not biased on either side when it comes to the first two points, so I think I would base my decision on the last two points.
如果您不必在租户之间链接数据，那么最好拥有多个数据库。维护更容易，设置更容易，性能也会更好。
当一张表中包含来自多个租户的数据时，表锁定和对大表的搜索查询可能而且很可能会减慢您的解决方案。
共享一个数据库的唯一原因是如果您有很多客户端并且每个客户端的行数非常少。
If you dont have to link data between the tenants, your go best with having multiple databases. Maintenance is easier, setup is easier and performance will be much better.
When having data from multiple tenants in one table, table locks and search querys over big tables can and most likely will slow down your solution.
The only reason to share one db i would see if you have very much clients and very low number of rows per client.