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.
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:
Define a schema and a view for one or more of the schema objects in Oracle – and then test the view:
On your Azure SQL MI server – create a linked server to the local address of the Azure SQL Virtual Machine:
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.
Leave Server Options at default – unless you plan on using RPC. You should now have a new linked server:
Test access to Oracle using a 4-part name query:
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:
Create the Linked Server using Microsoft OLEDB for ODBC driver and the DSN created above:
On the security tab, set the user id and password to use to connect.
Expand the new linked server to verify that you can see the Teradata tables:
Create a view for every object you want to expose:
Create a Linked Server on your SQL MI instance to your SQL VM:
With the appropriate security settings (only tested standard security):
Access the Teradata data using a 4-part name query:
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:
Create the Linked Server using Microsoft OLEDB for ODBC driver and the DSN created above:
On the security tab, set the user id and password to use to connect:
Expand the new linked server to verify that you can see the PostgreSQL tables:
Create a Linked Server on your SQL MI instance to your SQL VM:
With the appropriate security settings (only tested standard security):
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;
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;
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!