共享多租户数据库中有效划分数据的技术

Question

我为一家特许经营公司工作，并开始对每个特许经营所使用的应用程序进行完全重新设计，以管理其日常运作。它作为一个销售点和调度应用程序。目前，每个专营权都有自己的本地SQL数据库。桌面应用程序在它们的工具中运行，并从该数据库读取并写入该数据库。在公司端，我们需要获得关于所有专营权的汇总信息，因此每次应用程序保存本地记录时，它都会向公司服务器发送一个副本，该服务器将来自所有专营权的数据以相同的模式插入到共享数据库中。然后，公司可以在共享数据库上运行报告，其中包含来自所有专营权的记录。为什么设计成这样？在它设计的时候(十多年前)，利益相关者认为特许经营能够在没有互联网连接的情况下访问他们的数据是很重要的。

好吧，时代已经改变了，所有参与者最终都愿意转移到一个带有web应用前端的单一集中式数据库中，以支持所有的授权，而不是桌面应用。因此，在技术方面，我们正在设计一个多租户应用程序和一个共享的数据库。就像我说的，我们实际上已经有了企业端的数据库，但它不支持即时授权的读写功能。

为了让你了解我们正在处理的数据量，目前有几百个特许经营，300万个客户账户，其中很小一部分实际上是活跃的客户，以及800万的购买量。最大的表之一包含每周客户日历条目，并有超过9100万行。同样，这些记录中的每一项都是特定于单个专营权的，但它们将一起存储。例如，tbl_Customers有300万客户，每个客户都有一个指向tbl_Franchises的fk_FranchiseID。

该数据库将需要支持每天的特许经营(web应用程序最有可能使用基于实体框架的数据层)、个人特许经营报告和聚合企业报告，以及一些面向客户的网站功能，如显示时间表和客户帐户信息。我认为汇总报告、费用和增加新授权的容易程度是驱动共享数据库的因素。此外，我认为目前的计划是有一个负载均衡器与两个web和两个数据库服务器。

尽管有很多事情我仍然不理解，而且仍然在学习SQL Server，但我最关心的问题是：

• 分割专营权数据。该特许经营网络应用程序将只需要检索记录为其当前用户的专营权。在当前数据库中，有些表与tbl_franchise之间有5-10个联接。必须做所有这些连接，只是为了过滤专营权ID时，否则可能不需要联接，似乎它可能真的会损害性能。查询至少需要像现在对本地私有数据库一样快地运行，所以按专营权进行的过滤是可以忽略的。尽管看上去是错误的，但牺牲标准化并将专营权ID直接包含在一些或所有这些子表中更好吗？或者，如果键被索引并且查询没有使用联接表中的任何其他列，那么联接是否可以忽略不计？
• 隐私。我们需要使用户或开发人员不可能故意或意外地提取属于错误专营权的数据。Server 2016行级安全性似乎是其中的一个选项。不过，这又让我想到了第一点。需要大量联接才能将行与谓词函数中的专营记录关联起来。
• 锁争用。如果我们可以使用索引来安全地筛选其他特许经营的记录，在大多数情况下，特许经营管理应用程序应该是做短读和写操作。但是，突然间，所有的用户都可能同时访问这些表，我的脑海里响起了警钟。此外，我们还需要继续向特许公司报告其个人数据，并向公司提供汇总数据。这些报告中有几个计算量很大，不能接受脏读，所以我担心如果一个报告正在运行，我们可能会有几百个专营权无法使用这个系统。

因此，总的问题是:可以使用什么策略和技术来高效地隔离共享数据库中的租户数据？

Answer 1

我建议你调查一下表分区。对于表分区，应用程序仍然会看到它使用的单个“逻辑”表；但是，在物理上，每个客户端的数据可以放在一个单独的表、文件组、数据库中，甚至是Server实例中。

这给了你两样东西

1)它为您将客户端ID添加到所有表提供了一个借口，因为它将充当所需的分区键。

2)它允许您将客户端与其他客户隔离开来，如果您计划让客户直接访问他们的数据，这可能非常重要。如果不对数据进行分区，粗心大意的开发人员可能会对其他客户端发起不知情的DoS攻击，方法是锁定太多行或太用力地访问表--即使它们只有datareader权限。但是，如果桌子是物理分开的，这是不可能的。

尽管如此，我可能不会让他们直接访问实时数据库。这似乎非常危险。如果他们需要运行报告和临时查询，您可以给他们一个只包含他们数据的数据集市 (可能需要额外的费用)。