Art of the DBA Rotating Header Image

Powershell

A Month of SQLPS – Common Powershell cmdlets in SQLPS

One of the reasons to create a provider is so administrators have a common interface for using a section of the Windows ecosystem. Whether it’s the file system, the registry, or some other slice of the stack, you should be able to use a provider to browse, explore, and manipulate that part of Windows in the same way as you can in other providers. Because each part of the ecosystem is different, each provider will have its own way of functioning. A file or directory is simply not the same as a registry key. This means that some cmdlets will be implemented and some will not, all depending on how the provider is built.

With the SQL Server provider, there are only a handful of common cmdlets implemented:

  • Get-Location: Gets the current node.
  • Set-Location: Changes the current node.
  • Get-ChildItem: Lists the objects stored at the current node.
  • Get-Item: Returns the properties of the current item.
  • Rename-Item: Renames an object.
  • Remove-Item: Removes an object.

None of these are surprise, as they provide most of the basic navigation and retrieval of the provider. You probably know these cmdlets better by their aliases:

Set-Location SQLSERVER:\SQL
cd SQLSERVER:\SQL

Get-Location
pwd

Get-ChildItem SQLSERVER:\SQL\localhost\DEFAULT\Databases
dir SQLSERVER:\SQL\localhost\DEFAULT\Databases

As you can probably guess, if these cmdlets weren’t included, we’d have a pretty tough time getting around the provider. They are also core to our provider experience, so most users don’t even think about them. Having the cmdlets may not seem like a big deal, but without them we’d be stuck. It’s all about that common navigation experience.

Of these cmdlets, Get-ChildItem is most commonly used to work with objects in the provider. We’ve already covered how we can combine Get-ChildItem with either Select-Object or Format-Table to view specific properties of our objects. Add to this Get-Item, which covers the same function, just up a level, allowing us to work directly with a specific object:

Get-Item SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2012 | Format-List Name,Version,CompatibilityLevel,LastBackupDate,ActiveConnections

SQLPS-4-1

This small handful of functions let us get around. We can use Set-Location to browse to the path we need to work in, then leverage Get-Item and Get-ChildItem to acquire objects and work with them. The benefit here is we can use the same syntax for reviewing database objects, files, or environment variables. This common experience eases the use of Powershell and helps administrators move from one stack to another.

There are two other cmdlets on this list that I want to talk about, along with another one that is conspicuously missing. What we’ve covered here handles navigation, but the remaining cmdlets are more about object manipulation. In my next post, we’ll dive a little into those, why they are there, and what’s missing.

A Month of SQLPS: SMO Building Blocks

So far, we’ve covered how to start up SQLPS and some of how we can navigate within the provider. These are the first few steps along the path of using SQL Server and Powershell together. Next is to understand how it works behind the scenes. SQLPS makes a lot more sense once you understand how it’s constructed.

A key concept of Powershell is that everything in it is an object, specifically a .Net object. Everything. I stress this to new users of the language because so much of Powershell’s strength comes from this foundation. For people using other scripting languages, such as Korne or Bash, artifacts are simple strings or integers. Objects add an entirely new level of functionality to the language, giving scripters access to properties and methods for their scripts.

To see what the SQL provider is built on is a simple matter of using Get-Member to interrogate the components:

Get-ChildItem 'SQLSERVER:\SQL\localhost\DEFAULT\databases' | Get-Member

SQLPS-3-1

Note the object type, a Microsoft.SqlServer.Management.Smo.Database object. This is part of the SQL Server Management Objects (SMO) .Net library, an API that’s been around since 2002. What does this mean? That the way SQLPS interacts with SQL Server and its components is the same as if we were using almost any other SQL Server tool out there, including our age old standby, SQL Server Management Studio. There’s not strange voodoo magic going on here.

Because of this foundation on objects, we can leverage their methods and properties for our specific purposes. For example, let’s do a directory lookup on the path we use above:

dir 'SQLSERVER:\SQL\localhost\DEFAULT\databases'

SQLPS-3-2

Unsurprisingly, we get a listing of our databases, but the output columns are predefined. This is baked into formatting definitions within the provider, displaying these properties by default. That’s key, all the displayed columns are properties of the SMO database object. So what if we wanted to show different columns? It’s just a matter of displaying the properties we want:

dir 'SQLSERVER:\SQL\localhost\DEFAULT\databases' | Format-Table name,createdate,lastbackupdate

SQLPS-3-3

