Search code examples
azureazure-sql-databasegeo-replication

Handling external data source on Azure SQL and geo-replication

Below is my azure Set up

  1. Elastic Pool Primary (30 databases) - Active
  2. Elastic Pool Secondary (30 databases) - Read only geo-replicated.

Primary pool is used by WebApp's for read and writes. All reports run on secondary.

I recently had to add an External Data source on one of the database in primary, and the cross database query works well.

The good thing is this External data source configuration was also replicated and works well on secondary.

Problem : 

Used the following to create external data souce

CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred 
WITH IDENTITY = 'username', 
SECRET = 'password'; 

CREATE EXTERNAL DATA SOURCE ElasticDBQueryDataSrc WITH 
(TYPE = RDBMS, 
LOCATION = 'primarydbname.database.windows.net', 
DATABASE_NAME = 'db1', 
CREDENTIAL = ElasticDBQueryCred, 
) ;

When I run the query from my secondary it runs well, my concern is, it's using the external data source of primary db1.

Is there a better way to add External Data Source in secondary pool so that it can use it's own copy of db1? Or does azure sql handle this by itself?

Any feed back or help is appreciated.

Thank you

Solution

  • This is a consequence of the fact that the primary database is replicated in totality, including metadata stored there. To work around you may want to try configuring two different external sources on the primary - e.g. ElasticDBQueryDataSrcPrimary local to the primary pool and ElasticDBQueryDataSrcSecondary local to the secondary pool. You will be responsible for switching your data source between queries to primary and secondary.