1

I have two SQL Server 2016 VMs in Azure. I am trying to connect these two sql server instances using External datasource. I can safely connect the two instances from my local pc, remotely. While querying using external table/data source, I am getting this: enter image description here

Kindly note

i) I have used both RDBMS/SHARD_MAP_Manager

ii) Credential is valid credential

iii) I know, i can use Linked Servers, but i don't want it. As i am planning to move these databases to Sql Azure Db. External Table and datasource is not mandatory. You can suggest alternatives other than LinkedServers.

So i need a query or part of query that will work with both, Sql Server(Virtual/Physical server) and Sql Azure(Db as a Service)

Update

Same query ran flawlessly on Sql Azure db.

  • 1
    Elastic Database Query (External tables of type RDBMS or SHARD_MAP_MANAGER) is not supported on SQL 2016. You can use linked server + synonyms to create a compatible schema between External Tables in SQL DB and SQL in a VM, although the DDL to create them will be different. – David Browne - Microsoft Aug 2 '17 at 12:56
3

External data source type SHARD_MAP_MANAGER and RDBMS is only supported only on Azure SQL Database v12 or later. It is not supported on SQL Server 2016.

Ref:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql

-- Elastic Database query only: a shard map manager as data source   
-- (only on Azure SQL Database v12 or later)  
CREATE EXTERNAL DATA SOURCE data_source_name  
    WITH (   
        TYPE = SHARD_MAP_MANAGER,  
        LOCATION = '<server_name>.database.windows.net',  
        DATABASE_NAME = '\<ElasticDatabase_ShardMapManagerDb'>,  
        CREDENTIAL = <ElasticDBQueryCred>,  
        SHARD_MAP_NAME = '<ShardMapName>'  
    )  
[;]  

-- Elastic Database query only: a remote database on Azure SQL Database as data source   
-- (only on Azure SQL Database v12 or later)  
CREATE EXTERNAL DATA SOURCE data_source_name  
    WITH (   
        TYPE = RDBMS,  
        LOCATION = '<server_name>.database.windows.net',  
        DATABASE_NAME = '<Remote_Database_Name>',  
        CREDENTIAL = <SQL_Credential>  
    )  
[;]   

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.