We could then start to leverage the methods to automate many of our tasks. Say, for example, you wanted to script off your instance’s logins. While we could use the script task within management studio, we could also use Powershell and the SMO’s .Script() method to accomplish the same task:

dir 'SQLSERVER:\SQL\localhost\DEFAULT\logins' | ForEach-Object {$_.Script() | Out-file C:\TEMP\logins.sql -Append}

SQLPS-3-4

Note that this is exactly the same as if you had scripted these objects out in SSMS. This is because both tools are using the same .Net libraries under the hood. There’s very little difference between these tools, except the interface. This means that, as you make the transition from one tool to the other, you can expect similar behaviors.

Properties and methods are the secret sauce of Powershell and open up many avenues for automation and script writing that we’ll explore as we continue this series. The emphasis here is to understand that using the SQLPS provider isn’t a whole different from other tools you’re used to using. Next up, we’ll start talking about some of the standard Powershell cmdlets available to us in the SQLPS module and how we can use them.

A Month of SQLPS: The Provider

Next up in my series on the SQLPS module is to talk about the fundamentals of the provider. This is the core of the SQL Server module, providing an extension to the shell for managing and working with SQL Server. Providers intended togive administrators a file system-like interface for a part of the Windows ecosystem, allowing for a more intuitive way for managing parts of their environment.  

To list all your providers, just use Get-PSDrive:

GET-PSDrive

SQLPS-2-1

We have several different types of providers, including FileSystem, Environment, and Registry. SqlServer is  listed once you’ve imported the module. Once loaded, we can treat the SQL Server components as a file system, with many of the usual commands. Let’s switch to that drive and see what we have available to us:

CD SQLSERVER:\
dir

SQLPS-2-2

Right away we can see many familiar components. Each of these folders is a different part of the SQL Server stack where we can access and manage our environment. We’ll go ahead and browse into the SQL Engine to see what’s there:

CD SQL
dir

CD SHION
dir

CD DEFAULT
dir

It’s that easy to start browsing around our SQL Servers.

Entering the ‘SQL’ folder means we will be working directly with the SQL Engine. Now, by default the provider will only list the local machine under the SQL folder, but we can access any SQL host from here. Just change your location to that machine name. Note, it’s the machine, not the SQL Server instance.

Once you’re in the target machine, the provider will list all instances on that server. Most of the time you’ll probably see DEFAULT, but if you’re using named instances, then those names will be listed. Next, we’ll move down into the instance itself to get all the components for the instance. It should look pretty familiar, since you probably see those components whenever you open up the object explorer in SSMS.

Powershell providers were written to provide an intuitive interface so that administrators wouldn’t get bogged down in trying to get to different parts of their environment, so it’s not surprising that navigating SQL Servers is easy. Take note of the this pattern:

SQLSERVER:\Folder\Machine\Instance\Components

This is the path for SQL Server components and is key to referencing objects with the SQL Server provider.

However, it’s still a little rough sailing. Since using the provider can involve remote servers, your experience will be bound by the ability to communicate with your SQL Instances. It also means that if you’re using tab complete or Intellisense in the Powershell ISE, it will likely not work with provider components because it will timeout when trying to communicate. You will need to rely more on your knowledge of your instances and some of the components than using auto-completion elements.

Why does this happen? It has to do with the provider’s fundamental building blocks the Server Management Objects (SMO). This is a standard .Net library and brings with it rich, consistent functionality that is used throughout the Microsoft ecosystem. In the next post, we’ll go into detail of the SMO and how it’s implemented in the provider, why this an advantage, and what considerations we need to keep in mind as we use it.

A Month of SQLPS: Getting Started

Time for me to get off the bench and start blogging again. What better way to go than to explore the oft-maligned SQL Server Powershell module, SQLPS. Over the course of the next month-ish, I want to explore the SQLPS module, the cmdlets it provides, and the functionality within the provider. We’ll look at the good and the bad within this module, seeing how we can leverage its functionality to our benefit.

Before we can start using SQLPS, we’ll need to get it installed and loaded. Installing the SQL Server Powershell tools is part of the SQL Server setup, included when you install the SQL Server components, but not separately declared. This is good and bad, because while it will always be available to you on a machine with SQL Server components (client or server), you don’t have the ability to control this.

Once installed, loading it is simple. Just open a Powershell session and use the Import-Module command:

SQLPS-1-1

Uh oh! A warning already? What’s that about? Don’t worry, a little investigation can show us what’s going on. Let’s run that same command with the -Verbose switch:

SQLPS-1-2

