SQL Server Over a LAN

[Shaggy Hiker]

I've had intermittent issues with SQL Server over a LAN, as discussed in this thread:

https://www.vbforums.com/showthread....ing-SQL-Server

I suspect...well, I suspect lots of things, but there were too many variables, so I moved the DB from a Surface Pro 2 to a Surface Pro 7 so that the older WEP protocol on the Surface Pro 2 could be ruled out. At first, all was well, then I had a failure to connect with the 7, so the problem wasn't just the older WiFi on the 2. It might have been partially that, but it wasn't entirely that.

Just recently, I fired up WireShark and got looking at what was going on. To date, I haven't gotten a look at a failure, so I can't say anything about that, at this point, but I have found something kind of interesting.

The code I used was just this:

 Dim dSourceInstanct = SqlDataSourceEnumerator.Instance
 Dim mDTServers = dSourceInstanct.GetDataSources

This takes a good long time to show anything. In fact, it takes 10-20 seconds, which is a goodly amount of time on a modern computer. This code was running on my desktop computer, while the SQL Server instance was running on the Surface Pro 7.

What I found was this:
Right off, the Desktop sent out a broadcast on IPv4 and IPv6. Within roughly 0.1 seconds, the 7 responded with all of the information. These were tiny packets, but the information that ended up in the datatable was all there in plain text, so it was easy to see.

Over the next dozen seconds, a recognizable sequence of requests went out from the desktop. Other things were happening between these, but after studying a series of Wireshark captures, I recognized the patterns consistent around that code, and could eliminate all the extraneous other traffic on the network. The consistent part was that there were a series of calls on a BROWSER protocol and NBNS protocols, which seemed to go unanswered, but also appeared fairly meaningless. These were a second or so apart, so this took roughly five seconds.

Following that came a call and response from the Surface 7 to the Desktop using ICMPv6, followed by the same pattern of BROWSER and NBNS calls (that also appeared to get no response). Then there was a call and response from the Desktop to the Surface 7 using ICMPv6, and finally there were three more NBNS calls that appeared to get no response.

All of this ended up taking all of the time between the call to GetDataSources and the response. In other words, it sure looks like the Surface responded in 0.1 seconds, then something wasted a bunch of time making strange calls every second or so for the next dozen seconds.

Does anybody have a resource on what is actually going on with GetDataSources that might explain this consistent pattern, especially all the time wasting that seems to happen with those NBNS calls?

[Zvoni]

hmmmmm......
https://www.vbforums.com/showthread....rator-Instance

[Shaggy Hiker]

Yeah, I know. I stumble over this issue every now and then. This time, I'm approaching the problem differently. Something is going on, and the technique I'm using is known to be at least partially unreliable, but there also isn't anything else out there. So, what I'm trying to do is figure out in what way it fails when it fails, so I'm looking at how the request for SQL Server instances works when it does work, so that I can then see what is different when it is not working. Thus far, I'm just watching the network traffic by making specific, repeatable, tests to see what messages get sent and in which order. What I don't know is WHY a bunch of them are being sent. I can see how the request and response is working, so when it stops working I'll be able to identify at which point it fails, but following the call and response is a bunch of other stuff. That other stuff is always there, and always in the same order, but it isn't at all clear what purpose it serves...despite it being also clearly responsible for all the time that is taken.

I'm willing to study this through experimentation, but if there is documentation as to the significance of those messages, especially the ones after the servers have responded, I'd sure like to see it.