SQL Server 2019 Always On using Distributed Network Name

Question

We're running a couple SQL servers in Azure that are set up with an Always On availability group and Windows Failover Clustering. The servers are Windows 2019 and we're running SQL Server 2019. When we set up the cluster, it was set up to use a Distributed Network Name instead of a static cluster IP address. Thanks to this we shouldn't need an internal load balancer according to these notes: https://github.com/MicrosoftDocs/azure-docs/issues/34648.

I'm struggling to understand exactly how this works though. Based on what I read, it seems like our connection strings will point to the DNS name of the cluster (let's call it AgCluster). If I look in DNS, there is an A record for AgCluster pointing to sql1 and another pointing to sql2. When I use AgCluster in my connection string it seems to always connect me to the primary server, even if I have ApplicationIntent=ReadOnly set. When I query @@SERVERNAME I always get the same server.

So with the Distributed Network Name setup, what should I use in my connection strings to make sure read/write queries go to the primary and read only go to a secondary? Any guides on setting this up in general would be helpful. Thanks!

Answer 1

Something you were trying to do in May 2020 is doable now. In SQL Server 2019 CU8 (released 01/10/2020) they have added support for Distributed Network Name.

Have a look at this KB4578579 - Improvement: Availability Group listener without the load balancer in SQL Server 2019

Also for instructions on how to configure dnn take a look at this atricle Configure a DNN for failover cluster instance

Answer 2

Alright not sure if this will help anyone else, but I'll detail the answer. So apparently with Windows Server 2019 when you install a failover cluster, by default it uses a "Distributed Server Name" and doesn't add the cluster IP that you see in guides such as this https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-tutorial. There isn't even an option in the UI to create the IP. You have to use powershell with a special switch to have it create the cluster IP:

New-Cluster -Name -ManagementPointNetworkType Singleton -Node –StaticAddress -NoStorage

that -ManagementPointNetworkType is the key change. Without the cluster IP the Azure configuration doesn't work. I didn't understand the note in the guide above about this and thought that you could get away without the IP.

I'm still not sure what exactly was happening, but when I deleted my cluster and recreated it with the IP and then set up my internal load balancer again everything started working properly. If you're going to try this and are worried about downtime I was able to destroy and recreate my always on groups and windows failover cluster with almost 0 downtime (just had to restart SQL service a couple times).