3

We are trying to decide on a database design for a new web application. We expect to have close to 10,000 tenants and would like to keep their data in separate databases if it makes sense to do so. Each tenant will have a DB that is around 20MB they are tracking mostly the personal data of 50 to 100 youth and 100 to 300 adults plus events, attendance, awards etc. I do not know how many total users would log in at once but each tenant could have several.

My options as I understand them is to have:

  • A shared-nothing approach giving each tenant a separate database.

  • A schema approach giving each tenant separate tables.

  • shared-everything approach where each tenant has their own tenantID

I would ideally like the shared-nothing approach but I am unclear of how many databases you can have in SQL Server before you run into performance issues. Managing multiple databases is not a major concern for us but if SQL Server becomes unresponsive due to the number of DBs this is the kind of information I am seeking. Keep in mind the databases are small 20MB on average. I also need to easily restore backups for individual tenants. I have also read that you can have roughly 32,000 databases in SQL Server but other post suggest you shouldn't try to get anywhere close to this number. Even 1000 to 2000 databases can cause performance issues but it was unclear of the database sizes that caused the issues. If anyone has used thousands of small databases in a SQL Server environment please let me know.

Performance wise which approach would be the best route for us to take? Please also take into consideration of having multiple SQL Server Licenses would not be ideal cost wise. But if it is necessary please let me know.

I have also read several post here and also Multi-Tenant Data Architecture but I haven't seen a real clear winner with a large number of tenants but really small databases.

If anyone has experience with something similar please let me know how you handled this and if it is working out well. Any real world experience/advise would be greatly appreciated.

Thank You.

2
  • How many tenants are you expecting this year, and next year? Is the 10,000 number representing a forecast maximum?
    – Hannah Vernon
    May 19, 2015 at 1:08
  • 5000 to start. Hope to see 10000 in a year. I wouldn't expect more than 20000.
    – bhs8227
    May 19, 2015 at 1:26

1 Answer 1

2

Design a shared-everything system. This can be deployed in a shared-nothing way i.e. put each tenant in their own database, should that prove desirable. The extra development effort to type the additional predicate is very small and easy to do up front. The reverse - where you re-factor a shared-nothing system to become multi-tenant - will be a significant project and difficult to get right retrospectively. You will need some scripts or ETL packages to load / unload one tenant from a database. This will help with size & load balancing, privacy concerns and such like.

The schema-based approach has all the disadvantages of single-database and none of the advantages of multi-tenant. You will end up with a lot of dynamic code, stored procedures will be difficult to get right and statement recompiles will be ubiquitous, increasing response times.

Invariably some of your tenants will be much larger than others. My experience is that you will end up with a handful of tenants representing 30%-70% of the total data volume. These will each have their own DB and, perhaps, infrastructure. They will consume 60% of your support and maintenance time. The others will all fit into one or two databases.

I've run development instances with roughly 1,000 databases online sharing about 8GB of memory. This worked well enough for dev. I'd never try this in production as the risk of problems snowballing would be too high.

If you're feeling brave you may like to experiment with auto-close on one or more database. This will save some resources on the instance. This is definitely not recommended. I mention it merely as an observation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.