This is an old post that’s been sitting in my queue for a long time, and I just finally thought I’d publish it, for what it’s worth. The word on the street was that SQL Server 2008’s PowerShell support is going to be limited.
I’m here to say: it’s not so much limited, as it it cautiously restrained. A lot of people were apparently hoping for a SQL Server provider like this demo one that could fully navigate tables and list their contents etc., but I think the SQL Team has decided not to allow that, and I think they probably did the right thing.
Honestly, a provider that can navigate into SQL datatables would be extremely frightening, to the point that it might need to be banned or blocked in some instances. Just imagine a SQL Server dba who’s brand new to PowerShell, but thinks that new provider sounds interesting … so he fires up PowerShell and switches over to the customer orders table (you know, the one with 2 million records of purchases) and does:
cd SQL:\Server\Northwind\Orders
ls | where { $_.ShipName -like "Robert *" }
Just think about that line for a second. Not only is it fetching every record in the table, it has to build a .net object for every single one. One of the most commonly used cmdlet’s in PowerShell is Get-Member, which we typically see used when you’re trying to figure out which properties are available on an item. Applied to the previous example, you can imagine doing something like this:
cd SQL:\Server\Northwind\Orders
ls | Get-Member
Now you’ve retrieved every record from the table … just for the sake of finding out what the columns are! You’re not even using the data. If you’re lucky, the memory use of creating 2 million System.Data.Row objects would cripple your dba’s PC before they were able to try something like this:
cd SQL:\Server\Northwind
ls |
ForEach {
$Property = $_ | gm |
where { $_.Name -like "*Name" } |
Select -First 1; $_."$($Property.Name)"
} | Where {$_ -like "Robert*"}
Anyway. Hopefully this counter example will be enough to not only make you feel better about using a cmdlet instead of a provider, but also to make some people think about how they choose to design things that can cause potentially crippling network traffic or server load. The thing is that although it’s possible to allow the -Filter parameter of Get-ChildItem (ls) to specify both a WHERE clause and a TOP count … that wouldn’t help unless you were sure your users would always filter that way instead of by passing it through | Where-Object.
As a side note: The management studio protects you from this sort of load on your system to some extent by caching data and limiting how many rows it pulls over at a time, but not as much as it could if it allowed you to reorder columns and sort them after you had retrieved the data. It also has the distinct advantage of not necessarily having to create .Net objects to pass into the pipeline for each row on the fly (although, maybe that’s how it works, I don’t know, but I would hope not).
Anyway, from what I’ve understood, the SQL powershell provider will let you navigate the schemas, and will provide some cmdlets to do queries that should replace some of the functions we’ve all been using to do queries. Maybe they’ll even provide something that can do built in paging, like: Get-SqlQuery “Select * from Northwind.Orders” -Next 10 …