SQL Server Powershell is here!

I am very excited that SQL Server will ship with a pretty solid first release of Powershell extensions with SQL Server 2008. The SQL Server Powershell extensions deliver on a vision that we’ll expand on in the next releases to come. So what’s in this release of SQL Server Powershell?

- SQLPS – a minishell that gives you a complete pre-configured Powershell with all of SQL Server’s extensions preloaded.

- SQL Server Agent integration – A new job subsystem for Powershell

- SQL Server Management Studio Integration – context menus on every applicable node in Object Explorer (with connection context reuse. Including SQL security!)

- Four new Providers! – new providers for SQL Server relational engine, Registered Servers, Data Collection, and SQL Server Policy Management

- SQLCMD integration – SQLCMD compatible script execution within Powershell (reuses the SQL Server connection context, and even database context of the provider!)

- SQL Server Policy Management integration – Allows evaluation of any Policy

- Various other cmdlets – support the provider, such as conversion of a SMO Urn to a Powershell path, encoding and decoding of SQL identifiers.

- SQL Server Powershell redist – allows you to install SQL Server Powershell with your application or on any machine you need to have it on (this still being built so with the caveat it may be shipped later, or being cut altogether – don’t flame me yet).

As always with every release, a lot of things were left on the cutting floor, such as Powershell editor integration & execution within SSMS (with grid output), Powershell script generation from any SSMS menu, SMO objects generating Powershell script, more cmdlets that address common user scenarios, Analysis Server support. And this doesn’t include a long list of ideas that for the sake of brevity will not discuss here.

The excitement about this release is palpable, and there is no shortness of ideas for the next releases. I think you’ll be pleased with this first release.

---

From recent posts and email I know there is some explanation needed why we ship ‘pre-packaged’ SQLPS functionality. Let me address that here as well.

SQLPS is slated to be replacing SQLCMD and other tools that people now use to do ad-hoc management and management task automation. It’s more than that, we want uniformity of management across ALL of SQL Server services, whether it is Analysis Server, SQL Server, Integration Services, Reporting Services etc.

SQLPS.exe is a Minishell (also called “custom shell”). It is a form of pre-packaging of Powershell functionality, and it is available to anyone who wants to do this (make-shell). It is regular Powershell, albeit with limitations that the Powershell team decided to impose on it – it is a ‘closed’ shell, which doesn’t allow adding other snapins.

We are shipping SQLPS to make life of our DBA’s a whole lot easier. If they need to have quick access to the SQL providers, assemblies, cmdlets, default security settings, everything is there. We could have possibly done this through a startup script but not everything can be accomplished this way. We are changing the default security settings, without affecting the settings for overall Powershell. Minishells have their own settings.

We also use SQLPS to execute SQL Agent jobs. We pipe the data into SQLPS, as we cannot pass it on the cmdline. From that perspective it serves our purpose very well. This is somewhat harder (if not impossible) to accomplish using the startup script approach.

That said, in the next release we will look into it to ship a SQPS that is more flexible. Listening to the feedback, we should have a form of packaging that allows users to add cmdlets/providers.

We are however not stopping anyone to create their own startup scripts that include the cmdlets and/or providers for SQL Server, Exchange, IIS etc. etc. To get you started on this, here is a script that exactly does that:

#
# Initialize-SqlpsEnvironment.ps1
#
# Loads the SQL Server provider extensions
#
# Usage: Powershell -NoExit -Command "& '.\Initialize-SqlPsEnvironment.ps1'"
#
# Change log:
# June 14, 2008: Michiel Wories
# Initial Version
# June 17, 2008: Michiel Wories
# Fixed issue with path that did not allow for snapin\provider:: prefix of path
# Fixed issue with provider variables. Provider does not handle case yet
# that these variables do not exist (bug has been filed)

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
throw "SQL Server Powershell is not installed."
}
else
{
$item = Get-ItemProperty $sqlpsreg
$sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}

#
# Preload the assemblies. Note that most assemblies will be loaded when the provider
# is used. if you work only within the provider this may not be needed. It will reduce
# the shell's footprint if you leave these out.
#
$assemblylist =
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum"

foreach ($asm in $assemblylist)
{
$asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}

#
# Set variables that the provider expects (mandatory for the SQL provider)
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml
Pop-Location

Write-Host -ForegroundColor Yellow 'SQL Server Powershell extensions are loaded.'
Write-Host
Write-Host -ForegroundColor Yellow 'Type "cd SQLSERVER:\" to step into the provider.'
Write-Host
Write-Host -ForegroundColor Yellow 'For more information, type "help SQLServer".'

Now, go and use SQL Server Powershell :-). Give us your feedback, and I promise you we’ll listen to it, and we’ll keep on improving on it. This is written for you, the DBA, the IT-PRO, the casual user who needs to do some ad hoc administration or anyone else who uses Powershell for their administrative tasks.

Michiel

Comments

• Anonymous
  June 14, 2008
  PingBack from http://blog.a-foton.ru/2008/06/15/sql-server-powershell-is-here/

• Anonymous
  June 14, 2008
  So with sqlps.exe being a closed minishell.. i know its a dedicated host of its own but is it a seperate installed of the powershell engine, or just a seperate host. I'd be very curious if you package and distribute the whole powershell engine or not.

