Art of the DBA Rotating Header Image

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

#TSQL2sDay: #Powershell and Extended Events

I’ll be the first to tell you I’m not a fan of extended events. The why is a whole other blog post unto itself, but since T-SQL Tuesday is all about learning, I figured I’d take a stab at things. Furthermore, since I like to talk about Powershell, it seems like a good opportunity to explore a little about how we can manage extended events through that language.

The SQLPS Provider

The simplest entry point into interfacing with SQL Server is the SQLPS provider. Yes, I know there’s a lot of gripes about it (and that’s yet ANOTHER blog post I’m working on). However, it’s still a good starting point. To load up the provider is easy:

Import-Module SQLPS

Providers in Powershell are nice because we get to interact with different parts of the stack as if they were file systems. When you load up the provider, you’ll get a SQLSERVER drive that you can do a directory lookup on.

XE_PosH_1

XE_PosH_2Notice how we have an XEvent folder? This is how we can start exploring our extended events. The format for the lookups is the standard format.  If we drill down, we start to see lookups that match what we see in Management Studio. Looking at SQLSERVER:\XEvent\PICARD\Default\sessions will give us a quick listing of all the sessions on that instance and if they’re running or not.

XE_PosH_3

Thinking Objectively

As we further explore this, we need to remember that everything in Powershell is an object. This specifically means that with the SQLPS provider, everything is an SMO object. By leveraging the trusted Get-Member cmdlet, we can quickly find out that we are dealing with Microsoft.SqlServer.Management.XEvent objects and the Session class. So what can we do with this?

First off, I found a bit of a gotcha when trying to assign these sessions to objects in Powershell. Normally, I’d just use Get-ChildItem on the name to populate the variable, but when I tried to do that I ended up with a string object. Not very useful. In order to get the proper assignment, I had to work around it a bit and ended up with this:

$xe = dir | Where-Object {$_.name -eq 'system_health'}
$xe |  gm

Why this is different from the usual ‘dir system_health’ is unknown, but is the result of how the SQL Server team implemented the provider (yes, another post for another time). However, now we have an object and can start leveraging some of the properties.  Here’s a couple examples of some of what you can look at:

XE_PosH_4The calls I used for the above are:

$xe.Targets
$xe.Stop()
$xe.IsRunning
$xe.Start()
$xe.IsRunning
$xe.ScriptCreate().GetScript()

You’ll notice at least one funky bit of syntax here around scripting. While most SMO objects give you a .Script() method, these objects require you to first call a .ScriptCreate() (or alter or drop) method, which returns an ISfcScript script object. You then have to call that object’s .GetScript() method to actually generate your script. It’s a little bit of a long way around the horn, but you get there.

Practical Application

What can we do with this? I’ll be the first to admit that some of this is clunky, but much of that is probably my general unfamiliarity with this aspect of the provider and extended events in general. However, I can definitely start to see some specific use cases, such as:

  • Backing up existing custom sessions across my environment by scripting them out with a scheduled job.
  • Checking that all system_health sessions on my SQL Servers are running.
  • Executing a mass change to my extended events sessions using the methods.

To effectively leverage Powershell with extended events, we need to play to the language’s strengths. The focus here is on multi-server execution and scripting for consistency. This is also just a start. As you (and I) continue to explore this facet of the SQL Server provider, many other use cases and situations may show up.

Thanks out to Jes Borland(@grrl_geek) for hosting this month’s T-SQL Tuesday. Remember to watch the #tsql2sday tag for other great blog posts.

The Cult of Automation

Last week, while perusing through the usual interesting links from Twitter, I came across an article from Kendra Little (@Kendra_Little) titled “Not Everything Should be Automated”. It’s brief post where Kendra talks about why she has stopped automating some of her tasks. The title is attention grabbing (well, it grabbed MY attention), but there’s a core debate hidden in here that I want to take sides on.

If You Liked It, You Should Have Wrote a Script For Itbrodoyouevenscript

I often hear “you can’t automate everything”. As a card carrying member of the Cult of Automation (caps mine), I usually respond with “why not?” Many of my daily tasks can and should be automated. When doing these tasks manually, I am exposing the work to risk from skipping something, flubbing a syntax, or some other human error. The cost of these errors is compounded by the fact that it often takes a person longer to execute a process manually then if it were automated. This all adds up and can heavily impact your the value you bring to your employer.

