I'm setting up a SaaS system, where we're planning to give each customer their own database. The system is already set up so that we can easily scale out to additional servers if the load becomes too great; we're hoping to have thousands, or even tens of thousands of customers.
- Is there any practical limitation on the number of micro-databases you can/should have on one SQL Server?
- Can it affect performance of the server?
- Is it better to have 10,000 databases of 100 MB each, or one database of 1 TB?
When I say "micro-databases", I don't really mean "micro"; I just mean that we're aiming for thousands of customers, so each individual database would only be a thousandth or less of the total data storage. In reality, each database would be around the 100MB mark, depending on how much usage it gets.
The main reason to use 10,000 databases is for scalability. Fact is, V1 of the system has one database, and we have had some uncomfortable moments when the DB was straining under the load.
It was straining CPU, memory, I/O - all of the above. Even though we fixed those problems, they made us realize that at some point, even with the best indexing in the world, if we're as successful as we hope to be, we simply can't put all our data in one big honkin' database. So for V2 we're sharding, so we can split the load between multiple DB servers.
I've spent the last year developing this sharded solution. It's one license per server, but anyway that's taken care of since we're using VMs on Azure. Reason the question comes up now is because previously we were offering only to large institutions and setting up each one ourselves. Our next order of business is a self-service model where anyone with a browser can sign up and create their own database. Their databases will be much smaller and much more numerous than the large institutions.
We tried Azure SQL Database Elastic Pools. Performance was very disappointing, so we switched back to regular VMs.