Multi-tenancy - single database vs multiple database

Question

We have a number of clients, whose systems share some functionality, but also have quite a degree of diversity. The number of clients is growing - always a healthy thing! - and the diversity between their businesses is also increasing.

At present there is a single ASP.Net (Web Forms) Web Site (as opposed to web project), which has sub-folders for each tenant, with that tenant's non-standard pages. There is a separate model project, which deals with database access and business logic.

Which is preferable - and most importantly, why - between having (a) 1 database per client, with only the features associated with that client; or (b) a single database shared by all clients, where only a subset of tables are used by any one client.

The main concerns within the business are over:

• maintenance of multiple assets - backups, version control and the like
• promoting re-use as much as possible

How would you ensure these concerns are addressed, which solution is preferable, and why? (I have been also compiling responses to similar questions)

Edit: here's the highlights from my research from other sources:

Should I use one database per application or share a single database amongst multiple applications

Splitting up a single project into libraries

Supporting multitenancy

Multi-tenancy - single database vs multiple database

http://msdn.microsoft.com/en-us/library/aa479086.aspx

http://stackoverflow.com/questions/2213006/how-to-create-a-multi-tenant-database-with-shared-table-structures

http://cloudcomputing.sys-con.com/node/1610582

http://blogs.msdn.com/b/cbiyikoglu/archive/2011/03/23/moving-to-multi-tenant-model-made-easy-with-sql-azure-federations.aspx

http://ask.sqlservercentral.com/questions/3615/one-database-or-multiple.html

http://devlicio.us/blogs/anne_epstein/archive/2009/04/24/the-case-for-multiple-dbs-in-multi-tenancy-situations.aspx

http://ayende.com/blog/3497/multi-tenancy-the-physical-data-model

http://www.codeproject.com/Articles/51334/Multi-Tenants-Database-Architecture

http://discuss.joelonsoftware.com/default.asp?design.4.319460.16

http://stackoverflow.com/questions/3479297/multiple-application-using-one-database

http://stackoverflow.com/questions/1676552/single-or-multiple-databases

http://mikehadlow.blogspot.co.uk/2008/11/multi-tenancy-part-1-strategy.html

http://mikehadlow.blogspot.co.uk/2008/11/multi-tenancy-part-2-components-and.html

http://www.sqlservercentral.com/Forums/Topic893107-373-1.aspx#bm1047297

Answer 1

If you are using SQL Server, use one database but use schemas. Use dbo for stuff that is general to all clients and create a schema for each client and make that the default schema for users from that client. Now you can have a general object (say a getBudget proc) in the dbo schema and a customized one for the client in their schema with the same name.

Answer 2

You're missing some concerns. Problems will come with growth. If you can assume that someday you'll grow bigger than one DB server - one complex database will definitely cause you a headache. Unless you'll invest in architecture in advance. But it is also expensive step )

So, just do not forget, that it is both many times cheaper and many times easier to scale out few different databases, than huge ones )

Answer 3

Since the clients databases and functionality are diverging, then it means that at one point they will end up being different systems, so in this case I would recommend separate systems since the costs of maintaining the customizations for each client will outweigh the benefits of a single database system.

Single database systems are best for when the changes between different customers are merely configurations but not additional features for each client.