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).
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 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.
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 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.
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.
devMobile's blog 