PowerShell

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.