Finding SQL Servers with PowerShell Part 2

Can’t believe it’s taken me this long to get back to this series but man life has been busy! There are at least two other ways (and maybe even 3) that I want to show you for finding SQL Servers on your network. This next way is very similar the my first post but the results are a bit nicer because it actually puts the server and instance name together in a column called Name. For people just starting out with PowerShell this one difference is reason enough to use this method over the other

This is [one of] the same method[s] that Mladen Prajdić ( blog | twitter ) uses in SSMS Tools Pack to find SQL Servers. Mladen was nice enough to send me the C# code that he uses and patient enough to explain to me what the heck to do with C# code! As luck would have it he was using the same method that I had found just a few days earlier. After I was finally able to translate it to PowerShell I was elated to find Mladen’s C# code and my PowerShell code returned the exact same list of instances in my environment.

I changed the table around a little to accommodate the extra column from the function and also columns to track the method used and the date-time the discovery was made:

CREATE TABLE FoundSQLServers (
Name VARCHAR(128),
ServerName VARCHAR(128),
InstanceName VARCHAR(128),
IsClustered VARCHAR(5),
VersionNumber VARCHAR(64),
DiscoveryMethod VARCHAR(10),
DiscoveryOccured datetime2
)

Beyond just the function being a little different I changed the output so that this time we’re inserting directly into a table in the database instead of diverting to CSV first. Fire up your favorite PowerShell editor and run this:

$SQL = [Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers() | ` foreach {
invoke-sqlcmd -query "INSERT INTO dbo.FoundSQLServers VALUES ('$($_.Name)', '$($_.Server)' ` , '$($_.Instance)', '$($_.IsClustered)', '$($_.Version)', 'EnumAvail', SYSDATETIMEOFFSET())" `  -database SandBox -serverinstance "Win7NetBook"
        }

Now, you may or may not have received an error message with that last command and it completely depends on your setup but that’s a discussion for another post. For today we’ll just say that if you got this error message:

Unable to find type [Microsoft.SqlServer.Management.Smo.SmoApplication]: make sure that the assembly containing this type is loaded.Then all that you’ll need to do is load this Assembly first (once, at the beginning of your PowerShell session) and you’ll be good to go.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

Back to that table… You’ll notice that the table doesn’t have a primary key and that’s because I’ve left that up to you. I use a table similar to this just to dump my results; I don’t use this as my master list or anything like that. I query this table to see what has shown up recently that I didn’t know about but I use a different table to sort instances between Prod/QA/UAT/Dev and what-not.

SELECT [Name]
,[ServerName]
FROM [dbo].[FoundSQLServers]
GROUP BY [Name]
,[ServerName]
ORDER BY [Name]

There you have it, not just a different way to find SQL Servers but also a nice comparison between outputting to a CSV file and outputting straight to a table inside of SQL Server.

There are some drawbacks to this method that I want to make sure to call out: 1) I’ve never seen it work over a VPN connection so make sure to RDP into another machine and run it from there. 2) This method is no good at crossing domains so you’d have to RDP into a machine on the other domain for that too. 3) Anytime the Version column isn’t populated you can expect the IsClustered column to be wrong.

Hopefully the next method (or two) won’t take me as long to get posted