Getting Information from the SQL Server Provider with PowerShell
Summary: Guest blogger, Mike Fal, talks about using the SQL Server provider with Windows PowerShell.
Microsoft Scripting Guy, Ed Wilson, is here. We have a new guest blogger today. Please welcome Mike Fal. Mike Fal. Mike has over 15 years of experience as a database administrator. He has worked for many different industries, including healthcare, software development, marketing, and manufacturing, and he has experience supporting databases from 1 GB to 5 TB in size. To follow him on Twitter, use @mike_fal.
Windows PowerShell providers are an integral part of PowerShell, but their use is so subtle that sometimes they go unnoticed. For those who are unaware, providers were added to PowerShell to give users of the language a way to navigate parts of the Windows ecosystem as if they were file systems. Administrators commonly browse to files and directories, so it makes a lot of sense to give them a way to see other aspects of the stack in the same way.
One of the providers that isn’t very well known is the one included with the SQL Server module. If you have installed any of the SQL Server components, whether to a server or a client, you will have access to the SQLPS module. You can load it as you would any other PowerShell module:
Import-Module SQLPS
You will get a warning message when you do this, but do not worry. This warning is because two cmdlets in the module do not match the verb standards of the PowerShell language. The module still loads all of its functions and its provider, which you’ll be automatically switched in to.
There is hidden power within the provider that often gets overlooked. What we need to keep in mind is that the provider acts as a file system. If we want to know about the size of our files or when they were created, we can easily look that up with Get-Item combined with Select-Object. We can also do that with the SQL Server provider because everything contained within it are .NET objects with properties relevant to SQL Server.
Today's Power Tip, Use PowerShell to Find Versions of SQL Server, is a perfect example of this. The path to each instance through the provider returns an SMO.Server object to represent the SQL instance. Attached to that are all sorts of properties we can view (use Get-Member to check them all out).
Due to how the provider is built, it will not have a list of all your instances (there are too many variables related to whether you can connect to your instances), so we have to create our own list. We can do this via an explicit array, a text file list, or even a SQL Server query. The result is retrieving each object and displaying its properties.
We can use this pattern for any aspect of our instance. Often, database administrators want to know how big are their databases or when they last were backed up. This is easy with the provider—it’s just a matter of returning the properties of the underlying SMO.Database object:
Get-ChildItem SQLSERVER:\SQL\PICARD\DEFAULT\Databases | Select-Object Name,LastBackupDate,Size
Now, I can hear the DBAs in the audience, “Why Powershell? I can do that with a T-SQL query!”
That’s true. But the strength of Powershell is its ability to execute across multiple instances. Let’s combine this call with our PowerTip to see what happens:
$instances = @(‘KIRK’,’SPOCK’,’PICARD’,’RIKER’)
$instances | ForEach-Object {Get-ChildItem “SQLSERVER:\SQL\$_\DEFAULT\Databases”} |
Sort-Object Size -Descending |
Select-Object @{n='Server';e={$_.parent.Name}},Name,LastBackupDate,Size
We’ve got some pipeline work here, but the result is a couple lines of PowerShell that give us a straightforward report of all of our databases, how big they are, and when they were last backed up. This gives an administrator a quick way to audit the environment.
If we remember that the provider works like a file system and combine it with the .NET object foundation of PowerShell, we open all sorts of reporting possibilities. Much of this work can be done directly via T-SQL, so it’s a matter of balancing your tools. PowerShell and T-SQL can work together to give us additional tools to manage SQL Server. In my next post, I’ll show how we can combine T-SQL and calls from the provider to execute management tasks across our environment.
That is all for now. Join us tomorrow when Mike will bring us more PowerShell goodness.
I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.
Ed Wilson, Microsoft Scripting Guy
Light
Dark
0 comments