KB4537868 - Improvement: Enable DNN feature in SQL Server 2016 and 2019 FCI

Improvement

The SQL Server 2019 and 2016 Failover Cluster Instance (FCI) listener is enhanced to work together with the Windows Server Failover Cluster (WSFC) Distributed Network Name (DNN) access point.

More information

SQL Server Failover Cluster Instance (FCI) listener currently works only together with  the Windows Server Failover Cluster (WSFC) Network Name and Virtual IP access point. Because Virtual IP does not work in the Azure environment, you have to configure an Azure Internal Load Balancer to work around this issue (See how to configure an Azure Internal Load Balancer).

This update provides another way for the SQL Server client to connect with FCI without a load balancer using the Distributed Network Name (DNN) resource in a Windows Server Failover Cluster. When a DNN resource is created, WSFC binds the DNN DNS name to the IP addresses of all nodes in the cluster. The SQL Server client will try to connect each IP address in this list to find the node that FCI is currently running on. This connection process is further accelerated by connecting all IP addresses in parallel if the SQL Server connection property MultiSubnetFailover is true. This enables the SQL Server client to connect to the currently running FCI instantly.

Compared to the previous workaround of using the Azure Internal Load Balancer, the DNN listener approach avoids the additional failover latency that's introduced by load balancer liveness probing. By default, that process takes 10-15 seconds. (See this Azure document about how to calculate the latency.) You don't have to configure and maintain the load balancer components. This simplifies the provisioning process. Removing the load balancer also removes one component that can potentially fail. This improves overall robustness.

The following steps are required to use this feature:

1. For an installed FCI, you have to create a DNN resource, and set its DNS name. Run the following three PowerShell commands as an administrator:

   • Add-ClusterResource -Name <dnnResourceName> -ResourceType "Distributed Network Name" -Group "<WSFC role of SQL server instance>"

     Get-ClusterResource -Name <dnnResourceName> | Set-ClusterParameter -Name DnsName -Value <DNSName>

     Start-ClusterResource -Name <dnnResourceName>

   For example:

   • Add-ClusterResource -Name dnn-demo -ResourceType "Distributed Network Name" -Group "SQL Server (MSSQLSERVER)"

     Get-ClusterResource -Name dnn-demo | Set-ClusterParameter -Name DnsName -Value dnnlsnr

     Start-ClusterResource -Name dnn-demo

   Explanation:

   • The first command adds a DNN resource to the WSFC by having a resource name of <dnnResourceName>. The resource name is used by WSFC to uniquely identify a WSFC resource. Use one that makes sense to you and is unique across the WSFC cluster. The resource type must be Distributed Network Name. The name of the group that this DNN resource belongs to must be the WSFC resource group (role) that corresponds to the FCI that you want to add the DNN resource to. The typical format of this group name is “SQL Server (instance name).” Therefore, for the default instance, the name will be “SQL Server (MSSQLSERVER)." You can also check the name of the group in Failover Cluster Manager console.

   • The second command sets the DNS name of this DNN resource. The DNS name is important because it's the name that clients use to connect to the FCI.

   • The third command start the DNN resource.

     By default, the DNN DNS name binds to all nodes in WSFC. Configure the possible owner of the DNN resource to include only the nodes of this FCI if not all nodes in WSFC participate in FCI.

2. Restart SQL Server instance.

3. Replace the Virtual Network Name (VNN) in SQL client connection string with the DNN DNS name, and set the MultiSubnetFailover property to "true." You can skip this setting if the SQL client version is later than 4.6.1.

Resolution

This improvement is included in the following cumulative update for SQL Server:

• Cumulative Update 2 for SQL Server 2019

About cumulative updates for SQL Server:

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

• Latest cumulative update for SQL Server 2019

Service pack information for SQL Server 2016

This issue is fixed in the following service pack for SQL Server:

• Service Pack 3 for SQL Server 2016

References

Learn about the terminology that Microsoft uses to describe software updates.