Have you ever clicked a drop-down to connect to one of your databases and noticed an instance that you don’t remember setting up? Or maybe a server that was recently setup for some new application in your organization, that no one mentioned it needing SQL, but you come back from your day off and notice that server is now one of your available SQL Servers? If your office is like mine those things happen all too often.
There are several different methods to finding out just how many SQL Servers are running on your “network” right now. Each method has pros and cons. But the biggest problem that I run into is: ‘what is defined as the network?’ Is it just the network of computers at your office? Does it include servers at your collocation facility? What if you have a completely separate domain for Development?
The first method I’m going to point out to you is really easy to use but it’s results vary depending on your network rights and where you run it from, but, it still can find machines that you didn’t even know about so lets have a look. Wait, why would I start out with an example that’s so imperfect? Because this method uses the same .Net components that are used to generate the list of servers you see when you click on the “<Browse for more>” option in the drop down of the SSMS connection window*. This means that you’ll see a list that you recognize (probably). Fire up the ISE and kick off this command:
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
Now if you’re just on you laptop or sitting at home or something you’ll probably get a nice short list like this: 
But if you’re at work, you’ll get back a much longer list; maybe even longer than you expected 🙂
Heck how many servers just came back anyways! The answer to that question is really easy, just pipe the output of the command to Measure-Object and find out like this:
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | Measure-Object
and you’ll get some output like this:
Now like I said earlier, these results are going to vary depending on where you run the command so here’s what I recommend. Run this command from your desk. Then try it from a machine that is on a different network segment that you’re usually on – like a Dev domain. Then go home, VPN in and run the command, see how many you get back now.
OK that’s great but with all of these variances can I do anything useful with this example? Sure you can. Take your results and put them in a table. First build a simple table to use:
CREATE TABLE FoundSQLServers (
ServerName VARCHAR(128),
InstanceName VARCHAR(128),
IsClustered VARCHAR(3),
VersionNumber VARCHAR(64)
)
Then build some insert statements off of the results like this:
$SQL = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | ` foreach { "INSERT INTO dbo.FoundSQLServers VALUES ('$($_.ServerName)', '$($_.InstanceName)', '$($_.IsClustered)', '$($_.Version)')" ` >> C:\Temp\INSERTFoundSQLServers.sql }
To open up the file that was just created in Management Studio just run this
Ssms.exe C:\Temp\INSERTFoundSQLServers.sql
…and you should get some nice insert statements for that table like this:
INSERT INTO dbo.FoundSQLServers VALUES ('WIN7ULT', 'KILIMANJARO', 'No', '10.50.1600.1')
Go ahead and insert those rows into your table, maybe even give the table a little bit different name like: FoundSQLsFromVPN or FoundSQLsFromDev. Run some queries to compare the results you get from different network segments; see which servers we found both at home (through VPN) and at work from your desk; and more importantly which ones are only found from one of those locations. We’ll take a look at a couple of different methods I’ve learned about in future posts but I wanted to get you started with finding out how well this method finds your servers and knowing how your network location can affect it. Just in case you’ve recently started a new job or something 😉
Special Thanks: I just wanted to take a moment to thank Grant Fritchey ( blog | twitter ) for doing a tech-check on this post and helping convey my points a little more concisely.
* Small Disclaimer: I was told that it’s the same .Net component used by applications like SSMS but since I don’t work at Microsoft and I’m not a developer I wouldn’t even know where to start to verify this but the list produced has always been very similar or down-right identical.
0 Responses
As always, nice post, Aaron!
I set up 4 runs on 4 different servers and 1 run on a Windows 7 machine.
My executions would vary from returning 70 to 75 instances. That is missing several that I ruled out due to different network configurations and I expected it not to find them. The execution from my local machine would often return the least amount which was odd. That was Window 7 which should have no difference to Windows 2008 Server (R2 or not).
I only had one instance where two different runs came back with identical results (that is out of 17 executions. Not good odds). Named instances did seem to be an issue. I could only get a return from 4 named instances and those were SQL Express instances so they (kind of) count. When I narrowed down the named instance issue, or so I thought I did, then one would pop up all the sudden.
Not much to report beyond what has been said. The inconsistency is/was hard to track down. There was no apparent reasoning at all why an instance would drop from the list and then shows back up from the machine it was executed from. So the statement that it is unreliable on catching all instances and normal behavior is very accurate.
Aaron,
Nice post but I ran across an issue you may want to check.
I tested this script and it only returns the server name I an on. If I start SQL Browser, it then returns additional servers (SQL Express) and returns the Instance, IsClustered, and Version info.
I then stopped SQL Browser and only my server name info returned. I am running Win XP SP3, so not sure if there is a difference running it from other OS versions.
By default SQL Browser is set to disabled, if I remember correctly. But I will check the script this week at work. We do have a separate domain for developers to do what they want. Then we control Pre-Prod and Prod so it will be interesting to see what I can find.
Thanks,
AJ
SQLAJ
AJ,
Having multiple instances on my machine it looks like I’m having the same result, ironically, my default instance is only show up when the browser is off howerever this behavior is exactly the same for me in SSMS as noted at the beginning of the post. I did notice that they only showed up under “Network Servers” and not at all under “Local Servers” which I thought was a bit odd.
SQL Browser is always enabled when you have a named instances. Here’s some more info on it.
http://msdn.microsoft.com/en-us/library/ms181087.aspx
Background
——————————————————————————–
Prior to SQL Server 2000, only one instance of SQL Server could be installed on a computer. SQL Server listened for incoming requests on port 1433, assigned to SQL Server by the official Internet Assigned Numbers Authority (IANA). Only one instance of SQL Server can use a port, so when SQL Server 2000 introduced support for multiple instances of SQL Server, SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434. This listener service responded to client requests with the names of the installed instances, and the ports or named pipes used by the instance. To resolve limitations of the SSRP system, SQL Server 2005 introduced the SQL Server Browser service as a replacement for SSRP.
How SQL Server Browser Works
——————————————————————————–
When an instance of SQL Server starts, if the TCP/IP or VIA protocols are enabled for SQL Server, the server is assigned a TCP/IP port. If the named pipes protocol is enabled, SQL Server listens on a specific named pipe. This port, or “pipe,” is used by that specific instance to exchange data with client applications. During installation, TCP port 1433 and pipe \sql\query are assigned to the default instance, but those can be changed later by the server administrator using SQL Server Configuration Manager. Because only one instance of SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances, including SQL Server Express. By default, when enabled, both named instances and SQL Server Express are configured to use dynamic ports, that is, an available port is assigned when SQL Server starts. If you want, a specific port can be assigned to an instance of SQL Server. When connecting, clients can specify a specific port; but if the port is dynamically assigned, the port number can change anytime SQL Server is restarted, so the correct port number is unknown to the client.
Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. SQL Server Browser support ipv6 and ipv4.
When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.
And just verified, unless we have some way to look at all of the subnets to which we have access, this seems to work about as well as SSMS. Your code looks good. It would be nice to have some other method to look at other subnets or otherwise figure out how SQLPing does its job and mimic that.
Peter,
I’m working on a way that you can specify a list of computers to poll and it’s progressing but there are definitely some kinks to it. I will see what I can do to streamline it.
Out of curiosity, what should we do if we’re running on non-standard ports? I found all of the local instances of SQL Server on our network, but didn’t find any of the actual servers. I guess I should mention that this “find all servers” functionality has always seemed lacking to me, regardless of how it’s called. 🙂
Giving a quick test of SQLPing to see if it behaves the same way – it’s been ages since I’ve run that tool. Glad not to be the “admin” now and just a dev.
Peter,
I’ll have to look into the other ports thing. I do have a PowerShell script around here somewhere to tell you what port your instance is running on; not sure if there would be a way to leverage that or not but I will see.
Nice find. I did notice one disclaimer in the MSDN docs – Due to the nature of the mechanism used by SqlDataSourceEnumerator to locate data sources on a network, the method will not always return a complete list of the available servers, and the list might not be the same on every call.
So, depending on the context and the state of machines on the network, your mileage may vary.
Regardless, it is a great tool for the toolbelt.
Steve,
Thanks for pointing out that it’s a documented behavior of SqlDataSourceEnumerator. I’m glad that you like what I put together, I’m hoping to show a few more ways to do this in the coming weeks (somewhere in my vast amounts of spare time).