Currently we have a website which includes a SQL DB. Both are hosted in one region in Azure
I have a business request to store all data for our US clients in a US datacentre, no replication. Therefore we need multiple databases (identical schema, different data) hosted in different regions (US, EU, ASIA).
How our API brings the data from all db's together is still to be decided, but I am open to suggestions.
Is there a specific application architecture that describes this scenario? Are there any examples?
The Elastic Database Client Library provides support in EF for doing this, see https://learn.microsoft.com/en-us/azure/sql-database/sql-database-elastic-scale-use-entity-framework-applications-visual-studio.
In essence, it allows you to set up the rules that determine which database to use for a certain request, so you can have a database per customer.
I have not personally used the library but have handrolled it myself before. If you do it yourself, the pattern is basically this; You create a master database in which a list of all your customers reside, including information about which region they are in. You then create a factory that will create a database connection string for a given customer and then set up your code so that when it needs a DbContext, it asks the factory, which will then construct the connection string and create a DbContext with the correct connection string.
With EF code first, the database will automatically be created for you. The hard part is handling migrations with Code First. I implemented a loop at startup that would explicitly call the "apply migrations" on each database. The challenge is that EF Code First doesn't expect you to have multiple databases so you have to implement a custom connection factory that the migrations code can use. I can't remember the exact details off the top of my head, but if you want to handroll this and use EF code first, let me know and I'll try to dig the code out.