Tag Archives: Powershell

PowerShell–Verify AD Principal

I recently needed to add a function which would verify if an Active Directory principal exists. I also did not know if the principal is a Group or a User.

My first attempt was to use the straight forward cmdlets Get-AdGroup and Get-AdUser in an “If” statement block (i. e. If Group is not found then try User).  My script looked like this:

If(!(Get-ADGroup -Identity $Principal)) {
    If(!(Get-ADUser -Identity $Principal)) {
        Throw "AD principal $principal was not found"
Write-Host "AD principal $principal found."

I found out immediately that when Get-AdGroup gets an error it writes an error message and the entire script block (everything under the first IF and including the second IF) gets ignored and the following statement “ Write-Host” gets executed. Because a not found condition is not a terminating error, adding –ErrorAction SilentlyContinue has no effect.

What I ended up doing is adding an -ErrorAction Stop to each cmdlet. That way I could use Try{} Catch{} to trap it at Get-AdGroup and use the trap to then check if the principal is a user using Get-AdUser. The final code looks like this:

Try {Get-ADGroup -Identity $Principal -ErrorAction Stop} 
    Catch { Try {Get-ADUser -Identity $Principal -ErrorAction Stop} Catch { Throw $_ }}

Write-Host "AD principal $principal found."

Know When SQL Server Availability Group Fails Over by Using Powershell

We recently started using HAG (High Availability Groups) in SQL Server and had several unplanned failovers. The failovers occurred for various reason which this article does not present. What was relevant to us was the failovers occurred without our knowledge and in one case, several days passed before we were aware a group had failed over to a secondary instance.

The purpose of this article is to show what was done to make us immediately aware of a failover as it occurs. This is accomplished by identifying an event occurrence associated with a failover and associating with a Windows Event Task. The event task would  then send an email containing the event message to a responsible group or person.

The event which I identified as being most relevant is Event 19406 from the SQL Instance (in this case the default instance of ‘MSSQLSERVER’) as presented here

I already knew how to attach a Windows Task to an event but what I did not know was how the data from the event is passed to the task. I wanted the task to be able to provide the event data in an email. In researching this I came across a great article on this very subject. The article presented all the information I needed including Powershell source code. I had to tweak the source code ever so slightly for my own use. The full article can be found at this link http://blogs.technet.com/b/wincat/archive/2011/08/25/trigger-a-powershell-script-from-a-windows-event.aspx and you should follow it. There is essential information you will need to know to edit and add to the Windows Task. Kudos to OttoHelweg2 for this fine article.

And, here is the source code I used based on the above mentioned article


$xpath = "<QueryList><Query Id='0' Path='$eventChannel'><Select Path='$eventChannel'>*[System[(EventRecordID=$eventRecordID)]]</Select></Query></QueryList>"
$event = Get-WinEvent -LogName $eventChannel -FilterXPath $xpath
$message = $event.Message

Send-MailMessage -From AvailabilityMonitor@myorganization.com -Subject "An SQL Failover is in Progress" `
    -To SQLServerGroup@myorganization.com -Body $message -SmtpServer mailserver.myorganization.com

The $eventRecordId and $eventChannel are passed to the Windows task from the triggering event and are used by Powershell to obtain the actual event record. The “message” data is then used as the Body of a Send-MailMessage cmdlet.

As mentioned, we are trapping the 19406 event. When reviewing the Event list, you will notice the event will fire several times in response to a failover. However, because the event is generated by the same task, the task associated with the event only fires once.

How To: Capture PowerShell Output in Windows Scheduled Tasks

In a previous blog Capture PowerShell Output in Windows Scheduled Tasks I stated the only reliable way I found to capture PS output is by using a CMD or Bat to execute the PS script. After stumbling upon the correct  way to call PowerShell using a –Command variable, I changed my mind. I found the correct way by reading the PowerShell help in PS v3 (imagine that) which says under –Command:

To write a string that runs a Windows PowerShell command, use the format:
“& {<command>}”
where the quotation marks indicate a string and the invoke operator (&)
causes the command to be executed.

So now, I can call PowerShell from Windows Task Scheduler with the correct syntax while still supplying various parms and send the whole command to an output file. The command would look like this:

Powershell.exe -NonInteractive -NoLogo -NoProfile -Command “& {D:\Scripts\SQL-Servers\Backup-SqlServer.ps1 –Server SQLSERVER -Verbose; Return $LASTEXITCODE}” 2>&1> D:\logs\SQL-Servers\Backup_SQLServer.txt”

This will execute my script, return whatever code I emit from the script, and write any output to a file.

SQL Server 2012 Uses PowerShell V2 Even If It Is On Windows Server 2012

Wow! PS version 3 is great! A host of beneficial changes. If you’re like me, you probably wanted to start using it right away to take advantage of some of the new and wonderful changes.

But, HOLD ON! If you are wanting to implement any of those new and upgraded cmdlets in SQL Server 2012, you can’t. Even if you are running on Windows Server 2012, you still can’t use PS 3 cmdlets. Yes, that’s right. The reason is SQL 2012 uses PS Version 2 internally. Check it out. Here is a screen shot of the $PSVersionTable command in SQL Server 2012 on Windows Server 2012 (after right-clicking a database and selecting Start PowerShell):

Here’s a screen shot of $PSVersionTable in Powershell on the same server

So, If you want to run PS 3 for any job steps on SQL 2012, you will need to choose the command type as Operating System (CmdExec) and not PowerShell.