SQL Server Powershell is here!

a-foton » SQL Server Powershell is here!

a-foton » SQL Server Powershell is here! — Sun, 15 Jun 2008 03:02:56 GMT

PingBack from http://blog.a-foton.ru/2008/06/15/sql-server-powershell-is-here/

re: SQL Server Powershell is here!

karl prosser — Sun, 15 Jun 2008 05:55:43 GMT

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.

re: SQL Server Powershell is here!

mwories — Sun, 15 Jun 2008 13:22:32 GMT

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

re: SQL Server Powershell is here!

Vasily Gusev — Sun, 15 Jun 2008 22:02:44 GMT

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

Why your "security" people just not sign their scripts/files? Or if its not just scripts - why not change execution policy (with notify on installation) as Exchange security people did?

This will be MUCH better than creating new separated binary shell when it is uneccessary. And you already did shown that it is not neccessary with script in this post ;)

re: SQL Server Powershell is here!

mwories — Sun, 15 Jun 2008 23:01:40 GMT

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.

re: SQL Server Powershell is here!

Vasily Gusev — Mon, 16 Jun 2008 05:45:48 GMT

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.

re: SQL Server Powershell is here!

mwories — Mon, 16 Jun 2008 11:30:59 GMT

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.

re: SQL Server Powershell is here!

Mark Ayers — Mon, 16 Jun 2008 19:39:54 GMT

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

re: SQL Server Powershell is here!

mwories — Mon, 16 Jun 2008 19:55:26 GMT

The SQL providers and cmdlets are available to anyone who uses Powershell. You can load the SQL cmdlets and provider in your startup script.

re: SQL Server Powershell is here!

JasonMassie — Mon, 16 Jun 2008 22:46:54 GMT

Thanks Michael. Please blog more often. :)

re: SQL Server Powershell is here!

Stacy Hein — Tue, 17 Jun 2008 19:03:54 GMT

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.

re: SQL Server Powershell is here!

mwories — Tue, 17 Jun 2008 19:25:45 GMT

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.

re: SQL Server Powershell is here!

Jim_B — Wed, 18 Jun 2008 18:19:44 GMT

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)

re: SQL Server Powershell is here!

mwories — Wed, 18 Jun 2008 19:48:27 GMT

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

Evaluating Policies On Demand Through PowerShell

Lara Rubbelke — Thu, 19 Jun 2008 07:53:00 GMT

In my previous blog I reviewed the different policy evaluation modes available in SQL Server 2008. In

The SQLPS Firestorm

Dan's Blog — Thu, 19 Jun 2008 09:15:28 GMT

Within the PowerShell community there's a firestorm raging over SQLPS.exe our mini-shell that we're introducing

Launching Vanilla PowerShell from SSMS

Dan's Blog — Tue, 01 Jul 2008 20:22:29 GMT

One of the criticisms of our PowerShell implementation is the integration with Management Studio launches

The best feature in SQL Server 2008 – sqlps.exe?

Eric and the .NET Framework — Wed, 06 Aug 2008 21:36:49 GMT

I am beginning to think it could be humble sqlps.exe. SQL Server 2008 includes powershell support – it

Loading SQL Server Provider

Richard Siddaway's Blog — Fri, 21 Nov 2008 16:22:07 GMT

Last night at the User Group meeting I showed a script to load the required assemblies and snapins that

Using SQL Server 2008 snap-ins with SQL Server 2005

Tim Benninghoff — Mon, 22 Dec 2008 06:14:13 GMT

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

PowerShell : Provider Sql Server

Mim — Sun, 11 Jan 2009 18:15:50 GMT

Je me suis mis récemment à PowerShell, spécialement orienté vers SQL SERVER. Lors de l’installation de