In this post you will see how to set up an Azure SQL Managed Instance and connect to over your onPrem network without a public endpoint published in Azure.

Install Azure SQL Managed Instance (PaaS)




For this LAB environment I throttel the controller for vCores and Storage to a minimum available and it is also spendy enough 🙁


The Administrator account (Managed Instance admin login) will be the SQL Server Sysadmin account you can later login with the SQL Management Studio.



As you can see the managed instance needs a dedicated subnet in your VNet.


By now I only have one subnet in my VNet which is used by a virtual machine. So because the managed instance needs a dedicated subnet without any existing resource in it, I can’t select this one. But in case to access the managed instance without a public endpoint and instead the existing IPSec VPN tunnel from my onPrem network, I need to use a subnet within the VNet which is tunneled to my onPrem network.

Network requirements
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/connectivity-architecture-overview#network-requirements

The SQL Managed Instance subnet can’t contain any other cloud service that’s associated with it, and it can’t be a gateway subnet. The subnet can’t contain any resource but SQL Managed Instance, and you can’t later add other types of resources in the subnet.




Here you can see the existing subnets by now, sub10 is the one with the virtual machine.



So I created a new subnet sub20 to use dedicated for the Azure SQL managed instance.




You can see that Azure can determine if a subnet is in use or empty, so the newly created sub20 subnet is empty and therfore can be selected for the managed instance to use.










The Connection type (private endpoint) you can change later on the fly between Proxy (default) and Redirect (recommended) in the SQL managed instanc under SecurityNetworking. Screenshot below!

Azure SQL Managed Instance connection types
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/connection-types-overview

The redirect connection type currently works only for a private endpoint. Regardless of the connection type setting, connections coming through the public endpoint would be through a proxy.



Changing the Connection type afterwards.








Under Additional settings you can change the Collation, the Time zone and the replication settings of your database instance.







Quickstart: Create a managed instance of SQL Managed Instance
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/instance-create-quickstart

Creation of SQL Managed Instance is a long-running operation that might take a few hours at a time, depending on specific circumstances

In my LAB environment it takes about 4 hours, so grab a used to the next coffee ☕ …


The deployment is based on a virtual cluster which contains of a set of isolated virtual machines that run inside your VNet subnet. So no matter what resource group you choose for the managed instance, this virtual cluster will be created in the resource group of your VNet. The other components will be created in the resource group you selected. You will see this in the screenshots below.





After finishing the deployment of the managed instance, you will have the following further objects in your Azure tenant/directory.

  • Azure sql managed instance
  • nsg network security group
  • Routing table
  • Virtual Cluster (created automatically in the resource group of your VNet)

Virtual Cluster
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/connectivity-architecture-overview#high-level-connectivity-architecture

At a high level, SQL Managed Instance is a set of service components. These components are hosted on a dedicated set of isolated virtual machines that run inside the customer’s virtual network subnet. These machines form a virtual cluster.

A virtual cluster can host multiple managed instances. If needed, the cluster automatically expands or contracts when the customer changes the number of provisioned instances in the subnet.


Here you will see the created virtual cluster for the SQLmanaged instance which will be placed automatically inside the resource group of the VNet.



When you click above on the virtual cluster you will find inside the virtual cluster overview page the created SQL managed instance.




All other resources like network security group, route table and the SQL managed instance will be created in the selected resource group.





Connect to Azure SQL Managed Instance from onPrem network over policy-based vpn


There is no need to adjust the routing table or inbound/outbound rules in the network security group.

By default the routing between the SQL managed instance subnet and your onPrem network, in case it is in the same VNet you have set up your S2S VPN tunnel with the Gateway Subnet, will do the Gateway subnet.

In the network security group there are by default an inbound and outbound rule for Port 1433 and also for Port 11000-11999 with source and destination tag VirtualNetwork.

The tag VirtualNetwork includes the virtual network address space (CIDR ranges defined in Azure), all connected on-premises address spaces, and connected Azure VNets (Peering or VPN Gateway).

So your onPrem network traffic to the managed instance by default will not be filtered on Azure site, but keep in mind, that you need to open the ports outbound on your onPrem Firewall.



Inbound security rules of the Network security group (NSG)



Outbound security rules of the Network security group (NSG)



NSG Best practice
http://gowie.eu/index.php/azure/best-practice/23-nsg-best-practice






Connect with SQL Management Studio from onPrem



Determine the Host (FQDN) from the overview page of Azure SQL managed instance.



Enter this host FQDN and you credentials you entered at creating the instance below. Therefore switch in SQL Management Studio from Windows Authentication to SQL Server Authentication.





And connected! From now on I can create the first dabase in my managed instance (PaaS).








Links


Quickstart: Create a managed instance of SQL Managed Instance
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/instance-create-quickstart

Connectivity architecture for Azure SQL Managed Instance
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/connectivity-architecture-overview

Connect your application to Azure SQL Managed Instance
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/connect-application-instance