Skip to main content
TechNet
sys.availability_group_listener_ip_addresses (Transact-SQL)
 

Updated: June 10, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2012) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Returns a row for every IP address that is associated with any Always On availability group listener in the Windows Server Failover Clustering (WSFC) cluster.

Primary key: listener_id + ip_address + ip_sub_mask

Column nameData typeDescription
listener_idnvarchar(36)Resource GUID from Windows Server Failover Clustering (WSFC) cluster.
ip_addressnvarchar(48)Configured virtual IP address of the availability group listener. Returns a single IPv4 or IPv6 address.
ip_subnet_masknvarchar(15)Configured IP subnet mask for the IPv4 address, if any, that is configured for the availability group listener.

NULL = IPv6 subnet
is_dhcpbitWhether the IP address is configured by DHCP, one of:

0 = IP address is not configured by DHCP.

1 = IP address is configured by DHCP
network_subnet_ipnvarchar(48)Network subnet IP address that specifies the subnet to which the IP address belongs.
network_subnet_prefix_lengthintNetwork subnet prefix length of the subnet to which the IP address belongs.
network_subnet_ipv4_masknvarchar(45)Network subnet mask of the subnet to which the IP address belongs. network_subnet_ipv4_mask to specify the DHCP <network_subnet_option> options in a WITH DHCP clause of the CREATE AVAILABILITY GROUP or ALTER AVAILABILITY GROUPTransact-SQL statement.

NULL = IPv6 subnet
statetinyintIP resource ONLINE/OFFLINE state from the WSFC cluster, one of:

1 = Online. IP resource is online.

0 = Offline. IP resource is offline.

2 = Online Pending. IP resource is offline but is being brought online.

3 = Failed. IP resource was being brought online but failed.
state_descnvarchar(60)Description of state, one of:

ONLINE

OFFLINE

ONLINE_PENDING

FAILED

Permissions

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

Querying the SQL Server System Catalog FAQ
Catalog Views (Transact-SQL)