Art of the DBA Rotating Header Image

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.

2 Comments

  1. Justin says:

    I am really looking forward to this series Mike!

  2. […] PowerShell Verbs Power Bi, PowerShell and SQL Agent Jobs Configuring Files for TempDB database A Month of SQLPS: Getting Started Bad habits when creating […]

Leave a Reply to Justin Cancel reply

Your email address will not be published. Required fields are marked *