We are currently producing a web application with database per tenant logic. Our current database setup is all hosted on a single VM with a one instance free SQL express edition.
All database are dynamically created server-side and added to the same one instance each time a manager register.
However, I anticipate that we might run into problems with high scalability in near future taken into account the cost of a new SQL edition, performance problem related to one instance with multiple databases, future development and management.
I'm considering switching on the elastic pool structure from Azure since we don't have many client right now but I don't really know if it would be beneficial for price and scalability at the start.
With a view to having more than 100 clients would it be beneficial to change my structure with the elastic pool?
Thanks!
Elastic Pools are well suited for a large number of databases with specific utilization patterns. For a given database, this pattern is characterized by low average utilization with relatively infrequent utilization spikes.
The more databases you can add to a pool the greater your savings become.
However, it is important assess if your specific collection of databases can benefit from being in a pool. You need to check the utilization pattern of the databases and notice the peak DTU and ideal times.
The price of a pool is a function of the pool eDTUs. While the eDTU unit price for a pool is 1.5x greater than the DTU unit price for a single database, pool eDTUs can be shared by many databases and fewer total eDTUs are needed.
If the sum of the DTUs of performance levels for single databases is more than 1.5x the eDTUs needed for the pool, then an elastic pool is more cost effective. For available sizes, see eDTU and storage limits for elastic pools and elastic databases.
To determine the Maximum number of concurrently peaking databases I would recommend you to take a good read of this - Maximum number of concurrently peaking databases