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