This week, I attended the Atlanta Powershell Usergroup presentation by Aaron Nelson (aka SQLVariant on twitter). I met Aaron in January after the Atlanta MS Pros meeting where I had volunteered to get a presentation organized on Powershell - I found Hal Rotenberg (aka Halr9000 on twitter) to give people an introduction to powershell (better a local MVP to do that than a novice).Aaron told me when we met in January he was giving a presentation in a couple of weeks on using Powershell with SQL Server. I encounterd Aaron Nelson a second time during the year at SQL Saturday - where he did that presentation again, and showed a method I had not see yet- that had $_.Script(). After SQL Saturday, I researched what that Script method was. After the meeting this week, I told him that I had found that he was using a method on the SMO object. For a DBA this object gives you the power of SSMS (SQL Server Management Studio) via Powershell. For a DBA this method would be one that should certainly be in their toolbox.
In the days following the presentation, I was coding on a development project that is targeting a good number of modifications to a production system. I was fairly sure that the Development databases were partially modified, and needed to prove that to others on the team. I remembered the SMO object and that Script method, and wanted to get the defintions of the database to compare to each other.
$load = [reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" )
# Derive the SMO Application Object
$smo = [Microsoft.SqlServer.Management.Smo.SmoApplication]
$server = new-object "Microsoft.SqlServer.Management.Smo.Server" $servername
$database = $server.Databases[$cfg.config.DatabaseOLD.Database]
$tables = $database.Tables
[string] $oldDatabaseDefinitions = $tables |? {$_.Name -match "T_" } |% { $_.Script()}
A few lines of powershell was able to give me the definitions in one string. I output the string to text files and with a file comparison tool could show exactly what changes were in the definitions between the databases we were dealing with.(Production,QA,two development databases, and a 'sandbox' database).
Also this week, I tried catching up on some listening of the powerscripting podcasts (last year my listening to this pocast was how I discovered Hal Rotenburg lived in the Atlanta area, and led me to recruit him for Atlanta MS Pros January presentation). In episode 106 there is an interview of Chad Miller, and they discussed the codeplex project SQLPSX(SQL Powershell Extensions)that he is involved with. From that discussion I learned more about SMO and how Microsoft had done usability studies on that object. Powershell combined with the SMO object seems to be key to those extensions. During the interview he also dicussed his extending the Powershell ISE - which I was not aware was open for extension. He extended it to interact with SQL server - the SMO object was part of the toolkit he used. I will have to start exploring some of the code out there for that project - as I am curious about how he went about this. That could make for an interesting presentation.
I saw on Chad Millers blog he did a Powershell ETL presentation for the Virtual Chapter of Pass, and between that video, and the Powerscripting interview I can tell that Chad Miller is a very capable speaker, and presenter - someone worth looking at to learn from.(Which is why he is a Powershell MVP).
Powershell combined with the power of existing functionality of the SMO object deserves more study. SMO comes with SQL Server 2005 and 2008, however they are different versions. At work I will need to make some room for SQL 2008 R2 on my workstation so I can work with the latest version. (always more to learn).