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 forwards, options, swaps 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.
I then created read only replicas of the original database to use for scaling out in my demo application.
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
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.,
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”.
devMobile's blog 