<w:sdtpr></w:sdtpr><w:sdt id="89512082” title="Post Title” storeitemid="X_19133381-BE7C-4F1A-98DF-18F2EE424C7F” text="t” docpart="DC9F1CADC2C240489A71F0D7E99C41C2” xpath=”/ns0:BlogPostInfo/ns0:PostTitle"></w:sdt>No wait, that’s kickboxing.  PowerShell is the something of the future.  The management interface, the uber-scripting language, the what??? 

PowerShell, and its SQL-targeted implementation shipped with SQL Server 2008, brings to mind Michael Faraday’s response when asked “What use is electricity?”  He replied “What use is a newborn baby?”  PowerShell is somewhat of a newborn baby, much like the very early versions of SQL-based databases were.  We see how those databases have grown and transformed IT and business in ways we never thought of.  Maybe the future of PowerShell is just as bright?

Enough philosophy, let’s see if we can put this baby to use.  Rather than the obligatory “Story of PowerShell”, I am just going to dump you to the Windows PowerShell home page.  http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx  You don’t need me to rehash the story or to write yet another “What is a cmdlet” post.

Now, let’s play with this toy and SQL Server.  SQL 2008 installs a SQL-Specific provider into PowerShell when launched from SQL Server Management Studio.  BOL includes instructions on how to configure PowerShell to add the SQL provider by default or to launch SQLPS.exe outside of SSMS.

The SQL Provider allows you to browse any SQL Server system list like the file system.  While this is all based on SMO (Server Management Objects), this is not exactly the same as browsing the SMO object model.  As a matter of fact, the toughest thing in PowerShell is bridging the gap between the provider and the object model.  Part of this is that the SMO object model is somewhat flat, while the provider shows as a hierarchy. 

Start with the provider and drill down to a particular column.  You get something like this:

SQLServer:\SQL\MachineName\InstanceName\Databases\Adventureworks2008\Tables\Sales.CreditCard\Columns\CardNumber

If we want an SMO object for the current item we do this:

PS SQLServer:\...\Sales.CreditCard\Columns\CardNumber> $MyColumn = Get-Item .  

Note the “.”  at the end.  Very important.  You get an error otherwise

If we just wanted a “blank” Column object we would do this:

PS SQLServer\...\Sales.CreditCard.Columns.CardNumber> $MyColumn = New-Object –TypeName Microsoft.SQLServer.Management.SMO.Column

Pretty much every object for SQL is directly under SMO in the object model, regardless of where it is in the provider hierarchy.  This makes it easy to find the documentation on each object since the links are all on one page.  The provider hierarchy should look very familiar since it is very close to what we see in SSMS.

Now we can create objects mapped to actual provider locations which represent real server elements.  From here it is not so hard to get or set properties, invoke methods, and make a lot of adjustments within the SQL Server system.  Since SSMS is based on SMO, we can reasonably assume that any task in SSMS can be accomplished from PowerShell.   Remember, PowerShell is a scripting environment, not an application development environment.  Some of the things in SSMS require a lot of “glue” to tie the object changes together to accomplish a particular task.

This brings us back to why is PowerShell for SQL still a baby?  Well, a baby can only do three things (eat, cry, poo).  PowerShell is not quite as limited, but it is really only good for automating some very specific tasks and not for general SQL Server operation and maintenance.  I see two critical cmdlets missing in the SQL PowerShell story before we can say it has grown to the next stage.  Copy-item and New-Item would make SQL much more complete.  Right now, if I want to create a copy of a table, alter its properties by adding a column and change its database (called a parent in the object model), then write the changes to the server, effectively createing the altered table in the new target database,  I would have to drill down and copy each column and each property of each column individually.   Copy-Item does with other providers such as the file system.  Right now, if you try to invoke the Copy-Item cmdlet you get:

Copy-Item : SQL Server PowerShell provider error:  Copy-Item is not supported.

And yes, it is bright red by default.

New-Item is a close cousin.  If I back up to the columns level (cd ..) and want to add a new column, I cannot use New-Item.  I get:

New-Item : The method or operation is not supported

This error message gives me hope that maybe I am not doing something correctly, but I suspect it is also not completely implemented.  I get the same message when I execute it at the database level of the provider.

So, while PowerShell is unfinished, it is still a cool tool do script a lot of tedious, repetitive junk where you just set the same thing over and over or to retrieve the same thing from all databases/tables/etc.  For version 1, I give it an “A”.