A Wandering Mind

I have a database that contains metadata for all the servers I support, such as server and domain names, instance names, SQL versions, etc. It proves very handy when trying to determine who owns what, or find out which servers need to be upgraded, etc.

Unfortunately, it’s also extremely frustrating to maintain. Right now all data is entered manually via T-SQL commands (I do at least have procedures that encapsulate some check logic) and it’s up to the operator to enter the data correctly. Given that we humans are prone to mistakes and slip-ups (not to mention laziness) that means that inevitably stuff gets out of date.

To try and mitigate this problem (and lighten my workload in the mean time), I’m in the process of writing some Powershell scripts that will iterate over the list of servers, attack them with some Posh remoting commands, and update anything in the database that is stale.

Let’s say we want to get back a list of servers and get some information from each of them. At first I thought this would be as simple as something like this:

Invoke-Sqlcmd -ServerInstance $ServerName -Query $SqlQuery -Database dbarepos | Invoke-Command -Cred $Cred -ScriptBlock $ScriptBlock

But then when I tried this, I got a rather vague error:

Invoke-Command : Parameter set cannot be resolved using the specified named parameters.

So apparently the Invoke-Command doesn’t immediately like getting that piped input. I have a hunch it was related to something Don Jones pointed out here, about how Invoke-Command binds piped input to it’s parameters.

In any case, my workaround was to just rewrite the code a little differently, which also gave me some more verbose progress output:


$servers = Invoke-Sqlcmd -ServerInstance $server -Query $sqlcmd -Database $db;
$servers | Foreach-Object {
Write-Host Querying $Server.Name;
Invoke-Command -ComputerName $Server.Name -Cred $Cred -ScriptBlock $ScriptBlock;
}

This ends up accomplishing the same thing, albeit in a bit more code (but not much, so I’m fine). I tested this and in about 2 minutes I could pull back a tremendous amount of information on all the servers, ranging from memory to PerfMon stats to even SQL Server specific information like database names and sizes. Truly a timesaver; I really should use Powershell more often!

Be Sociable, Share!

•