I keep highlighting that it is necessary for DBAs to understand the underlying infrastructure as it helps when it comes to resolving issues. I was having connectivity issues with my SQL Server 2019 instance as sqlcmd is throwing this error
HResult 0xFFFFFFFF, Level 16, State 1SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2019, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
This blog post highlights how to troubleshoot connectivity issues with SQL Server and gave me an idea why I am getting the issue. The first thing I did was to run an ipconfig /flushdns to make sure I was looking at the correct IP-hostname resolution from my DNS server. Then, I tried to PING the IP address of the SQL Server instance and it worked just fine (I usually add the -a switch on my PING command to include the hostname if it is indeed correct). I tried the reverse – PING the hostname – but my DNS server is returning a different IP for the same hostname. Lokks like the DNS entry for my SQL Server box is a bit screwed up. I logged on to the DNS server to validate and I was right. I modified the IP address of the A record pointing to my SQL Server box, ran another ipconfig /flushdns before doing another PING test to validate the change. Once everything is ok, I was able to connect to my SQL Server instance using sqlcmd .
Now, you might be wondering, “Why do I have to know?” If you’ve read some of my previous blog posts, you’ll know that I’m an advocate of knowing who’s on the team responsible for keeping the infrastructure highly available. It doesn’t matter if your database server is online and available from your perspective. If your applications could not connect to it, it’s as good as it being offline. In fact, I sometimes tell DBAs that my personal measure of availability is the number of phone calls I get from end users regarding the specific application. I shift my focus from being a DBA to being a service delivery manager to properly manage expectations.
It pays to understand a bit about something not directly related to your work sometimes. If we know the underlying infrastructure that our databases are a part of, it makes it easy for us to resolve issues, meet expectations and achieve availability goals.