Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Mirroring Status Check and Manual Failover PowerShell Scripts


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

Problem

In a previous 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 some PowerShell 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 
db mirroring

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 
manual failover

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 
desktop tools

Script and databases status for a SQL instance acting as MIRROR

.\ManualFailover.ps1 MC0Z5A9C\TEST2 0
high performance

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
executing demands

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.

displaying status after failover

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


next webcast button


next tip button



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools