Sql-server – Should we use SQL Server partitions for a multi-tenant database design

sql server

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?

Best Answer

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.