SQL Server Database Mirroring Status Check and Manual Failover PowerShell Script...

By:Alejandro Cobar |   Last Updated: 2019-02-06   |  |   Related Tips:More > Database Mirroring

Problem

In aprevious tip, we looked at some T-SQL scripts to provide handy tools to work with SQL Server database mirroring failover . In this tip we will look at somePowerShell scripts that can be used to failover databases that are using database mirroring.  

Solution

The PowerShell code for this tip is at the bottom of the tip.  This script uses Windows authentication to connect to the database servers.  If you want to use SQL Server authentication you will need to modify the script.

I will explain the different ways to execute the PowerShell script and the respective output from each execution.

Parameters the PS handles

There are two parameters that script uses:

• Instance Name: The SQL Server instance to connect to
• Execution Mode : 1 to proceed with the failover of the databases and 0 to view output only. If no parameter is specified for the execution mode, then the behavior is exactly the same as if 0 is specified.

Output for each scenario using the PowerShell Script

When a connection can't be made against the specified SQL Server instance

.\ManualFailover.ps1 MC0Z5A9C\TEST3 0

Script and databases status for a SQL instance acting as PRINCIPAL

• When all the databases are already in HIGH SAFETY mode.
• This won't trigger the failover.

.\ManualFailover.ps1 MC0Z5A9C\TEST1 0

Script and databases status for a SQL instance acting as PRINCIPAL

• When at least 1 database is in HIGH PERFORMANCE mode.
• This won't trigger the failover.

.\ManualFailover.ps1 MC0Z5A9C\TEST1 0

Script and databases status for a SQL instance acting as MIRROR

.\ManualFailover.ps1 MC0Z5A9C\TEST2 0

Performing failover and database status (pre/post) on a SQL instance acting as PRINCIPAL

• This will make sure that all the databases are in HIGH SAFETY mode (if not it will change them) and wait until the databases are in a SYNCHRONIZED state to move forward with the failover.

./ManualFailover.ps1 MC0Z5A9C\TEST1 1

With the final display of the status of the databases within the current SQL Server instance, you can confirm that the roles have successfully swapped after the successful failover execution.

PowerShell Script for Database Mirroring

Here is the complete script.

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!"

Next Steps

• If you want to try this script in your environment, make sure not to trigger the failover until you are 100% sure that it is exactly what you want to do.
• You can customize the output of the status of the databases within the SQL Server instance, remember that the spirit of this tip is to provide you with a tool to makes your life easier, as a DBA.
• You can take a look at a previous tip I posted on Database Mirroring Inventory & Monitoring , to complement your toolset for database mirroring admin tasks.
• You can also take a look at all the Database Mirroring tips , you will probably find several tips here.

Last Updated: 2019-02-06

About the author