How to query a replicated sharded database via Azure Elastic Scale?

Question

I'm converting an existing solution to use multi-tenant shards instead of a single database.

My primary database is replicated to a secondary Azure region (active geo-replication). We use this replicated database to fetch some data to reduce load on a primary database.

I'd like to replicate all shards together with a Shard Manager database so that I can connect to a replicated tenant database via replicated Shard Manager db.

The issue is that the replicated Shard Manager database still returns connections to shards located on a primary database server.

Is there any way I can force the replicated Shard Manager DB to return connections to replicated shards?

Answer 1

We ended up creating a connection string by getting original shard location from ShardMap and then change the database server.

var replicatedConnectionString = new SqlConnectionStringBuilder(shardedConnectionString);
replicatedConnectionString.DataSource = "replicated-db.database.windows.net";

replicatedConnectionString.InitialCatalog = shardMap.AsListShardMap<int>()
                                                    .GetMappingForKey(shardingKey)
                                                    .Shard
                                                    .Location
                                                    .Database;