best practices for share vs separate databases on multi-tenant

Topics: General, Installing Orchard
Jun 14, 2014 at 4:45 PM
Any learned lessons/best practices for whether to use separate db's vs shared db with table prefixes for each tenant? I know the content load for a site tends to be low, so I hate to create so many individual db's for a batch of tenant sites, but on the other hand the table count will get to be pretty high if I add each tenant to the same db. Looking for any lessons others have learned before I pick one method.
Jun 15, 2014 at 5:12 PM
We've gone with a single DB for DotNest for the following reasons:
  • Easier to maintain: e.g. easier to back up a single DB and easier to have just a single connection string for all of the tenants (what is easier to change).
  • Uses a lot less memory on the webserver: on the server there is one connection pool per connection string where a pool can have a set amount of connections (probably 180 by default?) and each connection uses an amount of memory (if I remember correctly then around 5MB). By having a single connection pool connection re-using is more frequent, thus the amount of connections in all pools at the same time can be lower (since instead of having at least as many connections as there are tenants you just have a set maximal number of connections, that can be lower than the tenant count).
  • Cheaper: we're using Azure SQL, thus less DBs mean less cost.
A single DB doesn't cause any inconvenience and the number of tables in a single DB doesn't affect performance.

The only good reason to use multiple DBs is if you need better performance than what a single DB can offer even if you can scale it up. At DotNest this won't be an issue for a long time since Azure SQL DBs can be scaled up greatly to offer huge computing power.
Jun 15, 2014 at 5:53 PM
Excellent, thanks for the feedback!