At what point does one database per client become unfeasible?

Question

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?

Answer 1

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 (a can be simple, b can 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.

Answer 2

I recommend you read Multi-Tenant Data Architecture, a white-paper that discusses the options you have, and pros and cons. To summarize, it gives three options:

• separate DBs
• separate schemas
• shared schema

You are now at separate DBs stage, which offers the best separation (isolation between tenants), but is the most difficult to manage. As you grow into hundreds of tenants you'll realize that the logistics of administering 100s of DBs are far from trivial. Think backup-restore (location of backed up files, jobs, schedules etc). Think how will you monitor and manage file allocation, disk space used and database growth across hundreds of DBs. Think what will be your High-Availability/Disaster-Recoverability scenario in a near future with 1000 tenant? 1000 mirrored DBs, 1000 log shipping sessions? Think what if in 6 months your dev team comes to you and say "I know how give this awesome feature to our product, we'll use Transactional Replication!", what will you say? "sure, let me set up 500 publishers, it will be fun"! Is not impossible to manage hundreds of DBs, but if you plan to, you better polish up your PowerShell skills and stop using the UI management tools right now.

Additionally you need to consider that multiple (hundreds) DBs have measurable impact on performance and cost:

• physical disk space is less efficiently used (every database must have some spare room, you'll have that spare room multiplied by the number of DBs)
• There is no way you can create a dedicate log disk for write intensive tasks, you'll will have to move all those LDFs onto one (or more) SSD storage
• log writes will be less efficient on frequent commits as they spread out across many individual log block records vs. aggregate into one (you'll get underused log blocks). See What is an LSN: Log Sequence Number to understand what I'm talking about.

Separated DBs come with some advantages though due to isolation, the main advantage being independent backup/restore.

Scenario like yours though are a perfect candidate for SQL Azure databases. No administration of disk space, no need to provide HA/DR, grow to hundreds/thousands of DBs etc.

Answer 3

At my previous job, we hosted not just one database per client -- in most instances, it was more than that! As I left, there were over 4,500 databases running in one MariaDB cluster, nearly 7,000 in another (ironically smaller) cluster, and 4 "shards" (completely separate, independent web and database servers, even in a wholly separate data center) each hosting 200-500 databases in a single MySQL server. And that company is still growing at a good clip.

The long and the short is that the success of that company proves that such an architecture is indeed feasible. (Caveat: Contrary to the apparent gains in isolation by using separate databases, all data was accessed through a trio of tightly coupled applications that all used the same database user/pass! I suspect performance may have suffered ever-so-slightly if each client had a separate user/pass -- but only slightly.)

From my experiences working closely with the sys admins (technically I was a programmer with the company, but in reality I was the best DBA they had, and the only person they had who knew how to set up a firewall!), performance-related concerns boiled down to concurrent accesses, query complexity/time, index performance, etc. -- all the usual suspects, in other words, and the number of databases on the server played no discernible part, a conclusion affirmed by the highly-paid specialist consultants we consulted regularly.

The bottom line is that you should focus your concerns on your application, on your infrastructure, and not on the number of databases you happen to have. All those other factors will be more than enough to keep you busy resolving performance problems and bottlenecks.