One of the key entry points for using Powershell and SQL Server is the SQLPS provider. If you’ve been reading my blog for any length of time, you’ve seen me make use of the provider for one task or another. It’s an incredibly useful tool and can help you manage some basic tasks in SQL Server without a lot of additional coding on your part.
The struggle that I see is there’s a lot of confusion about what the provider is and how it works. There are also challenges and gotchas around what the SQLPS provider does that frustrate both new and long time users of Powershell. I hope to provide some clarity over the next few posts as I review the basics of the SQLPS provider, how I’ve used it, and how to work around some of these challenges.
What are providers?
The idea of providers is a foundational concept in Powershell. They are fully documented over on MSDN, or directly in Powershell by using ‘Get-Help about_Providers’. Let me give you a starting point, though: the general idea is that providers extend different parts of the stack as file system paths in your shell. They give users an intuitive method to browse components like the registry, environment variables, and SQL Server as if they were directory structures.
You can start by using ‘Get-PSDrive’ from your favorite Powershell host. You’ll see something like this:
Note that I’ve called out the important bits here. You can consider the Name column the drive letter for each provider. The different types of providers are listed on the right. Understand that the drives we are used to seeing (C:\, E:\, etc.) are FileSystem providers and essentially equivalent to other providers, like environment variables. The trick is that the behavior of each provider is different once you start working within them, but accessing them works the same.
Using Providers
To use a different provider is just a matter of running some familiar commands. You can switch to a different provider by using “cd” and see what’s in the provider by using “dir” (which are aliases for Set-Location and Get-ChildItem). All pretty easy, right? Try this, for example:
cd Env:\ dir Get-Content PATH
Already you can see how handy this is, as it gives us a much easier way to view the PATH variable than that tiny little GUI box under Environment Variables.
Let’s talk specifically about the provider for SQL Server. The provider is not part of Powershell by default and you have to load it. Doing this is straightforward, you just need to import the SQLPS module that is installed whenever you installed the SQL Server 2012+ client tools.
Import-Module SQLPS #If you want to see what’s in the module, use this... Get-Command -Module SQLPS
When you execute this command, you’ll get a warning about cmdlet names. You can safely ignore this, the provider has loaded and I’ll cover this message in a later post. A gotcha here is that if your client tools are previous to SQL 2012, you’ll need to load a snap-in. This is an obsolete method, so I strongly recommend you upgrade your client tools to SQL 2012 or better.
The Provider Foundation
Before we get into actually using the provider, we should review what makes the provider tick. Remember that everything in Powershell is a .Net object. Everything. With this in mind, we can look at the building blocks that create the SQL Server provider by using the trusty Get-Member cmdlet:
cd SQLSERVER:\SQL\localhost\DEFAULT\databases dir | Get-Member
Don’t focus on the directory we are drilling into, we’ll cover that in the next post. You are going to get a sizeable output, but you should focus on the first part:
Notice that the typename is a Microsoft.SqlServer.Management.Smo.Database object. This shows us that the SQLPS provider is built on that long standing SQL Server Management Objects .Net library.
I call this out because if you browse the interwebz for examples of using Powershell and SQL Server, you’ll see a mix of code. Some folks will use the provider structure, others will instantiate .Net SMO objects. What you should understand is that these approaches use the same fundamental building blocks. The difference is that if you use the provider, you have some additional abstractions that SQLPS provides you (which means less code), whereas creating the SMO objects forgo them (translating to more code, but more control).
Moving Along
Providers give you a lot of flexibility within your shell experience. The intuitive nature of them allow for an easy entry into using Powershell, whether you have a coding background or not. This folds quite well into the initial intent of Powershell, which is to provide a useful scripting experience for administrators who do not have development experience.
In my next post, I will cover specifics for using the SQL Server Powershell provider. We will review the fundamentals and how these can be leveraged for common administrative tasks. If you have specific questions about the provider and how it can be used, I encourage you to leave these questions in the comments section so that I can answer them in a later post. If you prefer, you can also email your questions to ‘mike at this blog domain’.
I’m tweeting!