Azure cross database queries,External resources, E

问题:

I am migrating all my VM Sql Server 2012 database to Azure Sql Database. In my current structure I am using cross database queries to fetch data from different database tables.

I have created external table to my parent table using below query

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword';  

CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin  
WITH IDENTITY = 'yourServeradminlogin',  
SECRET = 'yourPassword';  

CREATE EXTERNAL DATA SOURCE RefmyDemoDB2  
WITH  
(  
    TYPE=RDBMS,  
    LOCATION='testdbdemoserver.database.windows.net',  
    DATABASE_NAME='myDemoDB2',  
    CREDENTIAL= yourServeradminlogin  
);  
CREATE EXTERNAL TABLE [dbo].[Department](  
    [DeptId] [int] NOT NULL,  
    [Name] [varchar](50) NULL  
)  
WITH  
(  
    DATA_SOURCE = RefmyDemoDB2  
);  


/****** Script for SelectTopNRows command from SSMS  ******/  
SELECT *  
  FROM [dbo].[Employee] E  
  INNER JOIN [dbo].[Department] D  
  ON E.DeptId = D.DeptId

I referred this link https://www.c-sharpcorner.com/article/cross-database-queries-in-azure-sql/

But when I create external table it doesn't shows table in external table folder like shown in below image.

In my case it directly showing in Tables folder.

Anyone knows why I don't see Department table in External Tables folder? How can I add such tables in External Tables folder?

回答1:

External tables are available in Azure SQL only to support a feature called "Elastic Queries", that may solve your problem:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview

If this is not enough for you, and you really need full cross-database query support, you have to use an Azure SQL Managed Instance:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance

which seems to be exactly what you need.