SQL Server – Cannot Add Always On Availability Group Listener to Second Subnet

alwaysonsql serverwindows-cluster

I have a WSFC cluster between two 2016 nodes – DB1 and DB2.

(Cluster Network 1 and 3 are set to NOT carry cluster traffic.)

Failover cluster manager shows that Cluster Network 2 has two subnets:

10.70.119.192/26

10.68.79.240/28

Node DB1 is on here, and has IP addresses in windows of:
10.70.119.212

10.68.79.254

Failover cluster manager shows that Cluster Network 4 has two subnets:

10.136.98.0/26

10.137.72.224/28

Node DB2 is in here, and has IP addresses in windows of:
10.136.98.40

10.137.72.238

For reference, I had previously set up an availability group listener (AG2) just fine. It uses static IPs of:
10.136.98.15

10.70.119.214

All is well with AG2.

Now, I want to set up AG1 on the other subnet.

USE [master]

GO

ALTER AVAILABILITY GROUP [AG1]

ADD LISTENER N'ag111' (

WITH IP

((N'10.137.72.228', N'255.255.255.240'),

(N'10.68.79.242', N'255.255.255.240')

)

, PORT=1433);

GO

(The above is generated from the Wizard. The wizard allowed me to choose the subnet from the dropdown – so its clear SQL knows about those subnets. )

Running this fails with:

Msg 41009, Level 16, State 7, Line 3
The Windows Server Failover Clustering (WSFC) resource control API returned error code 5894.  If this is a WSFC availability group, the WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid.  Otherwise, contact your primary support provider.  For information about this error code, see "System Error Codes" in the Windows Development documentation.
Msg 19476, Level 16, State 3, Line 3
The attempt to create the network name and IP address for the listener failed. If this is a WSFC availability group, the WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. Otherwise, contact your primary support provider.
Msg 19476, Level 16, State 1, Line 3
The attempt to create the network name and IP address for the listener failed. If this is a WSFC availability group, the WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. Otherwise, contact your primary support provider.

What I see in cluster logs is this:

00000c68.00001c34::2019/05/04-19:55:01.196 INFO  [RCM] rcm::RcmApi::CreateResource: (AG1, AG1_10.137.72.228, 7b048e90-89fc-4755-9ddf-19778818fc86, IP Address)
00000c68.00001c34::2019/05/04-19:55:01.196 INFO  [GUM] Node 2: executing request locally, gumId:1593, my action: /rcm/gum/CreateResource, # of updates: 1
00000c68.00001c34::2019/05/04-19:55:01.196 INFO  [RCM] rcm::RcmGum::CreateResource(AG1_10.137.72.228,7b048e90-89fc-4755-9ddf-19778818fc86,AG1)
00001c24.00002408::2019/05/04-19:55:01.197 INFO  [RHS] Registering HDL( 2089aead170 ) as a valid RHS resource handle
00001c24.00002408::2019/05/04-19:55:01.197 INFO  [RHS] OpenResource: opening resource AG1_10.137.72.228 of type IP Address with handle HDL( 2089aead170 )
00001c24.000003f0::2019/05/04-19:55:01.201 INFO  [RES] IP Address <AG1_10.137.72.228>: Unable to open node parameters key, status 2.
00001c24.000003f0::2019/05/04-19:55:01.201 INFO  [RES] IP Address <AG1_10.137.72.228>: Resource open, resource ID = 2590498176.
00000c68.00001488::2019/05/04-19:55:01.201 INFO  [RCM] HandleMonitorReply: OPENRESOURCE for 'AG1_10.137.72.228', gen(0) result 0/0.
00000c68.00001c34::2019/05/04-19:55:01.204 INFO  [GUM] Node 2: executing request locally, gumId:1594, my action: /dm/update, # of updates: 1
00001c24.00002408::2019/05/04-19:55:01.210 INFO  [RES] IP Address <AG1_10.137.72.228>: IpaValidatePrivateResProperties: attempting to change property Address to from 0.0.0.0 to 10.137.72.228.
00001c24.00002408::2019/05/04-19:55:01.210 INFO  [RES] IP Address <AG1_10.137.72.228>: IpaValidatePrivateResProperties: attempting to change property SubnetMask from 0.0.0.0 to 255.255.255.240.
00001c24.00002408::2019/05/04-19:55:01.214 ERR   [RES] IP Address <AG1_10.137.72.228>: IpaValidatePrivateResProperties: failed to find cluster network matching address 10.137.72.228 and subnet mask 255.255.255.240. Error: 259.
00001c24.00002408::2019/05/04-19:55:01.214 ERR   [RHS] Error 5894 from ResourceControl 20971654 for resource AG1_10.137.72.228.
00000c68.00001488::2019/05/04-19:55:01.215 INFO  [RCM] rcm::RcmApi::DeleteResource: (AG1_10.137.72.228, force=false)
00001c24.00002408::2019/05/04-19:55:01.215 ERR   [RES] IP Address <AG1_10.137.72.228>: IpaRetrieveLeaseDetails: Unable to read the 'Network' property. Error: 13.
00001c24.00002408::2019/05/04-19:55:01.215 ERR   [RES] IP Address <AG1_10.137.72.228>: IpaReleaseLeaseWhileOffline: IpaRetrieveLeaseDetails failed, 13.
00001c24.00002408::2019/05/04-19:55:01.215 INFO  [RES] IP Address <AG1_10.137.72.228>: IpaResourceControl, DELETE: IpaReleaseLeaseWhileOffline failed status 13.
00001c24.00002408::2019/05/04-19:55:01.215 ERR   [RHS] Error 13 from ResourceControl 22020102 for resource AG1_10.137.72.228.
00000c68.00001488::2019/05/04-19:55:01.215 INFO  [RCM] Letting all nodes complete any pending resource controls for resource AG1_10.137.72.228
00000c68.00001488::2019/05/04-19:55:01.218 INFO  [GUM] Node 2: executing request locally, gumId:1595, my action: /rcm/gum/DeleteResource, # of updates: 1
00000c68.00001488::2019/05/04-19:55:01.218 INFO  [RCM] rcm::RcmGum::DeleteResource(AG1_10.137.72.228)
00001c24.00002408::2019/05/04-19:55:01.218 INFO  [RHS] Closing resource with handle HDL( 2089aead170 )
00001c24.00002408::2019/05/04-19:55:01.218 INFO  [RHS] Close for resource with handle HDL( 2089aead170 ) has completed, releasing memory
00001c24.000003f0::2019/05/04-19:55:01.219 INFO  [RES] IP Address <AG1_10.137.72.228>: IpaClose entry
00001c24.000003f0::2019/05/04-19:55:01.219 INFO  [RES] IP Address <AG1_10.137.72.228>: IpaReleaseResource entry
00001c24.000003f0::2019/05/04-19:55:01.219 INFO  [RES] IP Address <AG1_10.137.72.228>: Resource closed.
00001c24.000003f0::2019/05/04-19:55:01.219 INFO  [RHS] Removing handle HDL( 2089aead170 ) from valid RHS resource handles
00000c68.00001488::2019/05/04-19:55:01.223 INFO  [GUM] Node 2: executing request locally, gumId:1596, my action: /dm/update, # of updates: 1

