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

Posted on by — No Comments ↓

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!

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

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