SQL Server PowerShell Provider follows the Version of PowerShell on the Host and other errata

There may be some misunderstanding on how the PowerShell Provider for SQL Server works. I’ve written an article or two explaining that you can use PowerShell with SQL Server, without having the SQL Server 2008 (or higher) provider around. After all, PowerShell just uses .NET, and SQL Server “Server Management Objects” or SMO listen to that interface as well.

In SQL Server 2008 and higher we created a “MiniShell” for PowerShell that gives you the ability to treat a SQL Server Instance as a drive (called a “Provider” or path or drive) and a few commands (called command-lets). Using these two simple constructs you can move around SQL Server quickly and work with the objects it holds.

I read the other day where someone stated that we had “re-compiled” PowerShell, so that you would have version 1.0 from SQL Server and 2.0 on your new server. Not so! Drop to a SQLPS prompt and a PowerShell prompt and type this in each:


They should return the same value.

You can think of a MiniShell as simply a compiled “profile” that gives you those providers and command-lets automatically – that’s all. In fact, you can load the SMO libraries yourself without the SQL Server 2008 Provider anywhere in sight. I do this all the time, since the MiniShell also has other restrictions.

Also remember that if you run a PowerShell script as a SQL Agent Job step type (in 2008 and higher) that you’re running under the context of the account that starts Agent – I think most folks know this, but it’s good to keep in mind.

There’s a re-written section of Books Online that goes over working with this very nicely – also covers the question “How to I connect to another server using the SQL Server PowerShell Provider” (hint: It’s just CD) and “How do I load all the SMO stuff if I don’t want to use the Provider” and more. Be sure and check out the note at the bottom that explains the firewall exceptions you’ll need to enable to CD to that remote server. Here’s that link: http://msdn.microsoft.com/en-us/library/cc281947.aspx

Comments (1)
  1. Aaron says:

    I wonder if my question led to this post- Max Trinidad had written a blog post and a flurry of tweets to support the idea that SQLPS is a self contained PowerShell install.

    Here's the general timeline:

    – Running MSSQL 2005 and PowerShell 1 on Windows XP 32-bit.

    – Upgraded to MSSQL 2008, happily began using SQLPS.

    … a few months pass …

    – Upgraded to PowerShell 2.

    – SQLPS started having performance problems, especially with tab completion. Tasks which used to complete in under 5 seconds now take 30+ seconds and spew forth many WMI errors.

    Max wasn't able to reproduce the problem. However, I just installed Windows 7 64-bit and am experiencing the same behavior with the combination of PowerShell 2 (built-in on W7) and MSSQL 2008 R2- tab completion often takes 30+ seconds, to the point that I try to remember not to use it. In this case, I did a clean install of both Windows 7 and MSSQL 2008 R2- no 2005->2008 upgrade, just fresh installs onto a new hard drive.

    <a href="max-pit.spaces.live.com/…/cns!A034D6A0DDC4E64E!1017.entry">Here is Max's post</a> where he provides his support for SQLPS being self-contained and therefore unaffected by my upgrade to PSv2…

    I understand that the immediate solution is to simply not use tab completion in SQLPS, but old habits die hard; the bottom line is that this functionality used to work fine on the combo of XP with PowerShell 1 running MSSQL 2008- the only stable factor for the instances of slowness is PowerShell 2.

    The problem persists even when I'm communicating with the SQL Server instance on localhost and the firewall is completely turned off…


Comments are closed.

Skip to main content