多租户和分区
您好，
我必须使我的应用程序符合 SAAS 要求。为了实现多租户，我对数据进行分区，每个分区将用于一个租户。并且这种分区将动态完成。
有人做过这样的事吗？ 您认为更好的方法是什么？
我正在使用 SQL 2005
问候 迪伊
HI There,
i have to make my application SAAS compliant .For achieving multi tenancy , i was thing of partitioning the data , and each partition will be for a tenant. and this partitioning will be done dynamically .
has anybody done something like this ?
what do you think the better approach be ?
i am using SQL 2005
Regards
DEE
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
每个分区方案有 1000 个分区的限制，并且您只能在单个字段上进行分区，因此如果您打算多租户超过 1000 个实例，您将不得不跳过更多的环节。您可以通过在多个分区表之上使用分区视图来扩展限制，但这会增加管理开销。您可以使用 DMV 并创建自己的自动化系统，该系统为每个客户端/租户生成新分区并管理问题，但它将特定于您的应用程序而不是通用的。
目前SQL Server中没有自动动态分区，在PDC09上有关SQL Azure未来路线图的时候提到过，但我没有听说SQL Server有它。
您的替代选择是每个客户端一个数据库或 SQL 实例，这种方法的好处在于，您可以在需要时为自己提供更多扩展的机会，并且如果您开始考虑更大的数据中心，您可以开始平衡跨服务器场的 SQL 实例等。如果您自动将所有数据存储在单个数据库中。
其他需要考虑的事项：
安全性：虽然您将数据存储在带有分区的单个数据库中，但您没有数据保护。由于代码中的任何一个错误，您都面临着将一个客户端的数据暴露给另一个客户端的风险。
升级：如果所有客户端都访问同一个数据库，那么升级将是一种全有或全无的方法 - 您将无法轻松地将某些用户迁移到新版本，同时让其他用户保持原样。
备份：您可以让每个分区占用一个单独的文件组并尝试管理这种情况。可以说，开箱即用，每个客户端的备份都混合在一起。如果单个客户端请求回滚到给定数据，您必须提前仔细计划如何在不影响系统其他用户的情况下执行该操作。
There is a limit of 1000 partitions per partition scheme and you can only partition on a single field, so if you intend to multi-tenant beyond 1000 instances you are going to have to jump through a lot more hoops. You can extend the limit by using a partitioned view on top of multiple partitioned tables, but this increases the management overhead. You can use the DMVs and create your own automated system that generates new partitions per client / tenant and manages the problem but it will be specific to your application and not generic.
At present there is no automatic dynamic partitioning in SQL Server, it was mentioned at the PDC09 in relation to the SQL Azure future roadmap, but I did not hear of it for SQL Server.
Your alternative choices are a database or SQL Instance per client, there are benefits to this approach in that you give yourself far more opportunity to scale out if the needed arises, and if you start looking at a larger data centre, you can start balancing the SQL Instances across a farm of servers etc. If you automatically have all the data in a single database.
Other things to take into consideration:
Security: Whilst you have the data in a single database with partitioning, you have no data protection. You risk exposing one client's data to another very trivially with any single bug in the code.
Upgrading: If all the clients access the same database, then the upgrades will be an all or nothing approach - you will not be able to easily migrate some users to a new version whilst leaving the others as they were.
Backups: You can make each partition occupy a separate file group and try manage the situation. Out of the box so to speak, every client's backups are mingled together. If a single client asks for a rollback to a given data you have to plan carefully in advance how that could be executed without affecting the other users of the system.