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

Improvement

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

More information

SQL Server Failover Cluster Instance (FCI) listener currently only works with Windows Server Failover Cluster (WSFC) Network Name and Virtual IP access point. Since Virtual IP does not work in Azure environment, you are required 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 SQL client to connect FCI without a load balancer leveraging the Distributed Network Name (DNN) resource in Windows Server Failover Cluster. When a DNN resource is created, WSFC binds the DNN DNS name with the IP addresses of all nodes in the cluster. SQL client will try to connect each IP address in this list to find the node that FCI is currently running on. Such connection process is further accelerated by connecting all IP addresses in parallel if the SQL connection property "MultiSubnetFailover" is true, enabling SQL client to connect to the current running FCI instantly. Comparing with the previous work around of using Azure Internal Load Balancer, the DNN listener approach avoids the additional failover latency introduced by load balancer liveness probing, which is by default 10-15 seconds (See Azure document on how to calculate the latency). You don't need to configure and maintain the load balancer components, which simplifies the provisioning process. Removing the load balancer also removes one component that can potentially fail, which improves overall robustness.

The following steps are required to use this feature:

  • With an installed FCI, you need to create a DNN resource and set its DNS name. Run the following three PowerShell commands as an

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

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

    3. Start-ClusterResource -Name <dnnResourceName>

For example:

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

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

    3. Start-ClusterResource -Name dnn-demo

                     Explanation:

    • The first command adds a DNN resource to the WSFC with resource name as <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 this DNN resource belongs to must be the WSFC resource group (role) corresponding with the FCI you want to add the DNN resource to. The typical format of this group name is “SQL Server (instance name)”, therefore for 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 is the name that clients use to connect to the FCI.

    • The third command start the DNN resource. 

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

  • Restart SQL Server instance.

  • 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 setting this property if the SQL client version is after 4.6.1.

Resolution

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

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:

References

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

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×