Erland Sommarskog (2/5/2014)
So these 10 databases have the same set of tables, with each database serving a specific client?I am not sure that I think it is a wise move to put all eggs into the same basket. Maybe if you had settled on actually merging all tables and rerwritten the application to use row-level security, but you say that you don't want to change the application.
Having separate databases means that you have to backup, reindex etc them individually. But it also means that you can restore the databases individually. If one client make something really stupid and scratch their data, you can restore that database without affecting other clients.
What database sizes are we talking about? Since you seem to be hosting a client application, moving the databases to the cloud may become of interest to you if it not already is. Currently, the maximum database size in Windows Azure SQL Database is 150 GB.
Erland,
We have one parent db per client + 5 to 30 project(child) dbs, and we have hundreds of clients. The project dbs will go under the parents as schemas.
The db sizes are less than 150GB but Azure does not accept more than 200 dbs as I remember.
Thanks,
Kuzey