Using the -Verbose switch gives us a lot of additional detail and we can see what the problem is. Powershell uses a list of approved verbs, which can be listed using the Get-Verb cmdlet. However, when writing the SQLPS module, the SQL Server team included Encode-SqlName and Decode-SqlName for converting SQL Instance names to the SQLPS path syntax. Encode- and Decode- are not approved verbs, which is why we see that warning.

(Don’t worry about what path syntax is, we’ll cover that in this series.)

What does this mean? Not much, the module still gets loaded just fine. All Powershell is trying to do is warn you that you have some code that doesn’t match the language standards and could be hard for users to find. As we’re already pretty far down the path with the language, this warning probably won’t go away anytime soon. Now, you can have Powershell skip all that verb checking stuff with the -DisableNameChecking switch, but I don’t think it’s really necessary. I just accept the warning message for what it is.

Once the module loads, your Powershell session is put into the context of the SQL Server provider, SQLSERVER:\. This can be a bit of a gotcha because many file lookup patterns change once you are in the SQL Server provider. A notable example is you won’t be able to browse UNC paths until you are back in the context of a file system provider. This is why I recommend this pattern for loading the SQLPS module:

$pwd = Get-Location
Import-Module SQLPS
Set-Location $pwd

This simply captures your current working location and, once the SQLPS module is loaded, will switch you back to it.

It should be noted that the SQLPS module is only available on SQL Server 2012 or better and requires Powershell 2.0. Previous versions will require other logic to load the SQL Server snap-in. In general, I recommend always using the current client tools when possible, but this behavior could limit you if you are SQL Server 2008 R2 or earlier.

Feels a bit like a rough start, doesn’t it? This initial experience is one of the biggest challenges for DBAs getting into Powershell. It’s another language and since the first experience is filled with gotchas, it’s hard to embrace. The reason I want to call these out early, though, is to help you over that first hill. Over this series of posts, I hope to show you the ins and outs so you can effectively use the SQLPS module and not get tripped up on it.

Helping Your #Powershell With Twitter

Today I had a bit of a Twitter conversation with some SQL folks I know that were looking for Powershell help. It’s a common enough thing, of course. The trick is, we SQL people are so used to having the #sqlhelp hashtag as our first line of defense for figuring out problems that it’s weird for us to not have something like that for our other hurdles. Where most of these hash tags bombard you with spam and noise, this one is almost pure signal, so we often forget how good we have it.

PoSHHelp_TweetDeckOther communities would like to capture that magic as well. I know those of us using Powershell want our own hash tag for help. Well, now we’re going to try. Some of the notables already use #PoSHHelp, but not a lot of people pay attention. A group of us want to change that. I’ve already added a column for it in my TweetDeck and will keep my eye out for Powershell questions I can help with. Folks like Shawn Melton(@wsmelton), Adam Bertram(@adbertram), Derik Hammer(@SQLHammer), Rob Sewell(@fade2black), Boe Prox(@proxb), and others will be looking for your Powershell problems and try to assist you over Twitter with the same care and grace as SQLHelp.

It’s going to take some work, but we’ll try and apply the same ground rules to this hash tag:

  1. Questions should fit into 140 characters.
  2. If they don’t, put your question and information on another site (like ServerFault.com) and link to it.
  3. DO NOT SPAM THE HASH TAG. This is important, because in order to make it useful it needs to be kept clean. Don’t use it to advertise your blog posts or articles, but only for Q&A.
  4. Don’t be a dick, a.k.a. Wheaton’s Law. It’s all too easy to let the anonymity of the internet get the better of us. Be polite and respectful to those using and accidentally mis-using the hash tag.

Also, there are some people who also use #PowershellHelp. I’m going to watch this too, but I would encourage you to use #PoSHHelp instead. Mostly because it’s shorter and will give you more room for your question.

So, if you have a problem…

And if no one else can help…

And if you have a Twitter account, maybe you can tweet your question to…

nRhfnZ0

 

Desired SQL Configuration with #Powershell – Part 2

Last week, as part of T-SQL Tuesday 68, I introduced you to two Powershell functions for validating and setting your SQL Server configurations. While useful, they depend on some sort of configuration source, which begs the question: How do we get this source? This post covers the third function that will help you create your configuration source, using an existing server’s values and allowing you to use them directly or modify them as you see fit.

Get-SQLConfiguration

The concept of Get-SQLConfiguration is simple: Get the sys.configuration values of a SQL Server instance and export them as a hash table that can be used by the other functions. The additional criterion to consider is that Test-SQLConfiguration and Set-SQLConfiguration both use the SMO properties to do this, so our configuration source must also use these names. The result is a function that uses the SMO to perform its export:

