Chris Golding

As part of administering SQL databases, I have quite a few PowerShell scripts to help automate the management and monitoring of databases and the server.  SQL 2012 introduced Always-On Availabilty Groups.  Always-On allows the creation of Availability Groups which is a logical collection of one or more databases.  The Availability Group allows you to have a database that exists on multiple servers at the same time, which is made up of a single read-write copy of the database (called the primary) and one or more read-only copies of the database (called secondaries).  In addition an Availability Group can be configured to use automatic failover so when the server running the primary becomes unavailable the database can automatically be brought up on one of the secondaries.

This adds a new wrinkle when running scripts because you do not always want to execute against both the primary and secondary copies of the database.  Instead you need to determine if a database is running on the primary and if it is do some action.

In PowerShell this is not a straight forward task, I ended up using SMO to determine if a database is active on the target server.

$serverName = "localhost"		# Hostname of the target server
$Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $serverName

$dbs = $Server.Databases		#Databases on the current server

# Loop Through each database and print if it is in primary
# or secondary. If a database is not in an availability group
# then the database status will not be printed.
$dbs | ForEach-Object {
    $targetAvg = $_.AvailabilityGroupName
    $dbName = $_.Name
    if ( $targetAvg )
    {
        if ( $Server.Name -eq ($Server.AvailabilityGroups | `
             Where-Object { $targetAvg -eq $_.Name }).PrimaryReplicaServerName )
        {
            "{0} is primary" -f $dbName
        }
        else
        {
            "{0} is secondary" -f $dbName
        }
    }
}