Sql-server – Is it possible to use cross database queries in both Azure and SQL Server 2016

I'm working on a project that is designed to run on Microsoft's Azure platform. At present we have a few developers working locally on their own machines with the intent of being able to publish the solution to Azure when we go live. We have been using dynamic stored procedures (sprocs that are essentially quoted strings with the database name replaced) so that we can make cross-database calls, however it turns out that these don't work on Azure.

One solution seems to be to use elastic queries, using the SQL methodology below:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'encryptionpassword';  

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

CREATE EXTERNAL DATA SOURCE mydb WITH   
    (TYPE = RDBMS,   
    LOCATION = 'path-to-database.database.windows.net',   
    DATABASE_NAME = 'dbname',   
    CREDENTIAL = SQL_Credential   
) ;  

CREATE EXTERNAL TABLE [dbo].[tc_City](
    [CityID] [uniqueidentifier] NOT NULL,
    [CityLabel] [varchar](50) NOT NULL,
    [County] [varchar](50) NOT NULL,
    [InsertionDate] [datetime] NOT NULL
) WITH ( DATA_SOURCE = mydb) 

select * from dbo.tc_City

This solution seems the most elegant, as it removes the need for dynamic stored procedures, which can be horrible to debug and modify. There is one major caveat: it doesn't seem possible to use the elastic query methodology on our local SQL server 2016 installs without adding a third party database engine. We upgraded from 2012 recently because we thought it would be possible to use the same technique, but it turns out that we have to install something called PolyBase to do elastic queries, and that is only supported with external databases of different types, such as Hadoop, rather than being capable of referencing another database within the SQL 2016 engine.

PolyBase methodology:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass';  

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential    
WITH IDENTITY = 'user',  
SECRET = 'pass';  

CREATE EXTERNAL DATA SOURCE Central WITH   
    (TYPE = HADOOP,   
    LOCATION = 'localhost',   
    CREDENTIAL = SQL_Credential   
);  

CREATE EXTERNAL TABLE [dbo].[tc_City](
    [CityID] [uniqueidentifier] NOT NULL,
    [CityLabel] [varchar](50) NOT NULL,
    [County] [varchar](50) NOT NULL,
    [InsertionDate] [datetime] NOT NULL
) WITH ( DATA_SOURCE = Central) 

select * from dbo.tc_City

As you can see, the code for accessing an external database via PolyBase ends up being mostly the same, with only the data source setting being different – which would mean we could essentially use one core set of SQL scripts/stored procedures and just run the relevant changes when publishing to Azure. Having to install Hadoop servers on our local machines just so that we can access another database within the same server without having to rewrite masses of SQL seems somewhat excessive and unnecessarily complicated.

Is there any way we can use the same methodology to access another database running on the same SQL server 2016 engine without having to write different SQL for the stored procedures running locally and those running in Azure? e.g. we don't want to have to write the "create external table …" on Azure and reference the data in that table via dbo.external_tablename, whilst having to locally refer to databasename.dbo.external_tablename – as that would mean writing two different sets of stored procedures.

Can the external data source be referenced by file instead, if that allows all of the subsequent SQL to be the same?