Best Practices for Database Server Configurations in Multi-Site Environments
When setting up multiple sites or tenants, it’s important to consider the configuration of your database server. This includes the number of databases required and the memory size needed for each.
Initial Setup
For the initial setup, you will need three databases:
- Identity Provider: This is used for login and user management. It’s a common database used across all sites/tenants.
- Server: This is used for resources and permission management such as dashboards and data sources. You will need one for each site/tenant.
- Data Store Database: This is used to save data from files and web data sources. You will need one for each site/tenant.
Memory Size
The Identity Provider and Server databases are used for store site and user metadata and will not grow exponentially. The average size for these databases will be around 20 – 50 MB in disk.
The Data Store database, on the other hand, will grow based on your data. If you are using files data source and web data source, the data will be extracted from the original data source and moved to this database for dashboarding. If you are using live SQL connections for data sources, it will not grow.
Without considering the Data Store database, you should reserve approximately 100 MB database storage for each site/tenant databases.
Recommendations
If you are using Azure SQL Database, we recommend using Elastic Pool. However, it’s recommended to limit 200 databases for each logical server for better maintainability.
Please note that these recommendations are based on general considerations. More specific details can be provided based on your usage such as the number of typical users, sites, etc.
