1

We've got a multisegment AG Listener named SQLAGL01 which points to these two addresses: 10.143.162.32 (the primary server) 10.140.165.32 (DR server)

Because the 3rd party (which means we can't fix it, and no we can't migrate to something else) application software uses old drivers which don't support MultiSubnetFailover=True, we're seeing issues where reports fail half the time because SQLAGL01 resolves to the DR address 10.140.165.32.

Following the Microsoft docs, we set RegisterAllProvidersIP=0 and restarted the cluster. However, SQLAGL01 still resolves to the DR address half the time.

In Failover Cluster Manager, the AG Listener's owner node is the primary server, and the DR address is offline. (Is that normal?)

Here's a screenshot of the cluster role: enter image description here EDIT: Added RegisterAllProvidersIP value.

PS C:\windows\system32> get-clusterresource "Cluster Name" | Get-ClusterParameter RegisterAllProvidersIP

Object       Name                   Value Type
------       ----                   ----- ----
Cluster Name RegisterAllProvidersIP 0     UInt32

EDIT 2: Added AG cluster resource values. There doesn't seem to be a RegisterAllProvidersIP resource for the AG Listener. enter image description here

EDIT 3: Added the actual Listener resource value.

PS C:\windows\system32> get-clusterresource KYHIXSQLAGL01_KYHIXSQLAGL01 | Get-ClusterParameter RegisterAllProvidersIP

Object                      Name                   Value Type
------                      ----                   ----- ----
KYHIXSQLAGL01_KYHIXSQLAGL01 RegisterAllProvidersIP 1     UInt32
  • You can double check that this is set properly using Get-ClusterResource yourListenerName | Get-ClusterParameter RegisterAllProvidersIP be sure that you're using the listener name and not the name of the AG itself. – AMtwo Sep 29 at 2:02
  • @AMtwo I added the RegisterAllProvidersIP results to the question. – RonJohn Sep 29 at 13:16
  • @AMtwo as for "be sure that you're using the listener name and not the name of the AG itself", the AG and the AGL have the same name: SQLAGL01, as shown in the screen print. Am I missing something? – RonJohn Sep 29 at 13:19
  • Part of the object names is obfuscated in your screenshot, so I can't tell what is under the orange scribble. IMHO, naming both the AG & the listener the same thing is confusing. It's like naming two of your kids "George." – AMtwo Sep 29 at 15:55
2

In your question, you included the following check to validate that you've set the RegisterAllProvidersIP to false:

PS C:\> get-clusterresource "Cluster Name" | Get-ClusterParameter RegisterAllProvidersIP

Object       Name                   Value Type
------       ----                   ----- ----
Cluster Name RegisterAllProvidersIP 0     UInt32

If you've used the Cluster Name then you have set the RegisterAllProvidersIP parameter on the wrong object.

There are several similarly named objects in your screenshot:

  • The Windows Cluster: WC01
  • The Availability Group: AG01
  • The AG Listener: AGListener01

To validate how the setting is configure, you should use the listener name not the cluster or AG name:

PS C:\> Get-ClusterResource AGListener01 | Get-ClusterParameter RegisterAllProvidersIP

Or, to do this a bit more stepwise, you can do Get-ClusterResource and you'll see something like this:

Name                         State   OwnerGroup     ResourceType
----                         -----   ----------     ------------
10.10.10.199                 Online  Cluster Group  IP Address
Cluster IP Address           Online  Cluster Group  IP Address
Cluster Name                 Online  Cluster Group  Network Name
AG01                         Online  AG01           SQL Server Availability Group
AG01_10.10.20.138            Offline AG01           IP Address
AGListener01                 Online  AG01           Network Name
File Share Witness           Online  Cluster Group  File Share Witness
IP Address 10.10.10.138      Online  AG01           IP Address

Out of all of these things, we only care about the objects that belong to the OwnerGroup for the AG. We can filter a bit further with Get-ClusterResource | Where OwnerGroup -eq AG01:

