http://www.sqlservercentral.com/blogs/chadmiller/2010/05/28/the-truth-about-sqlps-and-powershell-v2/

Printed 2014/09/01 03:00AM

The Truth about SQLPS and PowerShell V2

By Chad Miller, 2010/05/28

With the release of SQL Server 2008 R2 there have been claims that sqlps is really PowerShell V1 under the covers or that sqlps is PowerShell V2 because it returns $psversiontable information. Although, technically, sqlps neither PowerShell V1 nor V2, the answer to this question is a little more complicated and a closer look into the inner workings of sqlps is needed. Before we can look into the implementation details of sqlps let’s review how various Microsoft products implement custom shells.

Custom Shell Implementation and History

Microsoft products that provide PowerShell integration excluding the sqlps implementation which will cover next; provide a custom shell/console in one of two ways: (Note: profiles are not mentioned because they are not used in package products)

  1. Use a console file
  2. Call a PowerShell script on startup

Console Files

A console file is a special XML file with a psc1 extension that stores the configuration information for a PowerShell session including all the custom Snapins loaded. Creating a console file is simple and anyone can do it calling the cmdlet Export-Console. You can then use the console file when starting PowerShell by specifying the –PSConsolefile parameter:

Powershell.exe –PSConsoleFile path_to_consolefile

See Richard Siddaway’s post on console files for more information.

If you look at the shortcut properties of the following Microsoft products you’ll find the use of console files with the following syntax:

Product Syntax
Exchange 2007 C:\WINDOWS\system32\WindowsPowerShell\v1.0\PowerShell.exe -PSConsoleFile “C:\Program Files\Microsoft\Exchange Server\bin\exshell.psc1″ -noexit -command “. ‘C:\Program Files\Microsoft\Exchange Server\bin\Exchange.ps1′”
System Center Virtual Machine Manager 2008 C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -PSConsoleFile “C:\Program Files\Microsoft System Center Virtual Machine Manager 2008 R2\bin\cli.psc1″ -NoExit
Operations Manager C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -PSConsoleFile Microsoft.EnterpriseManagement.OperationsManager.ClientShell.Console.psc1 -NoExit .\Microsoft.EnterpriseManagement.OperationsManager.ClientShell.Startup.ps1

Startup Scripts

PowerShell console files have a couple of limitations first they don’t support modules and second they require starting PowerShell with the PSConsolefile parameter. There are also non-technical issues in the use of console files—many PowerShell users simply don’t like custom consoles and console files don’t lend themselves to reuse in a regular PowerShell console. Looking at Exchange 2010 we see the console file used in Exchange 2007 has been replaced with a startup script. The script is nothing more than a plain old PowerShell script with a ps1 extension. The properties of Exchange 2010 console looks like this:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -noexit -command “. ‘C:\Program Files\Microsoft\Exchange Server\V14\bin\RemoteExchange.ps1′; Connect-ExchangeServer -auto”

I’m not sure if other products use startup scripts. There doesn’t seem to be much documentation on best practices for custom console creation, but I suspect because of the limitations of console files we will see more products use startup scripts.

SQLPS

This brings us to sqlps, how does SQL Server provide a custom console? Well, sqlps is implemented as something called a minishell. The  sqlps use of a minishell is unique among Microsoft products. In fact sqlps is the only product to develop PowerShell integration in this fashion and will probably be last. The use of a minishell was not well received upon the initial release of SQL Server 2008. (Note: the intention of this post is not to critique sqlps usage of minishell, but rather to provide historical summary to the reader, so please no flaming sqlps). Unfortunately the release of SQL Server 2008 R2 includes zero changes to sqlps so, we’ll have to look to the next release of SQL Server to get it fixed. In the next section we’ll create our own minishell in order to see demonstrate sqlps is implemented

Creating A MiniShell

To really understand how a program works we must delve into the code.

Note: The demonstration that follows uses a deprecated tool called make-shell. This tool should not be used for creating PowerShell solutions as part of a product distributed. The purpose of the sample code is illustrate how sqlps minishell is created.

According to Michiel Wories’ (author of sqlps and creator of SMO) blog post SQL Server Powershell is here! sqlps was created via a utility called make-shell. Because make-shell has been deprecated the MSDN documentation has been pulled. A Bing search turns up little other an old post from PowerShell V1 pre-release days which seems to indicate that make-shell was used in beta builds of PowerShell to create cmdlets. The approach demonstrated is even more awkward than the installutil plus add-pssnapin approach to adding cmdlets adopted in V1 and much more complex than the simple import-module approach used in PowerShell V2. We should be thankful make-shell didn’t make into mainstream PowerShell V1 development and how truly simple it is to add cmdlets in PowerShell V2.