This is, after all, why GUIs were created. By providing that interface, we aid users with a method to execute tasks that can have mistake protection built in as well as providing a speedy way to complete the work. This is the conversation I end up having with folks who want to use the SQL Server Management Studio GUI to create databases, that they can do it faster than having to type out that tedious CREATE DATABASE statement. The benefits are less errors and faster time to completion.

Automating a process isn’t really that different, because you’re wrapping up an action in a script so it executes the same way every time. The goals are the same: reduce errors and increase speed. It’s just now, instead of providing a GUI for interaction, we’re scripting out all the actions and getting the heck out of the way. The result is repeatable, consistent action.

Why wouldn’t you automate then? To me, it seems a no brainer to script something out if you do it more than once. When you do that, you reduce your errors while increasing the speed of execution. This is the message of my Cult and one I would hope more people subscribe to.

How do you get to Senior DBA? Practice, Practice, Practice

Let’s go back to Kendra’s post, though. Her message is that she forces herself to type out commands instead of scripting or snippeting them because she wants to make sure she knows and understands those commands. It’s a matter of mastering the syntax. If you automate a task or put it behind some sort of interface, you can quickly lose touch on how to write it. You might forget the options or the exact syntax, which means your skills can get rusty.

As a musician, I know and understand the value of practice. When preparing for a concert, I often rehearse a section of music dozens of times in order to commit it to muscle memory. This means when it’s time to perform, my brain doesn’t get crossed up trying to remember what the notes were and I can focus on making music. Code practice is no different, because when it gets to crunch time, you shouldn’t be flailing with how to do a point in time restore because you can’t remember how a command is written.

This all sounds fine in theory, but let’s talk use case. Restore testing is a perfect example, because it’s something that will commonly be automated. I’ve written my own scripts to aid this and will often set up a regular job to select a database, restore it to a target server, run a DBCC check, and catalog a report of the process for later review. To manually test all my databases is way too long and if I were doing that, I couldn’t work on any of the cool projects. This makes automation a perfect and necessary solution.

However, I still do regular manual point in time restores. This is because I need the practice. I won’t do all my databases and I won’t do it every day (usually once a month), but I still do it. It keeps my skills fresh. It applies for other tasks as well. I have many automated processes, but I will occasionally bypass the automation so I can get my reps.

This takes us back to the GUI discussion. Many of the tasks in SQL Server we could do graphically through SSMS, but more experienced DBAs eschew that to typing the script. We’ve all used the learning tool of scripting out an SSMS action. To script is to understand, at the core, what is happening in SQL Server and gives us better knowledge of how to manage it. I remind many DBAs I’ve talked to that pretty much everything in SQL Server is a T-SQL command of some sort, so it behooves you to understand the relationship between the action and the syntax.

The Bone of Contention

Now we get to my issue with Kendra’s post. I don’t disagree with any of her statements, but I disagree with the tone of the post as set by the title. We’re not arguing against automation here, but instead championing practice. You still should try and automate as many tasks as possible. Just don’t lose sight of the skills you need to build that automation. It’s very difficult to write a script if you don’t know and understand the actions you need to script. Worse yet, if someone hands you a script and you run it without understanding how it works, you could be doing lasting damage to your environment.

Practice makes perfect, and perfect makes automation. You should build and practice your skills, understanding what makes the platform tick. Once you do that, you can script it. Once you script it, you can start practicing your next automation trick. Just don’t lose sight of the skills that got you this far.

Your #Powershell Profile

Keeping with the theme of my last blog post, let’s talk a little more about Powershell basics. More specifically, let’s talk about the basics of reusing your Powershell scripts. Most DBAs have their library of scripts that they take with them from job to job, helping them be more effective at their job. Whether it’s SQL, Powershell, or some other language, experienced data professionals always make sure to retain their work for when they’ll need it again.

Enter the profile. This is actually a concept that’s been around for a long, long while in the *nix world. The concept is simple: There is a script that runs every time you start up a session in your shell, configuring your environment. Many times in *nix, this will configure system variables like PATH or HOME, but you can also use it to load custom functions and aliases within your shell.

The Microsoft team borrowed this concept when constructing the Powershell language. We have access to a profile (well, 4 of them) that help us configure and customize our own environments. Similar *nix, it’s a .ps1 script that runs every time you open a new session. This allows you a lot of flexibility for reuse of your code.

Getting Started

As linked above, there are 4 different types of profiles you can use. We won’t focus on these different profiles, but instead stay with the basic profile: the one that applies to the current user and the current host (in these examples, the ISE). To view your profile, open it up in your text editor of choice by referencing the $profile system variable:

