Enumerating SQL Server

[Shaggy Hiker]

I've got something going on, and am in need of some direction as to where to look. On my home network, there should be a few different instances of SQL Sever Express running. One of them is fairly unreliable. I've discussed that in a different thread from a year or so back, but it's hard to rule out all the potential variables in that case. The instance is running on an older computer with a WiFi connection that uses somewhat dated technology. I can connect to it if computers are started in the right sequence, the stars are in alignment, and I sacrifice a chicken, but of late, the connection will drop if left unused for some length of time (unsure how long, but perhaps in the 15-30 minute range), and once dropped...I have to repeat the steps, and I'm running out of chickens.

To attempt to narrow down the issue, I decided to connect to a second, newer, computer also running over WiFi, but with modern security and Win11 (the other is Win10). I am attempting to connect from a third Win10 computer which is fairly new, though not as new as this new target for SQL Server. So, the original was Surface2, the new one with SQL Server is Surface7, and the one I am attempting to connect from is Desktop.

The program on Desktop is either one I wrote, or SSMS. In the program I wrote, I use the SQLDataSourceEnumerator like so:

Dim dSourceInstanct = SqlDataSourceEnumerator.Instance
mDTServers = dSourceInstanct.GetDataSources

This returns 0 records in the table. That's a bit of a surprise, since even Desktop has an instance of SQL Server Express on it, though no effort was made to expose that outside of Desktop.

My first thought was that I had Surface7 set up incorrectly, so I went into the SQL Manager and made sure that all the protocols were properly enabled. They were. I also added a rule to Windows firewall to allow the default SQL port. This hasn't changed anything.

In SSMS, a search for network servers first says "Retrieving data" then goes silent.

What am I not doing?

[sapator]

From here :https://stackoverflow.com/questions/...local-sql-serv


Subject to firewall rules (Blocked TCP/IP 1433 and UDP 1434)
Doesn't find SQL Servers if the SQL Browser is off
Doesn't find SQL Servers if they are hidden
List contents not guaranteed to be repeatable (due to timeouts). In fact, a subsequent call is quite likely to give a different list depending on the network I/O, server performance, number of servers on the network and other time-dependent constraints

Several sources say you have to make 2 calls to SqlDataSourceEnumerator.Instance.GetDataSources() ...

I should also add check that the SQL versions are the same. I think all the old databases can be up lifted to the latest 2022 version as we have done so at work (ok nothing below 2005)... Oh I remember the thread now. I would suggest for laptops looking if wifi is up and running after a few minutes as I have still got this issue that mu wifi closes if the laptop goes to sleep, so that is also a consideration. But you say you have a desktop also so that is another subject.

[Shaggy Hiker]

Wow, the links on the first reply are spectacularly bad. One leads to Bing, one leads to Microsoft Q&A without answers, I get a 403 on a third one, and the final one talks about porn on spotify. I'd say that the first reply in that link has been rather deliberately sabotaged.

[Shaggy Hiker]

After a bit more searching around, I figured out a few things:

1) Microsoft moved a whole lot of relevant resources, thereby breaking links in threads all over the place, including the links in the reply that Sapator linked to, and a thread on here (which I started back in 2016). The result of breaking those links is that the help provided is far less than it otherwise would have been. My old thread, in particular, has been rendered nearly worthless by this action, as all findings were in links that no longer lead anywhere.

2) I had neglected to turn on the SQL Server Browser on Surface7, which was at least a big part of the problem. Without that, the program simply wouldn't see that instance of SQL Server. I haven't studied that as much as I should, and if anybody wants to expound on what SQL Server Browser does (I think I have the name right), or link to something that talks about it, that would be useful, as I clearly need to understand things better. For one thing, I found that the approach I'm taking is never going to be fully reliable, nor will the SMO approach via smoapplication.enumavailablesqlservers (no link, just in case the links fail). I wasn't intending to rely on that, it was really a last resort, but it appeared that I couldn't connect even if I typed in the server name directly (I checked it twice, and saw no typos, but...it's a long name, so maybe that).

3) The other part of the problem is likely due to the age of Surface2. When I went to look at it, this morning, I found that it had lost connection to the internet, which was probably because it couldn't connect to WiFi. Well, if it can't connect to the WiFi network, then it's no surprise that my program couldn't find it. What I don't know is whether or not when the program loses track of the DB on Surface2, it is because Surface2 has lost the WiFi connection itself, or if there is something else going on. I might be able to look into that, but one thing is clear: Surface2 is pretty old. It has older WiFi hardware, and that isn't going to change. It may simply be unreliable when connecting to far newer technology. I don't think it's WEP, but it's old.

So, this may be resolved. I may need to move on, and apparently I hadn't set up Surface7 such that I could do so. More studies are needed, but it's a start.

[techgnome]

Been a while since I used SqlDataSourceEnumerator but I do remember it being notoriously unreliable... I could run it one sec, and get zero reponses, then repeat it... and get a dozen servers showing up. Run it a third time and half of those would "fall off" but return on the fourth shot. Fortunately for my needs at the time, it didn't need to be 100% reliable... just close enough.


-tg

[sapator]

At the very worst and from what I read. Do the call 2 times selecting the max results....Or update your Laptop....

[Shaggy Hiker]

Not a laptop, it's a Surface 2 Pro. Considering we're now at Surface 10, you get an idea of how old that computer is. Upgrading the hardware in the case isn't an option with a Surface, as they are essentially sealed...and the 2 used a different form factor from any subsequent devices. So...update? Yeah, that's essentially what the Surface7 is, an upgrade.

Basically, this call is already a "last chance" technique. Normally, the program just connects and all is well. It's when it does not that I have to go searching. What I'm trying to do is make it somewhat idiot proof. That isn't totally possible, of course, and even less so than normal in this scenario, but the data source enumerator is a bit of data on the subject. The fact that it isn't necessarily reliable isn't all that important simply because it is already what amounts to the third line of attack.

At this point, there are no issues, so there's nothing much to look at. By now, though, I know this is a temporary state that will be changing eventually.

I just wish I could be certain that when the Surface 2 gets lost, it's because of the WiFi adapter on that system. I can't do that, though. It could be the WiFi adapter, it could be the router, it could be some external interference. The reason to switch to the Surface 7 would be simply that I'd be able to then rule in/out the hardware in the Surface 2. Unfortunately, with the problem being totally intermittent (there were no issues, yesterday over several hours), it's maddeningly difficult to rule out any piece of the puzzle.

[sapator]

Ye I was assuming this?
https://www.phonearena.com/phones/Mi...e-Pro-2_id8154

Does it have an SSD or HDD? That would be an immediate uplift.

[Shaggy Hiker]

SSD, it's not stone age!

Actually, it's a pretty good computer. I bought it to carry on a long bike ride, and it worked pretty well for that. Great improvements were made in subsequent versions, though, so I bought the 7 for another long bike ride. The 7 has several improvements over the 2 from a usability standpoint, but the 2 was pretty good.