Today, I got a very interesting question about if could be possible to connect from external tables to Azure SQL Managed Instance, SQL Database or Synase. In this article, I would like to explain it.
Besides the option that we have with Linked Server, my first option was to use SQL SERVER 2019 and Polybase, after installing Polybase and using the following TSQL statement I was able to connect to Managed Instance, SQL Database and Synapse from my OnPremises or Azure Virtual Machine.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';
CREATE DATABASE SCOPED CREDENTIAL AzureSQLExternalTableCredentials WITH IDENTITY = 'UserName', Secret = 'Password';
CREATE EXTERNAL DATA SOURCE AzureSQLExternalTableDataSource WITH (LOCATION = 'sqlserver://servername.database.windows.net', PUSHDOWN = ON, CREDENTIAL = AzureSQLExternalTableCredentials);
CREATE EXTERNAL TABLE [dbo].[AzureSQLExternalTable_MyTable] ([id] [int] NOT NULL) WITH (DATA_SOURCE = AzureSQLExternalTableDataSource ,location='databasename.schemaname.TableName')
Running a query Select * from AzureSQLExternaTable_MyTable I was able to obtain the data.
Unfortunately, it is not possible to insert data to the table AzureSQLExternalTable_MyTable because external tables in AzureSQL, Synapse and SQL Server OnPrem there is not supported run DML commands.
Enjoy!
Posted at https://sl.advdat.com/3vsKm2Q
