sql server - Physical data separation in a singular database at a row level -

sql server - Physical data separation in a singular database at a row level -

problem:

let's have 5 clients. @ moment each client has it's own re-create of database. because there millions of rows , not want load of 1 client's database disturb performance of client's database.

this not optimal design because:

managing separate connections each client not flexible, changes need propagated across more 1 database, , a new database need created each new client.

question:

is possible include info 1 database have form of physical isolation between client info performs having several databases?

this isolation not @ table (or schema) level, more @ row level. example, don't want have separate employee tables each client (like employee_clienta, employee_clientb, employee_clientc), rather employee table clientid foreign key. means info within table need physically separated.

your question has multi-tenant database architecture. i've added multi-tenant tag you. might want click , read of other multi-tenant questions , answers before create changes. this answer summarizes of trade-offs, , links article microsoft.

is possible include info 1 database have form of physical isolation between client info performs having several databases?

no, not really. physical isolation means each client has have own database or own schema.

rather employee table clientid foreign key. means info within table need physically separated.

sharing table among clients, , isolating each client's rows client_id isn't physical isolation. that's logical isolation. selecting rows specific client depends on using client_id in clause.

you won't similar performance on same hardware because each table contain 5 times number of rows of original tables, , every query have include "client_id" in clause. might similar performance few clients , few rows, database grows, differences become more apparent.

sql-server database database-design multi-tenant