Tag Archives: Failover

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
image

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

Param($eventRecordID,$eventChannel)

$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.

Failover SQL Server with Powershell

To failover an SQL mirrored database is fairly easy using SSMS or T-SQL. However, in a DR situation when things are happening quickly and time is of the essence, there is not always the luxury of having the resources available to accommodate failing over a large number of databases. If at all possible it is most desirable to fail over all over your databases while the principal server is still available.

In preparation for our next DR test I developed a Powershell script which would identify all mirrored databases and failover those databases. In the event the principal server is unavailable, the script can also be used for a forced failover on the mirrored server. Our environment consists of mirroring databases on one server to another like server at our DR site. Although you could mirror various databases from one instance to a multitude of instances, we adhere to a one-to-one instance environment. The script assumes this one-to-one placement but it could be easily changed to accommodate a one-to-many or even a many-to-many environment. That’s the beauty of Powershell scripting.

The script is developed on a Windows 7 platform with SQL 2008 R2 Tools. It was tested on Windows Server 2008 and 2008 R2 with SQL 2008 R2 EE. You are free to use the script as is or to modify it as needed. However, I do ask you maintain the attribution to the author


Param(
	[CmdletBinding()]
	[Parameter(Mandatory=$true,Position=0)]
	[string]$PrincipalServer,
	[Parameter(Mandatory=$true,Position=1)]
	[string]$MirrorServer,
	[Parameter(Mandatory=$false,Position=2)]
	[String[]]$Database,
	[Parameter(Mandatory=$false,Position=3)]
	[switch]$Force
)

Function New-SMOconnection {
	[CmdletBinding()]
    Param (
		[Parameter(Mandatory=$true)]
		[string]$server,
		[int]$StatementTimeout=0
	)
	Process {
	If(!(Test-Connection -ComputerName ($server.Split('\')[0]) -Quiet -Count 1)) {
		Throw "Could not connect to Server Machine $($server.Split('\')[0])."
	}
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = $StatementTimeout
	Try {$conn.Connect()}
	Catch {
		Write-Exception $_.Exception
	}
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to $server SQL Instance."
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
	}
}

Function Write-Exception {
	Param($exception)
	Write-Host $exception.Message
	While ($exception.InnerException) {
		$exception = $exception.InnerException
		Write-Host $exception.Message
	}
}

Function Force-FailOver {
Param(
	[CmdletBinding()]
	[Parameter(Mandatory=$true,Position=0,ValueFromPipeLine=$true)]
	$database
)
	Process {
		Try{
			$database.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::ForceFailoverAndAllowDataLoss)
		}
		Catch {
			Write-Exception $_.Exception
			Return "`nUnsuccessful Failover for $($database.Name)"
		}
		Return "Database mirror $($database.Name) is failed over with possible data loss"
	}
}

Function Normal-FailOver {
<#
	Normal failover executes on the Principal database. Once the failover completes,
	we then need to adjust our Mirroring Safety Level back to OFF. We have to switch
	to the Principal database which is now on the mirror server and alter the Saftey
	level there.
#>
Param(
	[CmdletBinding()]
	[Parameter(Mandatory=$true,Position=0)]
	$smo,
	[Parameter(Mandatory=$true,Position=0,ValueFromPipeLine=$true)]
	$database
)
	Process {
		Try{
			$database.MirroringSafetyLevel = [Microsoft.SqlServer.Management.Smo.MirroringSafetyLevel]::Full
			$database.Alter()
			# We have to wait to do the actual failover. The database needs to
			# catch up after changing the safety level and altering the database
			Start-Sleep -Seconds 3
			$database.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::Failover)

			# Switching to server where the Principal now resides
			$db = $smo.Databases[$($Database.Name)]
			$db.MirroringSafetyLevel = [Microsoft.SqlServer.Management.Smo.MirroringSafetyLevel]::Off
			$db.Alter()
		}
		Catch {
			Write-Exception $_.Exception
			Return "`nUnsuccessfull failover for $($database.Name)"
		}
		Return "Database mirror $($database.Name) is failed over"
	}
}
# Initialize process
Set-StrictMode -Version Latest
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")

# Get a connection to the Mirror server
Try {
	$smoMirror = New-SMOconnection -server $MirrorServer
}
Catch {
	Write-Exception $_
	Throw "`nMirror server unavailable. Failover can not be run for $MirrorServer"
}

If($Force -eq $false) {
	# Get a connection to the Principal Server
	Try {
		$smoPrincipal = New-SMOconnection -server $PrincipalServer
	}
	Catch {
		Write-Exception $_.Exception
		Throw "Principal server $PrincipalServer is unavailable. Try running with the -Force parameter"
	}
	# Get a collection of mirrored databases from the principal server
	$databases = @($smoPrincipal.databases | Where-Object {$_.IsMirroringEnabled -and
		$_.Status -eq 'Normal' -and
		$_.MirroringStatus -eq [Microsoft.SqlServer.Management.Smo.MirroringStatus]::Synchronized})
}
Else {
	# Get a collection of mirrored databases from the mirrored server
	$databases = @($smoMirror.databases | Where-Object {$_.IsMirroringEnabled -AND ($_.Status -eq 'Restoring')})
}

# Use the database parameter list (if supplied) to limit the databases to the list
If($database) {
	$databases = @($databases | Where-Object {$database -contains $_.Name})
}

# Use a function to fail over the databases depending on the type of failover
If($Force -eq $true) {
	$databases | Force-FailOver
}
Else {
	$databases | Normal-FailOver -smo $smoMirror
}

If($smoPrincipal) {$smoPrincipal.ConnectionContext.Disconnect()}
$smoMirror.ConnectionContext.Disconnect()
Write-Host "`nScript completed for $($databases.Count) database(s)"