Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
    
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 '13 at 7:48
    
@jamiet: you can specify default schemas for a database user –  gbn Apr 5 '13 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 '13 at 8:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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