Do I Need to Master PowerShell?

As a SQL Server DBA, do you need to be a master of PowerShell scripts?

In this 9 minute episode, I talk about how much you need to know about PowerShell, and examples of ways I personally use (and struggle with) PowerShell.

This episode is part of TSQLTuesday. This month, our topic is hosted by Rob Sewell. Thanks, Rob!

No time to watch right now or read the transcript below? Listen in podcast format on iTunes , on Google Play, or plug this RSS feed into your favorite podcast app: http://dearsqldba.libsyn.com/rss

Related links

PowerShell fans love to join the SQL Server Slack channel. You can join here!

I mention Adam Machanic’s SQLQueryStress in this episode. The code is now on GitHub, is managed by Erik Ejlskov Jensen, and is open for contributions.

Transcript of this episode

Please forgive errors in grammar and punctuation: robots helped create this transcript.

Welcome to Dear SQL DBA: a podcast and YouTube show for SQL Server developers and database administrators. I’m Kendra Little.

This week’s episode is about PowerShell, and this episode is inspired both by a reader’s question, and by TSQLTuesday. It’s TSQLTuesday for September 2017, and the host this month is Rob Sewell. Rob has challenged people to think about, talk about, and to code a little bit with PowerShell this month.

I also recently got a question from a reader who asked: “As a database administrator, do I need to master PowerShell? How important is it for me to become an expert on PowerShell?”

This is something that a lot of people wonder…

How great do I need to be at scripting as a database administrator?

The amount of PowerShell that you need to know varies, but I do think that these days PowerShell really is a little bit of a default when it comes to basic things like managing files and directories and Windows.

Junior DBAs

Even junior DBAs need to be comfortable with different methods of managing files and folders, because of things like making copies of backups and moving backup files around – managing directories and permissions.

PowerShell is a great tool. When when I first started as a DBA, we just had Ye Olde Command Prompt, and we did DOS scripting for a lot of things. I think the default now is really to use PowerShell.

Getting comfortable with those basics isn’t super hard. You’ve got to be comfortable looking at simple PowerShell scripts as a Junior DBA.

Senior DBAs

As you advance in your career, you do want to build some more skills there but a lot of it– even as a Senior DBA you don’t really need to master PowerShell, as much as you just need to be comfortable searching for scripts, reviewing them, adapting and testing them out safely, and then fitting them to your own purposes.

You don’t have to write a bunch of code from scratch. You don’t have to be necessarily super fluent. You can instead be pretty good at finding good sources, cobbling together code.

There’s lots of folks out there in communities now who you can ask: do you have a snippet of code that I could use to do something? Could you review my PowerShell script for this and let me know if there’s a better way to do this?

There’s the SQL Server Slack community, there’s lots of PowerShell fans in there. There’s PowerShell folks on Twitter that you can ask for tips on things. You don’t have to be great at PowerShell yourself.

What if you LOVE PowerShell?

If you do really like PowerShell, if you start playing with it, and you find that you love scripting in it, and you want to do a lot of it, you can find DBA jobs where you can write a lot of PowerShell and use it to automate installations.

Set up very large environments. Do really cool things in the cloud. Do really cool migrations.

If you’re into the PowerShell and you find you have a talent for it, you really want to master it, then by all means that can fit really well into a career of working with data!

It just isn’t necessarily required: there are a lot of jobs that don’t require writing PowerShell all the time and expanding your knowledge. But dabbling in it is super helpful for many of us.

I have been messing with PowerShell for a while

Back when I was a DBA, I loved automating SQL Server installations. It was fairly early days for PowerShell then, but I did use it as part of the set up scripts for our environment. We had enough SQL Servers that installing and configuring Windows, and installing and configuring SQL Server –sometimes in clusters sometimes not – it was something we did fairly commonly. Scripting as much as I could and using PowerShell in there was fun, and it was really handy.

These days you can do more and more with PowerShell. I had to call out to a lot of more old-fashioned tools to do things like configure the local security policy, and I imagine (I haven’t looked at this lately) that there’s better ways to do that in PowerShell these days. It’s been quite a few years since I did that project!

These days I still use PowerShell. I have been using it recently to generate test load against SQL Server. I wrote a session on finding top queries in SQL Server, and to do a demo of finding top queries you need to generate some activity with queries, right?

One of the cool things about using PowerShell for this – you might just wonder, “why don’t you just write a script that you run in Management Studio to generate the activity?” You can do that, but with PowerShell it acts more like a an application. Management Studio is an application, I get that, but for example with PowerShell, when you’re calling a stored procedure in SQL Server you can say, “hey I’m calling a stored procedure” and be really clear about the fact that you’re not executing ad-hoc SQL. That actually shows up differently in the performance counters in the SQL Server, because the SQL server sees the command and knows immediately, “hey this is a stored procedure! I’m going to go match it.” instead of saying, “oh, this is query text. I see in the query text it’s the stored procedure, I’m gonna go run it.”

You can also easily measure duration in the PowerShell.

It’s really easy to do things like set up reusable code and functions, to say, “okay, we’re going to loop through and run this a certain amount of times.”

There are some things though that I struggle with with PowerShell

It’s really easy to do things like – PowerShell has “start-job”, where you can say, “okay I want to start a few jobs.” This sounds like a great way to be able to run activity on multiple threads at once against your SQL Server. And it works, but the thing I’ve struggled with is it’s not that easy to control and make all the jobs start at the same time.

For lots of demo purposes, I want multiple threads running something at the same time to be able to generate activity in some patterns, and what I’d find with start-job is some of my jobs start, and then some of the other jobs start a little later. Which when it comes to things like automation is probably what you want! You probably don’t necessarily want all these jobs hammering your laptop or wherever you’re running the jobs from at the same time.

But for my purposes it wasn’t that perfect, and it wasn’t that easy to control.

That being said, if I want to run a burst of queries on multiple threads exactly at the same time, I might use another tool for that. There’s a tool called SQLQueryStress which was originally written by Adam Machanic instead of PowerShell. If you search for it, I think someone else has taken over. I think Adam open-sourced it, and someone else has taken control of the code, but for testing and generating demos, there’s other options too.

PowerShell is really flexible, and I love having multiple tools out there!

I also suspect that maybe someday someone will listen to this episode and say, “I’ve got a great way that you can make running those jobs easier, or a different way to do it.”

Because the cool thing about PowerShell is it’s really flexible and a lot of people love using it. It’s always evolving, there’s new cool stuff to do.

Whether you are just getting comfortable with it, whether you’re at the level where you’re finding and testing scripts from other people, or if you are that person who loves to write scripts, I encourage you to share your scripts online and to join the SQL Server community– whether it’s in the SQL Server Slack Channel, or on Twitter, and chat about PowerShell.

Check out TSQLTuesday.com if you want to read all of the posts that people put up for SQL Tuesday on PowerShell. Or maybe you want to join us and participate in TSQLTuesday yourself in a future month?