多租户和分手 [英] Multitenancy and Partitaioning

问题描述

我必须使我的应用程序与SAAS兼容.为了实现多租户，我要对数据进行分区，每个分区都将用于一个租户.并且此分区将动态完成.

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 ?

我正在使用SQL 2005

i am using SQL 2005

问候 DEE

推荐答案

每个分区方案最多只能有1000个分区，并且只能在一个字段上进行分区，因此，如果您打算在1000个以上的实例中进行多租户，则可以将不得不跳很多圈.您可以通过在多个分区表之上使用分区视图来扩展限制，但这会增加管理开销.您可以使用DMV并创建自己的自动化系统，该系统为每个客户端/租户生成新分区并管理该问题，但这将特定于您的应用程序而不是通用的.

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.

目前，SQL Server中没有自动动态分区，在PDC09上提到了与SQL Azure未来路线图有关的内容，但是我没有听说过SQL Server.

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.

您的替代选择是每个客户端一个数据库或SQL实例，此方法的好处是，如果需要，您可以给自己更多的机会进行扩展，如果您开始寻找更大的数据中心，则可以开始在服务器等服务器场之间平衡SQL实例.如果您将所有数据自动存储在单个数据库中.

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.

要考虑的其他事项:

安全性:尽管您将数据存储在具有分区功能的单个数据库中，但是却没有数据保护.您可能会冒着一个代码中的任何错误将一个客户的数据暴露给另一个客户的风险.

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.

这篇关于多租户和分手的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！