MSSQL Listener issues

Question

I have a problem with my SQL production cluster that I am really struggling to figure out.

Recently, we had to add a secondary NIC to our production cluster for SQL because in AWS land, most instance network interfaces are limited to 15 IPs. As we hit that, this project came up. I trialled and tested on newly built systems, which worked and seemed happy.

The problem is that everything created on the secondary NIC seems very temper mental, only working partially. I can log in to a SQL listener that uses IPs from the secondary NIC so long as I fail it over to the secondary node, but I still can't connect my apps to the DB with SQL listener. If everything is on the same node, then I can't connect to anything on that was setup with IPs from the 2nd NIC added.

I keep getting the error:

A network related or instance-specific error occurred while establishing a connection to the SQL server. The server was not found or was not accessible. verify that the instance name is correct and that SQL Server is configured to allow remote connections (provider: Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Sorry, the post isn't letting me attach images for some reason :|

Steps taken:

• Tested SQL listeners on the Primary NIC - they all work fine, can connect to their respective listeners and interact with their DBs.
• nslookup against the affected listener DNS and this comes back with
  the DCs DNS server IPs.
• Checked TCP/IP is enabled and it is.
• Tried restarting SQL services, nothing.
• Checked SQL is running (certainly is otherwise we would have customer complaining)

Setup:

Server 1 -

NIC 1 - 172.26.208.128/26

NIC 2 - 172.26.208.192/26

Server 2 -

NIC 1 - 172.26.209.128/26

NIC 2 - 172.26.209.192/26

SQL 2019 Standard Edition

Windows 2019 Data Centre edition.

As you can see they are on separate subnets per NIC, so its not a necessarily a subnet issue, I have made sure no IPs are conflicting/replicated else where which there isn't.

I have looked in WSFC and I can see the subnets are listed there.

I have noticed is that the secondary NICs aren't showing in SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for MSSQLSERVER > IP Addresses. I would of expected to see this there with its Primary IP it was assigned, am I correct in thinking this?

If so, how would I configure the secondary NIC there so going forward, there is no issues with new SQL listeners I create?

EDIT (15/06):

Ok, so I have enabled logging on the affect client servers that are trying to connect to the affected DNS addresses for the SQL listeners and can see this error code:

DNS_ERROR_RECORD_DOES_NOT_EXIST 9701 (0x25E5) DNS record does not exist.

But when I look in my DNS management console, I could see the DNS entry with a timestamp (this suggests its a dynamic record), I have attempted to recreate these records with PTR records included, and still receive the same error.

I am still baffled as to why the 2nd NICs can't communicate with the DC and retrieve DNS info.

I can confirm the 2nd NIC on both Server 1 and 2, have:

• The DNS setup pointing at the DCs IPs.
• Connected to the domain network
• Can lookup DNS records from AD
• The Metrics between NIC1 and NIC2 are different.
• Done an ipconfig /dnsflush on the SQL and Web servers.

Answer 1

Ok, so I have found a fix I guess.

As we all know Microsoft AD creates DNS records dynamically.

I tried recreating these as static records with PTR checked but not "allow any authenticated user to update dns record with the same owner name" and it didn't work, so I tried again with the second option checked and it allowed to work.

I find this fix, not the best way of going about it and will further investigate. If anyone has a better way, I would be glad to hear it :)

Answer 2

When adding a NIC or a new IP address to any SQL Server installation you have to ensure that the SQL Server instance knows the IP address. If you don't see the IP address in the SQL Server Configuration Manager then the instance is not aware of the new IP address.

To address this issue perform a Repair of

• all the SQL Server instances on the server
• the shared tools on the the server

This can be achieved by running

• the SQL Server Installation (x64) setup in Programs and Features
• the setup.exe executable on a suitable installation media

After that the SQL Server Configuration Manager should show the IP addresses in the ... > SQL Server Network Configuration > Protocols for MSSQLSERVER > IP Addresses.