Azure跨数据库查询，外部资源，外部表 [英] Azure cross database queries,External resources, External tables

问题描述

我正在将所有 VM Sql Server 2012数据库迁移到 Azure Sql数据库.在当前结构中，我正在使用跨数据库查询从不同的数据库表中获取数据.

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

我引用了此链接 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.

就我而言，它直接显示在 Tables 文件夹中.

In my case it directly showing in Tables folder.

谁知道为什么我在External Tables文件夹中看不到部门表?如何在External Tables文件夹中添加此类表?

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

推荐答案

外部表仅在Azure SQL中可用，以支持称为弹性查询"的功能，这可以解决您的问题:

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

如果这还不够，并且您确实需要完整的跨数据库查询支持，则必须使用Azure SQL托管实例:

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.

这篇关于Azure跨数据库查询，外部资源，外部表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！