SQL Server Database Mirroring Status Check and Manual Failover PowerShell Scripts

if ($args[0].Length -gt 0){ 
 
    $server = $args[0]
 
    #Attempt to connect to the SQL Server instance and exit the whole thing if it is not successful
    try{$test = Invoke-Sqlcmd -Query "SELECT 1" -ServerInstance $server -EA SilentlyContinue}
    catch{
        Write-Host "/*"$args[0]"is not a valid instance, please check and try again... */" 
        break
    }
 
    #If the connection to the instance is successful, then attempt to retrieve any mirroring configuration
    Write-Host "/* Checking mirroring status for"$args[0]"*/`n"
    $hasMirroring = Invoke-Sqlcmd -Query "SELECT COUNT(*) AS 'value' FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL" -ServerInstance $server
 
    if ($hasMirroring.value -gt 0){
        $operatingModeFlag = 0
        $operatingModeTSQL = ""
        $failoverTSQL = ""
 
        #Collect all the databases that are in HIGH PERFORMANCE mode and are acting as the Principal
        $highPerformanceDatabasesQuery =" 
        SELECT DB_NAME(database_id) AS [Database]
        FROM sys.database_mirroring
        WHERE mirroring_safety_level = 1 AND mirroring_role_desc = 'PRINCIPAL'
        "
        #Collect all the databases that are already in HIGH SAFETY mode and are acting as the Principal
        $highSafetyDatabasesQuery =" 
        SELECT DB_NAME(database_id) AS [Database]
        FROM sys.database_mirroring
        WHERE mirroring_safety_level = 2 AND mirroring_role_desc = 'PRINCIPAL'
        "
 
        $highPerformanceDatabases = @(Invoke-Sqlcmd -Query $highPerformanceDatabasesQuery -ServerInstance $server)
        $highSafetyDatabases = @(Invoke-Sqlcmd -Query $highSafetyDatabasesQuery -ServerInstance $server)
 
        if ($highPerformanceDatabases.Length -eq 0){
            $operatingModeTSQL = "/* All databases are either in HIGH SAFETY mode or all are acting as the MIRROR */`n"
        }
        else{
            $operatingModeFlag = 1
            $operatingModeTSQL = "/* These databases must be changed to HIGH SAFETY mode */`n"
            foreach ($highPerformanceDatabase in $highPerformanceDatabases){
                $operatingModeTSQL += " ALTER DATABASE "+$highPerformanceDatabase.Database+" SET SAFETY FULL;`n"
             }
        }
 
        if (($highPerformanceDatabases.Length -gt 0) -or ($highSafetyDatabases.Length -gt 0)){
            $failoverTSQL = "/* Once all databases are in HIGH SAFETY mode, the following TSQL can be used to proceed with the failover */`n"
            if($highPerformanceDatabases.Length -gt 0){
               foreach ($highPerformanceDatabase in $highPerformanceDatabases){
                    $failoverTSQL += " ALTER DATABASE "+$highPerformanceDatabase.Database+" SET PARTNER FAILOVER;`n"
                }
            }
            if($highSafetyDatabases.Length -gt 0){
               foreach ($highSafetyDatabase in $highSafetyDatabases){
                    $failoverTSQL += " ALTER DATABASE "+$highSafetyDatabase.Database+" SET PARTNER FAILOVER;`n"
                }
            }
        }
 
        $operatingModeTSQL
        $failoverTSQL
 
        #Show the status of the databases
        $dbStatusQuery = "
        SELECT 
           DB_NAME(database_id) AS 'DB',
           mirroring_role_desc  AS 'Role',
           mirroring_state_desc AS 'State',
           CASE mirroring_role_desc
              WHEN 'MIRROR'    THEN mirroring_partner_instance
              WHEN 'PRINCIPAL' THEN SERVERPROPERTY('SERVERNAME')
           END AS 'Principal Instance',
           CASE mirroring_role_desc
              WHEN 'MIRROR'    THEN SERVERPROPERTY('SERVERNAME')
              WHEN 'PRINCIPAL' THEN mirroring_partner_instance
           END AS 'DR Instance',
           CASE mirroring_safety_level
              WHEN 1 THEN 'HIGH PERFORMANCE'
              WHEN 2 THEN 'HIGH SAFETY'
           END AS 'Operating Mode'
       FROM sys.database_mirroring
       WHERE mirroring_state IS NOT NULL;
       "
        $dbStatus = Invoke-Sqlcmd -Query $dbStatusQuery -ServerInstance $server
 
        Write-Host "Database mirroring information"
        Write-Host "------------------------------"
        $dbStatus 
    }
    else{
        Write-Host "/* Database mirroring is not configured in this SQL Server instance */"
    }
 
}
else{
    Write-Host "/* Please enter an instance as a parameter to proceed */"
}
 
if (($args[1].Length -gt 0) -and ($args[1] -eq 1) -and ($failoverTSQL.Length -gt 0)){
    Write-Host "/* Executing all TSQL commands...*/`n"
    Write-Host "-----------------------------------"
    if($operatingModeFlag -eq 0){
        Write-Host "/* All databases are in HIGH SAFETY mode */`n"
    }
    else{
        Write-Host "/* Changing all qualifying databases to HIGH SAFETY mode */`n"
        Invoke-Sqlcmd -Query $operatingModeTSQL -ServerInstance $server
        Write-Host "/* All databases are now in HIGH SAFETY mode */`n"
        Write-Host "/* Waiting for all the databases to be in SYNCHRONIZED state */`n"
 
        $unsynchedDatabases = 0
        While($unsynchedDatabases -eq 0){
            $unsynchedDatabases = Invoke-Sqlcmd -Query "SELECT COUNT(*) AS 'value' FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL AND mirroring_state <> 4" -ServerInstance $server
            Start-Sleep -s 5
        }
        Write-Host "/* All databases are now in SYNCHRONIZED state */`n"
 
    }
    Write-Host "-----------------------------------"
    Write-Host "/* Performing the failover for all the databases */`n"
    Invoke-Sqlcmd -Query $failoverTSQL -ServerInstance $server
    Write-Host "-----------------------------------"
    Write-Host "/* Displaying the status of the databases after the failover */`n"
    $dbStatus = Invoke-Sqlcmd -Query $dbStatusQuery -ServerInstance $server
    $dbStatus
}
 
Write-Host "Done!"