function Get-SQLConfiguration{
param([string]$InstanceName='localhost'
  ,[string[]] $Filter
  )

$smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
$output = @()
if($Filter){
  $configs = $smosrv.Configuration | Get-Member -MemberType Properties | Where-Object {$Filter.Contains($_.Name)}
}
else{
  $configs = $smosrv.Configuration | Get-Member -MemberType Properties | Where-Object {$_.Name -ne 'Properties'}
}

foreach($config in $configs){
  $output += New-Object PSObject -Property ([Ordered]@{'Name'=$config.Name;
  'DesiredValue'=$smosrv.Configuration.$($config.Name).RunValue})
}

return $output

}

The function itself is not complex, but I did want to add one other feature to it. A simple export might be overwhelming, considering the number of configuration properties within SQL Server. Most of these will actually be left at their defaults. To aid us, I also added a Filter parameter that accepts an array of SMO configuration property names and will only export those properties.

There are a couple patterns to use this function. The simplest is to just run it for one server and use the output to check another:

$configurations=Get-SQLConfiguration -InstanceName PICARD
Test-SQLConfiguration -InstanceName RIKER -Configs $configurations

This call will essentially compare and find the differences between the two instances. If we wanted to get more specific, we could use our Filter parameter to only compare one or two configurations:

$configurations=Get-SQLConfiguration -InstanceName PICARD -Filter @('FillFactor')
Test-SQLConfiguration -InstanceName RIKER -Configs $configurations

It is also possible to store and manage these configurations by using the Get-SQLConfiguration function. Because we’re working with hash tables, we have a variety of storage options. The easiest to get started is to use the built in Export-CSV function and save our configurations as a delimited file (I like pipe, but commas will do):

Get-SQLConfiguration -InstanceName PICARD | Export-Csv -Path .\PicardConfig.cfg -NoTypeInformation

With the text file that is generated, we can go in and edit our configs, removing what we want and editing values we need to update. The result can then be stored in source control for tracking and audit purposes. We could even go as far as to load the configurations into a database table and call them from a central administrative server. There are a lot of possibilities.

The Code

While I’ve published the functions in these two blog posts, there’s an easier way to get ahold of them. These are part of my SQLConfiguration module published on GitHub, which you can download and use (along with my other SQL configuration functions). Please keep in mind this module and the code contained within should still be considered a work in progress and the functions are provided as is. The usual disclaimers apply, so be careful using it and test it thoroughly before making regular use of it. Of course, if you do find any issues, I would love to know about them so I can review and update my code.

There are many additional items that can be added to this. The largest that comes to mind is dynamic configuration values, as these functions consider your configs to be static. Items like minimum memory, maximum memory, and maximum degree of parallelism depend on the hardware configuration and there are various ways to calculate these values. There are also configurations not stored in this part of SQL Server, such as default database directories and SQL Server agent settings. These functions have plenty of room for enhancement. They’re a good start, however, to giving you additional tools to manage your environment, helping to make your SQL Server deployments more consistent and reliable.

#TSQL2SDAY 68: Desired SQL Server Configuration with #Powershell

Welcome to another month of T-SQL Tuesday, started by Adam Machanic(@adammachanic) and hosted this month by Andy Yun(@SqlBek). The topic for this month’s blog party is “Just say ‘NO’ to defaults!”, a call on what we have learned and how we manage SQL Server defaults in our environments. While you will probably find lots of posts out there on what you should or should not set your SQL Server configurations to, I wanted to share with you a post on some tools that can help you manage these configurations.

Management Overhead

Many database professionals, SQL Server or otherwise, learn very quickly that you don’t want to stick with your default settings. The database vendors usually try and set some general values that can apply to most situations, but these typically don’t last long in any enterprise. You probably have a set of configurations you change from the defaults whenever you install a new SQL Server instance.

The struggle, whether you have 5 SQL Servers or 500, is keeping these settings consistent. Sure, you can script out your changes (and should), but how do you manage the changes over time? What if someone changes a setting, how do you enforce your configurations? Or maybe someone else sets up an instance and doesn’t apply your scripts? It becomes an ugly problem to manage.

Since I’m a Powershell fan, I’m also a fan of Desired State Configuration. While the technology is still new on the scene, it’s quickly turning into an effective way to manage your server builds. When thinking about Andy’s topic and the problem of managing changes to your instance defaults, my ‘eureka’ moment was that the DSC model could easily be applied to managing these configurations. The result was three Powershell functions that provide tools you can use to control the settings in your environment.

