Powering up your SQL Server DBA skills with PowerShell

simple-talk - Practical PowerShell for SQL Server Developers and DBAs – Part 1

There is a lot of confusion amongst DBAs about using PowerShell due to existence the deprecated SQLPS mini-shell of SSMS and the newer SQLPS module. In a two-part article and wallchart, Michael explains how to install it, what it is, and some of the excellent things it has to offer.

• Introduction
• Configuring Your PowerShell Environment for SQL Server Support
• Executing Queries with Invoke-SqlCmd
  • Default Context
  • Custom Aliases
  • Organizing Your Output

Introduction

PowerShell has a lot to offer to both DBAs and database developers. It is uniquely positioned to be both a shell (for doing things) and a scripting language (for programming) and it excels at both. This is due in large part to having all that has come before—on not just Windows-based systems but also Linux-based ones—as stepping stones to know what has worked well and what not so well.

Arguably the most common task you do with SQL Server Management Studio is to execute queries, with the benefits of interactive query editing windows. From the command line, the equivalent tool is the sqlcmd utility, which lacks the benefits of interactivity but gains the power of scripting. PowerShell provides an adaptation of sqlcmd in the form of the Invoke-Sqlcmd cmdlet, its principal workhorse for query execution. Part 1 covers querying with Invoke-Sqlcmd as well as configuring PowerShell to use it.

Besides providing this and a few other cmdlets, though, PowerShell also adds a new dimension to interacting with your database through its unique ability to navigate through your database hierarchy from the command line. This is loosely analogous to using the object explorer in SQL Server Management Studio. But you will see in Part 2 how this unique interface to databases on top of PowerShell’s native environment can make you very productive indeed. Also see the accompanying wallchart that distills the key details out of both parts into a one-page reference.

...

..."

PowerShell, don't leave your brain without it! I'm telling you (cough... I'm looking in the mirror when I say this too... cough) you need to become comfortable with PowerShell if you are in any kind of Windows Server role, be it database, cloud, Office services, Exchange, AD, Azure, IT support, just about anything. PowerShell IS the way of the future...