Lesson Learned #176: Using Polybase to connect from SQL OnPremises to Azure Managed Instance/SQLDB

Posted by

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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!

 

This articles are republished, there may be more discussion at the original link. But if you found this helpful, you're more than welcome to let us know!

This site uses Akismet to reduce spam. Learn how your comment data is processed.