Although the make-shell tool has largely disappeared from online docs, the utility and documentation are still available as part of the Windows SDK (both 6.0A and 7.0 versions). Once you install the SDK (any version higher than 6.0A), you can simply copy the standalone make-shell executable to any machine. We’re now ready to build our own SQL Server minishell.

Setup

First you’ll need to install SQL Server 2008 or SQL Server 2008 R2. The Express edition will suffice. Next we need to setup our build directory. To keep things simple and because the paths are a little tricky, we’ll create a directory called C:\makeshell and copy the following files from C:\Program Files\Microsoft SQL Server\100\Tools\Binn, C:\Windows\System32\WindowsPowerShell\v1.0 and C:\Program Files\Microsoft SDKs\Windows\v7.0\Bin. The contents of C:\makeshell should contain:

en<DIR>*
Certificate.format.ps1xml
DotNetTypes.format.ps1xml
FileSystem.format.ps1xml
Help.format.ps1xml
make-shell.exe
Microsoft.SqlServer.Management.PSProvider.dll
Microsoft.SqlServer.Management.PSSnapins.dll
PowerShellCore.format.ps1xml
PowerShellTrace.format.ps1xml
Registry.format.ps1xml
SQLProvider.Format.ps1xml
SQLProvider.Types.ps1xml
types.ps1xml

*The en directory contains the MAML help file for the SQL Server provider and cmdlets (Microsoft.SqlServer.Management.PSProvider.dll-Help.xml and Microsoft.SqlServer.Management.PSSnapins.dll-Help.xml)

The make-shell utility needs to reference the System.Management.Automation.dll. If you’re using PowerShell V1 this assembly is available in the C:\Program Files\Reference Assemblies\Microsoft\WindowsPowerShell\v1.0\ directory. Stating with PowerShell V2 the reference assemblies are no longer included with the PowerShell installation. Instead you’ll need to install Windows SDK 7.0 or higher which provides the reference assemblies in the same location as V1 (C:\Program Files\Reference Assemblies\Microsoft\WindowsPowerShell\\v1.0\ ).

Test Scenario #1:

Steps

  1. Launch PowerShell with -noprofile switch
  2. CD to C:\makshell directory
  3. Run the following command to create a minishell called sqlps2:
.\make-shell.exe -out sqlps2 -ns Community -lib "C:\\Program Files\\Microsoft SQL Server\\100\SDK\\Assemblies\\,C:\\Program Files\\Microsoft SQL Server\\100\Tools\Binn\\,C:\\Program Files\\Reference Assemblies\\Microsoft\\WindowsPowerShell\\v1.0\\" -reference "Microsoft.SqlServer.Management.PSSnapins.dll,Microsoft.SqlServer.Management.PSProvider.dll,Microsoft.SqlServer.Smo.dll,Microsoft.SqlServer.Dmf.dll, Microsoft.SqlServer.SqlWmiManagement.dll,Microsoft.SqlServer.ConnectionInfo.dll,Microsoft.SqlServer.SmoExtended.dll,Microsoft.SqlServer.Management.Sdk.Sfc.dll, Microsoft.SqlServer.SqlEnum.dll,Microsoft.SqlServer.RegSvrEnum.dll,Microsoft.SqlServer.WmiEnum.dll,Microsoft.SqlServer.ServiceBrokerEnum.dll,Microsoft.SqlServer.ConnectionInfoExtended.dll, Microsoft.SqlServer.Management.Collector.dll,Microsoft.SqlServer.Management.CollectorEnum.dll" -formatdata SQLProvider.Format.ps1xml -typedata SQLProvider.Types.ps1xml

And viola, you should have a sqlps2.exe minshell ready for use. An interesting side-effect of make-shell is that it also produces a C# source file. I’ve included both the executable and generated source code in the download. I could not find any licensing agreement that will allow me to distribute the SQL Server provider, snapin or PowerShell help files, so you’ll need to copy the files specified in the setup section. The source code is really interesting as  it provides details on why minishell’s behave as they do. Looking at sqlp2.cs source code we see a minishell is really nothing more than a PowerShell host that implements its own RunspaceConfiguration class. As Joel Bennett noted in his blog post Is PowerShell $ShellId too big a burden? using the RunspaceConfiguration class in a custom PowerShell host comes with some expensive side effects:

  1. You have to configure the available .net Assemblies, Cmdlets, Format files, Initialization Scripts, Providers, Scripts, and Type files.
  2. You don’t inherit anything from PowerShell.exe (like for instance, the ExecutionPolicy)
  3. Some cmdlets just don’t work like Add-PSSnapin which means you can not load additional cmdlets

