Sometimes in the process of configuring Always On Availability Group on the database server, it displays Microsoft SQL Server error 19471.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related SQL queries.

In this context, we shall look into how to resolve this SQL error.

Nature of SQL Server Error 19471

Recently we received a support request that the customer was receiving an error while configuring Always On Availability Group again on the database server. We noticed that the listener failed to create during AOAG configuration and it displayed Microsoft SQL Server error 19471.

On checking the AlwaysOn High Availability folder on any of the replica, everything was working fine. AOAG was configured successfully and working fine, but listener was not configured due to above error.

There is no listener showing in their respective folder of the AOAG configuration. When tried creating Listener name from “Add Listener…” wizard, it failed with same error.

How to resolve SQL Server Error 19471 ?

In order to fix this SQL error, follow the steps provided below to to add the listener.

To add add the listener:

• Create a Client Access Point (CAP) for the AOAG listener in Failover Cluster Manager.
• Bring CAP Online.
• Repair the CAP, if it is not coming ONLINE.
• Once CAP comes online, remove it from the failover cluster manager.
• Add a listener to AOAG in SQL Server Management Studio (SSMS).

We have to remove CAP after adding, repairing, and bring it online. Creating CAP and bringing it online is to just verify that everything is fine or not.

If CAP will not come online, then there might be some issue at a certain level that needs to be fixed first before creating a listener.

Launch failover cluster manager and click on the Roles tab from the left side pane. Here we can see our availability group role in the right-side pane. Right-click on this role and select “Add Resources”. Then click on “Client Access Point“.

We will get a screen to enter listener details. Enter listener name into name section and listener IP into Address section. On clicking the Next button to proceed, it resulted in the error given below:

'Listener_name' listener is already in use in Active Directory.

The listener name and IP address should be unique and unused. So we need to remove these listener details from Active Directory.

Once listener details will be cleared from Active Directory, go ahead and try to add listener name to the existing AlwaysOn Availability Group. This time we will be able to create the listener name.

Failed to add Listener after clearing DNS entries and DNS Status showing “The handle is invalid”

If we are still facing the same issue during adding listener or configuring AlwaysOn Availability Group, then there is a possibility of some entries in AD for the listener name and IP during our failed attempts to create the listen to name. If there is no entry, then follow the steps given below:

We will create a Client Access Point in the failover cluster manager for the Availability Group role.

• Right-click on Availability Group role, select “Add Resources” and then “Client Access Point”. We will get a screen to enter the listener name and corresponding IP address.
• Click on the Next button to proceed post entering the listener details.
• We will get a screen to proceed, once we click on the next button.
• Click on Finish button to create the Client Access Point in failover cluster manager for this listener.
• Once we will click on finish button, Client Access Point for our listener will be created in the failover cluster manager. The Availability Group role was running fine. But after adding Client Access Point, it has changed into Partial Running mode.
• We can see CAP is showing as offline, due to which Availability Group role has changed into Partial Running mode. Now we will bring it online manually.
• Right click on Listener CAP and click on “Bring Online” option.
• We can see Listener IP has come online whereas listener name failed to come online. Let us check the properties of the client access point (CAP).
• Right click on Listener Name and choose Properties option. We will get a screen where we can see NetBIOS, DNS and Kerberos status.

NetBIOS and Kerberos status is showing OK. But DNS status is not OK and showing “The handle is invalid.”

This is the issue that needs to be fixed to create listener.

"The handle is invalid" Error

When we checked the cluster events, we saw below error details:

Cluster network name resource 'Cluster Name' failed registration of one or more associated DNS name(s) for the following reason:

The handle is invalid.

Ensure that the network adapters associated with dependent IP address resources are configured with at least one accessible DNS server.

To fix this DNS issue (The handle is invalid), we will repair this CAP.

• Right click on Listener Name showing in failover cluster manager, click on “More Actions” and then click on Repair.
• Once we will click on Repair, it will come online and then go in offline.
• Now right click on Listener name again and click on Bring Online button. This time Listener Name will come online if CAP has been repaired successfully. We can repeat the repair, if CAP is not coming online.
• If we are still facing issue, then we can again contact to domain admin to look into Active Directory server.
• Now we can see Client Access Point for the listener is showing online.
• If we go to the property of this Listener CAP, we can see NetBIOS, DNS and Kerberos status is showing OK.
• Close above property window.
• The next step is to remove this Client Access Point for this Listener and create the listener in SSMS that will create Listener client access point (CAP) automatically in the failover cluster manager.
• Once we click on remove button, another window will come to proceed. Click on OK to proceed.

How to add Listener ?

• Once this CAP is removed, then connect to primary replica of the AOAG configuration. Expand the AlwaysOn High Availability folder and then Availability Group Listeners folder. We will not be able to see anything as there is no listener as of now.
• Right click on Availability Group Listeners folder and click on “Add Listener…” option.
• We will get New Availability Group Listener Wizard to enter the listener details.
• Enter the Listener DNS Name then followed port by choosing Network Mode.
• Click on Add button to enter Listener IP Address.
• Once we are done with all details, click on OK button to apply this changes.
• Add Listener wizard will disappear if listener will be created successfully.
• Otherwise, it will give the error on the same wizard.
• As we have created Availability Group Listener for this AOAG configuration, we can go ahead and verify this in SSMS by expanding Availability Group Listeners folder.
• We can see the same listener name is showing under Availability Group Listeners folder on both primary as well as secondary replica.

[Need urgent assistance in fixing SQL errors? – We're available 24*7. ]