sql - Multi-Tenancy 与多个数据库

Question

我正在开发一个定制的CRM解决方案，该解决方案将通过Web/SaaS模型出售。我预计会有数十或数百个客户使用此解决方案。我将使用MS SQL作为数据库引擎。

选项1是拥有一个数据库，并在表上包括一个TenantId列，一个合适的索引，并在每个数据库访问中使用'where tenantId = {...}'。

选项2是为每个客户端有一个单独的数据库，从而避免需要TenantId和where子句。

我预计每个客户将拥有成千上万的记录，而不是数百万。

如我所见，无论选择哪种方式，都会有总计的数据页数。该决定似乎集中在SQL是更擅长管理多个数据库还是具有TenantId和索引的单个数据库。最初，该解决方案将在单个数据库服务器上运行，但最终将迁移到SAN。

有人对此有意见吗？

Answer 1

有一篇有趣的MSDN文章，标题为Multi-Tenant Data Architecture，您可能想看看。作者简要分析了某种方法可能比另一种方法更合适的地方:

The number, nature, and needs of the tenants you expect to serve all affect your data architecture decision in different ways. Some of the following questions may bias you toward a more isolated approach, while others may bias you toward a more shared approach.

• How many prospective tenants do you expect to target? You may be nowhere near being able to estimate prospective use with authority, but think in terms of orders of magnitude: are you building an application for hundreds of tenants? Thousands? Tens of thousands? More? The larger you expect your tenant base to be, the more likely you will want to consider a more shared approach.

• How much storage space do you expect the average tenant's data to occupy? If you expect some or all tenants to store very large amounts of data, the separate-database approach is probably best. (Indeed, data storage requirements may force you to adopt a separate-database model anyway. If so, it will be much easier to design the application that way from the beginning than to move to a separate-database approach later on.)

• How many concurrent end users do you expect the average tenant to support? The larger the number, the more appropriate a more isolated approach will be to meet end-user requirements.

• Do you expect to offer any per-tenant value-added services, such as per-tenant backup and restore capability? Such services are easier to offer through a more isolated approach.

请注意，在您的情况下，“共享方式”为选项1，“隔离方式”为选项2。对于前两点，您在任何一方都没有偏见，所以我认为我的决定将基于后两点。