.NET Core web API + Dapper – Readonly query workloads with Geo Replication

Geo Replication process

One of the easiest ways to create read-only replicas of an Azure SQL Database database is with Active geo-replication(it’s also useful for disaster recovery with geo-failure to a geo-secondary in a different Azure Region).

The first step was to remove all the Microsoft SQL Server features used in the The World Wide Importers database (e.g. Memory Optimized tables) which were not supported by the Azure SQL Database tier I was using.

SQL Service Management Studio(SSMS) Deploy to Azure wizard

I then used the “Deploy Database Wizard” to copy my modified World Wide Importers database to an Azure SQL Database.

Azure Portal Primary Database in SQL Service Management Studio(SSMS)

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.

Azure SQL Database no replicas configured
Azure Portal Create Geo Replica
Azure Portal Create Geo Replica Confirmation

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

Azure Portal Primary Database and four Geo-replicas
Azure Portal Primary Database and Geo-replicas in SQL Service Management Studio(SSMS)
Azure Application Insights showing multiple Geo-Replicas being used.

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”.

WebAPI Dapper Azure Resource Group

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.

.NET Core web API + Dapper – Offloading readonly query workloads.

A Scaling Out Scenario

Initially this was going to be a post about Sharding but after some discussion with my manager at one of the companies I work for it evolved into a post about using the Dapper Object Relational Mapper(ORM) with Azure SQL Database and Active geo-replication or SQL Data Sync for Azure to offload read-only query workloads to improve scalability.

The company builds a Software as a Service(Saas) product for managing portfolios of foreign currency forwardsoptionsswaps etc. Part of the solution has a React application which customers use to get an “aggregated” view of their exposure to different currency pairs for different periods etc.

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 first step was to remove all the Microsoft SQL Server features used in the The World Wide Importers database (e.g. Memory Optimized tables) which were not supported by the Azure SQL Database vCore or DTU tier I had was using.

I then uploaded my modified World Wide Importers database to an Azure SQL Database Server.

SQL Server Management studio with World Wide Importers open in Object Explorer tab
Azure SQL Database SQL Server Management Studio (SSMS) initial database

I then created read only replicas of the original database to use for scaling out in my demo application.

Azure SQL Database SQL Server Management Studio (SSMS) with replicas databases

The configuration strings of the read-only replicas are loaded as the application starts.

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)

	var errorHandlerSettings = Configuration.GetSection(nameof(ErrorHandlerSettings));

	var readonlyReplicaServersConnectionStringSettings = Configuration.GetSection("ReadonlyReplicaServersConnectionStringSettings");


	services.AddDapperCachingInMemory(new MemoryConfiguration
		AllMethodsEnableCache = false
	services.AddDapperCachingInRedis(new RedisConfiguration
		AllMethodsEnableCache = false,
		KeyPrefix = Configuration.GetValue<string>("RedisKeyPrefix"),
		ConnectionString = Configuration.GetConnectionString("RedisConnection")

Then code was added to the controller to randomly select which read-only replica to use. More complex approaches were considered but not implemented for the initial version.

public class StockItemsReadonlyReplicasController : ControllerBase
    private readonly ILogger<StockItemsReadonlyReplicasController> logger;
    private readonly List<string> readonlyReplicasConnectionStrings;

    public StockItemsReadonlyReplicasController(ILogger<StockItemsReadonlyReplicasController> logger, IOptions<List<string>> readonlyReplicasServerConnectionStrings)
        this.logger = logger;

        this.readonlyReplicasConnectionStrings = readonlyReplicasServerConnectionStrings.Value;

    public async Task<ActionResult<IAsyncEnumerable<Model.StockItemListDtoV1>>> Get()
        IEnumerable<Model.StockItemListDtoV1> response = null;

        if (readonlyReplicasConnectionStrings.Count == 0)
            logger.LogError("No readonly replica server Connection strings configured");

            return this.StatusCode(StatusCodes.Status500InternalServerError);

        Random random = new Random(); // maybe this should be instantiated ever call, but "danger here by thy threading"

        string connectionString = readonlyReplicasConnectionStrings[random.Next(0, readonlyReplicasConnectionStrings.Count)];

        logger.LogTrace("Connection string {connectionString}", connectionString);

        using (SqlConnection db = new SqlConnection(connectionString))
            response = await db.QueryAsync<Model.StockItemListDtoV1>(sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]", commandType: CommandType.Text);

        return this.Ok(response);

The Read-only replica server connection string setup template in appsettings.Development.json.

  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
  "ConnectionStrings": {
    "WorldWideImportersDatabase": "ThisIsNotTheDatabaseConnectionStringYouAreLookingFor",
    "DefaultConnection": "ThisIsNotTheDefaultDatabaseConnectionStringYouAreLookingFor",
    "RedisConnection": "ThisIsNotTheRedisConnectionStringYouAreLookingFor"
  "ReadonlyReplicaServersConnectionStringSettings": [
  "ApplicationInsights": {
    "ConnectionString": "ThisIsNotTheApplicationInsightsConnectionStringYouAreLookingFor"
  "ErrorHandlerSettings": {
    "Detail": "Default detail",
    "Title": "Default title",
    "UrlSpecificSettings": {
      "localhost": {
        "Title": "Title for localhost",
        "Detail": "Detail for localhost"
      "": {
        "Title": "Title for",
        "Detail": "Detail for"

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

Sample application Azure App Service Configuration
Azure Application Insights with connections to different read-only replicas highlighted

I had incorrectly configured the firewall on one of the read-only replica database servers so roughly one in four connection attempts failed.

Azure Application Insights failed database connection displayed

The customer’s application was also fairly compute intensive so we configure the Azure App Service to Auto scale based on the CPU load.,

Azure app service horizontal auto scale configuration

I’ll outline the configuration of Active geo-replication or SQL Data Sync for Azure in a couple of future posts.

The current solution works but I need to tidy up few issues like the StockItemsReadonlyReplicasController getting constructor getting a bit “chunky”.