Determining Active Mirrored SQL Database with PowerShell

We run SharePoint farms at both our North and South locations. The content databases are mirrored. The SharePoint farms remain up, with the fallback database set to readonly mode when the site is not primary.

The simplest way to tell where we are running is to figure out which database is active. Here's some simple PowerShell code that can be run on any machine able to reach the SQL server.

The code uses the "IsAccessible" property to find the "active" database. This could be used to stop processing of scripts in the alternate location when it is not primary or any other function that might be needed to control operation. This code only lists the application and the operating location to the host output.


# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# Servers to check
$applications = Import-Csv -Path "mirroredapplications.csv"
foreach ($app in $applications) {
    $sqlservers = @();
    $sqlservers += $app.NorthDBServer
    $sqlservers += $app.SouthDBServer
    Write-Host "APPLICATION:" $app.Application -NoNewline
    foreach($server in $sqlservers) {
        $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server;
# Get mirrored databases
        $database = $srv.Databases | Where-Object {$_.Name -eq $app.DB};
        if ($database.IsAccessible -eq $true) {
            if ($server -eq $app.NorthDBServer) {
                Write-Host ": FOLSOM"
            } else {
                Write-Host ": ALHAMBRA"
            } # endif
        } # endif
    } # foreach
} # foreach

The mirroredapplications.csv file:

Application,NorthDBServer,SouthDBServer,DB
Appl_A,sqlsvrp06,altsqlp06,web_content_A
Appl_B,sqlsvrp02,altsqlp02,web_content_B
appl_C,sqlsvrp11sql,altsqlp11sql,web_content_C