I am currently looking to build an SaaS in ASP.Net hosted on Azure Cloud. I am looking for advice on how to best build my database and the Entity Framework that goes with it. Once a customer registers on the web app, the app needs to create a seperate database for each customer on my Azure SQL server.
I have started looking into the option of elastic pooling, but it has left me quite confused. To tell you a bit about my database, it has one "meta"-database for all general settings. And then each customer has a database with his portfolio.
Example
database [Settings] with tables (Currency, Stocks, Bonds) [
[Customer1] SomeFinanceProduct [Currency as foreign, stock as foreign], SomeOtherFinanceProduct [Currency as foreign, bond as foreign]
[Customer2] SomeFinanceProduct [Currency as foreign, stock as foreign], SomeOtherFinanceProduct [Currency as foreign, bond as foreign]
[Customer3] etc.
I would appreciate some help from more experienced developpers. Many thanks, this is an important issue for me. I have also found this post from 2015 where they said that the solution would be soon released, but I have not found anything on the web.
I can't speak to the Entity Framework part of your question too much, however, I can speak to the elastic pool side of things.
It's important to note that an Azure elastic pool is just a billing and resource allocation construct. As far as your application or its code is concerned, there is no difference if you use an elastic pool. You still have a database, it lives on a server, and that server (and indirectly, but more specifically in the case of Azure, that database) has resource constraints.
In Azure, you traditionally create a database and choose a service or pricing tier. You pay X dollars in exchange for Y resources (CPU, memory, storage size, connection counts, etc) for that database. You repeat this for every single database you create. Over time, databases grow in size or usage and they become more demanding, so you must change the service tiers of each database individually as this happens. As you have more and more databases, this becomes tedious and cost ineffective.
With elastic pools, you can take any number of individual databases and drop the individual service/pricing plans and instead buy a big bucket of resources [i.e. the elastic pool] and give those resources to all of the databases. The theory is that collectively you need fewer resources with this approach, and this allows you to save money. It also makes better use of the resources you are buying.
The reason you need fewer resources is because generally databases experience peak demand at different times. When you buy resources individually, you have to over buy on every single database to handle the peaks (which means you have a lot of wasted resources just sitting there unused). On an elastic pool, since all database are in the pool together you only buy enough extra resources which will cover however many peaks you typically would have going on concurrently at the same time; now you have fewer resources sitting idle wasting money.
As I mentioned, the other benefit of using elastic pools is that you can make better use of the resources you have. Consider a database which has very low demands placed upon it; you'd naturally purchase a small (and thus cheap) plan for it. Then consider a database which has high demands placed upon it; you'll likely buy a plan with much greater resources. Now, occasionally the low use database gets some big hits. With the small plan, the resources aren't enough and performance degrades terribly. Meanwhile the other database has tons of resources and much of it is being unused. Wouldn't it be nice if the small database which is experiencing the unusual peak could borrow some of those resources for a few minutes? That's exactly what elastic pools do! Elastic pools have lots of built in scalability wins for your applications!
The last important thing to note, is that elastic pools cost more per unit of resource, than regular databases. This means that there is a break even point and that its more expensive to use elastic pools until you have enough databases to make it worthwhile. For my needs I've found 10-15 databases to be a fairly good break even point. Once you have enough, create the pool. Then, as you add more databases to the pool later on, the "per database" costs start going down even more.
--
So to get back to your question, elastic pools will not specifically affect your ability to use Entity Framework for your project. Regardless of whether you choose to pool your databases or not, you'll have to get your code to talk to the appropriate customer specific database based on who is logged in.