Multi-tenancy: What benefit does one-db-per-tenant provide?

Question

From what I've read, it seems like each site on the SE network lives in a single app, but has its own database. This seems costly, and I'm not sure what the benefit is. My novice theory is that since each db has less rows in each table, reads and writes are a little faster across the board.

This benefit comes at the seemingly huge cost of applying any migrations or db updates across databases, which is more time consuming and introduces higher costs for redundancy, DevOps, etc.

What benefit does this have over single-db + single-app architecture?

Answer 1

Keeping each tenant in a separate database makes it very easy to move a highly-demanding tenant to their own server, place their data/log files on faster I/O, etc. If you put everyone in the same database, you're eventually going to hit a wall on your current hardware and then you're either going to move everyone to bigger hardware anyway.

The maintenance part is not really a big deal, and actually makes some things easier. For deployments to identical schemas, you don't really need any additional DevOps resources, you just need a loop (or tools like SQL Farm Combine or Red-Gate Multi Script). Things that are easier are backups - while it sounds like more administrative overhead, and you'll still have about the same amount of overall data to back up, separate databases actually allows you much greater control - you can put different backups on different drives, run them on different schedules, they should be smaller and faster, and you can even keep different tenants on different recovery models. One additional bonus: restoring to a point in time due to a problem with one tenant only affects that tenant.

Another benefit is keeping each tenant's data separate - which sometimes satisfies a legal requirement, but also makes it very easy to delete a tenant or move them to a different server without affecting any other tenant.

Some other narrative in these answers over on dba.SE:

https://dba.stackexchange.com/a/16767/1186

https://dba.stackexchange.com/a/33556/1186

This is also a large facet in the design of cloud-based solutions like Windows Azure SQL Database.