Why This DBA Learned To Love PowerShell #tsql2sday

This long overdue blog post is part of T-SQL Tuesday, my first time joining in after years of lurking, hosted this month by Rob Sewell of beard and PowerShell fame.

You should learn PowerShell. No seriously, I’m not saying you need to be an expert or anything, but an understanding of the basics will open your eyes (and free up your time) to do more interesting things, like writing more PowerShell scripts! Yes, T-SQL is great, but why limit yourself to just one thing?

Start with a real problem or process you might be doing manually. My own eyes were first opened to PowerShell before I became a DBA; part of my role at the time involved upgrading a web application written in house. After seeing others copy and paste files, clicking in user interfaces, the task was handed to me. The first thing I did was write a list of all the steps involved, then put a checkbox next to them, “Can automate” and presented this to my boss. Most steps had a tick, so he asked me what I was waiting for…

Much of it involved creating folders, copying files, stopping IIS application pools, backing up a SQL database, other fun stuff like that. The resulting script took a few hours to write, but about 4 minutes to run from start to finish. The manual process could take anywhere between 1-2 hours, mistakes not included, so with automation, we could potentially upgrade all the systems in the time it previously took to do a single system. Yikes!

Learning curve aside, it can save a lot of time. It was a pretty steep learning curve at first, but there’s so much out there now to help you. If books aren’t your thing, there’s E-learning free and paid, blog posts galore, official training courses, it’s all out there. There’s never been a better time to learn this.

I wondered if PowerShell would still be useful to me as a DBA. The answer is yes! When I joined my current employer, the other DBA (now a Dev) was very into PowerShell. His enthusiasm for it definitely helped.

Enter the dbatools PowerShell module. Then I found Chrissy LeMaire and the dbatools project. Now I knew what enthusiasm about PowerShell, SQL (and beer) really was 🙂

After Chrissy talked me through my first pull request, I joined the nascent team to help out with Quality Assurance. This involved testing and reading a lot of other people’s code, which accelerated my learning. Some of the worlds foremost PowerShell experts and DBAs were on hand to answer my silly questions.

The fun part? Even the testing for dbatools is automated these days, as Chrissy talked about in her T-SQL Tuesday post today!

Although I’m no longer contributing directly to dbatools, I still use the module and pour through the code on a daily basis. There’s so many interesting techniques at work in that code. With over 200 commands now, there’s almost never a time when something I’m trying to accomplish isn’t already available.

Some of the commands I use the most are Copy-DbaAgentJob, Copy-DbaLogin, Backup-DbaDatabase, Restore-DbaDatabase and the frickin’ rest! If something is missing, the team are very receptive to new ideas and enhancements. There’s no way I could write the stuff you’ll find in dbatools, but can I use that as a starting point to solve my own problems? You bet.

Here’s a very simple script I put together, which requires the dbatools and SqlServer modules. This grabs all of Ola Hallengren’s Maintenance Solution SQL Agent Jobs from every SQL instance in my server estate. Works On My Machine! I wrote this while in the process of replacing a decade worth of Maintenance Plans and bespoke backup scripts with Ola’s single solution. The script output tells me everything I need to know about the status of the jobs, piped to Out-GridView, so I can interact with the information.

My intention is to turn this into a real function, which my non-DBA colleagues can use in my absence to ensure backups and other maintenance tasks are running. Why PowerShell? They’re already using this tool for their own work as sysadmins, so it’s a common language between us. This means they’re also going to be comfortable using dbatools. Holiday here I come? Well not yet, there’s too much work to be done, but I can dream.

At the time it was first written, I don’t think there were many SQL Agent commands in dbatools, besides those intended for use during migrations (no longer the case), which is why it requires the SqlServer module too. I will probably re-write the entire thing using just dbatools. It isn’t perfect, by any means, but it gets the job done, so I move on to other things!

So why do I love PowerShell? Speaking of other things, I have personally used PowerShell for deleting old backups on network shares and in Azure Storage, grabbing data from Active Directory, setting up Windows Server Failover Clusters, Enabling Availability Groups for instances and more.

Colleagues have used it for even more, someone finds a new use for it every day. One extreme example was someone who was previously uninterested in learning new things, who attended a PowerShell course, returning to the office full of ideas. He automated a task he’d been doing for years manually, on hundreds of servers.

The simple reason I love it is the time it saves, you’ve just got to be willing to put the investment into learning.

I’m still learning PowerShell (and T-SQL) every day, it’s just another tool in the toolbox for me. Some things are easier with PowerShell, others are easier with T-SQL. If you’re still arbitrarily limiting yourself to T-SQL, what are you doing? Join us on the PoSh side!

Leave a Reply

Your email address will not be published. Required fields are marked *