This explains why a minishell behaves the way it does. It also explains why running sqlps on PowerShell V2 results in the same cmdlets as sqlps on PowerShell V1—you have to explicitly list each cmdlet available to the host when creating the shell. The make-shell utility is generating the executable and source code to create the list of cmdlets automatically for the PowerShell host developer. Since sqlps was created using make-shell and PowerShell V1 assemblies ONLY PowerShell V1 cmdlets are available in sqlps.

We’re now ready to startup our new minishell and run a few commands:

PS C:\makeshell> $shellid
Community.sqlps2
PS C:\makeshell> get-command | where {$_.commandtype -eq ‘Cmdlet’} | measure-object

Count    : 130
Average  :
Sum      :
Maximum  :
Minimum  :
Property :

PS C:\makeshell>

Our minishell, sqlps2.exe, is functionally equivalent to sqlps.exe and the output above shows the number of cmdlets is 130, the same as sqlps. Note: if you’re using an OS with UAC enabled you’ll need to run the minishell as administrator in order to set the execution policy to remotesigned on first run.

Test Scenario #2
  • Test machine OS: Windows 7 x64
  • PowerShell version 2
  • SQL Server 2008 R2 Express Edition
  • Makeshell build directory with the files and directory documented in the setup section
  • Windows 7 and .NET 3.5 SP1 SDK (provides PowerShell V2 assembly)Before creating a new sqlps2First let’s take the sqlps2.exe including the entire folder created from a PowerShell V1 assemblies in Test Scenario #1 and run the executable on a system with PowerShell V2.

    PS C:\makeshell> $shellid
    Community.sqlps2
    PS C:\makeshell> get-command | where {$_.commandtype -eq ‘Cmdlet’} | measure-object

    Count    : 130
    Average  :
    Sum      :
    Maximum  :
    Minimum  :
    Property :

    PS C:\makeshell>

    The number of cmdlets is still 130 and zero V2 cmdlets are available within our sqlps2.exe minishell. This is not surprising considering sqlps2 like sqlps was created by referencing PowerShell V1 assemblies. Next let’s create a new sqlps2 referencing the PowerShell V2 assembly.

    Steps

    1. Launch PowerShell with -noprofile switch
    2. CD to C:\makshell directory
    3. Run the following command to create a minishell called sqlps2:
    .\make-shell.exe -out sqlps2 -ns Community -lib "C:\\makeshell\\,C:\\Program Files\\Microsoft SQL Server\\100\SDK\\Assemblies\\,C:\\Program Files\\Microsoft SQL Server\\100\Tools\Binn\\,C:\\Program Files\\Reference Assemblies\\Microsoft\\WindowsPowerShell\\v1.0\\" -reference "Microsoft.SqlServer.Management.PSSnapins.dll,Microsoft.SqlServer.Management.PSProvider.dll,Microsoft.SqlServer.Smo.dll,Microsoft.SqlServer.Dmf.dll, Microsoft.SqlServer.SqlWmiManagement.dll,Microsoft.SqlServer.ConnectionInfo.dll,Microsoft.SqlServer.SmoExtended.dll,Microsoft.SqlServer.Management.Sdk.Sfc.dll, Microsoft.SqlServer.SqlEnum.dll,Microsoft.SqlServer.RegSvrEnum.dll,Microsoft.SqlServer.WmiEnum.dll,Microsoft.SqlServer.ServiceBrokerEnum.dll,Microsoft.SqlServer.ConnectionInfoExtended.dll, Microsoft.SqlServer.Management.Collector.dll,Microsoft.SqlServer.Management.CollectorEnum.dll" -formatdata SQLProvider.Format.ps1xml -typedata SQLProvider.Types.ps1xml

    We’re now ready to startup our new PowerShell V2 based minishell and run a few commands:

    PS C:\makeshell> $shellid
    Community.sqlps2
    PS C:\makeshell> get-command | where {$_.commandtype -eq ‘Cmdlet’} | measure-object

    Count    : 220
    Average  :
    Sum      :
    Maximum  :
    Minimum  :
    Property :

    PS C:\makeshell>

    The V2 based minshell contains most of the 236 base PowerShell V2 cmdlets minus those that are not implemented in RunspaceConfiguration class. Interestingly enough import-module is included which allows us to add cmdlets to sqlps2.

    Conclusions


  • Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.