So the error seems clear – its not permissions etc, but:

IpaValidatePrivateResProperties: failed to find cluster network matching address 10.137.72.228 and subnet mask 255.255.255.240. Error: 259.

But how can that be? Cluster Manager clearly shows network 10.137.72.224/28 as a valid subnet on the network. Why does it think that IP is not valid?

(Trying this with the actual IPs registered in windows yields same result. AG2 doesn't use a registered one either).

If I try add a "Client access point" directly in WSFC, as opposed to the SSMS wizard, it does not allow me to enter a static IP address from the subnets I'm trying to use here. Only shows the first subnet (that has AG2 on it).

Many thanks

Best Answer

Pretty sure this is a bug in the UI for WSFC / SSMS.

Using powershell, I added the IP addresses manually, and the network name, tied it all together with dependencies, and it all works.

Some scripts to get anyone going who needs similar:


Add-ClusterResource -Name "IPAddress1" -ResourceType "IP Address" -Group "AG1" 
Get-ClusterResource -Name IPAddress1 | Set-ClusterParameter -Multiple @{"Network" = "NET_PRIV_DB2";"Address" = "10.137.72.228";"SubnetMask" = "255.255.255.240";"EnableDHCP" = 0} 

Add-ClusterResource -Name "IPAddress2" -ResourceType "IP Address" -Group "AG1" 
Get-ClusterResource -Name IPAddress2 | Set-ClusterParameter -Multiple @{"Network" = "NET_PRIV_DB1";"Address" = "10.68.79.242";"SubnetMask" = "255.255.255.240";"EnableDHCP" = 0} 


Add-ClusterResource -Name "ag1_sql_ag" -Group "AG1" -ResourceType "Network Name" 
Get-ClusterResource -Name "ag1_sql_ag" | Set-ClusterParameter -Multiple @{"DnsName" = "ag1_sql_ag";"RegisterAllProvidersIP" = 1} 

Set-ClusterResourceDependency -Resource ag1_sql_ag -Dependency "[IPAddress1] or [IPAddress2]" 
Start-ClusterResource -Name ag1_sql_ag -Verbose 



Add-ClusterResourceDependency -Resource 'AG1' -Provider 'ag1_sql_ag'
Related Topic