Using Heterogeneous Linked Servers from Azure SQL MI
Published Jan 27 2022 03:25 PM 9,454 Views

Introduction

 

Often when migrating from on premises SQL Servers, the topic of linked server usage comes up because there are different levels of support in Azure. SQL Server running in a virtual machine (IaaS) is the same product as you run on premises, so linked servers are fully support (assuming the target is visible from Azure).  Platform as a Service (PaaS) SQL offerings have different levels of support. Azure SQL DB does not support linked servers at all (although external tables may offer some relief). Azure SQL Managed Instance (MI) has the ability to create linked servers, but only to SQL Server targets (i.e. SQL Server, Azure SQL DB/MI and Synapse SQL). Although extending this capability to other data sources is on the roadmap, this blog provides an immediately available work around. 

 

SQL Server Linked Servers

 

Linked Servers is a capability that allows database access from the SQL Server platform to an OLEDB target. Depending on the target capabilities, you may also be able to call stored procedures on the target platform by enabling the RPC option for the linked server.

 

Linked Servers in Azure SQL MI

 

Although on the roadmap, Azure SQL Managed Instance (MI) does not support linked server creation to any other database engine than flavors of SQL Server. Although not ideal from a price or performance perspective, this paper presents a work around for the current limitation.

 

Non-SQL Server Work Around

 

The proposed work around involves setting up an Azure Virtual Machine (VM) with SQL Server Standard Edition (you may be able to do this with lower cost editions of SQL Server). The database drivers for the target database can be loaded on the VM and the SQL Server on the VM can use these drivers to access the target database platform.

 

The Azure SQL MI instance can be configured to access the tables on the SQL Server on the VM via a linked server on MI. To expose the tables and views on the remote non-SQL Server database engine, we use views in a SQL Server database to access the remote tables using 4 part names.

 

Note that this same work around is possible using an on-premises SQL Server machine/VM. In this case you would need to provide the ability for MI to connect to this SQL Server, instead of exposing the Oracle, Teradata, or whatever on premises database server you are wanting to provide linked server access to, to the Azure SQL VM. For that matter, you can use this capability to expose any data service, that you can connect to with a linked server, whether it is in other Azure VMs or even other clouds but be keep in mind the networking implications (i.e., collocate the VMs on the same virtual network, in the same region/geography etc.).

 

For the Azure SQL MI instance to be able to access the Azure SQL VM, it needs to be on a subnet of the MI virtual network (virtual network). This is typically the same way a jump box for the MI instance is configured. Note that all the traffic between the Azure SQL MI instance and the SQL VM is on a private network. To ensure data privacy while accessing the remote non-SQL Server database, we recommend ExpressRoute, if the database is on premises, or virtual network peering, if it is on another Azure virtual network. If the data is hosted in another cloud, we recommend that the connection use at least TLS to encrypt the data on the wire.

 

MitchvanHuuksloot_0-1643318400940.png

 

Depending on how you provisioned the SQL Server VM, you may need to explicitly set up a firewall rule to allow SQL Server connections. If you provisioned the SQL VM from the SQL Server on Windows marketplace offering, the firewall rule should already be in place.

 

Connecting to an Oracle Server

 

Install and Configure the Oracle Client (64 bit) – it may help to install SQL Developer to verify the connection. If your Oracle server is on premises, you will need to open port 1521 to it, ideally on a private internet connection via a VPN or ExpressRoute.

 

Define the linked server in the SQL Server on the Azure Virtual Machine:

 

MitchvanHuuksloot_1-1643318400956.png

 

Define a schema and a view for one or more of the schema objects in Oracle – and then test the view:

 

MitchvanHuuksloot_2-1643318400972.png

 

On your Azure SQL MI server – create a linked server to the local address of the Azure SQL Virtual Machine:

 

MitchvanHuuksloot_3-1643318400976.png

 

