0

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.

JWL
  • 13,591
  • 7
  • 57
  • 63

1 Answers1

2

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
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • Thanks. I had seen hints of this in the docs, but being new to elastic queries, needed some concrete examples to deepen my understanding. – JWL Feb 13 '18 at 20:15