Picking up a New Hammer

The other day I was answering a question in the forums on PowerGUI.org that I thought would be a quick answer. The only problem was that I had my T-SQL hammer in my hand and not my PowerShell hammer. When I read the question I thought of a way to solve the problem using T-SQL and PowerShell; the only problem was I didn’t think outside of this toolset. PowerShell MVP Shay Levy ( blog | twitter ) chimed in with a much better answer.

Now granted I usually prefer to give answers that your typical data professionals will be comfortable with, ones that pass along a query, but in this case I have to go with the solution that you might not be as comfortable with. There’s a good reason though: It’s faster.

here was my answer:

#Only load this part once when you open up your PowerShell Session
add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100            
#Create a text file in your c:\temp\ directory called AllServers.txt
#Put each server\instance on a new line.
foreach ($svr in get-content "C:\temp\AllServers.txt")             
{
#sp_databases | format-table
invoke-sqlcmd -query "SELECT @@SERVERNAME AS 'ServerName', @@VERSION AS 'SQL Version'" -database master -serverinstance $svr >> "c:\temp\MySQLServerVersions.txt"
}

and here was Shay’s answer:

$null = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')            
Get-Content Allservers.txt | Foreach-Object {
   $sql= New-Object Microsoft.SqlServer.Management.Smo.Server $_
   $sql | Select-Object @{n='ServerName';e={$sql.name}},@{n='Version';e={$sql.version}}
}

Without a doubt Shay’s answer is better because it returned the same results in a better format AND ran in a quarter of the time of my answer. In my environment my solution took a respectable 60 milliseconds to run, Shay’s answer using the SMO took 15 milliseconds. What’s my point today? The SMO is worth learning because it can be faster.�
Where can you learn the SMO? I’m glad you asked 🙂 Take a look at this MSDN article by SMO expert Allen White ( blog | twitter )