DesignAndCode software development thoughts..

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).

Powershell 2.0 is considered to be part of the "Windows Manangement Framework" and has a download page. Powershell 2.0, WinRM(remoting service), and BITS 4(Background Intelligent Transfer Service) makeup this management framework. Powershell 2.0 was shipped with Windows 7 and Server 2008 R2, and was made available for Windows XP SP3 and Vista SP1. Version 2 is completely compatible with version 1 - so any scripts written with version 1 will work in version 2.

On Dec 22, 2009 a new Powershell 2.0 SDK was put out for dowload by Microsoft.

There are two videos from the Micrsoft PDC2009 - SVR12 and SVR13 that show writing a simple GUI using the powershell API. During these videos it is mentioned that the powershell ISE (Integrated Scripting Environment) was developed with this same API.

There are Microsoft examples on how to host powershell within an application- which is what a developer would do to utilize powershell within their own application. This is the direction that Microsoft has started with their enterprise level tools. Now developers can embed powershell so that you can develop functionality as a cmdlet/script and the exercise that functionality from the GUI (Silverlight,Web,Windforms, or WPF).

The first hurdle in hosting powershell is getting a reference to it. System.Management.Automation does not show up in the .Net list of items. Browse to
C:\Program Files\Reference Assemblies\Microsoft\WindowsPowerShell\v1.0\
and there you find the System.Management.Automation.dll. Verify that your reference works. If you can enter the code line below in the project and Intellisense works (the create method is visible) - you have the right version of the DLL in place.

PowerShell ps = PowerShell.Create();

The Microsoft links on how to host powershell have a good number of starting projects to show the usage of some parts of the API. The examples there are only Console examples. The Code posted on the Powershell blog for the SVR12/13 did use GUI's, however I did not appreciate these exampels as much - as it added a few users onto my machine for showing the security aspects that they used in the demo. The examples are a start, however it will take more for work to get a command of using the full power of the Powershell 2.0 SDK.

Powershell SDK can also execute powershell commands on remote machines using the WinRM functionality - if the remote machine allows the execution and the credentials of the user attempting are authorized. This remote execution allows for collecting information from remote machines back to one location for reporting/processing.

The Microsoft PDC 2009 presentations were targeting development using powershell API toward the Admin community, and controlling what access a given a user - programatically controlling this based on security roles.

With functionality being stored completely in PowerShell cmdlets/scripts, the practice forces a complete seperation of Logic and Presentation - which simplifies testing. Reworking the presentation layer is also greatly simplified. The PDC2009 videos described how the Exchange 2007 team wrote their first GUI in 10 months,as they developed their powershell cmdlets- when they were asked to reskin their GUI; it took only two weeks - the Exchange team was euphoric with that result. They did not have to put everything in the GUI, as the more advanced, or special case scenarios, could be handled by a powershell cmdlet only - thereby reducing clutter in the GUI and overall simplified maintenance.