SQL on Azure VM AlwaysOn with DNN listener

Question

Hi:

I have created a 2 nodes SQLServer AlwaysOn on Azure VM, with DNN listener. SQLServer version is 2019 CU15, Window version is 2019.

e.g.
node1 (10.0.2.15)
node2 (10.0.2.16)
WindowCluster (DNN)
DNNListener (DNN, port 6789)

WindowCluster and DNNListener is pingable and it returns either node1 or node2 ip

I have tried to failover SQLServer AlwaysOn a couple times, e.g. failover primary replica from node2 to node1. After that, checked and SQLServer Availability dashboard shows that node1 is the primary replica.

When I ping WindowCluster and DNNListener in Dos prompt, both returns 10.0.2.16, which is node2 ip.

1. Want to check, should WindowCluster and DNNListener ping return primary replica host ip in this case? If it should always return primary replica host ip, any suggestion on where I should check/fix the problem?
2. I did some testing in SSMS, by setting "DNNListener, 6789" as the server name, and adding "MultiSubnetFailover=True" in the Additional Connection parameters. e.g. node2 is the primary replica and the session connects to node2.
   After I failover AlwaysOn, e.g. from node2 to node1, and refresh the "DNNListener, 6789" session, it is still connecting to node2 after refresh.
   I have tried to disconnect the session and connect back again in SSMS using "DNNListener,6789". Still connecting to node2 (checked Availability group, showing it is the secondary). If I close SSMS and open it again, connect to "DNNListener,6789", then it is now connect to node1(the primary node).
   Is it normal or something not right?

Thanks.

Answer 1

Hi,

I have tried to failover SQLServer AlwaysOn

Your description sounds like you run failover on the SQL Server but not the Window Cluster (both VM running well) and therefore in the SQL Server level you moved to the secondary but checking the information in the Window Cluster level the primary machine is running well. I assume that you follow something like this procedure:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

Don't confuse between failover of the SQL Server and the fact the machines both can stay available.

So how the application should know to point the right SQL Server?

As documented here well: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

Once you've configured your availability group listener, you'll need to update your connection string to connect to the Always On availability group listener. This will route traffic from your application automatically to the intended replica