CODEWITHSUNDEEP
sql-serverazureazure-sql-database
Ajay
Ajay

Reputation: 6590

Azure cross database queries,External resources, External tables

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. enter image description here

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?

Upvotes: 1

Views: 2375

Answers (1)

mauridb
mauridb

Reputation: 1569

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

https://learn.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://learn.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance

which seems to be exactly what you need.

Upvotes: 1

Related Questions