My First PowerShell: A Simple Story With Dramatic Flair

Recently, I needed an automated way to restart remote servers in my test environment for SQL Sentry. I knew that this sounded like a PowerShell-ish thing, but I lack what most people call experience when it comes to PowerShell. Maybe that's what everyone calls it?

First Attempt

First, I came across Restart-Computer.

Restart-Computer -ComputerName $ComputerName -Credential $Credential -Force

This seemed to be exactly what I needed.

Next, I opened PowerShell and gave it a try:

PowerShell Restart-Computer with Login Popup Window

As shown above, this prompted for credentials to complete the task. Technically, this was what I wanted. However, I wanted to place Restart-Computer in a loop, therefore the popup asking for credentials wouldn't suffice.

Avoiding Popup for Credentials

I discovered that I could use a secure string for the credentials, however, none of the initial examples I found explained how to create the secure string. Way to make me feel even more like a foolish newbie.  A little more searching and I was on my way.

Creating a Secure String

Read-Host "Enter Stuff" -AsSecureString | ConvertFrom-SecureString | Out-File "C:\PowerShell\mysecurestring.txt"

PowerShell Create a Secure String

Voilà! A secure string is created from a string that totally isn't my password. Seriously, I'm not *that* trusting.

PowerShell the Secure String

Using a Secure String

Now that I've created a secure string, I can use it in a credentials object to avoid the popup:

$username = "mconnors"
$password = Get-Content -Path "C:\PowerShell\mysecurestring.txt" | ConvertTo-SecureString
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $password
$strComputer = "OA-SQL-100"
Restart-Computer -ComputerName $strComputer -Authentication default -Credential $cred

Clearly this is something that looks more loop-friendly:

PowerShell Using the Secure String

Now it was loop-ready and I wrapped it in a simple for-loop.

The PowerShell Loop

$username = "mconnors"
$password = Get-Content -Path "C:\PowerShell\mysecurestring.txt" | ConvertTo-SecureString
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $password
$strComputer = "OA-SQL-"
For ( $intInc = 1; $intInc -le 100; $intInc++ ) {
    $serverNameOrIp = $strComputer + $intInc
    Write-Host $serverNameOrIp
    Restart-Computer -ComputerName $serverNameOrIp -Authentication default -Credential $cred
}

One of the first problems I saw was the computer name was OA-SQL-1 instead of OA-SQL-001. I had a formatting issue to resolve. Padding the zeros into the formatting was easy enough:

$serverNameOrIp = $strComputer + "{0:000}" -f $intInc

Windows PowerShell ISE

Another problem was that pasting the loop into the PowerShell command window and hitting enter didn't look right. It wouldn't run, either. There were '>' for all new lines and it appeared to simply hang when I would execute it. A coworker asked where I was running it and suggested that I use the Windows PowerShell ISE window instead of the more old-school PowerShell command window. I tried that and my loop executed without requiring any changes. Of course, I'd nearly lost my mind at that point…

Output

Once it was working, I made some tweaks to output the computer name and timestamp so I could see the progress. I added a sleep timer so the host machine wouldn't flounder while restarting 100 VMs. I ran into some issues with the SQL Server service timing out on restart across a few random machines. Better to wait a little longer to know they're all safe than discover that during test execution.

As seen above, I was using Write-Host in my script, until I was advised against it. It turns out that Write-Host is kind of bad.

The Solution (so far)

The following script is what I'm executing now for various ranges of servers. It gets the job done. Could it be improved? Probably. I still have much to learn about PowerShell.

$username = "mconnors"
$password = Get-Content -Path "C:\PowerShell\mysecurestring.txt" | ConvertTo-SecureString
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $password
$strComputer = "OA-SQL-"
For ( $intInc = 1; $intInc -le 100; $intInc++ ) {
    $serverNameOrIp = $strComputer + "{0:000}" -f $intInc
    $CurrentDate = Get-Date
    $CurrentDate = $CurrentDate.ToString('hh:mm:ss')
    $restartString = '{0} {1}' -f ($serverNameOrIp, $CurrentDate)
    Write-Output $restartString
    Restart-Computer -ComputerName $serverNameOrIp -Authentication Default -Credential $cred
    Start-Sleep 30
}

What I Learned

What I believed would be a quick foray into a line or two of PowerShell turned into a larger learning experience.

Things I Know Now

• First, to use Windows PowerShell ISE instead of just PowerShell.
• Second, how to store and access a secure string for credentials.
• Formatting output, such as the padded 0s and desired date/time.
• Speaking of output, why I should use Write-Output instead of Write-Host (thanks, Allen! (b|t)
• Finally, PowerShell might *actually* be cool. I've been hearing that for years, but now I've experienced it!

What's Next

So, I have this small amount of PowerShell knowledge now. It's created intrigue for me. I'm sure it can make my life easier and that I can integrate it into Visual Studio Load and Performance Testing to automate a few more steps for me. I'll be sure to share anything interesting in the future. In addition, I recently met Warren Estes at SQL Saturday Boston and heard wonderful things about his presentation, Get-Session "Powershell 101", so now is the perfect time to ask him to speak at Maine PASS!