When you migrate your SQL Server data estate to Azure SQL Database managed instance, what you get is not just a good old SQL Server, secured and enriched with the newest features, and without a burden of managing backups, patching, and maintaining high availability. You also unlock enormous opportunities for creating extra value out of your existing data, by combining it with other data sources, leveraging cloud-only assets, and engaging it in new and modern scenarios, all of that powered by Microsoft cloud services that integrate easily and securely with managed instance.
This article gives you a detailed overview of available options for integration of Azure SQL Database managed instance with other Microsoft cloud services and provides an overview of common scenarios enabled by the integration. Many services can be integrated with managed instance in multiple ways, as summarized at the end of the article. Detailed description for each of the integration methods should help you choose the optimal one for your scenario.
What are the options for connecting cloud service to SQL Database managed instance?
Azure SQL Database managed instance is always deployed within customer’s Azure virtual network (VNet) and by default it is not accessible from outside of the VNet. This means that connecting to managed instance from Microsoft cloud services may require taking specific steps that vary depending on the concrete service.
There are three ways to connect Microsoft cloud service to a managed instance:
- Deploying service within the VNet hosting managed instance, or within a connected VNet – both options reserved for services that can be deployed within a VNet
- On-premises data gateway. Don’t take “on-premises” attribute here literally
- Leveraging public endpoint of managed instance
Let’s go through the details of all three options and use cases that each of them suits best.
Deploying service into the same or connected VNet
This option is reserved for Azure services that can be deployed into a virtual network. App Service Environments (ASE), Azure Kubernetes Service (AKS), Redis Cache, Virtual Machine Scale Sets, Azure Data Factory SSIS Integration Runtime (IR) are examples of such services, and the full list can be found here.
Things are straightforward if everything is deployed in the same VNet. Just make sure to leave enough IP address space within the VNet to create a separate subnet to deploy service into - managed instance requires a homogeneous subnet and can share a subnet only with other managed instances. Of course, you need to set the NSG rules on both subnets to allow traffic on port 1433 between them.
If you plan to deploy service into a different VNet, the two VNets need to be connected. You can connect two VNets via VNet peering or VPN gateways. To learn about differences and what option to choose for your scenario, check Choosing between Azure VNet Peering and VNet Gateways blog post. Note though that at the time of writing this article (December 2019) VNet peering cannot be used for integration with managed instance if VNets are in different Azure regions, due to a constraint related to peering and type of load balancer used for managed instance.
When to choose VNet integration?
This is the recommended way of integration. It should be used whenever involved services support deployment within a VNet, as the most secure deployment option.
Using on-premises data gateway
On-premises data gateway acts as a bridge to provide fast and secure data transfer between your non-internet routable data sources and several Microsoft cloud services. These cloud services include Power Platform (Power BI, Power Automate, PowerApps), Azure Analysis Services, and Azure Logic Apps.
“On-premises” data gateway is a slightly misleading name, as Microsoft cloud services nowadays can use it for data transfer not just from the on-premises data sources, but also from the Azure services deployed into virtual network, including Azure SQL Database managed instance.
Installing and configuring on-premises data gateway
To install on-premises data gateway on a virtual machine in Azure, follow these step-by-step instructions. Don’t choose personal mode unless it will be used for Power BI only, and by you only . The article also covers creating high-availability configuration in a form of a cluster of multiple on-premises data gateways.
Once on-premises data gateway is installed, you need to configure the Network Security Group (NSG) on the subnet containing virtual machine hosting on-premises data gateway, so that on-premises data gateway can communicate outside of the subnet. The detailed instructions for configuring Network Security Group (NSG) rules on the subnet can be found here.
You will notice that there is no inbound traffic needed to the on-premises data gateway. It relies on the Azure Service Bus for cloud connectivity and establishes outbound connections to the region of Microsoft cloud service. Of course, Network Security Group (NSG) rules also need to enable outbound traffic to managed instance’s subnet. Accordingly, NSG rules on the managed instance’s subnet need to allow inbound traffic from the on-premises data gateway.
When to choose on-premises data gateway?
On-premises data gateway is a solution available to the subset of Microsoft cloud services. Use it if you are ok with a few specifics:
- You can use only SQL authentication when connecting to managed instance this way, with credentials encrypted and stored on the on-premises data gateway. User credentials from Microsoft cloud service cannot be passed onto the instance or database via the gateway. This means that database features like row-level security cannot be leveraged with on-premises data gateway.
- You need to provision and maintain virtual machine to host on-premises data gateway. It’s recommended practice to regularly upgrade on-premises data gateway to the newest version.
- You may need to monitor and optimize performance of on-premises data gateway depending on the intensity of workload and amount of data transferred.
- There is certain latency introduced when querying managed instance via on-premises data gateway, due to additional components in the data flow.
Leveraging public endpoint access to managed instance
There are many cloud services simply not supported by on-premises data gateway. There are also scenarios where creating and maintaining on-premises data gateway to enable access from outside the virtual network customers consider too expensive and a kind of an overkill. Even though it may sound counter-intuitive, there are also cases where companies enforce usage of public endpoint for security reasons.
Public endpoint is an opt-in feature of Azure SQL Database managed instance that enables data access to managed instance from outside of the VNet. It is a great example of Microsoft listening the voice of customers and making their everyday work easier.
Here are some of the use cases for public endpoint:
- Integrating with cloud services – the article you are reading is all about this
- Higher data throughput is needed than possible when using a VPN connection.
- Company policies prohibit access to PaaS service from within corporate network.
Accessing managed instance through the public endpoint
The first step is to enable public endpoint for the managed instance, as it is disabled by default.
Access to a managed instance through the public endpoint requires using specific host name format <mi_name>.public.<dns_zone>.database.windows.net and fixed port 3342. Specific host name format is needed so that it can be resolved to the public IP address, and not instance’s private IP address within the VNet. This is an example of providing such a host name and port when using SQL Server Management Studio (SSMS) to connect to a managed instance, using Azure Active Directory authentication:
Azure portal provides an easy way to copy the connection string targeting public endpoint, in addition to the standard private endpoint connection string, for multiple client drivers. You can find it on the Connection strings tab of the managed instance Azure portal blade:
If you are using Business Critical service tier for your managed instance, you can use public endpoint to connect to a free read-only replica by simply adding ApplicationIntent=ReadOnly property to your connection string. This is very useful in scenarios where cloud service issues only read queries, since generated read workload will be offloaded from the primary replica and leave more resources for other read-write workloads.
Of course, to be able to connect to a managed instance through the enabled public endpoint, you need to allow inbound traffic on destination port 3342, via Network Security Group (NSG) rules of the subnet hosting managed instance. Scope the source IP address range as narrow as you can. Make sure you go through the detailed instructions for securing public endpoint.
When to choose public endpoint?
When enabled, public endpoint can be accessed by any application or cloud service from outside your private IP address space that generally can connect to SQL Server, as long as it uses correct host name format and port 3342, and if inbound traffic from its public IP address is allowed on the NSG. When choosing between on-premises data gateway (if it supports concrete cloud service) and public endpoint, consider the following aspects of public endpoint:
- Public endpoint support Azure Active Directory authentication, unlike on-premises data gateway which is limited to SQL authentication.
- Redirect connection type, which offers latency and throughput performance improvements over Proxy connection type is not supported with public endpoint. You still can set Redirect connection type for your instance, and it will be used for connections through private endpoint, but public endpoint connections will fall back to Proxy connection type.
- There is no auto-failover group listener for public endpoint. If you are using auto-failover groups as a disaster recovery feature of managed instance, you are probably also using auto-failover group listener for transparent reconnect to a new primary after failover. It’s currently available only for private endpoint of managed instance.
-
While general recommendation is to lock down inbound connectivity and scope the source IP address range as much as possible, preferably to the level of concrete IP address, it’s not always possible as some cloud services use wider address range. In that case try to scope down the source IP range, make it always up to date, and minimize complexity of security rules by leveraging service tags.
Summary
Now that we went through the details of all three options for integrating Microsoft cloud services with Azure SQL Database managed instance hopefully you can choose the right one for your specific scenario.
There are more than a hundred of different Microsoft cloud services, and new ones are being introduced from time to time. Even though integration with database is not applicable to all of them, maintaining full matrix of integration options would go way beyond the scope of a blog post. Here is the summary for a limited subset of services commonly used with managed instance:
| Service | VNet integration | Data gateway | Public endpoint |
| Azure App Service | Yes* | Yes | |
|
Azure Functions |
Yes** |
Yes | |
|
PowerBI |
|
Yes |
Yes |
|
Azure Analysis Services |
|
Yes |
Yes |
|
Power Apps |
|
Yes |
Yes |
|
Power Automate |
|
Yes |
Yes |
|
Azure Logic Apps |
Yes*** |
Yes |
Yes |
|
Azure Stream Analytics |
|
|
Yes |
|
Dynamics 365 - Data Export Service |
|
|
Yes |
* With App Service Environments (ASE) only
** Premium plan for Azure Functions required
*** With Integration Service Environments (ISE) only
Next Steps
This is the first one in a series of articles related to integration of Azure SQL Database managed instance with other Microsoft cloud services. In the next few articles we will cover integration with specific services going to the next level of details, starting from PowerBI.