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: