Physical data separation in a singular database at a row level

Question

Problem:

Let's say that we have 5 clients. At the moment each client has it's own copy of the database. This is because there are millions of rows and we do not want load of one client's database to disturb the performance of another client's database.

This is not an optimal design because:

Managing separate connections for each client is not flexible,
Changes need to be propagated across more than one database, and
A new database need to be created for each new client.

Question:

Is it possible to include all this data into one database but have some form of physical isolation between the client data so that it performs similarly to having several databases?

This isolation would NOT be at a table (or schema) level, but more at a row level. For example, I don't want to have separate Employee tables for each client (like Employee_ClientA, Employee_ClientB, Employee_ClientC), but rather an Employee table with a ClientID foreign key. This means the data WITHIN a table would need to be physically separated.

score 3 · Answer 1 · answered Feb 08 '13 at 09:43

3

If performance is your main concern, you can use the Partitioned Tables feature to place separate clients' data in separate filegroups.

If you want to limit each clients' visibility of other clients' data, you might consider the options outlined in Granting Row-Level Permissions in SQL Server

answered Feb 08 '13 at 09:43

Damien_The_Unbeliever

234,701
27
340
448

Would it make sense to partition these tables across filegroups on SEPARATE physical hard drives? – Dave New Feb 08 '13 at 13:18
This *might* work for the OP, who's talking about 5 clients. But it's kind of an exotic use of partitioning, which is designed for scaling rather than as a core structural component for a multi-tenant architecture. And it's not practical in the more general case. More than 1000 partitions on x86 hardware isn't supported, and the default limit is 15000 partitions (on a server with 16 gigs of RAM). – Mike Sherrill 'Cat Recall' Feb 09 '13 at 12:17

score 1 · Accepted Answer · edited May 23 '17 at 10:24

Your question has to do with multi-tenant database architecture. I've added the multi-tenant tag for you. You might want to click it and read some of the other multi-tenant questions and answers before you make changes. This SO answer summarizes most of the trade-offs, and links to an article by Microsoft.

Is it possible to include all this data into one database but have some form of physical isolation between the client data so that it performs similarly to having several databases?

No, not really. Physical isolation means each client has to have its own database or its own schema.

rather an Employee table with a ClientID foreign key. This means the data WITHIN a table would need to be physically separated.

Sharing a table among all clients, and isolating each client's rows by their client_id isn't physical isolation. That's logical isolation. Selecting rows for a specific client depends on using their client_id in a WHERE clause.

You won't get similar performance on the same hardware because each table will contain five times the number of rows of the original tables, and every query will have to include "client_id" in the WHERE clause. You might get similar performance for few clients and few rows, but as the database grows, the differences will become more apparent.

Physical data separation in a singular database at a row level

2 Answers2