My organisation is creating a multi-tenant data architecture to support analysis of data uploaded by multiple different clients. The database is Microsoft SQL Server.

It has been suggested that partitioning the database by Client ID might be a good way to manage the data volume (which could be considerable, certainly billions of rows over time)

Is this a good idea? Why or why not?

2 Answers 2


There are lots of ways for you to manage your data volume, but what you haven't really described here is the end result that you desire. Is it performance? Is it manageability? Is it security? Is it scalability? Is it your ability to add in new clients quickly and easily over time?

When you say 'manage the data volume', I am thinking you are looking to administer the data in a way that allows for decent performance. That makes me think of partitioning, but I don't know enough about the data to say that this is the right thing to do. Partitioning by client ID could be fine, but if you have thousands of clients, it may not be the right choice.

I think you should find the real pain points you are trying to solve and then look to architect a solution that mitigates those pain points.


Personally I'd consider schemas per client

This means separate tables and security per client. Which means separate disks if needed.

I don't see much advantage in partitions here

  • Hi, Does the use of different schemas per client not mean that mikera is going to have to write different code for each tenant? (unless of course he uses dynamic SQL - which I certainly would not advocate). If you are advocating the sort of seperation that schemas provides then I would just go the whole hog and have a separate database per tenant - at least that way you don't have to write tenant-specific code. Personally speaking, if you want a multi-tenant topology then I think it would be better to have an identifier per tenant in each table, thus ensuring separation of data per tenant.
    – jamiet
    Apr 5, 2013 at 7:48
  • @jamiet: you can specify default schemas for a database user
    – gbn
    Apr 5, 2013 at 9:53
  • Agreed, though I have to be honest and say I'm not understanding how that's helping here - apologies if I'm missing something obvious. Correct me I'm wrong but that still means you're going to have to reproduce the same code for each tenant, no?
    – jamiet
    Apr 10, 2013 at 8:07
  • @jamiet You are correct about needing to reproduce the code per each Schema. Having a default schema just means that it will be searched for objects that are not schema-qualified, prior to looking in the dbo Schema. There does not appear to be any benefit to separating clients per schema over separating per database. Oct 31, 2015 at 2:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.