Configure a DNN listener for an availability group
Applies to: SQL Server on Azure VM
Tip
There are many methods to deploy an availability group. Simplify your deployment and eliminate the need for an Azure Load Balancer or distributed network name (DNN) for your Always On availability group by creating your SQL Server virtual machines (VMs) in multiple subnets within the same Azure virtual network. If you've already created your availability group in a single subnet, you can migrate it to a multi-subnet environment.
With SQL Server on Azure VMs in a single subnet, the distributed network name (DNN) routes traffic to the appropriate clustered resource. It provides an easier way to connect to an Always On availability group (AG) than the virtual network name (VNN) listener, without the need for an Azure Load Balancer.
This article teaches you to configure a DNN listener to replace the VNN listener and route traffic to your availability group with SQL Server on Azure VMs for high availability and disaster recovery (HADR).
For an alternative connectivity option, consider a VNN listener and Azure Load Balancer instead.
Overview
A distributed network name (DNN) listener replaces the traditional virtual network name (VNN) availability group listener when used with Always On availability groups on SQL Server VMs. This negates the need for an Azure Load Balancer to route traffic, simplifying deployment, maintenance, and improving failover.
Use the DNN listener to replace an existing VNN listener, or alternatively, use it in conjunction with an existing VNN listener so that your availability group has two distinct connection points - one using the VNN listener name (and port if non-default), and one using the DNN listener name and port.
Caution
The routing behavior when using a DNN differs when using a VNN. Do not use port 1433. To learn more, see the Port consideration section later in this article.
Prerequisites
Before you complete the steps in this article, you should already have:
- SQL Server starting with either SQL Server 2019 CU8 and later, SQL Server 2017 CU25 and later, or SQL Server 2016 SP3 and later on Windows Server 2016 and later.
- Decided that the distributed network name is the appropriate connectivity option for your HADR solution.
- Configured your Always On availability group.
- Installed the latest version of PowerShell.
- Identified the unique port that you will use for the DNN listener. The port used for a DNN listener must be unique across all replicas of the availability group or failover cluster instance. No other connection can share the same port.
Note
If you have multiple AGs or FCIs on the same cluster and you use either a DNN or VNN listener, then each AG or FCI needs its own independent connection point.
Create script
Use PowerShell to create the distributed network name (DNN) resource and associate it with your availability group.
To do so, follow these steps:
Open a text editor, such as Notepad.
Copy and paste the following script:
param ( [Parameter(Mandatory=$true)][string]$Ag, [Parameter(Mandatory=$true)][string]$Dns, [Parameter(Mandatory=$true)][string]$Port ) Write-Host "Add a DNN listener for availability group $Ag with DNS name $Dns and port $Port" $ErrorActionPreference = "Stop" # create the DNN resource with the port as the resource name Add-ClusterResource -Name $Port -ResourceType "Distributed Network Name" -Group $Ag # set the DNS name of the DNN resource Get-ClusterResource -Name $Port | Set-ClusterParameter -Name DnsName -Value $Dns # start the DNN resource Start-ClusterResource -Name $Port $Dep = Get-ClusterResourceDependency -Resource $Ag if ( $Dep.DependencyExpression -match '\s*\((.*)\)\s*' ) { $DepStr = "$($Matches.1) or [$Port]" } else { $DepStr = "[$Port]" } Write-Host "$DepStr" # add the Dependency from availability group resource to the DNN resource Set-ClusterResourceDependency -Resource $Ag -Dependency "$DepStr" #bounce the AG resource Stop-ClusterResource -Name $Ag Start-ClusterResource -Name $Ag
Save the script as a
.ps1
file, such asadd_dnn_listener.ps1
.
Execute script
To create the DNN listener, execute the script passing in parameters for the name of the availability group, listener name, and port.
For example, assuming an availability group name of ag1
, listener name of dnnlsnr
, and listener port as 6789
, follow these steps:
Open a command-line interface tool, such as command prompt or PowerShell.
Navigate to where you saved the
.ps1
script, such as c:\Documents.Execute the script:
add_dnn_listener.ps1 <ag name> <listener-name> <listener port>
. For example:c:\Documents> .\add_dnn_listener.ps1 ag1 dnnlsnr 6789
Verify listener
Use either SQL Server Management Studio or Transact-SQL to confirm your DNN listener is created successfully.
SQL Server Management Studio
Expand Availability Group Listeners in SQL Server Management Studio (SSMS) to view your DNN listener:
Transact-SQL
Use Transact-SQL to view the status of the DNN listener:
SELECT * FROM SYS.AVAILABILITY_GROUP_LISTENERS
A value of 1
for is_distributed_network_name
indicates the listener is a distributed network name (DNN) listener:
Update connection string
Update the connection string for any application that needs to connect to the DNN listener. The connection string to the DNN listener must provide the DNN port number, and specify MultiSubnetFailover=True
in the connection string. If the SQL client does not support the MultiSubnetFailover=True
parameter, then it is not compatible with a DNN listener.
The following is an example of a connection string for listener name DNN_Listener and port 6789:
DataSource=DNN_Listener,6789;MultiSubnetFailover=True
Test failover
Test failover of the availability group to ensure functionality.
To test failover, follow these steps:
- Connect to the DNN listener or one of the replicas by using SQL Server Management Studio (SSMS).
- Expand Always On Availability Group in Object Explorer.
- Right-click the availability group and choose Failover to open the Failover Wizard.
- Follow the prompts to choose a failover target and fail the availability group over to a secondary replica.
- Confirm the database is in a synchronized state on the new primary replica.
- (Optional) Fail back to the original primary, or another secondary replica.
Test connectivity
Test the connectivity to your DNN listener with these steps:
- Open SQL Server Management Studio.
- Connect to your DNN listener.
- Open a new query window and check which replica you're connected to by running
SELECT @@SERVERNAME
. - Fail the availability group over to another replica.
- After a reasonable amount of time, run
SELECT @@SERVERNAME
to confirm your availability group is now hosted on another replica.
Limitations
- DNN Listeners MUST be configured with a unique port. The port cannot be shared with any other connection on any replica.
- The client connecting to the DNN listener must support the
MultiSubnetFailover=True
parameter in the connection string. - There might be additional considerations when you're working with other SQL Server features and an availability group with a DNN. For more information, see AG with DNN interoperability.
Port considerations
DNN listeners are designed to listen on all IP addresses, but on a specific, unique port. The DNS entry for the listener name should resolve to the addresses of all replicas in the availability group. This is done automatically with the PowerShell script provided in the Create Script section. Since DNN listeners accept connections on all IP addresses, it is critical that the listener port be unique, and not in use by any other replica in the availability group. Since SQL Server listens on port 1433 by default, either directly or via the SQL Browser service, using port 1433 for the DNN listener is strongly discouraged.
If the listener port chosen for the VNN listener is between 49,152 and 65,536 (the default dynamic port range for TCP/IP, add an exclusion for this. Doing so will prevent other systems from being dynamically assigned the same port.
You can add a port exclusion with the following command:
netsh int ipv4 add excludedportrange tcp startport=<Listener Port> numberofports=1 store=persistent
Next steps
Once the availability group is deployed, consider optimizing the HADR settings for SQL Server on Azure VMs.
To learn more, see: