PowerShell, isn’t that a Windows Administration tool? This is certainly how it is positioned by Microsoft, but why should the DBA community give it more than a passing glance? My guest is Mike Fal and we chat about ways data pros can use PowerShell.
Mike says there are two reasons why PowerShell is valuable for SQL Server folks. I hope you will join us for the conversation. We promise not to bore you with syntax.
Are you using PowerShell? I would love to hear your stories in the comments at the bottom of the page.
Transcription: Level Up Your Game With PowerShell
Carlos L. Chacon: This is the “SQL Data Partners Podcast,” Episode 32. I am Carlos L Chacon, your host. Welcome, compañeros, to the show. Thanks for joining us on the SQL trail.
This podcast is dedicated to SQL server-related topics, designed to help you become more familiar with what’s out there and how you might use certain features or ideas and apply them in your environment. The SQL community is a great big family. We like to connect with others, as well.
Today, our topic is PowerShell. I have to admit, I’m a little nervous about this subject. I was in Seattle at the Brent Ozar FreeCon last November, November of 2015.
There were about 30 of us at this event. I was trying to reach out to some folks about podcasting topics and wanted to see if they’d come onto the show with me.
In that invitation, I said, “Some topics may not work well in a podcast. PowerShell is one of them.” Sorry, Mike.
Of course, Mike Fal who’s our guest today. He’s a big PowerShell guy, and he thought that was full of baloney. He said, “No, this is a great topic. You need to talk about it. You need to have me on your show.” I thought, “OK.”
He’s here to prove us wrong. Mike is a data base consultant with UpSearch. We’re not going to be talking about syntax or anything like that. Although, we might hit one or two pieces that might come up.
However, we are going to discuss why you my Compañeros need to think about PowerShell. How it can make your lives a little bit easier. Maybe how you would go about getting started, or increasing your knowledge in PowerShell.
Today’s episode is brought to you by SQLCRUISE — Learn. Network. Relax. Grow. Just add water. Check out sqlcruise.com for more information. Let’s get to it. Welcome to the program. Mike, thanks for coming on the “SQL Data Partners Podcast.” It’s good to have you.
Mike Fal: It’s good to be here, Carlos. Thanks for letting me tag along here.
Carlos: When I think about community members, you are one of the folks that has a good niche or a good brand in the PowerShell space. You talk about it. You blog about it. You give lots of presentations about it.I know I wanted to have you on, and talk a little bit about PowerShell. Ultimately, this is a SQL Server target audience, so why as SQL Server folks do we need to be concerned about PowerShell?
Mike: I know that it’s an interesting question and one that I often think about. A little bit of additional background with myself is this. I am a SQL Server DBA. I’ve been one for 15 years. I get it where people come from the SQL Server background and say, “What’s this new PowerShell thing? Why should I care about it?Why does it matter to me? It’s funny too, because PowerShell isn’t that new. It’s been around actually for almost 10 years now.
Carlos: I know. Isn’t that crazy?
Mike: Yeah. But it’s been seen as a system administrator tool, and I get it. That’s partly on how Microsoft pushes it. That’s partly on how the SQL Server team supports it. It’s just in general how it gets exposed.To answer your question why, it’s interesting…I have a slide that I put into my presentations to answer this exact question, because it’ll always come up. There’s two reasons primarily in my mind, why PowerShell is valuable for SQL Server folks.
The first is that it works outside of the stack. What I mean by that is, your SQL Server is very good at managing data. But it’s not so good at other Windows tasks, like if you need to get some information from the Active Directory. You can do it. It works, but it’s a pain in the butt.
Carlos: Yeah. It is a pain.
Mike: Yeah. Linked servers and ADSI objects, it’s a nightmare. It’s the same with the file system. If we go to make a backup in the file path, to where we’re backing up doesn’t exist, SQL Server throws out a big, fat error, and says, what am I supposed to do?PowerShell is baked into the Windows operating system, and in my mind lives outside of the stack, or alongside with it. It can interact with the SQL Server. It can interact with the file system. It can interact with Active Directory. Exchange any number of things, and in a better manner.
There’s always the conversation, for example, the SQL Server, we can do a lot of stuff through SQL Command Shell. But there’s security concerns around that, and how that interacts. I know there’s things you can do around that, and there’s that ongoing debate.
But PowerShell gives you a more robust, a more flexible way to work with the file system and the operating system. Then, alongside it, interact with SQL Server. That’s the primary, the number one reason, why I would say to SQL Server folks why you would want to consider PowerShell for your tool belt.
Then the second is, when Jeffrey Snover, who is a Technical Fellow at Microsoft, and the guy who basically came up with PowerShell, when he came up with it, and his team put it together, they realized that, when they were building this sort of tool, administrators aren’t dealing with one server, or two servers, or three servers.
They’re dealing with 20 servers, 50 servers, a 100 servers. So PowerShell has multi-server execution kind of baked right into it. From the ground up it’s designed to say, I’m not going to just run a task in one place. I’m going to run it in a number of places.” We run into this in SQL Server all the time, like, “OK, I’m going to go and execute a script on a SQL Server.
Then I’m going to run that same script on another server, and then another server.” We have other tools available to use to help with that. I know Red Gate has their Multi Script tool. You can within central management server do multi-server execution. They’re a little clunky. I don’t want to say Red Gate’s tool is clunky, but it’s also not supported [inaudible 6:33] as I understand it.
We have this situation where we need to do the same thing in multiple places. PowerShell is built to do that from the ground up. There’s the third reason I also throw out. It’s kind of a “duh” reason. I think it makes a lot of sense to everybody. It actually applies to any script. It’s not just PowerShell, it’s T-SQL, it’s VBScript, it’s what-have-you.
If I write a script…if I put a task into a script and automate it, I can hand that script to somebody else, and they don’t even necessarily have to have knowledge of what’s in the script. They can just run it, right?
Mike: The nice thing about PowerShell is most system administrators are familiar with PowerShell, so let’s say for example I have a script — and I actually have several demos on this — of using PowerShell to automate my SQL install from start to finish. Not just to install SQL Server, but to configure it, get all the file paths set up, set service counts, what-have-you.If I wrap that all up in a script and I hand it to a system administrator who may not be familiar with SQL, but he’s like, “I know PowerShell. I can run a script, and I have some general understanding of that,” he can take that and do that task for me. Because the other thing is, I’m a lazy DBA. I don’t want to be clicking buttons and running SQL installs.
I want to be designing architectures and building high level concept stuff, so I’ll hand the menial tasks off to somebody else, particularly if I can script it, or, honestly, if I can wrap it up in a PowerShell script and automate it completely so it’s push-button. That’s the long and sordid story of why I would say SQL Server should consider PowerShell for their toolbelt.
Carlos: Yeah, there you go. I think with that script, as you mentioned, as you hand that off to the admin, it’s so something they feel comfortable with, but it’s also something that they wouldn’t necessarily feel uncomfortable with. You hand then a T-SQL script, now they have to get into Management Studio, or something else.Now they feel like they’re touching the database. They may like, “Well, hey, that’s not me. That should be you.” You’re like, “Hey, here’s a script.” I think that could make that transition a little bit easier.
Mike: That’s absolutely right. I think that’s also one of the things that saves you some time with using PowerShell is if you’ve scripted everything out, you don’t have to add the additional layer of, “Well, I have to log into Management Studio and run this.” Because there are utilities baked into PowerShell to execute directly against SQL Server. So it can save you a step or two.
Carlos: Now Ron Deskins, who is part of the Richmond User Group community, he’s one of the leaders. He works for Markel, a large organization. His opinion — now he said this a year ago — but he said that if you were an administrator now, and he’s including database administrators in that, and you didn’t know PowerShell, that in five years, you wouldn’t be able to find a job.
Mike: That’s a fairly extreme statement.Obviously people like to make those extreme statements because it starts a buzz. But I will say that PowerShell is becoming more and more important in the Microsoft stack. Certainly, it’s getting pushed as a necessary tool for system administrators. Aspects like Exchange and Active Directory are, at this point, starting to be administered almost completely through PowerShell.
In fact, you have GUIs, Graphic User Interfaces — to manage things like Active Directory and IIS and Exchange. They’re all executing PowerShell commands underneath. So it really is, for system administrators, yes, I would say that’s definitely the case. SQL Server administrators, it’s a little fuzzier, simply because one, yes, most of what we do is T-SQL.
As long as you have a solid understanding of T-SQL, you’re going to be employable, you’re going to be fine. However, you’re going to be at a disadvantage to people who know PowerShell, to people who can interact with that language to manage tasks in larger scales and in more efficient manners.
I get where the sentiment is coming from. I sort of agree with that sentiment. At the same time, people should not be panicking and going, “Oh my God, what am I going to do?”
Carlos: [laughs] Let’s get into a little bit of that, some of the differences between T-SQL and PowerShell, or that balance.
Mike: In one aspect, they’re not all that different, because for the most part, PowerShell and T-SQL are top-down, interpreted at runtime script languages. You write a T-SQL script, and it goes from the top to the bottom and executes stuff. PowerShell is a very similar way. In PowerShell, you can create functions that are certain programming constructs that you can apply within PowerShell.I like to think of PowerShell more as an object-aware language, and not like a C#, which is object-oriented and you create classes and you instantiate objects in all of this. PowerShell, because it’s a top-down scripting language, it still has that particular feel to it, but it’s built on .Net. That was another conscious decision with the PowerShell team when they built it. They built it on top of the .Net framework.
Everything within PowerShell is an object, and you get this robustness of manipulation and data access. Strings are not just strings. They’re string objects. Integers are integer objects. You get date time objects. I think that’s actually a good parallel between SQL Server and the PowerShell language is, PowerShell supports this idea of strong data types. Databases are built on data types, right?
Mike: They’re very important to us. Selecting the right data type is a key component to database design. Just as well with PowerShell. To extend it out a little more of this comparison, PowerShell also has a lot in common with the Unix environment in Korn shell Bash shell. For people out there who are familiar or have done some time in Linux, they’re probably, “Oh, OK. I’ve done some of that scripting.”Some of the syntax and the format of PowerShell has a lot in common with Korn shell and Bash shell. At the same time, it takes it a step further by adding the strong data types. There are some similar concepts. At the same time, PowerShell is different in the way that you…it’s more like a C# in the way you writes PowerShell.
You’re going to have curly braces, and you’re going to have loops, and you’re going to be creating variables. Certainly, variable declaration is completely different in PowerShell than it would be in T-SQL.
Carlos: It’s interesting that you’ve just mentioned a couple of things, like all of a sudden you’re talking about PowerShell, and you’re in Korn shell, and all these things, and I’m like, “Oh, my gosh.” Somebody, for example, like myself, who was never a programmer. I took a class in college, but that was not my thing. I come from the networking side.I think, “Oh, man. T-SQL was, in a sense, one hurdle,” and now you’re saying, “OK, gosh, now I got to go learn all the shell and everything here.” [laughs]
Mike: No, not at all. Again, to speak to my background a little bit, I came up from system administration myself. I don’t really have a programming background. My “Secret shame” that I always like to joke to people about is I actually have a music performance degree out of college.I went to CU Boulder and studied to be a musician. Because that didn’t pay so well, I found myself going into IT. I’ve never really done any sort of programming outside of the scripting stuff. I did dabble a little in Java and C# at one point, just to see what it was like and if it would interest me. You don’t want to see my C# code, man. You do not want to see it.
I get it, where it feels a little overwhelming. That’s the thing is it’s, anytime people look at PowerShell, they’re like, “Oh, my God. It’s this whole new thing that I have to learn.” I would say it’s not as new or there’s not as much to take on as people might think. First off, yes, PowerShell is very deep. Then again, so is SQL Server.
If you really want to be a true expert at PowerShell, you’re going to spend a lot of time with it. [laughs] But to get started, most of us have used CMD.exe. We’ve gone in, we’ve run directory workups. We’ve done IPConfig, ping, all of that stuff. You can do this all in PowerShell. Then you start to take little steps beyond that.
You say, “OK, now I’m going to do a directory lookup, but now I’m going to stuff that into a variable and maybe process through those files. Maybe I’m going to add some code that will check and see if — we go back to the backup path example — check and see if the backup path exists before it tries to execute a SQL backup.” If it doesn’t, create it, then execute the SQL backup.
Those are some very simple patterns that people can ease into. That being said, yes, PowerShell is a deep language and takes a long time to…I’d say people can get their feet under them in probably about three months. Just messing around with it and playing with it. I’ve been working with it for two and a half years now. I’ve got a pretty good grasp, but there’s plenty of stuff that I’m still like, “Oh, that’s beyond me.”
Carlos: [laughs] Interesting. You mentioned the backup example. If we were to apply the same idea to T-SQL learning, a lot of us, there are a lot of scripts out there that we will take from the Internet, and we will try to apply it in our environments. Is that something we could start with PowerShell?
Mike: Yeah. Actually, it totally is. There’s plenty of examples, and folks, I’m not the only SQL person talking about PowerShell. I talk about it a lot. I did that because I didn’t feel like there were enough voices, but there are some pretty good voices out there. For example, Allen White works at SQL Century, been doing this stuff for a long while.He’s got tons of great blog posts up on sqlblog.com, of things that he’s done with PowerShell, little learning cases, whatnot. Ben Miller, aka @DBAduck — he’s out of Utah. Another really strong PowerShell guy — got a lot of good scripts out there. Laerte Junior — he’s a PowerShell MVP, but SQL Server DBA out of Brazil. He writes a lot of stuff up on Simple Talk. There’s resources out there.
Again, people, if they want to go into my blog at mikefal.net — Mike F-A-L — because sometimes people want to add the extra L. I don’t blame them. It happens all the time. I’ve got a bunch of little scripts and snippets and things that I’ve messed around with. There are resources out there.
Carlos: We will post these that you’ve mentioned. If we connect and you have a few others you want to toss my way, we will put these up on the show notes for today’s episode, so it’s sqldatapartners.com/podcast. You can go there, search for today’s episode on PowerShell, and find this information.
Mike: Yeah. I actually will provide free, because I have several slides that I put at the end of my presentations that I go through different resources. Books that people can use, blogs that are useful, online training. Just to mention it, Microsoft Virtual Academy has like hours of free PowerShell training. If people want to do webinars, go up to.Now, it’s just generic PowerShell training. A lot of those are targeted to people who are doing more system administration stuff, but once you get the language fundamentals under you, then it’s up to your imagination. It’s up to, “Hey, what kind of problem do I need to solve?”
Carlos: The sky is the limit.
Carlos: Now, so you recommend someone starting, and let’s say they’re either…you brought the example CMD. I can use CMD, kind of feel comfortable with that. I want to start using PowerShell. Should I take a couple of classes to Microsoft Virtual Academy to understand some syntax there before I jump into, let’s say, using a PowerShell script to back up my databases, or should we just jump right into one of your scripts that we could use to do that?
Mike: Everybody’s learning method’s a little different. Some people like the in person classes, some people, they want to read, and study everything, some people, they like, “I’m going to dive in, and I’m going to put my hands into it up to my elbows, and just get messy. Just…”
Carlos: Yeah, “Let’s do it.”
Mike: “…full throttle over myself, and see what comes out.” Personally, I’ll be honest. I’m the latter of like I just dive in, and I start trying to figure stuff out. A good starting point no matter what your learning path is, any of these paths that we’ve talked about, I always recommend the book “PowerShell in a Month of Lunches.” It’s a book by Don Jones and Jeff Hicks.
Carlos: I have the book.
Mike: A lot of people do, and what it does is it breaks up the PowerShell fundamentals into, I want to say, 26, 27 different lessons that are each about an hour long. The idea is, is on your lunch hour, you just sit down, you want to run through the exercise. It’s pretty low impact, but you reinforce these fundamentals over the course of the month.Lots of people use that, they love it. I’ve got nothing but good things about this book, so I always recommend to people that. Now, for me what was useful, once I had the language fundamentals, a couple of things that are good patterns for people to get into to learn PowerShell, one, stop using CMD.exe. Use PowerShell.exe.
The reason is, is because pretty much everything you can do in CMD.exe, you can do in PowerShell.exe. If you needed to run a ping, or you do an IP config, again any of these commands, you can do that through PowerShell.exe and then you’re in the language. You have the access to all the other PowerShell functionality.
It’s just that’s a good app to get into. Then I will typically recommend to people, once they’re in there, just walk through the file system. If you got to go to a directory, or you need to maybe delete some files. One of the big light bulb moments to me for PowerShell, and why it was such a useful tool is DBAs.
Most of us have experienced the scenario of that 3:00 AM phone call, where backup was failing because the drive filled up, because it’s just full of old backups. We’ve all been there. We go in and plug in the computer, we go into each of the directories and we delete what we needed to. What I found is — if I need to delete all my transactional backups older than three days, I wanted to do that, I can do that across an entire drive with one line of PowerShell.
That saves me 10s, 20s, 30s of minutes, if not an hour. That was to me the big light bulb moment of like, “Oh my God. This is amazing.” That’s what I recommend to people for learning. It’s to say, “Get into PowerShell, get on that command line, manipulate the file system, explore things that way.” It starts to reinforce the patterns of how the language works.
Carlos: That’s a good point. As administrators, we have this mantra of, “Do it once, and then script it so you don’t have to do it again,” but auto this task and you make a good example of… If you’re doing that more than once then you’re not doing yourself any favors. You get a ticket, maybe other people see it like, “Oh, I have to engage this person.” You find some value, but make that easier on yourself. Instead of having to take that time, script it out.
Mike: Exactly. It comes back to again…I’m a lazy DBA, so I try to automate absolutely as much as I possibly can. I’ve had those kind of conversations with people. A lot of people come to me and say, “We can’t automate everything.” And I’m going, “Well, I think you can and at least you should make the effort.” We can have the conversation.There’s certainly a lot of good philosophical points about should be automated and what shouldn’t be automated. To use an example, I can sit down and automate out backup or restore testing. I actually have scripts for that, where I automatically restore databases to another server, I do DBCC checks and I email if there’s any errors or any problems with that process. A lot of people are like, “Oh, that’s great, that’s saves me a lot of time.”
I would also say that there’s a lot of value in practicing the method itself. Even though I have a script and a Powershell script that does that automated database restore, I will still sit down and manually restore the database, point in time, because I need to make sure I’m still good. When a fire breaks out…
Carlos: It hits the fan.
Mike: …and everything goes to heck, you’ve got to know what you’re doing. It’s that fine line between automation and practice.
Carlos: There you go. Great advice from Mike Fal. Thanks for that information. We’ve started the conversation, of course we’ll have some additional information at sqldatapartners.com/podcast. If you want to check out Mike’s stuff and some of the scripts that he’ll make available to us. Mike, it is time for what I call the SQL Family portion of the program.
Mike: OK, then.
Carlos: Here, we like to talk more about you, and some of your life experiences. Now, one of the things we’d like to talk about is tools. How people are working, what they’re using. Now, we’ve been talking about PowerShell, and if that’s your favorite tool, that’s cool, but we want to know what your favorite tool is. Free tool, paid tool, why do you use it, and why do you like it?
Mike: Oh, gosh. I think probably my favorite free tool is sp_WhoIsActive.
Carlos: Ta da.
Mike: Adam Mechanic script.
Carlos: Yes, Adam Mechanic
Mike: I will be perfectly honest that I think I would find it hard to troubleshoot problems if I didn’t have it because it’s just so useful for wrapping up information about a problem. When somebody calls me, and says, “Hey, there’s a slow query,” or, “We’ve got a problem,” almost my first command that I execute is sp_WhoIsActive to see blocks, what are the processes waiting on, and it’s all right there.Sp_WhoIsActive is probably my favorite free community tool that I would use.
Carlos: Very cool. Before we go on, I do want to mention our sponsor sqlcruise.com. Now, Mike, are you a cruiser?
Mike: Actually, so I’m glad you brought that up. I went on the last Alaskan Cruise a couple years back.
Carlos: There you go.
Mike: I can’t say enough good things about SQL Cruise, and the kind of amazing opportunity it is. It was career changing for me. What I always like to tell people is one of the instructors was Buck Woody for that particular cruise.Because of the intimacy of the cruise, it’s 20 people. Tim keeps everything small, and you got a lot of good face time with the instructors. I had the opportunity the opportunity to walk around Ketchikan Alaska for three and a half hours with just me, and Buck Woody just talking.
Carlos: Very cool.
Mike: He really opened my eyes to some things with where the industry is going, patterns and practices, things that I should consider out with my career. It did really change my career. I can’t recommend strongly enough for people to consider SQL Cruise as a training opportunity.
Carlos: I agree. Tim and Amy Ford have put together a great training environment, and they’ve offered listeners of the show $100 off the price of admission. Of course, we’ve missed out on the 2016 cruise already, but you can check out sqldatapartners.com/sqlcruise for more details on how to get on board, and how to save that $100, so check it out.Mike, you inherit a million dollars. What are you going to do with it? [laughs]
Mike: Whoa. What am I going to do with a million dollars? Gosh, you know, I’ll be honest. It’s not really a question I’ve given a lot of thought to, because in general, I tend to be, look, if it’s not a possibility, I’m not going to spend a lot of mental cycles on figuring it out.I probably would sit down, and probably try, and start my own SQL server consulting business. I have a lot of admiration for the Paul Randalls and the Brangosars and even yourself, Randy Knight — these guys have started their own boutique SQL consulting firms. That’s one of the exciting things of going to up search — I’m getting to participate in one of these unique little groups.
One, I think these outfits get it. They understand the balance of community involvement and helping people out with highly targeted, highly focused, highly specialized SQL Server consulting skills. I want to give more community members the opportunity to participate in that, because too many of us, we get stuck in the day to day grind. We’re at the corporate job, which we all get tired of.
A million dollars…I’m the kind of person that…I like challenges, I like things put in front of me, so I wouldn’t get the million dollars and say, “Well, I’m going to go…I might spend some time on a beach.” I will confess, I probably would spend a little time just relax…
Carlos: At least the weekend, right?
Mike: Yeah, at least the weekend, but I would get bored so I would want to invest that money in myself to continue to challenge myself and push myself forward. To be perfectly honest, owning and running a business — that’s a pretty steep challenge for me, but one that would be pretty cool, if I could pull it off.
Carlos: There you go. If the numbers come your way, make sure you let the compañeros here at SQL Data Partners know, and we’ll make sure that you get lots of good talent there with you. Our last question, Mike — if you could have one superhero power, what would it be and why do you want it?
Mike: It would be the ability to read other people’s minds. This is because…We’ve all been there, right? We sit down, we sit with — whether they’re our customers, whether they’re our clients, whether they’re our co-workers. One of the things that I…
Carlos: The spouse.
Mike: Spouse. What have you? One of the things I always struggle with is communication. Not personally, as you can tell, I talk a lot, I’m pretty open with that. Somebody will come to you and say, “Hey, I’ve got this problem or this thing’s going on.” We have to sit and tease them and work with our interns.It’s very much an art form to figuring out without being able to read somebody’s mind — what they’re thinking and what they want. If I could read their mind, I’d skip all those steps like, “Got it. All right. I know what you got. I know what you need, I’m going to take care of that for you.” Or, “I know what you’re trying to get at, no confusion, we’ll get it sorted.”
Carlos: Much responsibility, much privilege. Or much privilege, much responsibility there, right? Start reading my mind. I don’t want to hear about my dirty laundry online.
Mike: I will keep that to myself, sir. I promise.
Carlos: Very good. Mike, thanks for being here. I so appreciate your time.
Mike: Thank you, Carlos. This is a great opportunity and I look forward to having more conversations with you over the air, and hopefully at Summit.
Carlos: There you go. Compañeros, if you liked today’s episode I invite you to leave a comment or review on iTunes or Stitcher. Of course, you can go to our website, at sqldatapartners.com/podcast and leave your comments there. This will allow others to find it more easily, so they can enjoy the program as well. Thanks for tuning in, and I hope to see you on the SQL trail.