SQL Server

Over the last two posts, I’ve covered what the SQLPS provider is and how you can get started with it. Now it’s time for other side of the story. If you’ve started working with the provider or done some reading on the web, various hurdles have probably popped up. These hurdles can frustrate and discourage you, so let’s discuss them before that happens.

Slower than Molasses in January

The initial complaint you’ll usually see with the provider is that it’s slow. Mind numbingly slow. So slow that it breaks tab completion in the ISE. Unfortunately, due to the way the provider was implemented using the SMO, we don’t have a lot of options

What can we do? Unfortunately, there are no magic workarounds to implement. This limitation is why you will see a lot of Powershell/SQL development either use the SMO directly or implement .Net code that avoids this completely. The challenge here is that these approaches can be too advanced for administrators who are not used to code development. My recommendation is to be patient with it. The provider is your entry point and is your starting point. As you get more comfortable with Powershell, you will find SMO and other .Net methods will perform better for you. Begin with understanding the language.

What You Least Expect

Another challenge with the provider is that sometimes it will behave in a fashion that doesn’t quite make sense. As with the speed, we’re tied to how the SQL Server team at Microsoft implemented the provider. It is, after all, an API and this means we can only use the it as it was written. What I struggle with is that there there are elements of the provider that weren’t thought all the way through and occasionally they catch me by surprise.

One example of this is an interesting piece of behavior I discovered recently with a several online colleagues (Paul Timmerman, Chris Sommer, Derik Hammer, and Johan Bijnens). Basically, we found that there was no real way to refresh a Central Management Server listing within an existing Powershell session. Once a CMS registry was populated in your provider session, you were stuck with it. If you made changes to your CMS in SSMS, you wouldn’t see those changes propagated until you opened a new Powershell session.

While frustrating, it’s not a show stopper. The challenge, however, is that there are other little land mines like this in the provider and they pop up when you least expect it. This particular issue is not really a problem because you can always save a script and re-open your host. However, it should behave a little more consistently and at least give the user the opportunity to execute a manual refresh of the cached object. I’m hoping that the SQL Server team will be able to address this in future versions of SQL Server.

The Mini-Shell

Speaking of things that behave not as you would expect, I want to cover the notorious mini-shell. If you have ever right clicked in Management Studio Object Explorer, you’ve probably seen a Start Powershell option.

The intent of this option is to open up a command window with the SQL provider already loaded. This shell also duplicates what SQL Server does if you ever run a Powershell script within a SQL Server Agent job step. What catches people by surprise is how it behaves. In SQL Server 2012 or prior, the host machine will load using Powershell version 1.0 or 2.0, no matter what version you have installed on the host machine. This can be annoying because modules won’t be automatically loaded and some cmdlets aren’t available to you. This was fixed in SQL 2014, so whatever version is installed on the host machine will be used.

While this can present a hurdle for developing scripts to be used in the SQL Agent, it will not block you completely. You can always double check this by starting the Powershell task in Management Studio and interrogating the $PSVersionTable system variable. Beyond that, I haven’t had much reason to use the mini-shell for any Powershell tasks and prefer to work in the ISE or the standard Powershell command window host.

A Fixer-Upper

The SQLPS provider is klunky. It suffers from a little neglect by the SQL Server team and some slapdash coding to implement it. In light of some of the Powershell tools provided for Windows Server, Exchange, and Active Directory, it’s pretty frustrating to see the SQLPS provider languish the way it has. I have some theories around that and I hope that someday I can talk with the product team to see what changes can be made.

However, it should be noted that if you’re just getting started with Powershell, the SQLPS provider is still the best place to start. If you’ve read my other posts, you know by now there has been an effort to make the provider an intuitive path for building automation around SQL Server. Don’t let the hurdles stop you,. The provider will let you get far enough on its own and help you develop patterns that you can later improve and enhance.

Please feel free to leave any comments below on your experience with the provider and other questions you may have regarding it.

In the previous post, I gave an overview of what the SQLPS provider is and how it works. Now I want to pull back the curtains a bit and demonstrate how you can navigate the provider and use it effectively for managing your SQL Server instances. For those new to Powershell, this can be an effective way to start using the language without a lot of “overhead” for learning syntax and methods.

Being Direct

Focusing on the concept that providers allow us to browse parts of the Windows stack as directory structure, let’s start by just reviewing what “directories” are available with SQLPS:

Right away you can see that there’s a lot of familiarity here, as we can see many components of SQL Server that we’re used to seeing. For the time being, let’s go straight into the SQL Database Engine and look around by browsing down into the SQL\LOCALHOST\DEFAULT path:

Pay attention to the structure of the directory paths:

This pattern is used throughout the provider, offering a consistent structure for our objects. The next thing to notice is that the list of items under our instance is pretty standard and looks very much like what we would find under the object browser in SQL Server Management Studio. The provider is merely an abstraction for all the usual parts of SQL Server that we’re used to seeing.

Objects and Details

The most basic thing we can use the SQLPS provider for is getting lists of our SQL Server objects. Just like getting a directory listing of our files, we can list out our databases with a simple command:

Note the use of the -force flag. If you leave the -force flag off, the provider will only return user databases. Including this flag will display all databases, including the system databases.

What should also be noted is that there are many more fields here available to us, which we can see using Get-Member. As DBAs, a common question is when our databases were last backed up. Within the SMO, each database has the .LastBackupDate property that shows the last full backup, which we can easily include in our directory call:

Getting Things Done

While listing objects (and you can list logins, agent jobs, and other parts of your instance in much the same way) is effective, what else can we do? The key to the next step is recalling that objects within the provider are all SMO database objects.  All of these objects will have attendant properties and methods. We can leverage the properties to accomplish some pretty standard tasks.

One thing I tend to do with my databases is set the owner to ‘sa’. This common task is easy to do in T-SQL and I certainly don’t want to discourage that option, but it’s also an easy task to manage in Powershell by using the .SetOwner() method:

cd SQLSERVER:\SQL\LOCALHOST\DEFAULT\databases
$dbs = dir

foreach($db in $dbs){
$db.SetOwner('sa')
}

#List the directory contents again and look at the owner
dir

With these few lines, I can quickly change the owner of all my databases. How cool is that? Now, one note about permissions. Any actions you execute through the provider will use your current security context. Typically, DBAs will have enough permissions to perform these tasks, but if the account you connect to does not have rights for a task, it will fail.

And so much more

There are so many more examples of what we could use the provider for. Simply using directory lookups can provide a lot of flexibility, but there’s more we can do leveraging the methods and properties of the Provider objects. From this point, it can get very advanced and complex. Focus on the fact that SQLPS provider is a starting point, a place where can quickly get in and manage SQL Server with Powershell.

Next up is some of the warts of the SQLPS provider. A lot of folks who have been using the provider have run in to some challenges. In a lot of ways, how the SQLPS provider works is a little clumsy. This doesn’t mean you shouldn’t use it, but I do want to cover some of the frustrations so that you are aware of them and they don’t prevent you from effectively using this aspect of Powershell.

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’.