Derek Comingore Blog

Derek Comingore's Microsoft Business Intelligence Blog

SQL Server 2008: PowerShell (PSH) Integration


Before I get into this new SQL Server 2008 feature I want to briefly review why we have a new command-line in the Windows platform…In the current Windows ‘landscape’ we have a variety of technologies that can be used to programmatically administer various Windows data stores:
1.       MS-DOS and Batch Files
2.       Windows Script Host
3.       VBScript/Jscript & COM Object Models
4.       CLR Languages & Managed Object Models
This current Windows programmatic administrative environment is a mess to say the least and when you need to incorporate multiple data stores into a single ‘script’ it can be more challenging. What is needed is a single environment for administrators to easily manage and manipulate the various data stores.
Enter Windows PSH…with PSH we have an extensible, unified, command-line environment for programmatic administration of the Windows platform! Through the development and use of PSH ‘Providers’, both Microsoft and the larger community can continually adapt PSH to more repositories on the Windows platform! And like most new Windows components, the new command-line is built on top of the .Net Framework and the underlying CLR.
I will not delve into the details of PSH, please see the Resources section of this blog post to find the product specific documentation. You should become familiar with concepts such as cmdlets, providers, and the associated PSH scripting language. There is plenty of good content already out there on these topics.
I read somewhere (on a blog), someone labeled the SQL Server 2008 PSH Integration as a ‘sleeper feature’ and I have to agree. While in the PSH marketing material, the technology is being touted as one of the quickest adopted technologies, in the real-world I’ve yet to come across its real-world use. I am sure there are many organizations currently using PSH; however I do doubt the current marketing statements about its overall community adoption. Very few folks I work with know anything beyond the product’s name, little alone what SQL Server 2008 provides in the way of PSH and thus this blog post.
So what does SQL Server 2008 provide us with in the way of ‘PSH Integration’? Plenty (for a V1 feature)…
1.       Full Relational Engine & Policy Navigation & Manipulation (this is done via the SMO/Policy Managed classes and is implemented via PSH providers)
2.       Object Explorer in Management Studio allows you to start a new PSH session using the current context of the relational engine hierarchy.
3.       A new SQL Server Agent PSH native job step
4.       A new cmdlet for invoking TSQL or XQuery statements
So what does the new feature not provide…Replication/SSIS/SSAS Navigation/Manipulation! There is however a community built SSAS 2005|2008 PSH provider, the link can be found in the resources section of this post. Essentially, we have functionality that is exposed via the SMO and new policy classes and thus functionality entailed in RMO, the DTS/SSIS managed classes, and the AMO object model are not yet exposed via a PSH provider (at least not out-of-the-box).
PSH & SQL Server 2008 PSH Integration Resources:
1.       PSH Homepage
2.       PSH Team Blog
3.       PSH 1.0 DL Page
4.       PSH Scripts Repository
5.       SQL Server 2008 PSH TechNet Homepage
6.       SQL Server Analysis Services 2005|2008 PSH Provider via CodePlex

Legacy Comments

re: SQL Server 2008: PowerShell (PSH) Integration
Just one thing to say: PoSH is fantabulous! I have used it in our company since the first official release and will never come back to VBScript or JScript. It's universal, well-thought and well-designed, can easily interact with COM, .NET, WMI, SMO, the Registry and many more in a standardized way. One word of caution, though: If you start using it, pay good attention to its type promiscuosity and variable scoping rules which are so different from those of statically typed languages like C# or VB.NET...