Feb 29th, 2012 under SQL Server PowerShell Gist
The following script, meant to run as part of a pre-check for automated reboots, will check that all mirrored databases are in a Synchronized status and the witness is in a Connected status. If the above conditions are true, the script returns a 0 exit code, otherwise, it returns < 0.
See http://msdn.microsoft.com/en-us/library/ms189902.aspx for more on mirroring quorum.
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# Usage
if ($args.Length -ne 3)
{
Write-Host "Usage:"
Write-Host ("{0} sqlinstance1 sqlinstance2 sqlinstance3" -f (Split-Path -Leaf $MyInvocation.InvocationName))
Write-Host "Where:"
Write-Host " sqlinstanceX are all sql servers instances involved (order does not matter)"
Write-Host ""
Write-Host "Full safety w/ automatic failover check"
Write-Host "Returns error code < 0 if any mirrored databases do not have a 'Synchronized' status"
Write-Host " and witness does not have a 'Connected' status"
Write-Host ""
Write-Host "Reference: http://msdn.microsoft.com/en-us/library/ms189902.aspx"
exit 0
}
# Servers to check
$sqlservers = @($args[0], $args[1], $args[2]);
foreach($server in $sqlservers)
{
Try
{
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server;
# Get mirrored databases
$databases = $srv.Databases | Where-Object {$_.IsMirroringEnabled -eq $true};
Write-Host $server;
Write-Host "==================================";
$databases | Select-Object -Property Name, MirroringStatus, MirroringWitnessStatus
# Check for not mirrordb's not synched or witness not being connected
$trouble = $databases | Where-Object {([string]$_.MirroringStatus) -ne "Synchronized" -or ([string]$_.MirroringWitnessStatus) -ne "Connected"}
if ($trouble.Count -gt 0)
{
Write-Host "Witness-to-partner or partner-to-partner quorum detected"
exit -2
}
}
Catch
{
[System.Exception]
Write-Host $_
exit -1
}
}
# All appears to be well
Write-Host "Full quorum detected"
exit 0