January 20, 2014 at 10:28 am
Hi all,
I have a strange one occurring at the moment. Users have complained that at time connecting to an instance fails(They all connect using Servername, Port so the issue is not the browser). Following investigation I've identified an issue and it is a strange one.
The port we are listening on is non default. I have a powershell script to connect to the port to check if it is open(Similar to PortGUI.exe) and record the results in a text file. Below is a snippet from the output(I've removed the servername and port) showing the port returning as open and filtered or close and back to open again. During this time I cannot connect using SSMS. I have also run the same query to another named instance on the same node(this is a cluster) and this does not show the same issues. Any advice is greatly appreciated.
[ServerName]: Port [PortNumber] is open. 17/01/2014 16:32:01:603
[ServerName]: Port [PortNumber] is open. 17/01/2014 16:32:02:615
[ServerName]: Port [PortNumber] is open. 17/01/2014 16:32:03:629
[ServerName]: Port [PortNumber] is closed or filtered 17/01/2014 16:32:04:644
[ServerName]: Port [PortNumber] is closed or filtered 17/01/2014 16:32:06:656
[ServerName]: Port [PortNumber] is closed or filtered 17/01/2014 16:32:08:668
[ServerName]: Port [PortNumber] is closed or filtered 17/01/2014 16:32:10:681
[ServerName]: Port [PortNumber] is closed or filtered 17/01/2014 16:32:12:693
[ServerName]: Port [PortNumber] is closed or filtered 17/01/2014 16:32:14:706
[ServerName]: Port [PortNumber] is open. 17/01/2014 16:32:16:717
[ServerName]: Port [PortNumber] is open. 17/01/2014 16:32:18:230
[ServerName]: Port [PortNumber] is open. 17/01/2014 16:32:19:244
Kind Regards,
Vets
January 21, 2014 at 8:55 am
Are you using dynamic ports, or a fixed port?
Firewall?
January 21, 2014 at 9:00 am
Fixed port.
Firewall is open. Have tested by turning it all and issue persists.
Would it be possible that an application is flooding, for the want of a better word, the port and stopping it accecpting new requests?
January 21, 2014 at 9:10 am
I doubt it - check your SQL Server logs at those times. Also, perhaps turn on logging of both successful and failed logins - look for patterns at the times it changes from working to not and vice versa.
If you really want to know, ask your network team to open up a packet sniffer for awhile.
January 21, 2014 at 9:42 am
Thanks for the response.
The issue appears to occur before the connection reaches SQL so the logs show nothing. Changing the logging requires a restart which right now is not an option.
We have tried WireShark however unable to identify the issue. Also testing connections on the local machine while the outage is there has proved unsuccessful
I will continue to investigate and update this thread with any information I come across
January 21, 2014 at 10:13 am
Use Wireshark or whatever other packet sniffer simultaneously on both the client machine and the SQL Server machine; keep them running from the time it's working until it's not, then look at what started happening - timeouts vs. TCP Reset vs. whatever. If nothing else, you can show evidence to the network team that it's between the machines.
Also check the Perfmon network counters, particularly the error counters - maybe there's some bizzare sporadic errors in the network traffic (someone sets large magnets next to your Ethernet cable, or a duplicate MAC address)?
January 23, 2014 at 3:24 am
Just a quick update on the situation. After enabling error outputs on the powershell I am seeing the following during outages:
"No connection could be made because the target machine actively refused it 127.0.0.1:[Port]"
Will be pursuing this looking at possible Backlog issues and see if I can identify an issue from here.
I've testing the port using SSMS and Powershell from the same subnet and also on the active node on the cluster, both of which are failing during the "outages"
I've also been following up with some networking colleagues to check through the traces we've been taking.
Thank you for your responses and valuable insight
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply