SQL University – Powershell Redux

[Note: I took my original SQLU blog post for Powershell and updated it for use this week. So, if you’ve heard me say all these things before, now you know why.]

Welcome back to SQL University, the only university conducted entirely through blog posts, has no exams, and is organized by a chicken. This week we are going to talk about Powershell and how you can win the lottery. Wait a minute…apparently Powershell and Powerball are two different things altogether. One of them requires you to spend many hours learning how to work a system that can ultimately make your life richer and easier and the other is a lottery.

Since this is not a gambling column (although you do take a chance every time you come here) I will focus on just Powershell this week and leave the gambling to the folks at the Australian lottery that keep telling me how much I have won which is really funny because I thought you had to “be in it to win it” and I don’t recall purchasing a ticket to a foreign lottery but apparently I am worth millions all over the globe.

Anyway, let’s get started with the lesson this week.

tempdb

The very first thing you need to do here is to seek the answer to this question: What is Powershell?

Great question. Any other questions?

Go here and you’ll get yourself a hard-nosed definition that includes the words “scripting language”. Now, quick survey: Who here has time to learn yet another scripting language? If you raised your hand then I need you to do the following: move your arm until your hand is in front of your face, still a full arm’s length away. Make sure the you are looking right at your palm. Now, take your hand and slap yourself in the face repeatedly until you understand that you don’t have time to learn anything new. Not unless you sacrifice something else, or you have the ability to inject additional minutes into your day, will you really have enough time to learn something new.

Rather than spoon feed you definitions of what Powershell is according to some dubious Wikipedia entry, I am going to tell you how you should think of Powershell instead. Don’t think “command-line” and “scripting”. Instead, think this:

“It is a text-based automation solution.”

Oh, sure, POSH does indeed have a language, and yeah, you will have a learning curve. But rather than sit around and think to yourself “why would I want to spend the time migrating my stuff to use Powershell?” I would rather have you sitting around think to yourself “how can I take my existing scripts and make them easier to use and understand?” And if you start thinking of POSH as a text-based automation solution I think you will start to find more and more ways to make it useful.

msdb

After you develop an understanding of what Powershell is (or, what it can be) you can move to the next question you need answered: How would I use Powershell?

Another great question. And one that has a different answer for everyone.

How often have you come across something new and been tempted to rewrite existing code because you saw something shiny? I think we have all been tempted to do that at one time or another. But is that what you should really be doing at this point? That would be like marrying a girl you just met when a computer error assigns both of you to the same room. So, let’s take a step backward and figure out a little bit more about how we can use Powershell before we think about how to apply it in our shop.

And how do we do that? Simple: we use SQL Server Management Studio 2008. Just right-click on a folder inside of SSMS and look for the ‘Start Powershell’ option as I have done on the ‘Databases’ folder here:

Once you choose this option you will open a command prompt. This command prompt will connect to the instance and be located in the exact directory that you selected in SSMS. In this case, you will be in the database directory.

Ready for the fun part? In that command window go ahead and type in “dir” and examine the results. I assumed that the “dir” command was familiar to you in some way. If so, that’s great. If not, that’s not a problem at all, just consider yourself a blank slate and now type in “Get-ChildItem” and examine the results again. Look familiar? Are you starting to get an idea about how Powershell is structured? Are you thinking that it is simply a directory structure? That’s great, because in a way that is exactly what it is for you.

Since a directory is essentially just an object you should start thinking about everything in Powershell being a collection of objects that have properties. For example, if you wanted to you could drill down into each index on each table inside of each database on every instance you manage.

At this point you should stop and think about the question at the start of this section: How would I use Powershell? If you are starting to get a feel for how Powershell is structured then you might start to get a sense for some of the tasks that you could accomplish, or that you have accomplished in the past using other tools and scripts.

model

By now you should have an idea of what Powershell is and also have a feel for how to navigate your way around. Once you really start to get comfortable you are going to want to start building your own scripts. Let me save you some time now and point you to a tool that I think is going to be very useful for you: PowerGUI.

PowerGUI is a way for you to easily work with Powershell scripts. It also allows for the inclusion of PowerPacks, which are ways for you to extend the capabilities of PowerGUI. If you are looking to get started working with Powershell then I would recommend you become friendly with PowerGUI right from the start.

At this point you should also have an awareness in the difference between Powershell and SQL Powershell (which is commonly called a mini-shell). So if you come across scripts that are built to help you manage or administer SQL instances you need to be aware that some scripts can be launched from Powershell and other scripts assume that you are running SQL Powershell. Want to see more? Open a command prompt and try running both “powershell.exe” as well as “sqlps.exe”.

If you are going to be at this level then you are going to be writing your own scripts, so you need to be mindful of these subtle differences. What’s more is that you want to be efficient, and PowerGUI helps you to be efficient with your time.

And If you have gotten this far then you are ready to make the leap to the next level…

master

Have you ever heard someone say “no need to reinvent the wheel”? Those are the same people that would have stood in the way of the telephone, overnight mail delivery, or bacon stuffed olives.

If you want to master Powershell, then you are going to need to reinvent the wheel. Several wheels, probably. You probably already have a bunch of scripts in your shop right now, helping you to manage and administer your instances. Some of them may be hundreds of lines of code. You are going to take those scripts and rewrite them in Powershell. Not because you have to, but because you will want to.

I am always amazed at when I find some old script of mine that can be reduced to only a handful of lines of Powershell. And when that happens it becomes addictive; you start looking for more and more ways to streamline things. Backup scripts, restore scripts, defrag scripts, space used scripts, the list is endless.

resourcedb

Looking for a good Powershell resource? Start with Chadwick Miller’s Powershell category over at SQL Server Central. Then head over to see Aaron Nelson has to say and follow that with a trip over to see what Buck Woody has written about Powershell at his blog. Looking for some books? Go out and get your hands on a copy of Microsoft SQL Server 2008 Administration with Windows PowerShell. Looking for videos? Check out this collection over at Channel 9. There is a lot of information available on Powershell. Do not go diving into all of the Powershell information without a plan of action. If you want to dive in to surf around, that’s fine, but don’t go too deep that you lose too much oxygen. Have an idea about a specific task that you are trying to solve before you go to deep, it will make things easier for you in the long run.

• Aaron Nelson (blog | @SQLVariant)
• Chad Miller (blog | @cmille19)
• Sean McCown (blog | @midnightdba)
• Buck Woody (blog | @buckwoody)
• Dan Jones (blog | @msdtjones)
• Allen White (blog | @SQLRunr)

And there you go, that should be enough to help get you started. One thing to keep in mind is that working with Powershell is a lot like dating. You get excited about the idea of the date and you think of it all day long. You have a nice dinner, a few drinks, and right when you think everything is going to go your way, nothing happens.

And you find yourself right back at it again the very next day.