For one of our systems, we have sensitive client data and store each client's data in a separate database. We have about 10-15 clients for that system.
However, we're developing a new system that will have 50-100 clients, maybe more. I am thinking it might be unfeasible to have one database per client in this instance (to store sensitive records and audit history). However I don't know if this is perfectly normal or not, or if there's another way of maintaining security.
Any thoughts on this?
Best Answer
Managing 100 or 500 databases is really not all that different from managing 5 or 10 - you just have to embrace automation and have a scalability plan in place (and don't plan to use high-cost-per-database features like mirroring across all clients).
At my previous job we used this architecture and I would never once have ever thought of merging two clients into a single database, even though some of the challenges can be "hard."
The big benefits are independent recovery models (
acan be simple,bcan be full, etc.), the ability to restore to a point in time (or remove entirely) a client without disrupting others, the ability to seamlessly move a resource-heavy client to its own storage or to a completely different server with very little in the way of transparency (you update a config file or table that tells the app where to find that client).I address several of the objections, and/or how to approach the problems, in these posts:
Handling growing number of Tenants in Multi-tenant Database Architecture
Multi-tenant database using SQL Server 2008?
Automation of backups of Large Number of Database
That all said, I don't think any of us can tell you the point at which management becomes impractical for you - just know that whatever specific challenges you come across, you can ask about those problems individually.