.NET Core web API + Dapper – Readonly query workloads with Data Sync

Azure SQL Data Synchronisation Process

Read-only replicas of an Azure SQL Database database with Active geo-replication are easy to setup but there are some disadvantages. e.g. bi-directional synchronisation is not supported, not all tables or selected columns of some tables might not be needed\should not be accessible for reporting, the overhead of replicating tables used for transaction processing might impact on the performance of the solution etc. Azure SQL Data Sync is a service built on Azure SQL Database that can synchronise selected data bi-directionally across multiple databases, both on-premises and in the cloud.

The first step was to remove all the Microsoft SQL Server features used in the the World Wide Importers database (e.g. Sequence Numbers, Column Store indexes etc.) which are not supported(see general limitiations) by Azure SQL Data Sync. I then used the “Deploy Database Wizard” to copy my modified World Wide Importers database to an Azure SQL Database.

Deploy Database to Microsoft Azure SQL Database running
Microsoft Azure SQL Database Servers with WorldWideImporters and ReadonlyReplicaHub database
Data Sync Group creation onReadOnlyReplicaHub database

For my “read-only replicas” scenario if there are any update conflicts the the source database “wins”.

Data Sync Group created
Data Sync Group configuration database management
Data Sync Group configuration adding “source” database synchronisation to Hub
Data Sync Group configuration adding “destination” database(s) synchronisation from Hub
Data Sync Group selecting source database StockItems table and columns to synchronise
Data Sync Group “destination” databases configured
Initial synchronisation of only StockItems table and seed data
Regular synchronisation of a subset of StockItems columns to destination databases
Source Azure SQL Database regular synchronisation compute utilisation
StockItemsReadOnlyReplicas Controller JSON after first replication completed
Azure application Insights Dependencies showing usage of different synchronised databases
StockItems table in source database with updated RRP
StockItems table in destination database with updated RRP after next scheduled snychronisation
StockItems table in destination database after next scheduled synchronisation

The Azure SQL Database Data Sync was pretty easy to setup (configuration in the hub database tripped me up initially). For a production scenario where only a portion of the database (e.g. shaped by Customer, Geography, security considerations, or a bi-directional requirement) it would be an effective solution, though for some applications the delay between synchronisations might be an issue.

.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)
{
	services.AddControllers();

	var errorHandlerSettings = Configuration.GetSection(nameof(ErrorHandlerSettings));
	services.Configure<ErrorHandlerSettings>(errorHandlerSettings);

	var readonlyReplicaServersConnectionStringSettings = Configuration.GetSection("ReadonlyReplicaServersConnectionStringSettings");
	services.Configure<List<string>>(readonlyReplicaServersConnectionStringSettings);

	services.AddResponseCaching();

	services.AddDapperForMSSQL();
#if DAPPER_EXTENSIONS_CACHE_MEMORY
	services.AddDapperCachingInMemory(new MemoryConfiguration
	{
		AllMethodsEnableCache = false
 	});
#endif
#if DAPPER_EXTENSIONS_CACHE_REDIS
	services.AddDapperCachingInRedis(new RedisConfiguration
	{
		AllMethodsEnableCache = false,
		KeyPrefix = Configuration.GetValue<string>("RedisKeyPrefix"),
		ConnectionString = Configuration.GetConnectionString("RedisConnection")
	}); 
#endif
	services.AddApplicationInsightsTelemetry();
}

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.

[ApiController]
[Route("api/[controller]")]
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;
    }

    [HttpGet]
    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": [
    "ThisIsNotTheReadonlyReplicaDatabaseConnectionStringYouAreLookingFor",
    "ThisIsNotTheReadonlyReplicaDatabaseConnectionStringYouAreLookingFor",
    "ThisIsNotTheReadonlyReplicaDatabaseConnectionStringYouAreLookingFor",
    "ThisIsNotTheReadonlyReplicaDatabaseConnectionStringYouAreLookingFor"
  ],
  "ApplicationInsights": {
    "ConnectionString": "ThisIsNotTheApplicationInsightsConnectionStringYouAreLookingFor"
  },
  "ErrorHandlerSettings": {
    "Detail": "Default detail",
    "Title": "Default title",
    "UrlSpecificSettings": {
      "localhost": {
        "Title": "Title for localhost",
        "Detail": "Detail for localhost"
      },
      "127.0.0.1": {
        "Title": "Title for 127.0.0.1",
        "Detail": "Detail for 127.0.0.1"
      }
    }
  }
}

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