How to add new data sources without changing elastic query?

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 yet find useful docs or advise on how to design and run Azure Elastic Queries that can operate reliably without being modified, 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.

1 answer

  • answered 2018-02-13 13:37 Alberto Morillo

    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