I have an availability group that has a listener.

The listener is called SQLPROD as you can see on the picture below. It uses port 1433

enter image description here

When I run the following script:

    ADD LISTENER 'SQLPROD' (WITH IP (('', '')));

I get this error message (when it already exists):

Msg 19477, Level 16, State 48, Line 4 The availability group 'SQLPROD_AG' already has a listener with DNS name 'SQLPROD_AG_SQLPROD'. Availability groups can have only one listener. Use the existing listener, or drop the existing listener and create a new one.

using the Always On Availability Groups Catalog Views I haven't found anything revealing:

select * from sys.dm_hadr_auto_page_repair  
select * from sys.dm_hadr_cluster_networks
select * from sys.dm_hadr_availability_group_states 
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.dm_hadr_availability_replica_cluster_nodes    
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_availability_replica_cluster_states   
select * from sys.dm_hadr_instance_node_map
select * from sys.dm_hadr_availability_replica_states   
select * from sys.dm_hadr_name_id_map
select * from sys.dm_hadr_cluster   
select * from sys.dm_tcp_listener_states
select * from sys.dm_hadr_cluster_members   

select * from sys.availability_databases_cluster    
select * from sys.availability_groups_cluster
select * from sys.availability_group_listener_ip_addresses  
select * from sys.availability_read_only_routing_lists
select * from sys.availability_group_listeners  
select * from sys.availability_replicas
select * from sys.availability_groups

All seems to be fine:

enter image description here

when I use powershell, the following commands:

Import-Module FailoverClusters  

enter image description here

Where can I find where the name SQLPROD_AG_SQLPROD is? Can I change this name?

  • 1
    What is the actual issue? The DNS name for that listener is already SQLPROD (at least according to your screenshots), is it not responding on that name? The name SQLPROD_AG_SQLPROD is the name of the Virtual Network Name resource in the cluster that represents the listener (last row in your PS screenshot), but this doesn't mean it is the actual listener DNS name. The error in SQL is because you already have a listener. If you want to modify the DNS name of a listener, you must remove it then create a new listener. – HandyD Feb 17 at 22:17
  • 1
    @HandyD the confusion was clarified in the accepted answer the network name resource (SQLPROD_AG_SQLPROD), and it is labelled incorrectly as the DNS name. – Marcello Miorelli Feb 18 at 10:24

The message that is returned is not accurate as it is referring to the name that it will give the network name resource (SQLPROD_AG_SQLPROD), and it is labelled incorrectly as the DNS name. You can see that the network name resource in your AG is named SQLPROD_AG_SQLPROD, and that is what it's referring to.

It really should say something like, "The availability group 'SQLPROD_AG' already has a listener with a network name resource of 'SQLPROD_AG_SQLPROD'." When the listener is created, the associated network name resource is created with a name according to the following syntax:

<Availability Group Name>_<DNS name>

Since a resource with that name already exists, it can't create it.

Also, underscores aren't allowed in DNS names, so the name given in the error message is obviously not a DNS name.

To see the DNS name associated with the listener, you can use SQL Server Management Studio and view the properties of the listener, or use Failover Cluster Administrator and view the properties of the network name.

The following PowerShell command will return the names and DNS names of all network name resources that are not core cluster resources.

Get-ClusterResource | Where-Object {$_.isCoreResource -eq $false -and $_.ResourceType -eq "Network Name" } | Get-ClusterParameter | Where-Object { $_.Name -eq "DnsName" } | Select ClusterObject, Value

If you want to filter by a specific AG name, you would use Where-Object {$_.OwnerGroup -eq "SQLPROD_AG" -and $_.ResourceType -eq "Network Name' } for the first WHERE clause.

With T-SQL, you can get this from the AG-related views. To list all:

select ag.name, agl.dns_name from sys.availability_group_listeners agl
join sys.availability_groups ag ON ag.group_id = agl.group_id

To list only the one for SQLPROD_AG, you would just need to add a WHERE clause:

WHERE ag.name = 'SQLPROD_AG'

If you're wanting to use a different DNS name for the listener, you have to drop the current listener and create a new one.


See Remove an availability group listener.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.