Lesson Learned #110 Azure SQL Managed Instance and Azure DNS

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

I got this recent case where customer wanted to create a transaction replication from Azure SQL Managed instance to another SQL Server inside the same virtual network, but in a different subnet, but all inside Azure.

All communications that happens on Azure Network if not set a custom DNS will use Azure DNS to resolve name of resources inside the network. However there is something very important in the documentation

https://docs.microsoft.com/en-us/azure/virtual-network/virtual-networks-name-resolution-for-vms-and-role-instances#azure-provided-name-resolution

"Azure-provided name resolution

Along with resolution of public DNS names, Azure provides internal name resolution for VMs and role instances that reside within the same virtual network or cloud service. VMs and instances in a cloud service share the same DNS suffix, so the host name alone is sufficient. But in virtual networks deployed using the classic deployment model, different cloud services have different DNS suffixes. In this situation, you need the FQDN to resolve names between different cloud services. In virtual networks deployed using the Azure Resource Manager deployment model, the DNS suffix is consistent across the virtual network, so the FQDN is not needed. DNS names can be assigned to both VMs and network interfaces. Although Azure-provided name resolution does not require any configuration, it is not the appropriate choice for all deployment scenarios, as detailed in the previous table."

Consider a Azure Resource Manager deployment it would not be necessary to use FQDN, however the way that the deployment of Azure SQL Managed Instance happens it will not share the same DNS sufix and we can see connectivity errors

There are some workarounds: use the private IP or the FQDN of the SQL VM. The default DNS sufix is documented in same article above ( https://docs.microsoft.com/en-us/azure/virtual-network/virtual-networks-name-resolution-for-vms-and-role-instances#name-resolution-that-uses-your-own-dns-server ) 

"When you are using Azure-provided name resolution, Azure Dynamic Host Configuration Protocol (DHCP) provides an internal DNS suffix (.internal.cloudapp.net) to each VM. This suffix enables host name resolution because the host name records are in the internal.cloudapp.net zone. When you are using your own name resolution solution, this suffix is not supplied to VMs because it interferes with other DNS architectures (like domain-joined scenarios). Instead, Azure provides a non-functioning placeholder (reddog.microsoft.com)."

For this test I will create a linked server from SQL MI to SQL VM and we can see that I was able to connect with success to IP and VM.internal.cloudapp.net, but failing to simple VM name

This behavior is also documented at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-custom-dns

"An Azure SQL Database Managed Instance must be deployed within an Azure virtual network (VNet). There are a few scenarios (for example, db mail, linked servers to other SQL instances in your cloud or hybrid environment) that require private host names to be resolved from the Managed Instance. In this case, you need to configure a custom DNS inside Azure.

Since Managed Instance uses the same DNS for its inner workings, you need to configure the custom DNS server so that it can resolve public domain names.