Improvement: Enable DNN feature in SQL Server 2019 FCI

Applies to: SQL Server 2019 on Windows

Improvement


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

More information


SQL Server Failover ClusterInstance (FCI) listener currently only works with Windows Server FailoverCluster (WSFC) Network Name and Virtual IP access point. Since Virtual IP does notwork in Azure environment, you are required to configure an AzureInternal Load Balancer to work around this issue (See how toconfigure an Azure Internal Load Balancer). This update provides another way forSQL client to connect FCI without a load balancer leveraging the DistributedNetwork Name (DNN) resource in Windows Server Failover Cluster. When a DNNresource is created, WSFC binds the DNN DNS namewith the IP addresses of all nodes in the cluster. SQL client will try toconnect each IP address in this list to find the node that FCI is currently runningon. Such connection process is further accelerated by connecting all IPaddresses in parallel if the SQL connection property"MultiSubnetFailover" is true, enabling SQL client to connect to thecurrent running FCI instantly. Comparing with the previous work around of usingAzure Internal Load Balancer, the DNN listener approach avoids the additionalfailover latency introduced by load balancer liveness probing, which is bydefault 10-15 seconds (See Azure document on how to calculate the latency). You don't need toconfigure and maintain the load balancer components, which simplifies theprovisioning process. Removing the load balancer also removes one component thatcan 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.