Name                        State   OwnerGroup   ResourceType
----                        -----   ----------   ------------
AG01                        Online  AG01         SQL Server Availability Group
AG01_10.10.20.138           Offline AG01         IP Address
AGListener01                Online  AG01         Network Name
IP Address 10.10.10.138     Online  AG01         IP Address

These 4 items represent the resources that make up my AG: The AG itself, the listener (listed as "Network Name"), and the two IP addresses, each in different subnets.

The item that I want to check for the RegisterAllProvidersIP parameter is the listener (Network name) resource: Get-ClusterResource AGListener01 | Get-ClusterParameter will give me the full list of parameters on the Listener object:

Object        Name                   Value                            Type
------        ----                   -----                            ----
AGListener01  Name                   AGListener01                     String
AGListener01  DnsName                AGListener01                     String
AGListener01  Aliases                                                 String
AGListener01  RemapPipeNames         1                                UInt32
AGListener01  HostRecordTTL          1200                             UInt32
AGListener01  RegisterAllProvidersIP 1                                UInt32
AGListener01  PublishPTRRecords      0                                UInt32
AGListener01  ResourceData           {1, 0, 0, 0...}                  ByteArray
AGListener01  StatusNetBIOS          0                                UInt32
AGListener01  StatusDNS              0                                UInt32
AGListener01  StatusKerberos         0                                UInt32
AGListener01  CreatingDC             \\dc01.am2.co                    String
AGListener01  LastDNSUpdateTime      9/29/2020 3:57:12 PM             DateTime
AGListener01  ObjectGUID             abc1234def567890ab12cd3456ef7890 String
AGListener01  DnsSuffix              am2.co                           String
AGListener01  ADAware                1                                UInt32

And I can get the specific parameter by explicitly asking for it Get-ClusterResource AGListener01 | Get-ClusterParameter RegisterAllProvidersIP

Object        Name                   Value  Type
------        ----                   -----  ----
AGListener01  RegisterAllProvidersIP 1      UInt32

If this is not set to 0, then you would need to use the block of code outlined in the docs, and be sure you're using the Listener name, and not the Cluster name. The example also recommends setting the TTL as well (picking an exact number is up to you, though the docs suggest 300 seconds). Note that offlining & onlining the Listener is necessary for the new settings to be picked up by the DNS name:

Get-ClusterResource AGListener01 | Set-ClusterParameter RegisterAllProvidersIP 0   
Get-ClusterResource AGListener01 | Set-ClusterParameter HostRecordTTL 300  
Stop-ClusterResource AGListener01 
Start-ClusterResource AGListener01 

After this is done (and the old TTL is expired), you'll be able to see the Lister has only a single IP address configured:

Resolve-DnsName AGListener01 
| improve this answer | |
  • I added another screenshot. This one shows all the cluster resources, and also the AG Listener resources. There doesn't seem to be a RegisterAllProvidersIP resource for the AG Listener named KYHIXSQLAGL01. (I decided that obfuscation is pointless.) – RonJohn Sep 29 at 17:55
  • Updated my answer with a more verbose description. You are looking at the AG object itself (KYHIUXSQLAG01) instead of looking at the Listener (which shows in the Windows Cluster as "Network Name"). Because your AG & Listener share the same name, it shows up as KYHIUXSQLAG01_KYHIUXSQLAG01. If the listener & AG have unique names, the format will be slightly different, as is in the case in my example. – AMtwo Sep 29 at 19:11
  • I added "EDIT 3", and am now about to update the RegisterAllProvidersIP value. – RonJohn Sep 29 at 19:17
  • That seems to have worked. Resolve-DnsName KYHIXSQLAGL01 now returns only one IP address. If the app testers give a thumbs up, I'll accept this answer. – RonJohn Sep 29 at 20:04
0

I believe you are on multi subnet failover cluster setup. That is probably you have primary on one site and DR on other.

If that is the case as far as I know other subnet IP will always show offline.

Also I am not sure if you tried to manually bring online and getting that error. If you can update your question with multi subnet setup in case it is or if not , probably you would like to paste more details what you are doing when you re getting that error while failing over

I will update my answer based on that

| improve this answer | |
  • I added the RegisterAllProvidersIP results to the question. – RonJohn Sep 29 at 13:16

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.