Test-SQLConfiguration

The first function I wrote was to evaluate the configurations on an instance. To do this, I started with the assumption that I’d have a hash table of Config(name) and DesiredValue(value) pairs. Each pair would be the SMO Configuration Class property and the desired value I wanted to check. Then I would simply loop through each one and, if it did not match, I would add that to an output array. The function would then return a collection of configurations that did not match my desired state.

function Test-SQLConfiguration{
  param([string]$InstanceName='localhost'
    ,[Parameter(Mandatory=$true)][PSObject] $Configs
  )
  $smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
  $output = @()

  foreach($config in $configs){
    if($config.DesiredValue -ne $smosrv.Configuration.$($config.Name).RunValue){
      $output += New-Object PSObject -Property (@{'Configuration'=$config.Name;
                   'DesiredValue'=$config.DesiredValue;
                   'CurrentValue'=$smosrv.Configuration.$($config.Name).RunValue})
    }
  }
  return $output
}

To test this, I put together a very simple pipe-delimited file of configurations I wanted to check. These configuration names had to match the SMO property names (which aren’t difficult to acquire) and the resulting file and output looks like this:

SQLServerConfig.cfg
 Name|DesiredValue
 FillFactor|80
 MaxDegreeOfParallelism|4
 CostThresholdForParallelism|40
 XPCmdShellEnabled|0

SQLConfigure_1

This function provides a quick view of which SQL Server configurations don’t match my desired values. What’s cool is now I can then take this function and easily run it across my entire enterprise.

SQLConfigure_2
With this function, I’ve removed the burden of validating my SQL Server instance configurations. Since the output is an object, there’s many flexible options for reporting and collecting the information. Writing this to a text file is a snap or uploading it to a database table for ongoing auditing.

Set-SQLConfiguration

The next step, after we’ve checked configurations, is to correct the violations. This next function works much like Test-SQLConfiguration and takes the same two parameters as Test-SQLConfiguration. The difference is that the function will now alter the value and then reconfigure the instance to apply the change.

function Set-SQLConfiguration{
  param([string]$InstanceName='localhost'
    ,[Parameter(Mandatory=$true)][PSObject] $Configs
  )
  $smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
  $output = @()

  foreach($config in $configs){
    if($config.DesiredValue -ne $smosrv.Configuration.$($config.Name).RunValue){
        $row = New-Object PSObject -Property (@{'Configuration'=$config.Name;
               'DesiredValue'=$config.DesiredValue;
               'CurrentValue'=$smosrv.Configuration.$($config.Name).RunValue})
        $smosrv.Configuration.$($config.Name).ConfigValue = $Config.DesiredValue
        $smosrv.Configuration.Alter()
        $row | Add-Member -MemberType NoteProperty -Name 'ConfiguredValue' -Value $smosrv.Configuration.$($config.Name).RunValue
        $output += $row
        if($smosrv.Configuration.$($config.Name).IsDynamic -eq $false){$reboot=$true}
    }
  }

  if($reboot){Write-Warning 'Altered configurations contain some that are not dynamic. Instance restart is required to apply.'}

  return $output
}

SQLConfigure_3

Because the function is built to accept a hash table of configurations, we can use the same pipe-delimited file (or any delimited file) to update the instance. Note the warning with the output. Because not all configurations are dynamic, the function will alert you if a non-dynamic configuration was changed. If you change a non-dynamic configuration, you will need to restart the SQL Service to complete the change.

Wrap Up

The last challenge is building out the configurations to check against. The third function I wrote will handle that, but I felt like covering that functionality would make this blog post to long. Next week I will cover the Get-SQLConfiguration function, additional techniques for extending these functions, and then tell you where you can get the code. Please note that this code is in a work-in-progress state, so use with caution. However, this also means that if you have any suggestions, I’d love to hear them so I can turn this into a functional tool that the community can use.

Thanks again to Andy for a great T-SQL Tuesday topic. Keep your eye on his invite blog post and the #TSQL2SDAY hashtag on Twitter for other great contributions.

#Powershell and SQL Server: SQLPS Challenges and Hurdles

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

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

#Powershell and SQL Server: Using the SQLPS Provider

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:

SQLPS_use_1

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:

SQLPS_use_2Pay attention to the structure of the directory paths:

SQLPS_use_3

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:

SQLPS_use_4

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:

SQLPS_use_5

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.

#Powershell and SQL Server: The SQLPS Provider

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:

SQLPS_Intro_1

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:

SQLPS_Intro_2

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