Check Mirror And Witness Status Before Rebooting

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