6 Reasons to Learn PowerShell

 

SQL Server professionals may be reluctant to start using PowerShell.

 

After all, you already know T-SQL well and can do a lot with that to manage SQL Server.

I’ve been using PowerShell to manage SQL Server for the last 5 years and it has revolutionized how I interact with SQL Server. For example, I recently needed to update a SQL Agent job across the entire production environment. That’s almost 100 SQL Servers. I have a Central Management Server with folders for each product in my employer’s portfolio. Then there are folders for Dev, Staging and Prod. I could have manually clicked into the first product family then the prod folder and right clicked that to make a connection to all the SQL Servers in that prod folder. Then I copy paste my script in there to run it against those prod servers I’ve connected to.

 

Then repeat.

 

For every product family in the company.

 

Instead, I save my script as a .sql file. I wrote a PowerShell script to connect to the registered server and I filtered on the folders containing “prod” in the name. This got me a list of SQL Servers I wanted to connect to. Then I loop over that list and run my .sql file against each SQL instance in my list. Once I had that, I executed it and I was done in under 60 seconds! Did you really hear that? I was done in under 60 seconds.

Here’s the code with variable replaced:

 

$SQLInstances = Get-DbaRegisteredServer -SqlInstance MyCentralManagementServer | WHERE {$_.Group -LIKE '*prod*'} | SELECT name

foreach($i in $SQLInstances)
{

Invoke-DbaQuery -SqlInstance $i -File "C:\MyPosHScripts\UpdateJob.sql"
}

 

 

6 Reasons to use PowerShell to automate your interactions with SQL Server

 

Reason #1 Automation saves you time

 

Doing repetitive tasks with a GUI, even SSMS to run scripts, takes more time than calling a routine written in PowerShell. Additionally, PowerShell can easily handle multi-query scenarios where you need to run the same script across multiple SQL Servers and you can do it without manually connecting to servers in your Central Management Server.

 

Reason #2 Automation provides consistency

 

Automation via PowerShell will make everything about your environment more consistent. PowerShell automation creates an easily repeatable process, not only for you, but others who can use the PowerShell you’ve written. This repeatability breeds consistency across your environment, extends the power of your team to do more quicker and everyone will be doing things in the same way.

 

Reason #3 Automation results in reduced and easier troubleshooting

 

By providing the previously mentioned consistency, troubleshooting is made easier because there are fewer “one-off” SQL Server builds and situations. There are few things more frustrating than having a problem on a server because someone did something strange to it when they set it up. Automation eases troubleshooting because your environment looks the same across the board and features are implemented in the same way everywhere.

 

Reason #4 Automation with PowerShell allows you to continue using the T-SQL scripts you’ve already written.

 

The cmdlet Invoke -dbaquery in the DBATools PowerShell module lets you connect to one or more SQL Servers and then call a .sql file to execute a script. No need to be concerned about throwing away years of scripts to make the switch to using PowerShell. Your current collection of scripts is still usable!

Reason #5 Automation with PowerShell is easier to get started with than you think.

 

PowerShell has a built-in help system that enables IT staff to discover commands to use, explains how those commands work, and provides examples that are easy to understand. This help system makes getting started with PowerShell quite easy. Just use  get-help and the command you want to learn about after that, like this:

 

Get-Help invoke-dbaquery -FULL

 

 

Reason # 6 Automation skills are required if you want to advance your career to the Senior level.

 

Employers are embracing automation more and more. The cloud is part of why that’s happening, but even companies who aren’t in the cloud regularly list various automation skills in their job postings. Automation skills are essential if you want your career to take off in a big, fast way.

 

The tl;dr on 6 Reasons to Learn PowerShell

  • Automation saves you time
  • Automation provides consistency
  • Automation results in reduced and easier troubleshooting
  • Automation allows you to continue using the T-SQL scripts you’ve already written.
  • Automation with PowerShell is easier to get started with than you think.
  • Automation skills are required if you want to advance your career to the Senior level.

 

 

Next Steps To Take

 

 

  1. Check out my category on PowerShell. There’s a 3 part tutorial to get you started and numerous other posts where I demonstrate how to leverage PowerShell automation with SQL Server.
  2. If you have questions or comments, leave me a comment on this post, or message me on LinkedIn or Twitter
  3. Check out my Services page and set up a call with me via my Calendly so we can discuss the problems you’re having in your environment and how I can help.
>