ise $profile

If you don’t currently have a profile, you’ll probably get an error about the file not existing. To fix this, create the file:

New-Item -ItemType File -Path $profile -Force

Vioala! You now have a completely empty profile, which is what you will see when you try and open it again.

So Now What?

I can hear the internal dialog now:

“Woo hoo! A profile!”

“Ummm…yeah. That’s great. But what do we do with it?”

“…….”

“That’s what I was thinking, too.

I don’t want to leave you hanging, so let’s start making use of it. The first order of business with the profile is to use it for customizing your environment. Since I am a SQL DBA, I like  to load up the SQLPS module and the SMO objects. This means any time I’m in Powershell, I’ve got the SQL Server cmdlets and objects ready to go.

Next up, I have a function I wrote a while back for reporting on the available disk space on a server. It’s very handy and I use it almost every day. The code is not original, but I wrapped it in a function to save myself a LOT of typing. Usually, you’d package such a function in a module.  I ended putting it in my profile because it was easy. I also add to an alias to save myself even more typing.

This means I end up with a profile that looks a lot like this:

#load SMO library
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

#Load SQLPS Module
Import-Module SQLPS -DisableNameChecking

#Set Aliases
New-Alias -Name gfs -Value Get-FreeSpace

function Get-FreeSpace{
<#
.SYNOPSIS
Uses WMI to get capacity and freespace for all disks/mounts on a host.

.DESCRIPTION
Uses WMI Win32_Volume to query logical disks and provide drive size and usage for all
logical disks and mountpoints.  If no parameter is given, localhost is used.  Otherwise
the host name should be passed.

Mike Fal (http://www.mikefal.net) 2012-10-10

.PARAMETER
host - Name of machine information is being queried from, defaults to localhost

.EXAMPLE
Get-FreeSpace "CCX-SQL-PRD-01"
#>

param([string] $hostname = ($env:COMPUTERNAME))
gwmi win32_volume -computername $hostname  | where {$_.drivetype -eq 3} | Sort-Object name `
| ft name,@{l="Size(GB)";e={($_.capacity/1gb).ToString("F2")}},@{l="Free Space(GB)";e={($_.freespace/1gb).ToString("F2")}},@{l="% Free";e={(($_.Freespace/$_.Capacity)*100).ToString("F2")}}

}

Now if I had just added this to my profile and wanted to load it into the current session, I’d just have to execute it, like so:

. $profile

This loads the profile, running it as if it were any other .ps1 script (because it is).

The Power of the Profile

Using this, I have a customized Powershell environment ready to go any time I start. It lets me use Powershell the way I want to, allowing me to extend my shell with my own custom code or pre-packaged modules that I need access to. I’ve talked several times before about building Powershell tools, but at some point we all need a toolbox. By leveraging the profile, you can start building your own custom Powershell toolbox that you can rely on.

Want to know more? You can learn about Powershell profiles right from the shell itself: Get-Help about_profiles. How cool is that?

I’ll Just Leave This Here…

Last year you might have heard about a dust up around PASS voter eligibility. I got pretty vocal about it, but in the end PASS did the right thing. Now PASS is trying to do the right thing again by getting ahead of the issue and notifying the community to check their eligibility.

Here’s the deal. You need to update your PASS profile and make sure some mandatory fields are filled in. You’ll see if you’re eligible if you look at your profile page on the sqlpass.org website. You have until June 1 to do this.

But why wait?  Do it now. It won’t take long and helps both you and PASS maintain the integrity of the voting process.

Read more about it on the PASS website.

Exploring #Powershell

Over the past months, I’ve devoted a lot of time to scripts and tips on using Powershell. While helpful, these articles assume a level of familiarity with Powershell that a lot of SQL Server administrators don’t have. I wanted to take some time to help out those who are looking for a place to start.

The great thing about Powershell is that the Microsoft team has put a lot of effort into building cmdlets that allow you to discover how to use the language. The term ‘cmdlets’ was invented by the Powershell team to help make the language distinct, but they’re essentially functions. Three cmdlets, in particular, will help teach you the language and discover how you can use it:

  • Get-Command
  • Get-Help
  • Get-Member

I like to refer to these cmdlets with the mildly cheesy name “The Holy Trinity of Powershell Self Discovery”.  Yes, that’s a mouthful. With such a clunky name, though, it is hard to forget about them. Because of the information they provide, not a day goes by where I use one of these cmdlets while working with Powershell.

Get-Command

The first of these three, Get-Command, is the cmdlet that lets you browse the cmdlet ‘directory’. While there are several arguments we can pass to this cmdlet, really all you should concern yourself with at the beginning is passing wildcard searches for cmdlets you might be looking for. If you keep in mind that there’s a defined list of Powershell verbs, Get-Command becomes a good way to find cmdlets.

For example, let’s say we wanted to find a cmdlets to add a Windows firewall rule. The best way to start is to simple filter all the cmdlets containing the word “firewall”:

Get-Command *firewall*

Quickly, you’ll get a list of 30 functions for manipulating different parts of the firewall. A lot easier to read that the full list of 1000+ Powershell cmdlets. But we want to filter our list down even more, so let’s improve our filter. Since we know we want a new rule, we can improve our wildcard search:

Get-Command New*firewall*rule

Our result is a single cmdlet, New-NetFirewallRule. Remember that anytime we create something in Powershell, the standard syntax will be “New-” and the noun. We add this into our wildcard search, plus the word “rule” to further filter down. Combine this all into how we use Get-Command, and we have a powerful way to search the language.

Get-Help

Once you find your cmdlet, though, how do you learn how to use it? Fortunately for us, the Powershell team took a page from the *nix world. For anyone who as worked in Unix or Linux, you’ve probably made heavy use of the ‘man’ pages. This is a built in help system to help describe shell commands. Powershell has its own system and you access it using Get-Help (conveniently aliased in Powershell as ‘man’).

Using our previous example, let’s learn about the New-NetFirewallRule cmdlet. The syntax is easy enough:

Get-Help New-NetFirewallRule

Running this will output text to the host window that describes the cmdlet. By default, we’ll see a synopsis, a description, and an outline of the parameters the cmdlet accepts. Extremely useful, but this is just scratching the surface. Get-Help will also accept additional arguments that can give you even more detail or functionality  if you want it:

  • -Examples – Examples of how the cmdlet gets used
  • -Detailed – Even more information on how the cmdlet functions
  • -Full – The entire help file for the cmdlet in question
  • -ShowWindow – Output the full help file to a separate windows
  • -Online – Open up the TechNet page for the cmdlet

But wait, there’s more! Two more awesome things about the Powershell help system are that you can find help on more than just cmdlets and it is constantly updated. Powershell contains a list of topics that you can read on to see how to use the language. These are all found with the text “about_” and you can get a listing of these using:

Get-Help about*

If you want to learn more about how to use variables, for example, you can look at the about_Variables topic and read the specifics about using Powershell variables. There are over 100 different topics in the help system for Powershell 4.0, baking your learning right into the language.

Of course, this information can change and update frequently. In order to keep pace with this rate of change, the Powershell team separated the help system from your standard Windows updates and made manageable online. This means if you ever think your help system is out of date, you only have to run the Update-Help cmdlet when connected to the internet and your local help system will get all the current information.

Get-Member

One of the strongest advantages of Powershell is that it is built on .Net. This means that everything in Powershell is a .Net object. To explore these objects and what they have to offer, you can use the Get-Member cmdlet to provide information about a cmdlet.

The use is pretty simple. Take your variable and pass it via the pipeline to Get-Member:

[string]$AString = ‘Tea. Earl Grey. Hot.’
$AString | Get-Member

What will follow is a list of the object type, methods, and properties you can leverage with that object. You get a comprehensive view of all the capabilities of the object.

If we go back a few posts, you can read how I compared sqlcmd with Invoke-SqlCmd. Get-Member was key to this comparison, because it allowed me to explore the outputs of both:

$query = ‘SELECT type,count(1) FROM sys.objects GROUP BY type;’
$cmdout = sqlcmd -Q $query
$invout = Invoke-Sqlcmd -Query $query

$cmdout | Get-Member
$invout | Get-Member

Comparing these two outputs shows us a lot about the difference between the two commands. We can see that the sqlcmd output is an array of System.Strings and Invoke-SqlCmd is a collection of System.Data.Datarow. For more on the importance of this difference, read my other blog post.  The key is to understand how you can explore and discover this difference using Get-Member.

A Compass and a Map

Understanding Powershell is a long road. There’s a lot of depth to it, providing a great deal of functionality. It can be easy to get lost in the details. While we live in the day and age of LMGTFY, it still helps to have some basic tools at our disposal to find our way through something new. Get-Command, Get-Help, and Get-Member are three cmdlets you will always have access to, helping you find your way to solving problems in Powershell.

#Powershell in Atlanta

SQL Saturday Atlanta is coming up in two weeks and I’ll be giving my Introduction to Powershell for SQL Server DBAs precon the day before the event. I’m extremely excited to offer this precon and get more SQL Server professionals exposed to Powershell. Think you might be interested? Here’s the synopsis:

Maybe you’ve only heard of Powershell. Maybe you’ve seen a little bit of Powershell code, but still don’t understand how it works. You’ve been telling yourself for months now that you need to learn it. Why wait? The time to get started with Powershell is now.

This full day session will help build your foundation for learning and using Powershell. While we will be focusing on using Powershell as a SQL Server Database Administrator (or Developer), much of the material will also review general use for system administrators. By attending this training, you will gain an understanding of what Powershell is, how you can use it in your day to day management of your environments, and what specific things can be done using Powershell in a SQL Server environment.

You can sign up on Eventbrite and use the discount code ‘Mike_Fal’ to save $15 off the registration fee.  The session will be held at the Georgia State University Alpharetta Center on May 15 from 9am to 5pm.

I hope to see you there!

 

Availability Groups, Agent Jobs, and #Powershell

My current gig has me working a lot with Availability Groups. I love the options they give me with their ability to failover fast and replicate my data to other servers. It is a lot of peace of mind for high availability and data protection. The price of all this comes from a much more complex system to manage and the extra considerations needed to have a properly deployed Availability Group.

One such consideration is your SQL Agent Jobs. It is pretty well documented that your server level objects must be replicated or created outside of the actual Availability Group process. It’s not difficult, but must be thought about and addressed when setting up a server. If you source control your management jobs, then it should be a simple matter of executing a script to create these jobs on a new server. There are cases, though, where either jobs get out of sync or are created outside of the source control process. When this happens, administrators need to copy these jobs to the other servers.

The common way to accomplish this is to simply script out the job in Managment Studio, then run the script on the other nodes. While effective, this is also manual and prone to error, such as forgetting a server.

By automating this process, we can ensure consistency.  You can use Powershell to leverage the SMO and the .Script() method to basically duplicate your Management Studio action.  However, by using this and incorporating it into a script, we can ensure our consistency.  The following function is a pretty basic use of this that allows you to copy a single job from a source server to any number of targets:

function Copy-SQLAgentJob{
  param([string]$Source
  ,[string[]]$Targets
  ,[string]$JobName)

#Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$smosource = New-Object Microsoft.SqlServer.Management.Smo.Server $Source
$JobScript = $smosource.JobServer.Jobs[$JobName].Script()

foreach($Target in $Targets){
 $smotarget = New-Object Microsoft.SqlServer.Management.Smo.Server $Target
 if($smotarget.JobServer.Jobs.Name -notcontains $JobName){
   $smotarget.Databases['msdb'].ExecuteNonQuery($JobScript)
    }
  }
}

All this function does is find the source job, script it out, and then run that job creation script against all the other targets. Simple enough. One thing to call out is I’ve specifically written this function to stay completely within the SMO. Normally, I would use Invoke-SqlCmd to execute the SQL script, but I thought it simpler to use the SMO .ExecuteNonQuery() method (which runs a SQL batch and does not expect a return).

Now let’s implement this for a hypothetical Availability Group to copy from one node to all the others:

$PrimaryNode = 'PICARD'
$nodes = (Get-ClusterNode -Cluster (Get-Cluster $PrimaryNode).Name | Where-Object {$_.Name -ne $PrimaryNode}).name
Copy-SQLAgentJob -Source $PrimaryNode -Targets $nodes -JobName 'Backup Databases - Powershell'

Of course, we could also use this in a much simpler fashion, such as deploying a new job across all of our servers. Remember the Central Management Server trick I’ve implemented in the past? We can fold that in here for an easy deployment once we’ve created the job on a single server in our environment.

$CMS=’PICARD’
$servers=@((dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$CMS").Name)
$servers+=$cms

Copy-SQLAgentJob -Source $CMS -Targets $Servers -JobName 'Backup Databases - Powershell'

As always, the lesson learned here is to leverage our tools to make more our work more repeatable and consistent. While everything we have done here can be done in other methods, I prefer this because it minimizes the interaction needed to accomplish the task. The more any of us have to touch a process to get work done, the greater our risk of error. You’ve heard it before: consistency through automation, this time for SQL Agent jobs.