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:

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:

• Test machine OS: Windows XP with SP3
• PowerShell version 1
• SQL Server 2008 R2 Express Edition
• Makeshell build directory with the files and directory documented in the setup section

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

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

• sqlps runs on PowerShell V1 or PowerShell V2
• sqlps is neither PowerShell V1 nor PowerShell V2, rather sqlps is a minishell.
• Minishells implement the RunspaceConfiguration class which forces the shell developer to explicitly define the cmdlets available to the minishell
• Because sqlps was created using make-shell referencing the PowerShell V1 assembly, only PowerShell V1 cmdlets are available in sqlps
• The SQL Server 2008 R2 installation media includes PowerShell V1 under \1033_ENU_LP\xx\redist (where xx represents platform x86, x64, IA64)
• The SQL Server 2008 and 2008 R2 installer checks whether PowerShell is installed (V1 or V2) and passes the check whether PowerShell is installed or not
• The SQL Server 2008 and 2008 R2 installer will install PowerShell V1 if PowerShell is not installed on the machine.
• There are no changes in sqlps between SQL Server 2008 and SQL Server 2008 R2