On the Security tab, enter SQL credentials to use to connect to the SQL VM. The ability to use Azure AD credentials or Windows AD with this linked server maybe possible but was not tested.

 

MitchvanHuuksloot_4-1643318400984.png

 

Leave Server Options at default – unless you plan on using RPC. You should now have a new linked server:

 

MitchvanHuuksloot_5-1643318400989.png

 

Test access to Oracle using a 4-part name query:

 

MitchvanHuuksloot_6-1643318400995.png

 

Connecting to a Teradata Server

 

Install the 64-bit Teradata client, ensuring that you install the 64-bit ODBC driver. If your Teradata server is on premises, you will have to open port 1025 to it, ideally on a private internet connection via a VPN or ExpressRoute.

 

Create the System DSN in the 64-bit ODBC Control Panel:

 

MitchvanHuuksloot_7-1643318400999.png

 

Create the Linked Server using Microsoft OLEDB for ODBC driver and the DSN created above:

 

MitchvanHuuksloot_8-1643318401002.png

 

On the security tab, set the user id and password to use to connect.

 

MitchvanHuuksloot_9-1643318401007.png

 

Expand the new linked server to verify that you can see the Teradata tables:

 

MitchvanHuuksloot_10-1643318401009.png

 

Create a view for every object you want to expose:

 

MitchvanHuuksloot_11-1643318401019.png

 

Create a Linked Server on your SQL MI instance to your SQL VM:

 

MitchvanHuuksloot_12-1643318401023.png

 

With the appropriate security settings (only tested standard security):

 

MitchvanHuuksloot_13-1643318401027.png

 

Access the Teradata data using a 4-part name query:

 

MitchvanHuuksloot_14-1643318401035.png

 

Based on a techcommunity post, you can simplify the use of linked servers in MI by using Synonyms.

 

Connecting to a PostgreSQL/Azure Database for PostgreSQL Server

 

Install the 64-bit PostgreSQL pyodbc client from here. You must open port 5432 for communication.

 

Create the System DSN in the 64-bit ODBC Control Panel:

 

MitchvanHuuksloot_15-1643318401043.png

 

MitchvanHuuksloot_16-1643318401047.png

 

Create the Linked Server using Microsoft OLEDB for ODBC driver and the DSN created above:

 

MitchvanHuuksloot_17-1643318401053.png

 

On the security tab, set the user id and password to use to connect:

 

MitchvanHuuksloot_18-1643318401060.png

 

Expand the new linked server to verify that you can see the PostgreSQL tables:

 

MitchvanHuuksloot_19-1643318401070.png

 

Create a Linked Server on your SQL MI instance to your SQL VM:

 

MitchvanHuuksloot_20-1643318401074.png

 

With the appropriate security settings (only tested standard security):

 

MitchvanHuuksloot_21-1643318401078.png

 

Access the PostgreSQL data using a 4-part name query.

 

Performance Considerations

 

Given there is a double hop involved in this work around, your performance will be affected by a number of factors including; the performance of the source database server, firewall and network performance (ExpressRoute or public internet), SQL virtual machine performance, SQL VM network and network card bandwidth limitations, and MI performance. Larger results sets will likely experience more lag but remember this is a temporary work around until the MI dev team can provide heterogeneous linked servers natively.

 

To optimize any queries with joins, you can consider using the OpenQuery push down function. You can expose a view (or table valued function) to MI from the SQL VM as per example below;

 

MitchvanHuuksloot_22-1643318401093.png

 

Alternatively, you can push down directly from the MI server, but since there are two hops, the query developer would need to know the linked server names on both MI and the SQL VM and they would need to nest two OpenQuery statements as per example shown below;

 

MitchvanHuuksloot_23-1643318401106.png

 

Feedback and suggestions

 

If you have feedback or suggestions for improving this data migration asset, please contact the Azure Databases SQL Customer Success Engineering Team. Thanks for your support!

 

1 Comment
Version history
Last update:
‎Nov 09 2023 10:05 AM
Updated by: