How to add new data sources without changing elastic query?

Question

We have a scenario in which we wish to use Azure Elastic Query so as to allow us to run aggregate queries on multiple databases geographically distributed, and which might be added to with time. However, we can't yet find useful docs or advise on how to design and run Azure Elastic Queries that can operate reliably without being modified (by hand), while data sources are added or removed.

Any advise from someone with experience on this db tech would be very welcome.

As a further, specific constraint, the disparate source databases are all SQL Express DBs - we are considering mapping these to online Azure SQL instances (PaaS).

UPDATE: I've seen something similar being asked/answered here, but am seeking a better answer.

Answer 1

You can create external source with a specific name that will be used on your queries but programmatically change the location and database name used by sources using Dynamic SQL:

ALTER PROCEDURE CETFromNewLocation    AS

BEGIN

DECLARE @location varchar(100)

SET @location = 'myserver.database.windows.net'

DECLARE @CreateExternalTableString varchar(100)

SET @CreateExternalTableString = 'CREATE EXTERNAL DATA SOURCE MyExtSrc
WITH
(
    TYPE=SHARD_MAP_MANAGER,
    LOCATION=' + @location + ' DATABASE_NAME='ShardMapDatabase',
    CREDENTIAL= SMMUser,
    SHARD_MAP_NAME='ShardMap'
);'

    EXEC sp_executesql @CreateExternalTableString

END