Multitenancy and Partitaioning

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.

Thanks Andrew , i have currently having close to 100 tables , conservatively if i had "tenantId" to tables , atleast i need to add for over 50 tables, so i was thinking of an approach(if any) other than adding tenant ids or using one DB instance per client(i will have not more than 10 ) . is there any other approach ?

DEE 2009-12-21 15:01:16

One other approach would be to see whether you could use the Azure platform, which can make having multiple instances of a database a lot cheaper proposition. (Partitioning is an Enterprise only feature, not cheap licenses.)

Andrew 2009-12-21 15:05:58

Thanks Andrew,appreciate your comments

DEE 2009-12-21 15:11:56

Database per tenant is the typical ISP hosting aproach. In addition to what Andrew already called out (security, backup consitency, upgrade path) I'd also add licensing. Multi-tenant services usualy require special SQL Server licensing you have to negotiate with MS.

Remus Rusanu 2009-12-21 18:04:05

what sql server licensing you are taking about , i am under the impression that if i buy an sql server license then i can create any number of databases as i like ..is this not true?

DEE 2009-12-22 15:51:39

All that said, I don't think that separate databases or partitioning are the way to go just because you're worried about protecting data. There are many other ways to protect data using certain patterns (like middleware).

orokusaki 2010-01-26 01:41:50

ansaurus

tags:

views:

answers:

Multitenancy and Partitaioning

related questions