Which Synapse Connector to use for SQL Server on Azure VM?

Question

We have some SQL Servers on Azure VMs that we would like to use as Synapse sources. The VMs are on an Azure Virtual Network. We use Azure Active Directory for authentication to the database.

The following seem to fall under Azure SQL in the documentation:

1. Azure SQL Database
2. Azure SQL Managed Instance
3. SQL Server on Azure VM

There is also a distinct set of documentation for SQL Server.

When I look in the Synapse documentation under Pipeline and data flow -> How-to guides -> Connectors is see these possible connectors:

1. Azure SQL Database
2. Azure SQL Managed Instance
3. SQL Server

There is no distinct connector listed for SQL Server on Azure VM. When I open up Synapse Studio, go to Manage and try to create a new Linked Service, I am also able to find the same connectors above (one renamed Azure SQL Database Managed Instance), and still nothing like SQL Server on Azure VM or SQL Virtual Machine (another name you can find these under in Azure).

The reason this all makes a difference is that our SQL Servers on Azure VMs are on an Azure Virtual Network, and if they behave like the regular SQL Servers it seems that we will need a self-hosted integration runtime, where as the other Azure SQL types have linked service properties.

Our questions revolve around:

1. Which bits of documentation are most useful for trying to set up a Linked Service on a SQL Server on an Azure VM?
2. Will we need a self-hosted integration runtime, or will we be able to use the Azure IR?
3. Will the Synapse workspace need to be created with a Managed Virtual Network for this?

One final note that isn't a question really, but something to think about. In testing I tried setting up a Synapse workspace with a Managed Virtual Network, hoping I could just create a peer relationship with the existing Azure Virtual Network. However, the Synapse workspace Managed Virtual Network doesn't show up in my list of VNs, so no joy there. Maybe there are considerations I haven't thought about there, but it seems like that would have been the easiest approach to making the 2 VNs communicate easily.

Thanks,
Johnny

Answer 1

Running SQL Server on an Azure VM will be much closer to SQL Server on premises than either Azure SQL Databases or Azure SQL Managed Instances, so that is the best documentation to use.

Azure IR is focused on PaaS Services like Azure SQL Databases and is not currently available for VMs. To connect to your IaaS SQL Server VM you will need to use a self-hosted runtime. This does not require a Managed Virtual Network.

We take feedback from the forums back to the product team, but you can also upvote adding peering functionality to Managed Virtual Networks on UserVoice, which is used when prioritizing features: https://feedback.azure.com/forums/270578-data-factory/suggestions/41061697-azure-data-factory-managed-virtual-network-should.