Edit

Configure a DNN listener for an availability group

  • Article

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:

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:

  1. Open a text editor, such as Notepad.

  2. 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

  3. Save the script as a .ps1 file, such as add_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:

  1. Open a command-line interface tool, such as command prompt or PowerShell.

  2. Navigate to where you saved the .ps1 script, such as c:\Documents.

  3. 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:

View the DNN listener under availability group listeners in SQL Server Management Studio (SSMS)

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:

Use sys.availability_group_listeners to identify DNN listeners that have a value of 1 in is_distributed_network_name

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:

  1. Connect to the DNN listener or one of the replicas by using SQL Server Management Studio (SSMS).
  2. Expand Always On Availability Group in Object Explorer.
  3. Right-click the availability group and choose Failover to open the Failover Wizard.
  4. Follow the prompts to choose a failover target and fail the availability group over to a secondary replica.
  5. Confirm the database is in a synchronized state on the new primary replica.
  6. (Optional) Fail back to the original primary, or another secondary replica.

Test connectivity

Test the connectivity to your DNN listener with these steps:

  1. Open SQL Server Management Studio.
  2. Connect to your DNN listener.
  3. Open a new query window and check which replica you're connected to by running SELECT @@SERVERNAME.
  4. Fail the availability group over to another replica.
  5. 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: