I then created replicas in the same region (if the application had a global customer base creating read only geo replicas in regions close to users might be worth considering) for the read-only queries.
I created four replicas which is the maximum number supported. If more replicas were required a secondary of a secondary (a process known as chaining) could be use to create additional geo-replicas
The Azure Database Geo-replication was pretty easy to setup. For a production scenario where only a portion of the database (e.g. shaped by Customer or Geography) is required it might not be the “right hammer”.
The other limitation I encountered was the resources used by the replication of “transaction processing” tables (in the World Wide Importers database tables like the Sales.OrderLines, Sales.CustomerTransactions etc.) which often wouldn’t be required for read-only applications.
The database queries to lookup reference data (forward curves etc.), return a shaped dataset for each supported instrument type, then “aggregating” the information with C# code consumes significant database and processing resources.
The Manage UserSecrets(Secrets.json) functionality was used for testing on my development machine. In production Azure App Service the array of connections strings was configured with ReadonlyReplicaServersConnectionStringSettings:0, ReadonlyReplicaServersConnectionStringSettings:1 etc. syntax
I had incorrectly configured the firewall on one of the read-only replica database servers so roughly one in four connection attempts failed.
The customer’s application was also fairly compute intensive so we configure the Azure App Service to Auto scale based on the CPU load.,