• Anonymous
  June 14, 2008
  It's not a dedicated host. The Minishell uses the normal Powershell host to run, but it allows the entire environment to be preconfigured. It also has separate security settings, and that was one of the main reasons we selected the minishell as the packaging system. If we did not do this then we would have faced all users with a security message to allow ANY Microsoft Powershell script/extension to be trusted. Our security people did not like that, so we were able to allow SQLPS to trust our SQL related files without affecting overall Powershell security. SQL Server 2008 indeed includes the entire Powershell host (just the regular one that you can download from Microsoft.com) and installs that as a part of the prerequisites (unless you're running Windows Server 2008, which has Powershell included).

• Anonymous
  June 15, 2008
  The comment has been removed

• Anonymous
  June 15, 2008
  Thanks for your feedback. All of the files that we ship will be signed, but we limit the scope of security changes to our minishell, without effecting any overall Powershell settings. That said we heard the message about the minishell (see make-shell which was the mechanism we use to create this shell) loud and clear. We would have addressed it this release if we would not been in lock down mode for a while. This is how ship cycles work.  In the mean time there is an easy way to do this; run the initialization script as a part of your profile. This is what most users willl do who have several environments to manage.

• Anonymous
  June 15, 2008
  I'm still dont understand why it not possible to enable add-pssnapin in minishell. It not such significant change, but it will save SQL admins who wants (for example) JUST send email report using send-smtpmail (PS Community Extensions snapin) to write init script like you posted.

• Anonymous
  June 15, 2008
  We'll be looking at a better way to do this. The Powershell team is also aware of the limitations of the minishell and I expect you can see changes coming to this area. In the mean time I suggest to use an initailzation script as shown above. Thank you for your feedback Vasily, it is much appreciated.

• Anonymous
  June 16, 2008
  A closed shell, if that is the only way the SQL providers and cmdlets are available, is really, really bad news.

• Anonymous
  June 16, 2008
  The SQL providers and cmdlets are available to anyone who uses Powershell. You can load the SQL cmdlets and provider in your startup script.

• Anonymous
  June 16, 2008
  Thanks Michael. Please blog more often. :)

• Anonymous
  June 17, 2008
  I have been reading this and other blogs concerning the SQLPS mini-shell.  I , as a DBA, am shocked that you would gut a perfectly good tool like PowerShell.   What is the purpose of not allowing additional snapins or just allowing only the SQL cmdlets to run?  With the current PowerShell, I have created a script that saves me a month every year.  My entitlement review script goes into SQL Server and pulls a user's information from the database and AD; the information of the user's manager from AD; the database and object permissions of the user; and emails the manager asking them to confirm the permissions are necessary.  The script also alerts me when a user is no longer in AD.  So, with the "new and improved" SQLPS, I will have to give that month back?   This is a bad idea.  You are ruining a wonderful tool.

• Anonymous
  June 17, 2008
  Hi Stacy, you'll still be able to do this. SQLPS is nothing more than an ad-hoc environment that quickly gives you all of the SQL-related providers and cmdlets, but in your case you would just keep on doing what you are doing today, and add the SQL providers and snapins to your profile or init scripts so you can start using these in the type of scenarios that you mention. Most people who have more environments than SQL to manage will add the SQL providers and cmdlets to their profile/init script.

• Anonymous
  June 18, 2008
  out of curisoity what was the thinking behind providing the cmdlets to be added to a full powershell and building a minishell when ( IMHO) most users will simply add the snapins and cmdlets to their profile, or are you saying that the only way to run sqlps files is via the minishell? ( I haven't had a change to DL the CTP yet)

• Anonymous
  June 18, 2008
  Hi Jim, when we started to develop our provider and cmdlets we discussed packaging with the Powershell team. In Powershell V1 there are two packaging mechanisms available, one is a Console, and one is the Minishell. A Console can be created with Export-Console. It does not save the entire state, such as format and type extensions, and loaded providers, and was therefore not useful to us. The other option was a minishell (which is can be created with make-shell from the Powershell SDK). The minishell can give you a complete environment (albeit closed) but it is the only packaging option available in Powershell V1. Like mentioned before, there are no restrictions/limitation to use the cmdlets and provider in any environment you want to use these in. You don't HAVE to use SQLPS. You can use use "Powershell" (the command, as SQLPS is also Powershell ^^).

• Anonymous
  June 18, 2008
  In my previous blog I reviewed the different policy evaluation modes available in SQL Server 2008. In

• Anonymous
  June 18, 2008
  Within the PowerShell community there's a firestorm raging over SQLPS.exe our mini-shell that we're introducing

• Anonymous
  July 01, 2008
  One of the criticisms of our PowerShell implementation is the integration with Management Studio launches

• Anonymous
  August 06, 2008
  I am beginning to think it could be humble sqlps.exe. SQL Server 2008 includes powershell support – it

• Anonymous
  November 21, 2008
  Last night at the User Group meeting I showed a script to load the required assemblies and snapins that

• Anonymous
  December 21, 2008
  If your company is like mine, you are largely at the mercy of your software vendors as to when you'll

• Anonymous
  January 11, 2009
  The comment has been removed