Art of the DBA Rotating Header Image

September, 2015:

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


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'


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


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}


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:



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:



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:




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:


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:


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:


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 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…



#SQLPASS 2015 Board of Director’s Elections

Apologies for the unplanned blogging hiatus, but I’m back. While I’ve got a few technical post ideas bouncing around in my head, some events over the past two weeks have arisen that I want to blog on. If you’re hoping for more Powershell posts, do not despair! They will be along shortly.

Recently PASS took nominations for the annual Board of Directors election. This is an opportunity for members of the community to step up and volunteer their efforts on a global level to help shape and guide PASS, as well as act as a conduit for the community’s voice. Every year has a solid slate of candidates and this year is no different. In no particular order, they are:

As a community, we’ve got some tough decisions, as all four candidates are outstanding members of our #SQLFamily. Thanks to all of them for devoting so much time and effort to make our organization better. No matter the outcome of this election, the PASS community will be well served.

With all this being said, I wanted to share a little more with you about the new candidates and voice my support for them in this election. This is not to tell you how to vote, but to provide you some insight to who these gentlemen are so you can make a better informed decision. To be honest, I’m going to have a tough time myself.


Ryan Adams is a PASS member I’ve known for a couple years now and very few people can compete with his passion and drive for building the community. Ryan is the President of the Performance PASS virtual chapter, helping guide and build that outlet into one of the stronger virtual resources out there. This is in addition to being a board member for the North Texas SQL Server Users Group, one of the largest PASS chapters out there. Add to this the fact that Ryan is an active community speaker, sharing his knowledge and passion at numerous PASS events. This makes it no surprise that he’s an active Microsoft MVP, as he’s an excellent community voice for SQL Server and the people who support it.

But that’s all out there on the Googles for you to find. You probably want to know what sets Ryan apart. I can tell you that there are very few people as driven and energetic about PASS as Ryan is. I’ve had several conversations with him and his passion is infectious. Combine this with his grassroots organizational experience and it is no surprise that he has thrown his name into the hat. Ryan is someone who not only knows what needs to be done, but also how to make it happen.


Full disclosure before I get started: Argenis Fernandez is a close friend that I’ve known for several years. He’s had a tremendous impact on both my professional career and community participation. This shouldn’t discount any of want I would say about him and, hopefully, only strengthens these views.

I think most people have probably heard of Argenis at this point. He’s a Microsoft Certified Master and MVP. For a long time he’s been one of the strongest technical voices in the community. What sets Argenis apart is that he has a fantastic ability to connect with others. Anyone who has talked with Argenis knows that he genuinely cares about the success of those around him. He constantly mentors other and builds up those he talks with. In sports terms, he’s that super start that makes his team mates better.

My personal story relating to this is from two years ago, as I was just getting started with my speaking career. Argenis was in Denver on a consulting engagement and we were going to dinner. The conversation meandered, but at one point I mused about possibly taking the Microsoft certifications. Argenis simply looked at me and said “Dude, just take them. You can pass them.” As with most mentors, he didn’t just nudge or suggest, he outright shoved me. A year later, I had all five certs to get the MCSE Data Platform. I could tell you at least 10 other stories like this where Argenis motivated someone (not just myself).

The Election

This upcoming election, as I said, will be tough. While I spoke specifically about Ryan and Argenis, Tim and Jen have both demonstrated their commitment to PASS. Hopefully I have been able to fill in some gaps about why the new candidates should also receive your consideration.

What’s most important is that you vote. Last year I blogged about some issues I had with the direction PASS is going. This is your chance to influence that direction. The candidates have the opportunity to mold and shape PASS over the next several years, so these elections have tremendous impact. Over the next few weeks I’m sure you’ll get a chance to learn more about all four candidates, more than I have shared here. Take that opportunity. If you’re on Twitter, you can interact with them there and learn about the candidates yourself. And vote.